Re: INTERVAL data type and libpq - what format?

Tom Lane wrote:
> Ron Mayer <rm_pg@cheapcomplexdevices.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