I have a unix timestamp in seconds and want to convert it to a timestamp with timezone in postgres.
My confusion originates from the fact that the following does not add but removes the time zone:
SELECT to_timestamp(1632610656) at time zone 'Europe/Berlin' => "2021-09-26 00:57:36"
If I understand it correctly
to_timestamp creates a timestamp with time zone and given this the
at time zone 'Europe/Berlin would then remove the time zone? (this feels so strange to me?!).
If I now want to get the timestamp with time zone in
Europe/Berlin, is it correct that I need to do the following?
SELECT to_timestamp(1632610656) at time zone 'UTC' at time zone 'Europe/Berlin' => "2021-09-25 20:57:36+00"
SELECT to_timestamp(1632610656) at time zone 'Europe/Berlin' at time zone 'Europe/Berlin' => "2021-09-25 22:57:36+00"
They obviously have different results. The first one is the correct one, right?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Different from what your intuition would tell you, a
timestamp with time zone does not have a certain time zone associated. Rather, it is an “absolute timestamp”.
If you want a
timestamp with time zone to be displayed in a certain time zone, you have to set the
timezone parameter in your database session:
SET timezone = 'Europe/Berlin'; SELECT to_timestamp(1632610656);
If you want a
timestamp without time zone that shows what a Berlin clock would show at that time, use
AT TIME ZONE:
SELECT to_timestamp(1632610656) AT TIME ZONE 'Europe/Berlin';
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0