postgresql - timezone awareness in background threads

[Doug Easterbrook]

when I spoke at euromnis, I had examples of how to make an application timezone aware in postgres.

It is pretty easy:

1) you just change your timestamp fields to ‘timestamp with time zone’ within postgres (no changes to omnis for this part.

2) right after you connect to the database in omnis, issue a

set time zone ‘xxxx’     (Where xxx is any one of the postgres timezones — hundreds of them)



at that time I had a problem   the background threads start their own libpq connection and so I did not know of a way to get proper dates in a timezone aware manner on the sql worker thread.


well,   today I found the solution.    You can change the $selectNames string before issuing the sql and it works perfect.


all you have to do for a timestamp with timezone field is ADD     at time zone ‘xxxx’ after the field in the select, which we did in our table superclass



eg,   you change the select

select  TimeStampField  from myTable


to


select  TimeStampField at time zone ‘xxxx’ from myTable



and now the TimeStampField comes back to omnis in background thread or foreground statement as the proper local time :)


hope this helps some people



Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978