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.

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;

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

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.

⚠️Please be careful about this.

References:

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

Most voted answer is wrong with:

created_at::timestamptz AT TIME ZONE 'EDT' -- a Postgres specific shorthand for assuming the timestamp is in UTC

This is not the case. This shorthand is for assuming that the timestamp is in session's timezone (or in timezone that is specified in postgres config).

Rule of thumb: When you see ::timestamp or ::timestamptz or created_at::timestamp WITH TIME ZONE in SQL query to do conversion, it will propably give different results depending on your postgres timezone configuration and session variables:

• When you do timezoneTZ -> timezone conversion using ::timestamp it will save date in the session's timezone, not the UTC one!

• When you do timezone -> timezoneTZ conversion, postgress will asume that the value was stored in session's timezone, not the UTC one.

Remember that, timezone-aware field (when properly converted) always stores data in UTC and shows/computes it in the session's timezone:

set time zone "Europe/Warsaw";
SELECT extract(hour from ('2025-04-03T23:00:00' AT TIME ZONE 'UTC'));  --> 21

SELECT ('2025-04-03T23:00:00' AT TIME ZONE 'UTC')::timestamp;  --> 2025-04-03 21:00:00.000000

set time zone "Asia/Tokyo";
SELECT extract(hour from ('2025-04-03T23:00:00' AT TIME ZONE 'UTC'));  --> 14

SELECT ('2025-04-03T23:00:00' AT TIME ZONE 'UTC')::timestamp;  --> 2025-04-03 14:00:00.000000

How to work with timezones fields in Postgres?

a) when field is stored as timestamp [without timezone]

Read this section if you use timestamp field to store UTC dates like this:

WITH posts AS (
    SELECT '2025-04-03T23:00:00'::timestamp as created_at   -- 2025-04-03 23:00:00.000000
)

How to convert it to timezone-aware field?

We will run an experiment:

SELECT
    created_at,  -- 2025-04-03 23:00:00.000000
    created_at AT TIME ZONE 'UTC',  -- 2025-04-03 23:00:00.000000 +00:00
    created_at::timestamptz,  -- 2025-04-03 23:00:00.000000 +00:00
FROM posts;

Hmm... Both ways seems to work the same, but let's see what happens when I switch session timezone:

set time zone "Europe/Warsaw";

SELECT
    created_at,  -- 2025-04-03 23:00:00.000000
    created_at AT TIME ZONE 'UTC',  -- 2025-04-04 01:00:00.000000 +02:00 (good)
    created_at::timestamptz,  -- 2025-04-03 23:00:00.000000 +02:00 (bad)
FROM posts;

We can draw conlustions from this that:

• ✅ created_at AT TIME ZONE 'UTC' returns the same date as created_at and formats it based on session's timezone
• ❎ created_at::timestamptz assumes the data is stored in session's timezone (this is 99% wrong in most use-cases)

So, if we want timezone-aware field we should go with created_at AT TIME ZONE 'UTC'.

But, remember! Timezone-aware field always stores data in UTC and shows/computes it in the session's timezone:

set time zone "Europe/Warsaw";
SELECT extract(hour from ('2025-04-03T23:00:00' AT TIME ZONE 'UTC'));  --> 21

set time zone "Asia/Tokyo";
SELECT extract(hour from ('2025-04-03T23:00:00' AT TIME ZONE 'UTC'));  --> 14

So if you want predictable calculations, convert back to timestamp without timezone field as soon as possible. It's simple - you have to specify your target timezone as shown in section below.

How to display date in custom timezone?

You have to split this process to two steps:

1. AT TIME ZONE 'UTC' to specify timezone in witch the data is stored
2. AT TIME ZONE 'Europe/Warsaw' to specify in witch timezone we want the output

So the final query will be:

SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Warsaw' FROM posts;

If you are curious, this is a table comparing other (bad) solutions with this one:

b) when field is stored as timestamptz / timestamp with timezone

Timestamp with timezone always stores data in UTC (if you specify other timezone in the INSERT it does automatic convertion), so:

• You can do 1:1 conversion to timezone-less field using: AT TIME ZONE 'UTC'.
• If you want to do timezone converstion, just specify target timezone.

WITH posts AS (
    SELECT '2025-04-03T23:00:00+00:00'::timestamptz as created_at   -- 2025-04-03 23:00:00.000000 +00:00
)
SELECT
    created_at,  -- Base value: 2025-04-03 23:00:00.000000 +00:00
    created_at AT TIME ZONE 'UTC',  -- Value without TZ: 2025-04-03 23:00:00.000000
    created_at AT TIME ZONE 'Europe/Warsaw';  -- Time in warsaw: 2025-04-03 23:00:00.000000

Riddle to check your knowledge

What type of field and what value will this SQL return:

SELECT '2025-04-03T23:00:00+00:00'::timestamptz AT TIME ZONE 'Europe/Warsaw' AT TIME ZONE 'Europe/Warsaw';

'2025-04-03T23:00:00+00:00'::timestamptz AT TIME ZONE 'Europe/Warsaw' AT TIME ZONE 'Europe/Warsaw' is the same as 2025-04-03T23:00:00+00:00'::timestamptz. As the first part of the query converts timezone-aware time to Warsaw one and then we reverse this process because we refine the pure date with the time zone.

What this query does?

SELECT '2025-04-03T23:00:00'::timestamp AT TIME ZONE 'Europe/Warsaw' AT TIME ZONE 'Asia/Tokyo';

This query converts Warsaw local time 2025-04-03 23:00:00 to Tokoy's one.

What this query does?

SELECT '2025-04-03T23:00:00+00:00'::timestamptz AT TIME ZONE 'Europe/Warsaw' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'Asia/Tokyo';

1. It converts date to Warsaw one. 2. It assumes that the Warsaw one is the Tokyo one and stores it as UTC (I know, this doesn't make any sense). 3. It converts UTC to Tokoy's one (so the second step is basicly reverted), but as we've assumed that tokyo is warsaw, we get in result the Warsaw time: '2025-04-04 01:00:00'::timestamp).