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
– Jeremy, Commented Apr 20, 2021 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
– kp123154135, Commented Apr 20, 2021 at 19:01

Jeremy · Accepted Answer · 2021-04-20 19:25:35Z

1

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.

answered Apr 20, 2021 at 19:25

Jeremy

6,77322 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Laurenz Albe Over a year ago

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 · Accepted Answer · 2021-04-20 20:28:38Z

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, 2021 at 20:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Query Postgres Timestamp with Timezone wrong result

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related