在Postgresql中，如何使用“在时区”取消时区偏移。 [英] In Postgresql, how to un-invert the timezone offsets with "At Time Zone"

问题描述

我正在努力围绕Postgresql时区，但似乎无法弄清楚。 EST是美国的东部标准时间，通常 UTC-5 。

I'm trying to wrap my head around Postgresql timezones, and I can't seem to figure this out. EST is "Eastern Standard Time" in America, and is typically UTC-5.

示例1：基础测试

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

示例2：偏移为+5

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

示例3：偏移量为-5

Example 3: Offset is -5

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

很显然，一切都是倒退。 EST又来了……应该是UTC-5 。现在，我确实搜索了文档，并确实说明了 POSIX，倒退。 （正偏移量在GMT以西，而负偏移量在GMT以东。）

Clearly, everything is backwards. EST is again... supposed to be UTC-5. Now, I did search through documentation, and it does explain that things are "POSIX", which is backwards. (Positive offset is west of GMT, while negative offsets are east of GMT).

但是，如何解决这个问题？在应用程序层，我总是可以将+号转换为-号，但这对IMO而言似乎有些混乱。因此，我的最终问题。

However, how do I get around this? At the application layer, I can always invert the + sign to a - sign, but that seems a bit messy to me IMO. Thus, my ultimate question.

在数据库层（Postgres），有一种方法可以使用在时区语法，以便GMT-5对应于EST ？还是只需要反转应用程序层的所有内容？

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

推荐答案

使用间隔数据类型，如文档以获取适当的行为：

Use interval datatype as written in documentation to get proper behaviour:

在这些表达式中，可以将所需时区 zone 指定为
作为文本字符串（例如 PST）或 interval （例如
INTERVAL'-08：00'）。

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

基本测试：

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

时区信息：

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

正确的偏移量-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)

正确的偏移量+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) 

这篇关于在Postgresql中，如何使用“在时区”取消时区偏移。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！