AT TIME ZONE direction changes based on column having a timezone

Question

Why does Postgres do the opposite thing for TS with and without Timezones? In one case it adds hours, in the other case it subtracts. Just to confuse things more, when specifying GMT+x, the hours are added in the case of TZ-naive and subtracted when the column is TZ-aware, but the opposite is done when using a time zone specified as 'Region/City'

Africa/Johannesburg is ahead of UTC by 2 hours, but when I select the column with AT TIME ZONE 'Africa/Johannesburg' it returns the time as the stored value MINUS 2 hours.

SELECT
   admission_at AT TIME ZONE 'gmt+2' AS gmt_plus_2, 
   admission_at AT TIME ZONE 'Africa/Johannesburg' AS africa_jnb, 
   admission_at AS naieve 
 FROM admissions WHERE admission_at::date = '2018-06-11'
 ORDER BY admission_at LIMIT 3;
       gmt_plus_2       |       africa_jnb       |        naieve        
------------------------+------------------------+---------------------
 2018-06-11 08:00:00+00 | 2018-06-11 04:00:00+00 | 2018-06-11 06:00:00
 2018-06-11 08:00:00+00 | 2018-06-11 04:00:00+00 | 2018-06-11 06:00:00
 2018-06-11 08:00:00+00 | 2018-06-11 04:00:00+00 | 2018-06-11 06:00:00
(3 rows)

As a test I created a table with two columns, one for time with and one without timezone information.

SELECT
 time1,
 time1 AT TIME ZONE 'Africa/Johannesburg' AS naieve,
 time1 AT TIME ZONE 'gmt+2' AS t1_gmt2,
 time2, time2 AT TIME ZONE 'Africa/Johannesburg' with_tz,
 time2 AT TIME ZONE 'gmt+2' AS t2_gmt2 
FROM times;                   

           time1            |            naieve             |            t1_gmt2            |             time2             |          with_tz           |          t2_gmt2           
----------------------------+-------------------------------+-------------------------------+-------------------------------+----------------------------+----------------------------
 2018-06-14 08:47:35.382964 | 2018-06-14 06:47:35.382964+00 | 2018-06-14 10:47:35.382964+00 | 2018-06-14 08:47:35.382964+00 | 2018-06-14 10:47:35.382964 | 2018-06-14 06:47:35.382964
 2018-06-14 08:47:36.468022 | 2018-06-14 06:47:36.468022+00 | 2018-06-14 10:47:36.468022+00 | 2018-06-14 08:47:36.468022+00 | 2018-06-14 10:47:36.468022 | 2018-06-14 06:47:36.468022
 2018-06-14 08:47:37.342908 | 2018-06-14 06:47:37.342908+00 | 2018-06-14 10:47:37.342908+00 | 2018-06-14 08:47:37.342908+00 | 2018-06-14 10:47:37.342908 | 2018-06-14 06:47:37.342908
(3 rows)

The result is exactly the opposite of what I expect. Why would a timestamp stored as naive convert correctly to 'GMT+2' by adding 2, but a TS with timezone do the opposite?

And why would it be the other way around when converting to 'Africa/Johannesburg', where the TZ-naive sees the value returned having 2 hours deducted while the TZ-aware value gets 2 hours added for the same value?

Note: This is NOT a duplicate of "AT TIME ZONE" with zone name PostgreSQL bug? which relates to Time Zone abbreviations, which behaves differently from Time Zone Names.

In this instant regardless whether the name or the GMT+offset format is used, the result is reversed (time added vs time subtracted) depending on whether the column type is a TZ naive or TZ aware timestamp.

Jasen · Accepted Answer · 2018-06-15T10:08:44.293

Yes! it does!

The syntax to convert from timestamp with timezone (AKA timestamptz) to timestamp without timezone (AKA timestamp) is identical to the syntax to do the opposite transformation.

So, depending on which way you are converting the value you will see the printed value change in the corresponding direction.

You have a column called admission_at of type timestamp when you say admission_at AT TIME ZONE 'GMT+2' postgres interprets that to mean treat the values in admission_at as-if they are in the zone 'GMT+2' and produce a result of type timestamptz

If you actually want to treat the admission_at values as GMT timestamps and display the apropriate Johannesburg wall time, convert them first to timestamptz using the GMT rules and then back to timestamp using the Johannesburg` rules.

SELECT
   admission_at AT TIME ZONE 'gmt' AT TIME ZONE 'gmt+2' AS gmt_plus_2, 
   admission_at AT TIME ZONE 'gmt' AT TIME ZONE 'Africa/Johannesburg' AS africa_jnb, 
   admission_at AT TIME ZONE 'gmt' AS gmt 
   admission_at AS naieve 
 FROM admissions WHERE admission_at::date = '2018-06-11'
 ORDER BY admission_at LIMIT 3;

score 1 · Answer 2 · edited Jun 15 '18 at 11:23

I have found half the answer.

Postgres treats GMT backwards. According to the Manual for POSIX timezones, positive values are to the West of GMT.

This can be seen from Postgres by doing:

select * from pg_timezone_names where name like '%GMT+2';                                                                                                                                                                             
   name    | abbrev | utc_offset | is_dst 
-----------+--------+------------+--------
 Etc/GMT+2 | -02    | -02:00:00  | f
(1 row)

So GMT timezones are the opposite of what I would expect.

The other question of why a timestamp with timezone is treated the other way round from a timestamp without remains unanswered.

select now()::timestamp at time zone 'gmt+2';
           timezone            
-------------------------------
 2018-06-14 17:48:27.352746+00
(1 row)

select now()::timestamptz at time zone 'gmt+2';
          timezone          
----------------------------
 2018-06-14 13:48:34.642984
(1 row)

AT TIME ZONE direction changes based on column having a timezone

2 Answers2