postgresql - timezone awareness in background threads

[Doug Easterbrook]

hi Rob.

it is really quite simple to make an app timezone aware..  Dates are pretty much integral to every database app and are what probably cause the most consternation, time and effort to work with.   This is my learnings on making timezone aware app and tricks we use.


CONVERSION OF FIELDS:

this gets you all your fields that are timestamp without time zone

select column_name,table_name,data_Type from information_schema.columns where table_schema = 'public’
and data_type ilike 'timestamp without time zone'
and table_name not in (select table_name from information_schema.views)

then process the list to convert to timestamp with timezone

alter column  column_name type timestamp with time zone



WHAT TIMEZONES CAN BE USED BY POSTGRES

there are currently 590 of them in the list.  use the sql to get this and cache it in your app

select * from pg_timezone_names order by name


READING DATA AND NOT WORRYING

at the start of your app, issue the command that follows, where xxxx is a timezone in the list above.  you only do this ONCE

set time zone ‘xxxxx’



from that point on, all selects within omnis will get the data properly, in your time zone, regardless of how it is stored in the database.  so to answer your first question, just leave the schema in omnis as date/time (any format works) and you don’t have to do anything else.



WRITING DATA AND NOT WORRYING

since you issued the ‘set time zone’ command at the start of your app, then you can calculate your timestamp fields as #D or what ever you want it to be.   You don’t have to change your app in any way.

when you write to the database, your time goes in and postgres automatically adjusts it to a timezone field.   No changes to a table class, no changes to any update statement.   It just works.

if somebody else is using a different timezone and reads back the field, it shows the timestamp as if it were in their time zone.

eg,
— I write the time of 8 pm using the table class and I am in timezone ‘mountain’.
— you set your timezone to eastern and read the field.  it comes out as 10pm  (your time zone)
— you change it to 10:30 pm and write it back (your time zone)
— I read the field and it comes to me as 8:30 pm  (my timezone)

minimal change to the app…  that field is now timezone aware.



TRICKS THAT ARE HELPFUL

Postgres has functions like now() and current_date..   those are all timezone aware and are SERVER based timestamps

so we change SOME of our fields in the table class so that a statement that looks like the following in the @[] notation...

   update date_updated=@[$cinst.date_updated]

becomes, after a substitution

update date_update=now()

you don’t have to do the above … but we do.  why?  because sometimes people do silly things with their local time clock.   this lets us control the timestamp on certain fields to use the time at the server -  but it is automatically converted to our local time zone :).    Beautiful.  no work.  now the user can’t play around with time and get transaction logs out of order, whether on purpose or maliciously.



WHAT IF I WANT TO CHANGE THE TIME ZONE ON A ONE TIME READ OR WRITE


this was my discovery the other day.   If I set my timezone to mountain, then all timestamps are written and read properly.


however, I can over-ride a field for a one time use with the ‘at time zone’ clause.


eg

set timezone to ‘US/Mountain’  (once at the beginning of the app to change the global time zone for the user)


select   date_updated, date updated AT TIME ZONE ‘US/Eastern’ as eastern_time from table


the first column  ‘date_updated’ comes back according to my time zone.   this is what I expect

the second column has an explicit time zone, so that same field comes back two hours later because I told it to use a different time zone.     so, same field, two different values …. both time zone aware..  how cool!!!



HOW DOES THAT AFFECT BACKGROUND THREADS.


well, this was also the thing I discovered yesterday ….      I knew omnis background worker threads for read or write do not handle timezone.  you get one statment in the worker, not two.  so you can’t set the time zone explicitly using ‘set time zone’


but you can add the ‘at time zone’ clause to the sql statement in the select, insert, update and even in where clauses.  that makes it even better.

so you can create a sql statement for the background worker that looks like the following, and all of a sudden your background workers are time zone aware


select  date_updated at time zone ‘US/Mountain’, field2, field3,field4, another_date at time zone ‘US/Mountain' from table



DID I SAY TIME ZONE IN WHERE CLAUSES

absolutely….. the following statement is possible.  its a really silly example .. but it is possible


select date_updated at time zone ‘US/mountain’
  from my table
where date_updated at time zone ‘US/Eastern’ <= other_date at time zone ‘GMT’



FINAL TOUGHT ON DIFFERENCES IN DATES AND INTERVALS


this has nothing really to do with time zones, but we are finding that if we want a range of dates in a where clause, or things older than xx hours orr minutes, we are increasingly using the postgres INTERVAL clause.


eg. for stuff in past 24 hours...

select * from mytable where date_entered between now() and now() - interval '1 day’

or for stuff in past day

select * from mytable where date_entered between current_date and current_date - interval '1 day'


or difference between dates being more than 10 days

select * from mytable where date_entered - current_date > 10



sql does a lot and the more we embrace, the faster the app becomes.





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

> On Nov 17, 2016, at 2:26 AM, Robert Mostyn research <robertmostyn99 at gmail.com> wrote:
> 
> So Doug,
> 
> You’re saying I can set the timezone column on the db as timezone with timestamp and leave my schema class column as datetime,
> and embedding this "set time zone ‘xxxx’ " within the select clause handles everything invisibly to Omnis?
> 
> LIKE !
> 
> What about the insert/update statement?  We have to tell postgres that the timestamp I’m depositing
> with you is from “time zone ‘xxxx’ “ also, yes?
> 
> Rob