Converting UTC into specific timezone in PostgreSQL

Before I start: if you can, avoid this, transmit your dates in ISO 8601 and only format it at the last moment possible. If you have to do time math, read on.

tl;dr:

select
    table.timestamp::timestamptz at time zone 'America/New_York' as timestamp_et
from table;

The ::timestamptz converts it from timestamp to timestamptz type. If your column is already of this type, you can remove it. Note that timestamptz is horribly misnamed. It does not, as you might expect, store timestamp+timezone, instead it's "timestamp in current timezone" instead of "timestamp in unknown timezone". Why would you ever want to store either of these? Beats me.

The at time zone '...' portion converts it from current timezone into the specified timezone. Again, since postgres does not have a type that stores "timestamp+timezone", the result is timestamp without time zone or timestamp in unknown timezone. You can check that with pg_typeof.

Miscellaneous ramblings

Now that the cookbook-style section is over, here are some miscellaneous things I would very much not like to have to remember, but are unfortunately important.

What is the current timezone? You can find out by running SHOW TIMEZONE;. My database thankfully is in UTC (the Etc/UTC timezone), so I'm safe. If your database is in some other timezone, I'd like to extend my sincere condolences.

Can I change my local timezone? Yes, with SET timezone TO 'Europe/Prague'. What this does, is if you have timestamptz it will get printed in this timezone. Also, if you convert timestamp into timestamptz it'll use this timezone. This is because this conversion does not actually do anything, because timestamptz does not store the timezone at all.

What if my timestamps are in different timezone than the current timezone? Well, then you use timestamp at timezone '...'. Yes, that is the same syntax as converting from timestamptz to timestamp, but does the conversion in other direction. Anyway, the best way here is to just check if the output is what you expect as it depends on the types of your columns.

Warning: Only ever use named timezones. They correctly handle daylight saving times, offset changes and other fun things. Also, ISO 8601 and POSIX don't agree on which direction is positive and which is negative. Posix is positive to west, ISO 8601 is positive to east. Yes, it's cursed.

Final tip: always test your code with positive offsets and negative offsets. Eg. with America/New_York and Europe/Prague. It will reveal bugs where you are just truncating UTC timestamps instead of converting to correct day.

Summary

All in all, it's not too bad as long as you realize that timezone and timezonetz are basically used in the opposite circumstances than you'd expect. The tz variant should be the default one you reach for when handling data in UTC (as long as that is your default). The plain variant is used when you want to convert that into some other timezone (eg. when you want to aggregate by date).