PostgreSQL中的时区转换不一致 [英] Inconsistent time zone conversion in PostgreSQL
问题描述
我试图从PostgreSQL数据库的 timestamptz
字段中选择本地时间(如澳大利亚/布里斯班),并注意到当我使用 +10
时区缩写,PostgreSQL似乎从UTC值中减去10小时,而不是增加10小时。
I'm attempting to select a local time (as at Australia/Brisbane) from a timestamptz
field in a PostgreSQL database and noticed that when I use the +10
time zone abbreviation, PostgreSQL appears to subtract 10 hours from the UTC value instead of adding 10 hours.
如果我使用 AEST
作为缩写,则正确添加了10个小时。
If I use AEST
as the abbreviation, 10 hours are correctly added.
运行以下查询时,我希望返回的两个值相同。
When I run the following query I would expect both values returned to be the same.
select
('2018-01-01T00:00:00Z'::timestamp with time zone) at time zone 'AEST',
('2018-01-01T00:00:00Z'::timestamp with time zone) at time zone '+10';
但是,就我而言,我看到以下结果:
However, in my case I'm seeing the following results:
"2018-01-01 10:00:00" | "2017-12-31 14:00:00"
如果我运行以下查询,则所有行的时间间隔为10:00:00。
If I run the following query, all rows have a "utc_offset" interval of 10:00:00.
select * from pg_timezone_names
where abbrev in ('+10', 'AEST')
有人能解释一下这里发生的事情吗?
Could anyone offer an explanation as to what is going on here?
推荐答案
这实际上在文档:
PostgreSQL允许您以三种不同的形式指定时区:
PostgreSQL allows you to specify time zones in three different forms:
-
完整的时区名称,例如
美国/纽约。
[...]
时区缩写,例如 PST
。 [...]
A time zone abbreviation, for example PST
. [...]
除时区名称和缩写外,PostgreSQL还接受形式的POSIX样式的时区规范。 STDoffset
或 STDoffsetDST
,其中 STD
是区域的缩写, offset
是从UTC向西偏移小时的数字,而 DST
是可选的夏时制区域缩写,假定比给定偏移量早一小时。 [...]
In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications of the form STDoffset
or STDoffsetDST
, where STD
is a zone abbreviation, offset
is a numeric offset in hours west from UTC, and DST
is an optional daylight-savings zone abbreviation, assumed to stand for one hour ahead of the given offset. [...]
[...]
要记住的另一个问题是,在POSIX时区名称中,格林威治的 位置使用正偏移量。 PostgreSQL在其他任何地方都遵循ISO-8601约定,正时区偏移量是格林威治的 东 。
Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich.
因此 AEST
的POSIX等效为 -10
或 UTC -10
。
So the POSIX equivalent for AEST
would be -10
or UTC-10
.
这篇关于PostgreSQL中的时区转换不一致的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!