1

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?

1. Option

SELECT to_timestamp(1632610656) at time zone 'UTC' at time zone 'Europe/Berlin'
=> "2021-09-25 20:57:36+00"

and NOT

2. Option

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?

3
  • Not sure what you mean by "removes the time zone". SELECT to_timestamp(1632610656) at time zone 'Europe/Berlin' shows you what the time was in Berlin when there was 1632610656 in the server time zone.
    – mustaccio
    Mar 31, 2022 at 16:56
  • by "removes the time zone" I mean that the result has type "without time zone". What do you mean by server time zone? I have no guarantees about any server configuration, but just know that the number is a unix utc timestamp.
    – Stuck
    Mar 31, 2022 at 19:34
  • "With time zone" really means "with automatic time zone conversion". Consider the absurdity of saying "First convert this to Europe/Berlin, but then convert it to my session timezone before sending me the result". It only makes sense to convert a timestamp to some other timezone if you then stop monkeying around with it. The way you get it to stop monkeying around with it, is to make the result be of type plain timestamp (e.g. without automatic timezone conversion).
    – jjanes
    Mar 31, 2022 at 19:44

1 Answer 1

2

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';
1
  • I think part of my confusion was that it is possible to save a timestamp in a timestampz column.
    – Stuck
    Apr 1, 2022 at 8:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.