Postgres AT TIME ZONE function shows wrong time?

Question

I am using transformation to new timezone UTC+3 which is equal to EAT timezone, but Postgres (9.1) shows the wrong time

select '2015-01-13 08:40:00.0'::timestamp with time zone AT TIME ZONE 'UTC+03', 
       '2015-01-13 08:40:00.0'::timestamp with time zone AT TIME ZONE 'EAT';

(default timezone is Stockholm here)

The result is

"2015-01-13 04:40:00",
"2015-01-13 10:40:00"

Why?

it should be 2015-01-13 10:40:00

if using JodaTime with both timezones then it shows the same correct result '2015-01-13 10:40:00'.

Caisson

Answer 1

A time zone name spelled like 'UTC+3:00' is a POSIX time zone specification. In this style, zones west of GMT have a positive sign and those east have a negative sign in their name (e.g "Etc/GMT-14" is 14 hours ahead/east of GMT.)

See http://www.postgresql.org/docs/9.3/static/datatype-datetime.html#DATATYPE-TIMEZONES

Intersexual

Answer 2

From the Postgres documentation there is the option to use ::timestamptz instead of ::timestamp WITH TIME ZONE and I found preferred results when making the conversion; as it is the most concise of the available options while still being readable.

SELECT created_at                                              -- raw UTC data
      ,created_at::timestamp AT TIME ZONE 'EDT'                -- yields bad result
      ,created_at::timestamp WITH TIME ZONE AT TIME ZONE 'EDT' -- solution assuming the timestamp is in UTC
      ,created_at AT TIME ZONE 'UTC' AT TIME ZONE 'EDT'        -- solution that allows for explicitly setting the timestamp's time zone
      ,created_at::timestamptz AT TIME ZONE 'EDT'              -- a Postgres specific shorthand for assuming the timestamp is in UTC

---

2019-03-29 18:49:25.250431 -- raw UTC data
2019-03-29 22:49:25.250431 -- erroneous result  
2019-03-29 14:49:25.250431 -- accurate result    
2019-03-29 14:49:25.250431 -- accurate result   
2019-03-29 14:49:25.250431 -- accurate result

Marven

SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/Mexico_City', this gave me the correct result. Thanks brother! – Manville

Answer 3

I had similar problem, it gave me the wrong date and time but this answer here gave me a clear understanding and fixed my problem. PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone

So what I did was changing from

SELECT timestamp AT TIME ZONE '+08' FROM orders;

to

SELECT timestamp AT TIME ZONE 'UTC' AT TIME ZONE '+08' FROM orders;

Revision

Answer 4

EAT(East Africa Time) is three hours ahead of UTC (UTC+03:00).

It's represented as UTC+03:00 in ISO-8601 format.

But AT TIME ZONE only supports timezones represented in POSIX-style.

In POSIX-style, the positive sign is used for zones west of Greenwich. (Note that this is the opposite of the ISO-8601 sign convention used elsewhere in PostgreSQL.)

Format	PST	ART	W←UTC→E	EAT	HKT
ISO-8601	UTC-08	UTC-03	UTC+00	UTC+03	UTC+08
POSIX-style	UTC+08	UTC+03	UTC+00	UTC-03	UTC-08

In POSIX-style, the correct representation of EAT can be:

• 'UTC-03'
• 'UTC-3'
• '-3'
• -3

The result of the following SQL statement must be TRUE.

select now() at time zone -3 = now() at time zone 'EAT';

---

Confusingly, set time zone statement supports ISO-8601, not POSIX-style.

Execute the following SQL statements in sequence to find the difference.

#	SQL	Result
①	set time zone -3;	SET
②	select now();	2022-02-24 04:53:41.921391-03
③	select timestamp '2022-02-24 12:00' at time zone -3;	2022-02-24 06:00:00-03
④	select timestamp '2022-02-24 12:00' at time zone +3;	2022-02-24 12:00:00-03
⑤	set time zone 3;	SET
⑥	select now();	2022-02-24 10:54:10.283953+03
⑦	select timestamp '2022-02-24 12:00' at time zone -3;	2022-02-24 12:00:00+03
⑧	select timestamp '2022-02-24 12:00' at time zone +3;	2022-02-24 18:00:00+03
⑨	set time zone local;	SET
⑩	select now();	2022-02-24 15:54:51.79643+08
⑪	select now() at time zone -3;	2022-02-24 10:55:02.209886
⑫	select now() at time zone 3;	2022-02-24 04:55:09.498461

⚠️Please be careful about this.

---

References:

• AT TIME ZONE
• Time Zones
• POSIX Time Zone Specifications
• SET TIME ZONE

Diarmid