Re: BUG #1518: Conversions to (undocumented) SQL year-month and

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(at)gnomon(dot)org(dot)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(at)postgresql(dot)org
>

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