2

This question already has an answer here:

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.

marked as duplicate by LowlyDBA, hot2use, Philᵀᴹ, McNets, Vérace Jun 20 '18 at 2:09

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • I'm not using Time Zone Abbreviations anywhere. – Johan Jun 14 '18 at 15:01
  • @Johan in your examples, gmt+2 seems an abbreviation to me. – dezso Jun 15 '18 at 11:22
  • @dezso Firstly the GMT+offset is found in the "name" column and not in the abbrev column of pg_timezone_names system table. Secondly It isn't an abbreviation for a timezone, it is an offset from GMT. Thirdly the problem described here is completely different even at superficial level to the one people seem to think this is a duplicate of. This has absolutely nothing to do with DST. – Johan Jun 15 '18 at 11:33
2

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;
  • see also: Job 1:21 :) – Jasen Jun 15 '18 at 10:15
  • That explains it perfectly, I assume I just missed it in the documentation. Is there a sensible reason why it works like this? – Johan Jun 15 '18 at 11:03
  • It's basically because timestamp doesn't have a time zone until you give it one. once it's got a time zone it then respresents a fixed-point in time and can be rendered in some locale's zone, there's probably something in the SQL standard too. – Jasen Jun 16 '18 at 0:10
1

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)

Not the answer you're looking for? Browse other questions tagged or ask your own question.