Interval literal not ANSI compliant

[Ed Smith]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Tom Lane]

Ed Smith <ed*******@yaho o.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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)