Query returning UTC timezone although using AT TIME ZONE

Question

I have data in GMT timezone. I want to convert and display them in 'America/New_York'.

This is the code I use:

(d.start_time) AT TIME ZONE 'America/New_York'

It returns let's say 2021-10-25T09:30:00.000Z.

9:30 is the correct time. But why is it a UTC time zone timestamp instead of 'America/New_York'?

I tried various variations of with/without time zone and ::timestamp/::timestamptz but I probably doesn't understand something very basic in here...

What should I do differently so that it's valid 9:30am timestamp in 'America/New_York' timezone?

Answer 1

The result is correct: 2021-10-25T09:30:00.000 is a timestamp without time zone, and it is not a UTC timestamp, but local time in New York City.

If you want the timestamp to be displayed with the time zone offset of New York City, you have to do something different:

SET timezone = 'America/New_York';
SELECT current_timestamp;

       current_timestamp       
═══════════════════════════════
 2021-10-11 12:45:49.881037-04
(1 row)

Then PostgreSQL will display timestamp with time zone values with the offset you want.