postgreSQL SQLdb timestamptz

[goodname]

I'm using the stable Lazarus release (0.9.30 FPC 2.6.0 win32). Having trouble using SQLdb data controls to display PostgreSQL "timestamp with time zone" fields. When the result set contains time zone information the control displays the GMT instead of the local time as calculated by the database before going to the client.

Code: [Select]

SELECT tm AS tz, cast(tm AS timestamp without time zone) AS ts FROM tbl;tz  "2011-01-16 14:19:43.203-04:00"
ts  "2011-01-16 14:19:43.203"

Data Controls
tz displays as 2011-01-16 18:19:43
ts displays as 2011-01-16 14:19:43

Want the data control to display the ts value which is the local time. Could just CAST all timezone fields to non-timezone fields but this does not seam like a good solution.

Looking through the pqconnection unit it appears as though the two timestamps types are handled the same way. Line 416, 417
Oid_TimeStamp,  Oid_TimeStampTZ: Result := ftDateTime;

The function TPQConnection.LoadField seams to handle the ftDateTime type on lines 799 to 811. Think this would be the right place to handle time zones but don't know how to go about doing it or if there is a better way of handling this.

[Lacak2]

Yes it is as you wrote.
See bug report:
http://bugs.freepascal.org/view.php?id=18241
"In the future (if there will be users requests), we can add for "timestamp with timezone" shifting from UTC to local time (but it will require more changes into code)"

and

"Committed, but I think this will be a problematic longterm. The timezone situation on *nix is not exactly transparent."
it is because do handling with timezones (and DST) is not so easy when it must be crossplatform.
There is also request about providing such functions:
http://bugs.freepascal.org/view.php?id=17435
until there will be available such support IMO we can not move forward.

So ATM you must use CASTing to nontimezone timestamp.

[goodname]

Thank you Lacak2. Casting is a good fallback but I would like to suggest an approach for a proper fix.

Looks like current attempts have tried to handle time zone using pascal tools. See an eventual use for this if there is demand. The PostgreSQL database already has a lot of time zone handling capability built in so why not try to use them.

From a SQL query it is possible to get the number of seconds to add or subtract to get the local time. Don't see a way to get this information with libpq.

Code: [Select]

SELECT extract(timezone FROM tm) FROM tbl;http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC. (Technically, PostgreSQL uses UT1 because leap seconds are not handled.)

PQfmod may be helpful. Have not been able to find out what extra information if any is returned in the case of timestamp or timestamptz data types. This function is not well documented.
http://www.postgresql.org/docs/9.1/interactive/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO

The interpretation of modifier values is type-specific; they typically indicate precision or size limits. The value -1 is used to indicate "no information available". Most data types do not use modifiers, in which case the value is always -1.

The PQparameterStatus will not be likely to work as I think it returns the zone name and not the offset amount. Each record could have a different offset amount according to day light savings rules for the given zone.
http://www.postgresql.org/docs/9.0/interactive/libpq-status.html

[marcov]

That brings up a good point anyway. With what timezone is such field defined? The client's or the server's? These might not be the same.

[goodname]

PostgreSQL handles time zone confusion by storing "timestamp with time zone" as GMT. The database administrator or client can set time zone preference or let the database guess.

In the case I'm dealing with it is a read only server created timestamptz. Have set the client up to set the timezone output on connection. Was expecting the database to do the conversion from GMT to client local setting before going to the client.

[goodname]

Have figured out what I think is a good approach.

libpq has a function called PQGetf. The TIMESTAMPTZ title is a little over half way down the page.
http://libpqtypes.esilo.com/man3/pqt-specs.html

Using this information was able to add the following code to TPQConnection.LoadField

Code: [Select]

  TTimestamp = record
     date.isbc : SmallInt;
     date.year : SmallInt;
     date.mon : ShortInt;
     date.mday : SmallInt;
     time.hour : ShortInt;
     time.min : ShortInt;
     time.sec : ShortInt;
     time.usec : SmallInt;
     time.gmtoff : SmallInt;
  end;
var
  ts            : ^Ttimestamp;
...
        ftDateTime, ftTime :
          begin
          PQgetf(res,CurTuple,'%pg_catalog.timestamptz',x,ts);
          pint64(buffer)^ := BEtoN(pint64(CurrBuff)^);
          dbl := pointer(buffer)+ts.time.gmtoff;
          if FIntegerDatetimes then dbl^ := pint64(buffer)^/1000000+ts.time.gmtoff;

With the code additions the sqldbLaz package compiles but fails on the object inspector later. Suspect there is a simple fix but don't know what it is. Would like someone with more experience to review the code and approach.

[ludob]

Several problems:

Code: [Select]

  TTimestamp = record
     date.isbc : SmallInt;
     date.year : SmallInt;
     date.mon : ShortInt;
     date.mday : SmallInt;
     time.hour : ShortInt;
     time.min : ShortInt;
     time.sec : ShortInt;
     time.usec : SmallInt;
     time.gmtoff : SmallInt;
  end;
