Normalize INTERVAL ouput format in a db driver

Further, little libpq question:

When using INTERVAL types, can I rely on PQfmod(), PQfsize() to determine
the exact definition of the INTERVAL precision?

=> what YEAR/MONTH/DAY/HOUR/MINUTE/SECOND fields where used to create the column.

I get different values for the type modifier, but how to interpret this?

Can someone point me to the documentation or source code where I can find
more details about this?

I found this:

http://www.postgresql.org/docs/8.4/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO

But there are not much details in PQfmod() description...

Thanks!
Seb

Sebastien FLAESCH wrote:

Sebastien FLAESCH wrote:

According to the doc, INTERVAL output format is controlled by
SET intervalstyle.

I am writing an interface/driver and need a solution to
fetch/convert interval
values independently from the current format settings...

I could force my driver to implicitly set the intervalstyle
to iso_8601, but I
would prefer to leave this in the hands of the programmer...

Imagine you have to write and ODBC interface/driver with
libpq that must support
the SQLINTERVAL C structure, how would you deal with
PostgreSQL intervals?

Is it possible to query the current intervalstyle?

You can use "SHOW intervalstyle" to get the current setting.

Would it be an option to use the to_char(interval, text)
function to convert the interval value to a string you can
understand? That would make you independent of server parameters.

Another way to go is to retrieve the interval values
in binary format. That would make you dependent on the
setting of "integer_datetimes", but it might still be easier.

Yours,
Laurenz Albe

Albe Laurenz wrote:

Sebastien FLAESCH wrote:

According to the doc, INTERVAL output format is controlled by
SET intervalstyle.

I am writing an interface/driver and need a solution to
fetch/convert interval
values independently from the current format settings...

I could force my driver to implicitly set the intervalstyle
to iso_8601, but I
would prefer to leave this in the hands of the programmer...

Imagine you have to write and ODBC interface/driver with
libpq that must support
the SQLINTERVAL C structure, how would you deal with
PostgreSQL intervals?

Is it possible to query the current intervalstyle?

You can use "SHOW intervalstyle" to get the current setting.

Would it be an option to use the to_char(interval, text)
function to convert the interval value to a string you can
understand? That would make you independent of server parameters.

Another way to go is to retrieve the interval values
in binary format. That would make you dependent on the
setting of "integer_datetimes", but it might still be easier.

Yours,
Laurenz Albe

Thanks for the tip, I found SHOW after sending my initial mail.

I will however go by forcing a given intervalstyle in a first
time, this is not critical (I cannot use the internal binary
format).

My main concern now is to describe properly the type of interval
which is used in a SELECT list, with the PQfmod() and PQfsize()
libpq functions, I need some doc/spec here...

Thanks a lot!
Seb

I could manage to identify/describe interval fields by testing the different values
returned by PQfmod().

Could someone confirm that PQfmod() returns will not change in future versions?

I have seen in the docs that there is a deprecated compile-time option that defines
how time, timestamp and intervals are stored (using double or int64), I guess this
should not affect the value returned by PQfmod()... right?

Thanks!
Seb

Sebastien FLAESCH wrote: