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

I’m trying to bypass the Postgresql time zone and I can’t seem to figure this out. EST is “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 going backwards. EST again… supposed to be UTC-5. Now, I did search the documentation, and it does explain that things are “POSIX”, which is backwards. (Positive offset is west of GMT, and negative offset is east of GMT).

< p>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:

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:

< pre>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:

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)

WordPress database error: [Table 'yf99682.wp_s6mz6tyggq_comments' doesn't exist]
SELECT SQL_CALC_FOUND_ROWS wp_s6mz6tyggq_comments.comment_ID FROM wp_s6mz6tyggq_comments WHERE ( comment_approved = '1' ) AND comment_post_ID = 755 ORDER BY wp_s6mz6tyggq_comments.comment_date_gmt ASC, wp_s6mz6tyggq_comments.comment_ID ASC

Leave a Comment

Your email address will not be published.