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