>>>>> "Bruce" == Bruce Momjian <pgman@candle.pha.pa.us> writes:
Bruce> First, the fundamental issue with intervals is that they
Bruce> are not tied to a particular date, meaning there is no way
Bruce> to map a specific number of days to a number of months.
Bruce> (Some days are also 23 or 25 hours but that variability
Bruce> seems to be considered acceptable.)
Bruce> This is why the interval data type store both seconds and
Bruce> months.
Indeed. ANSI SQL regards these as two distinct kinds of interval data
types. There are day-time intervals, which store some combination of
days, hours, minutes and seconds, and are broadly equivalent to the
seconds field in PostgreSQL intervals, and there are year-month
intervals, which store some combination of years and months, and are
broadly equivalent to the months field in PostgreSQL intervals.
PostgreSQL instead implements a single hybrid interval data type, that
stores both. This isn't problematic in itself. However, in ANSI SQL
no casts exist between day-time intervals and year-month intervals.
PostgreSQL effectively implements a cast that always returns a zero
interval; it should raise an exception.
Actually, it looks like the underlying problem is more basic than all
this. Here's an example entirely with year-month intervals.
radius=# create table foo (a interval year);
CREATE TABLE
radius=# insert into foo values (interval '1 year 1 month');
INSERT 20947 1
radius=# select * from foo;
a
--------
1 year
(1 row)
I don't have a copy of the spec, but according to "A guide to the SQL
standard" conversions like this that would discard data are supposed
to raise an exception.
Ok, and how about this one, which is far worse:
radius=# create table bar (a interval month);
CREATE TABLE
radius=# insert into bar values (interval '1 year 1 month');
INSERT 20956 1
radius=# select * from bar;
a
-------
1 mon
(1 row)
The ANSI-compliant answer is 13 months, but PostgreSQL returns 1 month!
It seems to me that the ANSI compatible interval stuff should be disabled
by default, since it clearly doesn't work yet :-/
Like the ANSI interval literal issue (bug 1517) this is another
smoking gun for porters...
-roy