5 September 2017
I spent a few minutes worrying I had lost my mind: Results coming back to my application were different from those obtained by running psql directly on the database server, for identical queries.
As is often the case, the problem was time zones.
In particular, when casting a
TIMESTAMPTZ to a
DATE, the current client time zone setting is used. So:
xof=# BEGIN; BEGIN xof=# set time zone 'UTC'; SET xof=# select now()::date; now ------------ 2017-09-05 (1 row) xof=# set time zone 'Australia/Lord_Howe'; SET xof=# select now()::date; now ------------ 2017-09-06 (1 row) xof=# COMMIT; COMMIT
(Extra credit for knowing why I wrapped this example in a transaction block!)
Just a reminder to be very careful when converting items from time zone-aware formats (like
TIMESTAMPTZ) to non-aware ones (like
DATE). And always store data as a time zone-aware type, unless there is a very compelling reason to do otherwise.