Intervals and ISO 8601 duration

Поиск
Список
Период
Сортировка
От Sebastien Flaesch
Тема Intervals and ISO 8601 duration
Дата
Msg-id AM9P191MB1286AD77B5FBAF4640E7274AB0FD9@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM
обсуждение исходный текст
Ответы Re: Intervals and ISO 8601 duration
Список 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

В списке pgsql-general по дате отправления: