Re: INTERVAL data type and libpq - what format?

Tom Lane wrote:
> Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> writes:
>> Sam Mason wrote:
>>> You get an error because " 123 11" isn't a valid literal of an
>>> (undecorated) INTERVAL type.
>
>> Hmm..... should it be?
>
> Well, we do allow it if it's *explicitly* stated to be a day to hour
> interval:
>
> regression=# select interval '123 11' day to hour;
> interval
> -------------------
> 123 days 11:00:00
> (1 row)
>
> What's at issue here is what should happen without that context.
> I'm inclined to think this is ambiguous enough that accepting it
> silently isn't such a great idea. I'm also not convinced that the
> SQL spec says we must --- the syntax for <interval literal> does
> not appear to allow omitting the fields specification.
>
> In a related example,
>
> regression=# select interval '123 11' day;
> interval
> ----------
> 134 days
> (1 row)
>
> we seem to be adding the 123 and 11 together. This is, um,
> surprising behavior ... I'd be inclined to think throwing an
> error is more appropriate.
>
> regards, tom lane
>

FYI, with Informix, you don't pass a string in interval literals;
You pass digits directly and the interval qualifiers are mandatory:

=======================================================================

> select interval('123 11') from systables where tabid=1;

201: A syntax error has occurred.
Error in line 1
Near character position 27

> select interval(123 11) from systables where tabid=1;

201: A syntax error has occurred.
Error in line 1
Near character position 25

> select interval(123 11) day to hour from systables where tabid=1;

1261: Too many digits in the first field of datetime or interval.
Error in line 1
Near character position 37

> select interval(123 11) day(3) to hour from systables where tabid=1;

(constant)

123 11

1 row(s) retrieved.

=======================================================================

Seb