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

Answer 1

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

---

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

NOTE: from the documentation about the shorthand

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.

Answer 2

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;