The following bug has been logged online:

Bug reference: 1518
Logged by: Roy Badami
Email address: roy@gnomon.org.uk
PostgreSQL version: 8.0.1
Operating system: Solaris 9
Description: Conversions to (undocumented) SQL year-month and
day-time interval types silently discard data
Details:

Conversions to the (undocumented) SQL year-month and day-time intervals
silently discard data, instead of raising an exception.

Note, the following examples intentinally use non-standard interval syntax,
since SQL standard interval syntax appears to be broken...

radius=# create table foo (year_month interval year to month);
CREATE TABLE
radius=# insert into foo values ('1 year 1 month');
INSERT 19963 1
radius=# select * from foo;
year_month
--------------
1 year 1 mon
(1 row)

-- correct

radius=# insert into foo values ('1 hour 1 minute');
INSERT 19964 1

-- should be an error, I think?

radius=# select * from foo;
year_month
--------------
1 year 1 mon
00:00:00
(2 rows)

-- but instead the interval has been replaced by a zero interval

radius=# create table bar (day_time interval day to second);
CREATE TABLE
radius=# insert into bar values ('1 hour 1 minute');
INSERT 19968 1
radius=# select * from bar;
day_time
----------
01:01:00
(1 row)

-- correct

radius=# insert into bar values ('1 year 1 month');
INSERT 19969 1

-- should be an error, I think?

radius=# select * from bar;
day_time
----------
01:01:00
00:00:00
(2 rows)

-- but instead has been converted to a zero interval

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Roy Badami wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1518
> Logged by: Roy Badami
> Email address: roy@gnomon.org.uk
> PostgreSQL version: 8.0.1
> Operating system: Solaris 9
> Description: Conversions to (undocumented) SQL year-month and
> day-time interval types silently discard data
> Details:

I have finally found time to research your issues:

> Conversions to the (undocumented) SQL year-month and day-time intervals

Yes, I noticed that. Once I outline is behavior we need to revisit
that.

> silently discard data, instead of raising an exception.

Yep, noticed that too.

Looking at your examples, it looks terrible, but after researching it,
it isn't too bad, so let me lay out the information and we can decide
how to handle it.

First, the fundamental issue with intervals is that they are not tied to
a particular date, meaning there is no way to map a specific number of
days to a number of months. (Some days are also 23 or 25 hours but that
variability seems to be considered acceptable.)

This is why the interval data type store both seconds and months.

I ran a few tests using constants, which is clearer:

test=> select (current_timestamp - 'epoch'::timestamp)::interval;
interval
-------------------------------
12860 days 19:24:13.854829073
(1 row)

Notice it shows only days and time, not any years or months because it
doesn't actually know how many years or months.

> Note, the following examples intentinally use non-standard interval syntax,
> since SQL standard interval syntax appears to be broken...
>
> radius=# create table foo (year_month interval year to month);
> CREATE TABLE
> radius=# insert into foo values ('1 year 1 month');
> INSERT 19963 1
> radius=# select * from foo;
> year_month
> --------------
> 1 year 1 mon
> (1 row)
>
> -- correct

Should this be "mon" or "month"?

> radius=# insert into foo values ('1 hour 1 minute');
> INSERT 19964 1
>
> -- should be an error, I think?

The problem is that an interval restriction controls storage, but does
not invalidate input. The only good way to do that is with CHECK and
"date_trunc() != val".

In fact, the query below shows that the time information that is outside
the requested range is not even stored:

test=> select (current_timestamp - 'epoch'::timestamp)::interval year to month::interval;
interval
----------
00:00:00
(1 row)

> radius=# select * from foo;
> year_month
> --------------
> 1 year 1 mon
> 00:00:00
> (2 rows)
>
> -- but instead the interval has been replaced by a zero interval

What is confusing here is that instead of printing nothing, it prints a
zero time. Should it print something different, perhaps "0 mons"?

> radius=# create table bar (day_time interval day to second);
> CREATE TABLE
> radius=# insert into bar values ('1 hour 1 minute');
> INSERT 19968 1
> radius=# select * from bar;
> day_time
> ----------
> 01:01:00
> (1 row)
>
> -- correct
>
> radius=# insert into bar values ('1 year 1 month');
> INSERT 19969 1
>
> -- should be an error, I think?
>
> radius=# select * from bar;
> day_time
> ----------
> 01:01:00
> 00:00:00
> (2 rows)
>
> -- but instead has been converted to a zero interval

Again, the interval is zero so it prints zero time.

Does this help?

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Roy> I don't have a copy of the spec, but according to "A guide to
Roy> the SQL standard" conversions like this that would discard
Roy> data are supposed to raise an exception.

Just to clarify, my understanding is that in ANSI SQL it is valid to
convert from the data type INTERVAL YEAR TO MONTH to the data type
INTERVAL YEAR, but the conversion should raise an exception if the
value is not an integral number of years...

-roy


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Sat, 19 Mar 2005, Roy Badami wrote:

> Roy> I don't have a copy of the spec, but according to "A guide to
> Roy> the SQL standard" conversions like this that would discard
> Roy> data are supposed to raise an exception.
>
> Just to clarify, my understanding is that in ANSI SQL it is valid to
> convert from the data type INTERVAL YEAR TO MONTH to the data type
> INTERVAL YEAR, but the conversion should raise an exception if the
> value is not an integral number of years...

