Re: BUG #1518: Conversions to (undocumented) SQL year-month and - Mailing list pgsql-bugs

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