在 Postgresql 中，如何使用“At Time Zone”取消反转时区偏移量

发布于 2024-11-30 06:34:06 字数 1100 浏览 0 评论 0原文

我正在尝试了解 Postgresql 时区，但我似乎无法弄清楚这一点。 EST 是美国的“东部标准时间”，通常为 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

 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 以东）。

然而，我该如何解决这个问题呢？在应用程序层，我总是可以将+号反转为-号，但这对我来说似乎有点混乱。因此，这是我的终极问题。

在数据库层（Postgres），有没有办法使用“At Time Zone”语法使GMT-5对应于EST？或者我只需要反转应用程序层的所有内容？

原文

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.

Example 1: Base 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, 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).

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.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

药祭#氼 2024-12-07 06:34:06

使用文档中编写的间隔数据类型获得正确的行为：

在这些表达式中，可以指定所需的时区zone
作为文本字符串（例如，“PST”）或作为间隔（例如，
间隔“-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)

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

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').

Base test:

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

Timezone info:

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

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

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

回复收藏 0 原文

~没有更多了~