Query Postgres Timestamp with Timezone wrong result

Question

I have a table with a time column defined as

time TIMESTAMP DEFAULT CURRENT_TIMESTAMP

my database and the server have the timezone UTC

Tue Apr 20 18:22:50 UTC 2021

When I ran the following query

SELECT id, time AT TIME ZONE 'utc' AS "TZ: UTC", time AT TIME ZONE 'Europe/Berlin' AS "TZ: Berlin" FROM orders ORDER BY id DESC;

results to

 id  |           TZ: UTC            |          TZ: Berlin
-----+------------------------------+------------------------------
 600 | 2021-04-20 18:18:07.11372+00 | 2021-04-20 16:18:07.11372+00

I would have expected in the TZ: Berlin column is 2021-04-20 20:18:07.11372+00. When I run the query

SELECT id, time AT TIME ZONE 'utc' AS "TZ: UTC", time AT TIME ZONE 'UTC+2' AS "TZ: UTC+2" FROM orders ORDER BY id DESC

which results to the expected

 id  |           TZ: UTC            |          TZ: UTC+2
-----+------------------------------+------------------------------
 600 | 2021-04-20 18:18:07.11372+00 | 2021-04-20 20:18:07.11372+00

At this time of the year (summer) the timezones UTC+2 and Europe/Berlin are identical.

So what am I missing?

Kind regards

PS: The Version is

$ postgres -V postgres
postgres (PostgreSQL) 12.6

When I run

SELECT NOW() at time zone 'Europe/Berlin';

I get

          timezone
----------------------------
 2021-04-20 20:41:57.155641

You should really use `timestamp with time zone` instead of `timestamp`. It will help you avoid these issues. — Jeremy, Apr 20 '21 at 18:52
okay, I have a work-around, I have to change `time AT TIME ZONE `Europe/Berlin` `(time AT TIME ZONE 'utc') AT TIME ZONE 'Europe/Berlin')`. But I would like to understand better what went wrong. — kp123154135, Apr 20 '21 at 19:01

score 1 · Answer 1 · answered Apr 20 '21 at 19:25

Adding AT TIME ZONE to a timestamp without time zone merely adds the time zone to the timestamp. In your case, the time was stored as:

2021-04-20 18:18:07.11372

Adding the UTC time zone didn't change the hour portion of the time because it matches your session time zone.

Adding the Europe/Berlin time zone had the following effect:

2021-04-20 18:18:07.11372 = the time in Berlin, so we subtract two hours to get the time in UTC.

The case of UTC+2 is even more confusing. I suggest reading more about the posix time specifications.

Note this important bit:

The positive sign is used for zones west of Greenwich.

It worked as you expected, but for the wrong reason.

This might make it more clear:

 SELECT t,
       t at time zone 'UTC' as utc,
       t at time zone 'Europe/Berlin' as berlin,
       t at time zone 'UTC+2' as somewhere_west_of_utc,
       t at time zone 'UTC-2' as berlinish
FROM (
  VALUES ('2021-04-20T12:00:00'::timestamp)
) as v(t);
          t          |          utc           |         berlin         | somewhere_west_of_utc  |       berlinish
---------------------+------------------------+------------------------+------------------------+------------------------
 2021-04-20 12:00:00 | 2021-04-20 12:00:00+00 | 2021-04-20 10:00:00+00 | 2021-04-20 14:00:00+00 | 2021-04-20 10:00:00+00
(1 row)

Again, I would strongly advise you to use timestamp with time zone where all the timestamps will be stored as UTC.

I think it is good to use `timestamp without time zone` if the application does time zone conversions. Use `timestamp with time zone` if you want to do time zone conversion in the database. — Laurenz Albe, Apr 20 '21 at 20:30

score 0 · Answer 2 · answered Apr 20 '21 at 20:28

0

If you execute AT TIME ZONE on a timestamp without time zone, you get the absolute time when a clock in that time zone shows that time.

When a clock in Berlin shows 18:00, it is 16:00 UTC.

answered Apr 20 '21 at 20:28

Laurenz Albe

209,280
17
206
263

Query Postgres Timestamp with Timezone wrong result

2 Answers2