A brief reminder about casting TIMESTAMPTZ
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.
There are 2 comments.
YS at 11:39, 5 September 2017:
transaction block is used to get consistent value from function now()
I prefer to issue rollback or abort at the end in such cases, though in your case it doesn’t matter but it will be a cleaner view.
Adrian Klaver at 13:44, 5 September 2017:
To keep the change in timezone from affecting other sessions until you where sure of its effect.