CODEWITHSUNDEEP
postgresqltimezoneutc
aspyct
aspyct

Reputation: 3875

Postgres 9.6, timezones: why are those two queries giving me different times

Ok so I've always been fuzzy with the timezones. I'm getting better, but not there yet. Can you people enlighten me?

Context: I receive a UTC time from the frontend. For example, to denote the date 1 September 2019, I'll get 2019-08-31 22:00:00Z (we're in Brussels DST, hence the 2 hours difference).

From that date, I need to generate a series of 6 months before that. So that's March April May June July August.

I managed to find a solution, somewhat out of luck to be honest. I'm still not sure I understand the details of what happens below:

database=> show timezone;
 TimeZone 
----------
 UTC
(1 row)

database=> select generate_series(
        ('2019-08-31 22:00:00Z'::timestamp at time zone 'Europe/Brussels')::date - '6 month'::interval,
        ('2019-08-31 22:00:00Z'::timestamp at time zone 'Europe/Brussels')::date - '1 month'::interval,
        '1 month'::interval
      );
   generate_series   
---------------------
 2019-02-28 00:00:00
 2019-03-28 00:00:00
 2019-04-28 00:00:00
 2019-05-28 00:00:00
 2019-06-28 00:00:00
 2019-07-28 00:00:00
(6 rows)

database=> select generate_series(
        ('2019-08-31 22:00:00Z' at time zone 'Europe/Brussels')::date - '6 month'::interval,
        ('2019-08-31 22:00:00Z' at time zone 'Europe/Brussels')::date - '1 month'::interval,
        '1 month'::interval
      );
   generate_series   
---------------------
 2019-03-01 00:00:00
 2019-04-01 00:00:00
 2019-05-01 00:00:00
 2019-06-01 00:00:00
 2019-07-01 00:00:00
 2019-08-01 00:00:00

Why do I get wrong results if I use ::timestamp?

Upvotes: 2

Views: 30

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

There are actually two different &ldauo;operators” named AT TIME ZONE, one converting timestamp with time zone to timestamp without time zone and one the other way around.

If the first argument is a timestamp with time zone, the value is converted to a timestamp without time zone that shows what a wall clock in that time zone would.

If the first argument is a timestamp without time zone, it is interpreted in the session time zone (given by the value of the timezone parameter) and converted to an absolute time stamp.

Now timestamp with time zone is the preferred type of the date/time type category, so the string literal in your second query is interpreted as a timestamp with time zone in accordance with the documentation. In the first query it is a timestamp without time zone. Since there are different operators involved, it is not surprising that the results are different.

In your first query, the wall clock time 22:00 is interpreted as if the clock hung in Brussels, so it is actually 20:00 UTC. The first argument of generate_series, from which counting starts, is then Feb 28 2019, 20:00 UTC (before casting to date).

Upvotes: 3

Related Questions