interval behaviour

From: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: interval behaviour
Date: 2005-12-01 17:47:03
Message-ID: 1133459218.16010.56.camel@state.g2switchworks.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kelly Burkhart 2005-12-01 17:48:28 Re: default_index_tablespace?
Previous Message Jason Long 2005-12-01 17:42:48 information_schema._pg_keypositions() in 8.1???