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

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