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:

1xof=# BEGIN;


2BEGIN


3xof=# set time zone 'UTC';


4SET


5xof=# select now()::date;


6    now     


7------------


8 2017-09-05


9(1 row)


10


11xof=# set time zone 'Australia/Lord_Howe';


12SET


13xof=# select now()::date;


14    now     


15------------


16 2017-09-06


17(1 row)


18


19xof=# COMMIT;


20COMMIT

(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.