The following bug has been logged online:

Bug reference: 1517
Logged by: Roy Badami
Email address: roy@gnomon.org.uk
PostgreSQL version: 8.0.1
Operating system: Solaris 9
Description: SQL interval syntax is accepted by the parser, but the
interpretation is bogus
Details:

The parser accepts SQL interval syntax, but then silently ignores it,
treating it as a zero interval.

radius=# select date '2005-01-01' + interval '1' month;
?column?
---------------------
2005-01-01 00:00:00
(1 row)

radius=# select timestamp '2005-01-1 00:00:00' + interval '1' minute;
?column?
---------------------
2005-01-01 00:00:00
(1 row)

radius=#

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Roy Badami wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1517
> Logged by: Roy Badami
> Email address: roy@gnomon.org.uk
> PostgreSQL version: 8.0.1
> Operating system: Solaris 9
> Description: SQL interval syntax is accepted by the parser, but the
> interpretation is bogus
> Details:
>
> The parser accepts SQL interval syntax, but then silently ignores it,
> treating it as a zero interval.
>
> radius=# select date '2005-01-01' + interval '1' month;
> ?column?
> ---------------------
> 2005-01-01 00:00:00
> (1 row)
>
> radius=# select timestamp '2005-01-1 00:00:00' + interval '1' minute;
> ?column?
> ---------------------
> 2005-01-01 00:00:00
> (1 row)

Well, that certainly belongs in the 'bizarre' category. It should not
accept that syntax. It should require the 'month' or 'minute' to be in
single quotes. This is wrong:

test=> select date '2005-01-01' + interval '1' month;
?column?
---------------------
2005-01-01 00:00:00
(1 row)

This is right:

test=> select date '2005-01-01' + interval '1 month';
?column?
---------------------
2005-02-01 00:00:00
(1 row)

In fact when the 'month' is outside the quotes, it modifies the
'interval', like this:

test=> select date '2005-01-01' + interval '1 year' year to month;
?column?
---------------------
2006-01-01 00:00:00
(1 row)

and in fact the '1' is taken to be 1 second:

test=> select date '2005-01-01' + interval '1';
?column?
---------------------
2005-01-01 00:00:01
(1 row)

So, in fact these work just fine:

test=> select date '2005-01-01' + interval '1' second;
?column?
---------------------
2005-01-01 00:00:01
(1 row)

test=> select date '2005-01-01' + interval '1' hour to second;
?column?
---------------------
2005-01-01 00:00:01
(1 row)

Do we need help in this area? Yes. Where? I don't know.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Well, that certainly belongs in the 'bizarre' category. It should not
> accept that syntax. It should require the 'month' or 'minute' to be in
> single quotes.

No, it shouldn't; read the SQL spec. AFAICS the syntax
select interval '1' month
is perfectly spec-compliant. The variant
select interval '1 month'
is *not* per-spec, it is a Postgres-ism.

Tom Lockhart was working on this stuff shortly before he decided that
raising horses was a more interesting use of his spare time. It doesn't
look like he ever quite finished. I tried several back versions of
Postgres to see if it had ever operated correctly and the answer seems
to be "no" :-( ... although we have managed to fail in more than one
way over the years ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom> No, it shouldn't; read the SQL spec. AFAICS the syntax
Tom> select interval '1' month is perfectly spec-compliant. The
Tom> variant select interval '1 month' is *not* per-spec, it is a
Tom> Postgres-ism.

That is my understanding, though I don't have a copy of the spec (my
reference is Date & Darwen's "A guide to the SQL standard")

However, it may be better if the PostgreSQL parser rejected the
syntax. The current behaviour would seem to be a smoking gun for
people porting ANSI-compliant SQL applications (assuming such things
exist to PostgreSQL.

-roy


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom> AFAICS the syntax
Tom> select interval '1' month
Tom> is perfectly spec-compliant.

Well, it's not _perfectly_ spec compliant, because AIUI SELECTs
without FROM clauses are a postgres-ism, too. But I'm just
nitpicking...

-roy


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Roy Badami wrote:
>
> Tom> No, it shouldn't; read the SQL spec. AFAICS the syntax
> Tom> select interval '1' month is perfectly spec-compliant. The
> Tom> variant select interval '1 month' is *not* per-spec, it is a
> Tom> Postgres-ism.
>
> That is my understanding, though I don't have a copy of the spec (my
> reference is Date & Darwen's "A guide to the SQL standard")

We have links to the spec in the developer's FAQ.

> However, it may be better if the PostgreSQL parser rejected the
> syntax. The current behaviour would seem to be a smoking gun for
> people porting ANSI-compliant SQL applications (assuming such things
> exist to PostgreSQL.

So, we have a few major problems with intervals. Let me think a little
and I will summarize.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bruce> So, we have a few major problems with intervals. Let me
Bruce> think a little and I will summarize.

FWIW, AFAICT the problems I reported in bug 1517 and 1518 all relate
to undocumented features of PostgreSQL.

All the documented interval functionality works fine. The
undocumented support for ANSI SQL interval data types and litereals
doesn't :-/

-roy


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Roy Badami <roy@gnomon.org.uk> writes:
> All the documented interval functionality works fine. The
> undocumented support for ANSI SQL interval data types and litereals
> doesn't :-/

I think the reason it's not documented is precisely that Tom never
finished it. It may not be very far away though --- seeing that the
grammar support exists, I suspect the only missing piece is that
interval_in isn't paying attention to the typmod info, as it should
do to disambiguate input like '1'. Or maybe that support is partially
there but doesn't quite work. Feel like hacking the code?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Tom> Feel like hacking the code?

Hmm, in principle I might take a look some time; in reality it's
unlikely I'll have time any time soon...

There are some design issues involved, though. If you have the type
modifier, do you isnist on SQL syntax in the string?

ie do you accept

interval '1 day 1 hour' day to second

Personally I think it would be a bad idea to allow hybrid SQL/postgres
syntax like this.

IMHO, you should either write

interval '1 day 1 hour'

(postgres style), or

interval '1 1:00:00' day to second

(SQL style.)

Hmm, except writing the above has just raised another question. Is
that what the postgres-ism really means (I think it does) or does it
mean

interval '1 1' day to hour

Once you start distinguishing your interval types, does this become
important? Actually, I can't immediately see a case where it would
matter, but that doesn't mean there isn't one...

-roy


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Roy Badami <roy@gnomon.org.uk> writes:
> ie do you accept
> interval '1 day 1 hour' day to second

I think we have to, and the reason is that this isn't different under
the hood from reading the external value '1 day 1 hour' and storing
it into a column that has the DAY TO SECOND typmod. If we reject
the above we'd be breaking existing dump files. Furthermore this
would imply that dump output from a constrained interval column
would *have to* not have any decoration; ie we could only output
'1 1' and not '1 day 1 hour'. Regardless of what the spec says,
I find the former dangerously ambiguous.

I'm happy to see our code upgraded to accept the spec's syntax.
I won't be happy to see it changed to reject input that we used
to accept, especially when the only argument for doing so is a
narrow-minded insistence that we can't accept anything beyond
what the spec says.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq