CODEWITHSUNDEEP
postgresqlpostgresql-12
kp123154135
kp123154135

Reputation: 5

Query Postgres Timestamp with Timezone wrong result

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

Upvotes: 0

Views: 1878

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247950

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.

Upvotes: 0

Jeremy
Jeremy

Reputation: 6723

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.

Upvotes: 1

Related Questions