Re: date_trunc() in a specific time zone

>>>>> "Paul" == Paul A Jungwirth <pj(at)illuminatedcomputing(dot)com> writes:

Paul> Thinking about this some more, perhaps the timestamp-to-timestamp
Paul> version would depend on assumptions that aren't always valid. In
Paul> my world the server timezone is always UTC, and the database
Paul> clients always convert values to UTC before saving. But if those
Paul> assumptions don't hold the simple implementation of 2x AT TIME
Paul> ZONE might not work correctly. I can try it out and see....

There's a reason we tell people not to do this (i.e. not to store UTC
values in timestamp w/o tz columns) and to use timestamptz instead.
I should probably add an even more explicit entry to expand on
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

If you have a timestamp-in-UTC column and want to do a date_trunc in
some other specified zone (that's not the session timezone), you need
FOUR uses of AT TIME ZONE to do it correctly:

date_trunc('day', col AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kathmandu')
AT TIME ZONE 'Asia/Kathmandu' AT TIME ZONE 'UTC'

You can replace two of them with casts, but only at the cost of assuming
that the session timezone really is set to UTC, so there's no real gain.

With a date_trunc(text, timestamptz, text) function, the above could be
simplified to:

date_trunc('day', col AT TIME ZONE 'UTC', 'Asia/Kathmandu')
AT TIME ZONE 'UTC'

--
Andrew (irc:RhodiumToad)