Roy Badami wrote:
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 (AT) candle (DOT) 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 (AT) postgresql (DOT) org

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:
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:
We have links to the spec in the developer's FAQ.

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 (AT) candle (DOT) 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 (AT) gnomon (DOT) org.uk> writes:
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 (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Roy Badami <roy (AT) gnomon (DOT) org.uk> writes:
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

Tom> I think we have to, and the reason is that this isn't
Tom> different under the hood from reading the external value '1
Tom> day 1 hour' and storing it into a column that has the DAY TO
Tom> SECOND typmod.

I don't know anything about the postgres internals, but I don't see it
has to be this way.

INTERVAL '1 day 1 hour' DAY TO SECOND

won't occur in any existing dump file. But if it's important to treat
this the same as casting the string '1 day 1 hour' to type INTERVAL
DAY TO SECOND then yes, you'll have to accept it.

But this is just syntax; I don't see why you have to interpret it that
way...

But on refelction if you want to treat

INTERVAL 'postgres-interval' ansi-interval-type

as equivalent to

CAST (INTERVAL 'postgres-interval' AS INTERVAL ansi-interval-type)

that's probably not unreasonable. Though it creates an inconsistency
with the current (undocumented) postgresism of treating

INTERVAL '1'

as

INTERVAL '1 second'

since clearly you can't treat the ANSI interval

INTERVAL '1' HOUR

as
CAST (INTERVAL '1 second' AS INTERVAL HOUR)



-roy



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster