CODEWITHSUNDEEP
postgresqltimezonejodatime
kadkaz
kadkaz

Reputation: 153

Postgres AT TIME ZONE function shows wrong time?

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'.

Upvotes: 7

Views: 11944

Answers (4)

SMAG
SMAG

Reputation: 788

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

Upvotes: 10

jqgsninimo
jqgsninimo

Reputation: 7028

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:

Upvotes: 1

GameBoy
GameBoy

Reputation: 119

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;

Upvotes: 4

Andrej Urvantsev
Andrej Urvantsev

Reputation: 461

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

Upvotes: 2

Related Questions