Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group