Where did you get this definition from? libpqtypes.h says (http://libpqtypes.esilo.com/browse_source.html?file=libpqtypes.h)

Code: [Select]

257 typedef struct
258 {
259   int isbc;
260   int year;
261   int mon;
262   int mday;
263   int jday;
264   int yday;
265   int wday;
266 } PGdate;
267
268 typedef struct
269 {
270   int hour;
271   int min;
272   int sec;
273   int usec;
274   int withtz;
275   int isdst;
276   int gmtoff;
277   char tzabbr[16];
278 } PGtime;
279
280 typedef struct
281 {
282   PGint8 epoch;
283   PGdate date;
284   PGtime time;
285 } PGtimestamp;
AFAIK PGtimestamp is relatively new and will cause quite some backwards compatibility problems.

Code: [Select]

PQgetf(res,CurTuple,'%pg_catalog.timestamptz',x,ts);How did you define PQgetf? It is one of these functions that accept a variable number of parameters like printf. So your declaration should be something like

Code: [Select]

function PQgetf(...., array of const):longint;cdecl;  and the call should look like

Code: [Select]

PQgetf(res,CurTuple,'%pg_catalog.timestamptz',[x,ts]);

Code: [Select]

dbl := pointer(buffer)+ts.time.gmtoff;dbl is pdouble. You change the pointer while you intend to change the value. You can better change the line

Code: [Select]

          dbl^ := dbl^ / SecsPerDay;with

Code: [Select]

          dbl^ := (dbl^+ts.time.gmtoff) / SecsPerDay;

A solution is to execute the query

Code: [Select]

select now(),cast(now() as timestamp without time zone) calculate the difference between the 2 times and store that as the timezone correction.

Edit: Or even simpler, offset in seconds

Code: [Select]

SELECT extract(timezone FROM now())

[goodname]

Thankyou for the review ludob. I figured there were a lot of problems and was quite surprised to see the code compile.

Several problems:

Code: [Select]

  TTimestamp = record
     date.isbc : SmallInt;
     date.year : SmallInt;
     date.mon : ShortInt;
     date.mday : SmallInt;
     time.hour : ShortInt;
     time.min : ShortInt;
     time.sec : ShortInt;
     time.usec : SmallInt;
     time.gmtoff : SmallInt;
  end;
Where did you get this definition from? libpqtypes.h says (http://libpqtypes.esilo.com/browse_source.html?file=libpqtypes.h)

Code: [Select]

257 typedef struct
258 {
259   int isbc;
260   int year;
261   int mon;
262   int mday;
263   int jday;
264   int yday;
265   int wday;
266 } PGdate;
267
268 typedef struct
269 {
270   int hour;
271   int min;
272   int sec;
273   int usec;
274   int withtz;
275   int isdst;
276   int gmtoff;
277   char tzabbr[16];
278 } PGtime;
279
280 typedef struct
281 {
282   PGint8 epoch;
283   PGdate date;
284   PGtime time;
285 } PGtimestamp;
AFAIK PGtimestamp is relatively new and will cause quite some backwards compatibility problems.

Just made the TTimestamp record using what I thought would be reasonable types. For example min and sec are never going to be over 60. Did not know that PGtimestamp is new since the internals of libpq are new to me as well.

Code: [Select]

PQgetf(res,CurTuple,'%pg_catalog.timestamptz',x,ts);How did you define PQgetf? It is one of these functions that accept a variable number of parameters like printf. So your declaration should be something like

Code: [Select]

function PQgetf(...., array of const):longint;cdecl;  and the call should look like

Code: [Select]

PQgetf(res,CurTuple,'%pg_catalog.timestamptz',[x,ts]);

Code: [Select]

dbl := pointer(buffer)+ts.time.gmtoff;dbl is pdouble. You change the pointer while you intend to change the value. You can better change the line

Code: [Select]

          dbl^ := dbl^ / SecsPerDay;with

Code: [Select]

          dbl^ := (dbl^+ts.time.gmtoff) / SecsPerDay;


Well I suppose the above shows me just how much I have not learned about Pascal yet. First real venture into Pascal pointers and see that I got the symbols confused.

A solution is to execute the query

Code: [Select]

select now(),cast(now() as timestamp without time zone) calculate the difference between the 2 times and store that as the timezone correction.

Edit: Or even simpler, offset in seconds

Code: [Select]

SELECT extract(timezone FROM now())

Using extract to save the offset for the current day will not work as the offset changes according to day light savings rules for the given day. Think that I have gone about as far as I can with attempting a library fix. Will just start using the work around that I have been trying to avoid and cast all timestamptz to timestamp in the select queries.

Thanks

[Lacak2]

AFAIK PQgetf is not a function of libpq C client library, so we can not rely on it 

[ludob]

AFAIK PQgetf is not a function of libpq C client library, so we can not rely on it 

You are right. It is part of the libpqtypes extension http://libpqtypes.esilo.com/

[goodname]

So it looks like PQgetf cannot be used but the documentation for the function leads me to think there may be a pure libpq approach.
http://libpqtypes.esilo.com/man3/pqt-specs.html

When using PQgetf(3) binary mode, the timestamptz value is converted into the local machine's timezone. If the local machine's timezone can not  be determined, the value will be in GMT

If PQGetValue works the same way then providing local machine time zone to libpq should work. Don't know how to do this though.

[ludob]

You can set the local timezone at the session level by adding 'timezone=CEST' to TPQConnection.Params or on a per query basis with

Code: [Select]

select timezone('CEST',tm)

[merlinm]

So it looks like PQgetf cannot be used but the documentation for the function leads me to think there may be a pure libpq approach.
http://libpqtypes.esilo.com/man3/pqt-specs.html

When using PQgetf(3) binary mode, the timestamptz value is converted into the local machine's timezone. If the local machine's timezone can not  be determined, the value will be in GMT

If PQGetValue works the same way then providing local machine time zone to libpq should work. Don't know how to do this though.

sorry to dig up this old thread.   In my opinion, it is ok to use libpqtypes for this purpose.  It was designed specifically to make writing drivers of this sort easier.  Also, it will be much faster than using stock libpq.

Source: libpq author :-D.