Обсуждение: Normalize INTERVAL ouput format in a db driver
Hi all, Just testing 8.4rc2 INTERVALs... 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? Thanks! Seb
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: > Hi all, > > Just testing 8.4rc2 INTERVALs... > > 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? > > Thanks! > Seb > > >
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: > 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: >> Hi all, >> >> Just testing 8.4rc2 INTERVALs... >> >> 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? >> >> Thanks! >> Seb >> >> >> > >