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

Re: BUG #1517: SQL interval syntax is accepted by the parser,

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Roy Badami <roy(at)gnomon(dot)org(dot)uk>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1517: SQL interval syntax is accepted by the parser,
Date: 2005-03-23 21:49:38
Message-ID: 200503232149.j2NLncq16909@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Roy Badami wrote:
> 
>     Bruce> The reason I added it is that usually people think of the
>     Bruce> PG syntax as '1 hour 30 seconds'.  The '1:30' is a kind of
>     Bruce> subtle because both PG and ANSI support that syntax and we
>     Bruce> need to handle that.  The tricky part is that we can't say
>     Bruce> by looking at '1:30' whether it is PG or ANSI, and that
>     Bruce> will affect how we deal with the clause after it.
> 
> It could be either in ANSI:
> 
>    INTERVAL '1:30' MINUTE TO SECOND
>    INTERVAL '1:30' HOUR TO MINUTE
> 
> Similarly, pg interprets 
> 
>    INTERVAL '1'
> 
> as
> 
>    INTERVAL '1 second'
> 
> In ANSI, it could be any one of
> 
>    INTERVAL '1' SECOND
>    INTERVAL '1' MINUTE
>    INTERVAL '1' HOUR
>    INTERVAL '1' DAY
>    INTERVAL '1' MONTH
>    INTERVAL '1' YEAR
> 
> In ANSI you can only parse the string by reference to the interval type.

This is a good point.  I am thinking we are going to have some backwards
compatibility problems here.  Consider this TODO item:

        o Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 year' AS
          INTERVAL MONTH), and this should return '12 months'

In this, we know that '1 year' is PG syntax, and can do the cast
cleanly.  

However, in the case of '01:02' or '1', we don't know if that is PG
stynax or ANSI syntax.  I think we are going to have to interpret those
as ANSI.   Here is a clearer TODO item.  I changed this:

        o Interpret INTERVAL '1:30' MINUTE TO SECOND as '1 minute 30 seconds'

to this:

        o Interpret syntax that isn't uniquely ANSI or PG, like '1:30' or
          '1' as ANSI syntax, e.g. interpret '1:30' MINUTE TO SECOND as
          '1 minute 30 seconds'

This is going to be a backward compatibility problem, but to support
ANSI syntax we are going to need to do this.

-- 
  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 22:03:58
Subject: Re: BUG #1518: Conversions to (undocumented) SQL year-month and
Previous:From: Oliver JowettDate: 2005-03-23 21:44:19
Subject: Re: Precision and scale of numeric column reported as value

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