In PostgreSQL, how to cancel the time zone offset using “At Time Zone”

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

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

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

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

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

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

I am trying to bypass After checking the Postgresql time zone, I can’t seem to figure this out. EST is the “Eastern Standard Time” in the United States, 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: The offset is 5

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

Example 3: The offset is -5

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

Obviously, everything is backwards. EST again… supposed to be UTC-5. Now, I did search the documentation, and it did Explained that things are “POSIX”, which is backwards. (Positive offset is west of GMT, and negative offset is east of GMT).

But, how can I solve this problem? At the application layer, I can always reverse the symbol to-symbol, but this seems a bit confusing to me. Therefore, my ultimate question.

At the database layer (Postgres), is there How to use the “At Time Zone” syntax so that GMT-5 corresponds to EST? Or do I just need to reverse everything at the application layer?

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

< p>In these expressions, the desired time zone zone can be specified
either as a text string (eg,’PST’) or as an interval (eg,
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)

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

Appropriate offset 5:

< pre>SELECT ’08/31/2011 12:00 pm EST’::timestamptz AT TIME ZONE’+05:00′:: interval;
timezone
———————
2011-08-31 22:00:00
(1 row)