Harry E. Clarke wrote:
That is definately a bug:

test=> SELECT INTERVAL '100' YEAR;
interval
----------
00:00:00
(1 row)

As a work-around until we fix it, please use:

test=> SELECT INTERVAL '100 year';
interval
-----------
100 years
(1 row)

with the 'YEAR' in the quotes.

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:
We don't currently support that style of specifying interval constants,
and you shouldn't hold your breath expecting it to happen --- it will
require a whole bunch of abuse of the currently data-type-independent
processing of literal constants. I don't think anyone's even thought
about it since Tom Lockhart stopped working on that part of the code.
The fact that the syntax is accepted at all is just because he had
done some preliminary work on the grammar, but there's no infrastructure
behind the grammar for handling it.

In short, you need to calibrate your expectations as "feature addition
someday", not "bug fix".

This is the syntax we support.

regards, tom lane

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

Tom Lane wrote:
I did some more research on this item, and updated the TODO item:

o Support ISO INTERVAL syntax if units cannot be determined from
the string, and are supplied after the string

The SQL standard states that the units after the string
specify the units of the string, e.g. INTERVAL '2' MINUTE
should return '00:02:00'. The current behavior has the units
restrict the interval value to the specified unit or unit
range, INTERVAL '70' SECOND returns '00:00:10'.

For syntax that isn't uniquely ISO or PG syntax, like '1' or
'1:30', treat as ISO if there is a range specification clause,
and as PG if there no clause is present, e.g. interpret '1:30'
MINUTE TO SECOND as '1 minute 30 seconds', and interpret
'1:30' as '1 hour, 30 minutes'.

This makes common cases like SELECT INTERVAL '1' MONTH
SQL-standard results. The SQL standard supports a limited
number of unit combinations and doesn't support unit names in
the string. The PostgreSQL syntax is more flexible in the
range of units supported, e.g. PostgreSQL supports '1 year 1
hour', while the SQL standard does not.

I hope this helps.

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

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

Bruce Momjian <pgman (AT) candle (DOT) pha.pa.us> writes:
BTW, I looked at the problem a little bit and concluded that it wouldn't
be so invasive to fix as all that. The weak spot at the moment is that
parse_coerce() passes typmod -1 instead of the specified typmod to the
datatype's input routine when converting an unknown-type literal. It
has to do that to get the right behavior for varchar(N) and char(N)
.... but we could imagine hacking it to behave differently for interval.
At the most grotty,

if (targetTypeId == INTERVALOID)
pass targetTypeMod;
else
pass -1;

but maybe something cleaner could be devised. That would take care of
getting the info to interval_in(), and then the question is what
interval_in() should do with it. Your notes in the TODO entry look
like they summarize previous discussion accurately.

It's worth pointing out that this would also affect data input, eg
COPY into an interval column would interpret '100' differently depending
on how the column had been declared. I think this is OK but it'd need
some consideration.

Actually implementing this is left as a task for someone who feels like
hacking on the datetime code ... I don't particularly ...

regards, tom lane

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

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

Tom Lane wrote:

I think this is a perfect project for Summer of Code.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Alvaro Herrera <alvherre (AT) commandprompt (DOT) com> writes:
That's a thought. Another task in the same area is to fix things so
that timezone abbreviations can be user-configured. Neither one ought
to be a full summer, but maybe both together ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org