Re: INTERVAL data type and libpq - what format?

Yes, good point.

I realize now that I would have expected libpq to give me a way to specify
the exact decoration or precision of INTERVAL parameters...

As you can do with ODBC's SQLBindParameter(), where you specify the C type,
SQL type, precision/scale or length ...
I believe this is important when it comes to data type conversion (for ex,
when you want to insert a numeric/date/time into a char or the other way).
=> sort of cast, actually...

I known libpq functions like PQexecParams() or PQexecPrepared() have a
paramFormats[] parameter to specify if the buffer will hold a string
or the binary representation of the value... but that would not help
much (I don't want to deal with internal structures!).

I can manage to bind only basic INTERVAL values for all sort of INTERVAL
columns, no problem...
I did further tests using the "PnnnYnnnM ..." ISO format and that is
working much better.

However I would expect at least 2 classes of INTERVALs to be specified
in libpq parameters:

INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND(n)

Also: I still have the overflow issue with types like INTERVAL SECOND.
=> discussed in another thread "INTERVAL SECOND limited to 59 seconds?"

Thanks a lot!
Seb

Sam Mason wrote:
> On Tue, May 19, 2009 at 10:08:37AM +0200, Sebastien FLAESCH wrote:
>> I try to use the new 8.4 INTERVAL type with libpq, but get crazy with
>> the input formatting rules...
>
> I think you're giving the database conflicting instructions and it's
> getting confused.
>
>> fprintf(stdout,"++ Preparing INSERT ...\n");
>> paramTypes[0] = 23; /* INT4 */
>> paramTypes[1] = 1186; /* INTERVAL */
>> paramTypes[2] = 1186; /* INTERVAL */
>
> I don't really know 8.4, but I believe you're saying here that you
> explicitly want the values to be of basic INTERVAL type here, i.e. not
> INTERVAL DAY TO HOUR for parameter 3.
>
> Thus when you do:
>
>> paramValues[0] = "1";
>> paramValues[1] = "-12345 years";
>> paramValues[2] = " 123 11:00";
>> r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
>
> It's interpreting " 123 11:00" correctly as a basic INTERVAL value and
> then casting it to your more constrained version as you're saving in the
> table.
>
> However, when you do:
>
>> paramValues[0] = "2";
>> paramValues[1] = "-12345";
>> paramValues[2] = " 123 11";
>> r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
>
> You get an error because " 123 11" isn't a valid literal of an
> (undecorated) INTERVAL type. I think PG may do the right thing if you
> don't specify the types when preparing the query, but haven't tested.
>
>