In Postgresql, how to un-invert time zone offsets using "In Time Zone",

Question

I am trying to wrap my head around Postgresql time zones and I cannot figure it out. EST is "Eastern Standard Time" in America and usually UTC-5 .

Example 1: basic test

select '08/31/2011 12:00 pm EST'::timestamptz at time zone 'EST'; timezone --------------------- 2011-08-31 12:00:00 

Example 2: Offset is +5

 select '08/31/2011 12:00 pm EST' at time zone '+5'; timezone --------------------- 2011-08-31 12:00:00 

Example 3: Offset is -5

  select '08/31/2011 12:00 pm EST' at time zone '-5'; timezone --------------------- 2011-08-31 22:00:00 

Clearly, the opposite is true. EST again ... should be UTC-5 . Now I looked through the documentation, and this explains that all this is "POSIX", which is in the opposite direction . (Positive offset west of GMT, while negative offset east of GMT).

However, how do I get around this? At the application level, I can always invert the + sign to a sign, but that seems a bit messy for me IMO. So my final question.

At the database level (Postgres), is there a way to use the "At Time Zone" syntax so that GMT-5 matches EST? Or do I just need to invert everything at the application level?

Answer 1

Use the interval data type specified in the documentation to get the correct behavior:

In these expressions, the desired zone time zone can be specified either as a text string (for example, "PST"), or as an interval (for example, INTERVAL '-08: 00').

Basic test:

 SELECT '08/31/2011 12:00 pm EST'::timestamptz AT TIME ZONE 'EST'; timezone --------------------- 2011-08-31 12:00:00 (1 row) 

Time Zone Information:

 SELECT * FROM pg_timezone_abbrevs WHERE abbrev LIKE 'EST'; abbrev | utc_offset | is_dst --------+------------+-------- EST | -05:00:00 | f (1 row) 

Correct offset -5:

 SELECT '08/31/2011 12:00 pm EST'::timestamptz AT TIME ZONE '-05:00'::interval; timezone --------------------- 2011-08-31 12:00:00 (1 row) 

Correct offset +5:

 SELECT '08/31/2011 12:00 pm EST'::timestamptz AT TIME ZONE '+05:00'::interval; timezone --------------------- 2011-08-31 22:00:00 (1 row)