Thread: Interval literal not ANSI compliant

The Postgres INTERVAL literal is not compliant with
the ANSI 2003 SQL Spec.  Here's the Postgres way:

# select INTERVAL '45 DAY';
 interval
----------
 45 days
(1 row)

The spec. says

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

<interval string> ::= <quote> <unquoted interval
string> <quote>

Note specifically that the quotes only enclose the
number, not the interval qualifier.  So Postgres
interval literals should be

# select INTERVAL '45' DAY;
 interval
----------
 00:00:00
(1 row)

Note that Postgres accepts the ANSI form but then
interprets it completely incorrectly.  IMHO this is
much worse than rejecting the ANSI form.  Are there
plans to fix this?  I am using 7.4.3.



_______________________________
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
http://promotions.yahoo.com/goldrush

Ed Smith <edsmithed@yahoo.com> writes:
> The Postgres INTERVAL literal is not compliant with
> the ANSI 2003 SQL Spec.

Yup.

> Are there plans to fix this?

Step right up and have at it.  Tom Lockhart was working on migrating the
datetime support to be more like the (IMHO quite bizarre) spec syntax,
but he lost interest and dropped out of the project awhile back.
I don't think any of the rest of the current developers care much about
this point.  But we'd accept a patch, as long as it was reasonably
cleanly coded (ie, supportable into the future).

The interval datatype needs love in other ways --- for instance, in my
opinion it really ought to store months/days/seconds internally not
just months/seconds, so as to avoid surprising behavior at DST
transitions.  (The existing representation effectively assumes that a
day is always the same number of seconds, which is wrong on DST
transition days.)  But this area is not high on the list of interests
of any active PG developers.  We need somebody to take ownership of the
problem.

            regards, tom lane