Re: Intervals and ISO 8601 duration

Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com> writes:
> 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)

You can also say just INTERVAL, without any of the restrictions.

> It's not possible to define an INTERVAL YEAR TO SECOND(p), which makes
> sense,

It's not so much that it doesn't make sense as that the SQL standard
doesn't have such a spelling. They enumerate a few allowed combinations
(I think that no-modifiers is one of them), and we accept those for
pro forma syntax compliance.

> 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?

We regard these modifiers as similar to precision restrictions in
numerics and timestamps: we will round off low-order fields to
match the typmod, but we will not throw away high-order fields.

This probably doesn't match the SQL spec in detail, but the
details of their datetime types are sufficiently brain-dead
that we've never worried about that too much (eg, they still
don't have a model for daylight-savings time, last I checked).

What Postgres actually stores for an interval is three fields:
months, days, and microseconds. If we're forced to interconvert
between those units, we use 30 days = 1 month and 24 hours = 1 day,
but it's usually best to avoid doing that.

regards, tom lane