Intervals and ISO 8601 duration

From: Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Intervals and ISO 8601 duration
Date: 2023-01-12 13:40:43
Message-ID: AM9P191MB1286AD77B5FBAF4640E7274AB0FD9@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Sjöblom 2023-01-12 14:56:22 default icu locale for new databases (PG15)
Previous Message Julien Rouhaud 2023-01-12 07:39:33 Re: EXPLAIN and FK references?