Hmm, I'm not entirely sure what the spec says about this. I think the
covering clause in SQL92 is 6.10 (<cast specification>) GR 12d. The error
definition appears to be:


d) If SD is interval and TD and SD have different interval pre-
cisions, then let Q be the least significant <datetime field>
of TD.
i) Let Y be the result of converting SV to a scalar in units Q
according to the natural rules for intervals as defined in the
Gregorian calendar.

ii) Normalize Y to conform to the datetime qualifier "P TO Q"
of TD. If this would result in loss of precision of the
leading datetime field of Y, then an exception condition is
raised: data exception-interval field overflow.

And SQL 99 seems to add a sentence saying "whether to truncate or round in
the least significant field of the result is implementation-defined."

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

OK, here are the TODO items I have created:

* Add support for ANSI time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO SECOND
* Add support for ANSI date INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH
* Process mixed ANSI/PG INTERVAL syntax, and round value to requested precision

Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 year' AS INTERVAL
MONTH), and this should return '12 months'

Is this sufficient?

---------------------------------------------------------------------------

Roy Badami wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1518
> Logged by: Roy Badami
> Email address: roy@gnomon.org.uk
> PostgreSQL version: 8.0.1
> Operating system: Solaris 9
> Description: Conversions to (undocumented) SQL year-month and
> day-time interval types silently discard data
> Details:
>
> Conversions to the (undocumented) SQL year-month and day-time intervals
> silently discard data, instead of raising an exception.
>
> Note, the following examples intentinally use non-standard interval syntax,
> since SQL standard interval syntax appears to be broken...
>
> radius=# create table foo (year_month interval year to month);
> CREATE TABLE
> radius=# insert into foo values ('1 year 1 month');
> INSERT 19963 1
> radius=# select * from foo;
> year_month
> --------------
> 1 year 1 mon
> (1 row)
>
> -- correct
>
> radius=# insert into foo values ('1 hour 1 minute');
> INSERT 19964 1
>
> -- should be an error, I think?
>
> radius=# select * from foo;
> year_month
> --------------
> 1 year 1 mon
> 00:00:00
> (2 rows)
>
> -- but instead the interval has been replaced by a zero interval
>
> radius=# create table bar (day_time interval day to second);
> CREATE TABLE
> radius=# insert into bar values ('1 hour 1 minute');
> INSERT 19968 1
> radius=# select * from bar;
> day_time
> ----------
> 01:01:00
> (1 row)
>
> -- correct
>
> radius=# insert into bar values ('1 year 1 month');
> INSERT 19969 1
>
> -- should be an error, I think?
>
> radius=# select * from bar;
> day_time
> ----------
> 01:01:00
> 00:00:00
> (2 rows)
>
> -- but instead has been converted to a zero interval
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, here are the TODO items I have created:

> * Add support for ANSI time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO SECOND
> * Add support for ANSI date INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH

That example might better read, say,

* Add support for ANSI date INTERVAL syntax, INTERVAL '2005-3' YEAR TO MONTH

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, here are the TODO items I have created:
>
> > * Add support for ANSI time INTERVAL syntax, INTERVAL '1 2:03:04' DAY TO SECOND
> > * Add support for ANSI date INTERVAL syntax, INTERVAL '1-2' YEAR TO MONTH
>
> That example might better read, say,
>
> * Add support for ANSI date INTERVAL syntax, INTERVAL '2005-3' YEAR TO MONTH

Uh, I thought about that, but we are talking about an interval here, not
a year, so 2005 years seems like a strange number of years to span. I
will change it to 9-7 or something so it doesn't match the line above.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> * Add support for ANSI date INTERVAL syntax, INTERVAL '2005-3' YEAR TO MONTH

> Uh, I thought about that, but we are talking about an interval here, not
> a year, so 2005 years seems like a strange number of years to span.

Oh, right. Never mind ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

>>>>> "Bruce" == Bruce Momjian <pgman@candle.pha.pa.us> writes:

Bruce> OK, here are the TODO items I have created:

Bruce> * Add support for ANSI time INTERVAL syntax, INTERVAL '1
Bruce> 2:03:04' DAY TO SECOND

Bruce> * Add support for ANSI date INTERVAL
Bruce> syntax, INTERVAL '1-2' YEAR TO MONTH

You may as well use the correct ANSI terminology:

* Add support for ANSI day-time INTERVAL syntax, INTERVAL '1
2:03:04' DAY TO SECOND

* Add support for ANSI year-month INTERVAL syntax, INTERVAL '1-2'
YEAR TO MONTH

Bruce> * Process mixed ANSI/PG INTERVAL syntax, and round value
Bruce> to requested precision

Never round, I think. Throwing away precision should be an exception,
unless the discarded fields were zero.

Bruce> Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1
Bruce> year' AS INTERVAL MONTH), and this should return '12
Bruce> months'

Bruce> Is this sufficient?

You also need to make EXTRACT do the right thing.

eg EXTRACT (MONTH FROM INTERVAL '1-1' YEAR TO MONTH) => 1

but

EXTRACT (MONTH FROM INTERVAL '13' MONTH) => 13

Ditto for day-time intervals, of course.

I'll have a think about if there's anything else...

-roy

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org