Sebastien Flaesch <sebastien.flaesch@4js.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