The Build

08:51

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 TIMESTAMP or DATE). And always store data as a time zone-aware type, unless there is a very compelling reason to do otherwise.

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.