Thread: interval behaviour

I seem to have found a rather odd interval bug.

Reading through the sql2003 spec, it would appear that the "proper" way
to represent an interval would be:

interval '10' day

(see pp 144-5, section 5.3 of ISO/IEC 9075-2:2003(E))

Excerpt:

<interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval
qualifier>
<interval string> ::= <quote> <unquoted interval string> <quote>

Note that the qualifier is placed after the quote, and if you're not
sure, here's the excerpt from what an <interval qualifier> is:


<interval qualifier> ::=
    <start field> TO <end field>
  | <single datetime field>

<SNIP>

<single datetime field> ::=
    <non-second primary datetime field>
    [ <left paren> <interval leading field precision> <right paren> ]
  | SECOND [ <left paren> <interval leading field precision>
    [ <comma> <interval fractional seconds precision> ] <right paren> ]

<SNIP part deux>

<non-second primary datetime field> ::=
    YEAR
  | MONTH
  | DAY
  | HOUR
  | MINUTE

So, am I reading this right?

While PostgreSQL happily swallows

interval '10 day'

it is not, in fact, the way the spec says it should be.  That's ok,
since it seems to swallow the proper form just fine.

BUT IT DOESN'T!

Take these two examples: (both in 8.1.0)

test=# select now() + interval '6 month';
           ?column?
-------------------------------
 2006-06-01 11:41:32.017995-05
(1 row)

Looks good!

test=# select now() + interval '6' month;
           ?column?
------------------------------
 2005-12-01 11:41:27.17808-06
(1 row)

It took the arguments, but did not give the proper output.

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> Reading through the sql2003 spec, it would appear that the "proper" way
> to represent an interval would be:

> interval '10' day

This is not actually implemented.  The bizarre special-purpose syntax
the spec requires for datetime literals was something that was on Tom
Lockhart's to-do list, but when he got bored and left the project,
nobody else picked it up.  I'm not entirely certain how much work
remains to handle the spec syntax.  In the real world, the form we
do support is a lot better (can you imagine trying to programmatically
insert interval parameter values into a statement using the spec's
syntax?  Yech.)

            regards, tom lane

On Thu, 2005-12-01 at 12:08, Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
> > Reading through the sql2003 spec, it would appear that the "proper" way
> > to represent an interval would be:
>
> > interval '10' day
>
> This is not actually implemented.  The bizarre special-purpose syntax
> the spec requires for datetime literals was something that was on Tom
> Lockhart's to-do list, but when he got bored and left the project,
> nobody else picked it up.  I'm not entirely certain how much work
> remains to handle the spec syntax.  In the real world, the form we
> do support is a lot better (can you imagine trying to programmatically
> insert interval parameter values into a statement using the spec's
> syntax?  Yech.)

Actually, I too prefer PostgreSQL's syntax.  It's the worrisome
behaviour of swallowing the spec syntax but not giving the right answer
that scares me.  Would it be very hard to have it just return an error
in that case?  Rather an error than the wrong answer.

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> Actually, I too prefer PostgreSQL's syntax.  It's the worrisome
> behaviour of swallowing the spec syntax but not giving the right answer
> that scares me.  Would it be very hard to have it just return an error
> in that case?  Rather an error than the wrong answer.

Yeah, the problem is that Tom had that project about half done :-( ...
I think that the bison grammar has the support but then nothing gets done
with it in parse analysis.  Probably by the time you'd worked out where
to throw the error, you'd know enough to fix it to work properly.

We could instead rip out the grammar support, which wouldn't take any
complicated analysis, but that seems like going backwards ...

            regards, tom lane