PostgreSQL has the INTERVAL type, which can be defined with fields such as:
INTERVAL YEAR TO MONTH (year-month class)
INTERVAL DAY TO SECOND(p) (day-second class)
It's not possible to define an INTERVAL YEAR TO SECOND(p), which makes sense, since the number of days in a month can vary. Other SQL engines like Oracle and Informix also have 2 classes of interval types.
However, the ISO-8601 standard format for durations allows to specify year/month with day to second parts, for example:
P2Y10M15DT10H30M20S
Seems PostgreSQL accepts this format in input.
But what does it mean exactly?
What is the actual INTERVAL value and INTERVAL class of this?
Testing with V15.1:
What is the interval class in this case:
test1=> select cast('P2Y10M15DT10H30M20S' as interval);
interval ----------------------------------
2 years 10 mons 15 days 10:30:20
(1 row)
Should the following convert to a day-second interval?
test1=> select cast('P2Y10M15DT10H30M20S' as interval day to second); interval
----------------------------------
2 years 10 mons 15 days 10:30:20
(1 row)
Should PostgreSQL not raise an SQL error in above cases?
When using invalid INTERVAL fields, error is raised as expected:
test1=> select cast('P2Y10M15DT10H30M20S' as interval year to second);ERROR: syntax error at or near "second"
LINE 1: ...lect cast('P2Y10M15DT10H30M20S' as interval year to second);
Does PostgreSQL assume that a month is ~30 days?
I did not find details about this in the documentation.
Thanks in advance!
Seb
