Explicitly assign timezone to a timestamp without timezone in PostgreSQL

Question

Hitting my head on a wall with this one. How do I tell Postgres the timezone of the unaware timestamp column? I know the timezone based on another column in the database -- the timestamp value itself is unaware.

select 
  '2017-07-10 01:30:00'::timestamp as without_tz, 
  '2017-07-10 01:30:00'::timestamp at time zone 'America/New_York' as with_tz
-------------------------
without_tz: 2017-07-10 01:30:00
with_tz:    2017-07-09 22:30:00-07

My desired output is:

with_tz:     2017-07-10 01:30:00-07

Check phili.pe/posts/timestamps-and-time-zones-in-postgresql. You probably need to SET timezone before your select... — joanolo
– joanolo, Commented Jul 15, 2017 at 15:57
Hi thanks! SET timezone to 'America/New_York' does work; however, it doesn't seem to accept a variable as the timezone ... and I need that! Grr. — thornomad
– thornomad, Commented Jul 16, 2017 at 5:49

user10382738 · Accepted Answer · 2018-09-26 17:23:15Z

Short answer:

SELECT '2017-07-10 01:30:00'::timestamp AT TIME ZONE 'America/New_York' AT TIME ZONE 'America/New_York';
>> 2017-07-10 01:30:00

First, the output indicates your time zone is set to America/Los_Angeles or some other time zone definition that results in times being UTC-7. You may confirm this with SHOW TIME ZONE;.

Second, AT TIME ZONE functions differently on a timestamp or timestamptz. See the docs, but a timestamp is treated as the specified time zone and returned as local time with time zone:

Treat timestamp as New York, return as local timestamptz:

SELECT '2017-07-10 01:30:00'::timestamp AT TIME ZONE 'America/New_York';
>> 2017-07-09 22:30:00-07

While a timestamptz is treated as local time, converted to the specified time zone and returned as timestamp without time zone.

Convert local timestamptz to New York timestamp

SELECT '2017-07-10 01:30:00'::timestamptz AT TIME ZONE 'America/New_York';
>> 2017-07-10 04:30:00

Once you understand the timestamp-timestamptz conversion happening, you can reliably chain the AT TIME ZONE command.

Convert local timestamptz to New York timestamp which is treated as Chicago time and returned as local timestamptz:

SELECT '2017-07-10 01:30:00'::timestamptz AT TIME ZONE 'America/New_York' AT TIME ZONE 'America/Chicago';
>>  2017-07-10 02:30:00-07

To explain this more explicitly, this query converts a local timestamptz 1:30a to New York timestamp 4:30a, then treats 4:30a as Chicago timestamp and returns a local timestamptz 2:30a.

Similarly, the short answer above treats 1:30a as New York timestamp, then converts it to New York timestamptz 1:30a.

Collectives™ on Stack Overflow

Explicitly assign timezone to a timestamp without timezone in PostgreSQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related