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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Roy Badami <roy(at)gnomon(dot)org(dot)uk>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1518: Conversions to (undocumented) SQL year-month and
Date: 2005-03-23 05:38:39
Message-ID: 200503230538.j2N5cdb04252@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: 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(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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2005-03-23 05:49:41 Re: BUG #1517: SQL interval syntax is accepted by the parser,
Previous Message Tom Lane 2005-03-23 05:10:20 Re: [GENERAL] contrib module intagg crashing the backend