Post Closed as "Duplicate of…" by LowlyDBA, John aka hot2use, Philᵀᴹ, McNets, Vérace of
6 Changed the question title
source | link

At Time Zone subtracts instead of adds AT TIME ZONE direction changes based on column having a timezone

EDIT #2: I have found half the answer.

Postgress treats GMT backwards. According to the Manual https://www.postgresql.org/docs/9.5/static/datatype-datetime.html#DATATYPE-TIMEZONES 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 subtracts instead of adds

EDIT #2: I have found half the answer.

Postgress treats GMT backwards. According to the Manual https://www.postgresql.org/docs/9.5/static/datatype-datetime.html#DATATYPE-TIMEZONES 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

5 Half the answer
source | link

EDIT #2: I have found half the answer.

Postgress treats GMT backwards. According to the Manual https://www.postgresql.org/docs/9.5/static/datatype-datetime.html#DATATYPE-TIMEZONES 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)

EDIT #2: I have found half the answer.

Postgress treats GMT backwards. According to the Manual https://www.postgresql.org/docs/9.5/static/datatype-datetime.html#DATATYPE-TIMEZONES 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)
4 Not a duplicate of the issue relating to using Timezone abbreviations
source | link

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.

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.

3 typos corrected
source | link
2 Fixed typoes
source | link
1
source | link