postgresql – Postgres UTC日期格式和纪元演员,签名反转

发布时间：2020-12-13 16:01:11 所属栏目：百科来源：网络整理

导读：有人能解释我这种反转,我迷失在这里…… SELECT EXTRACT(EPOCH FROM '01-01-1970 00:00:00 UTC+01'::timestamp with time zone) = 3600 SELECT EXTRACT(EPOCH FROM '1970-01-01 00:00:00+01'::timestamp with time zone) = -3600 Postgres 8.3.14 解决方法

有人能解释我这种反转,我迷失在这里……

SELECT EXTRACT(EPOCH FROM '01-01-1970 00:00:00 UTC+01'::timestamp with time zone)

=> 3600

SELECT EXTRACT(EPOCH FROM '1970-01-01 00:00:00+01'::timestamp with time zone)

=> -3600

Postgres 8.3.14

解决方法

这个

1970-01-01 00:00:00+01

是一个ISO 8601 timestamp with a +1 hour offset和1意味着格林威治以东.这些中的抵消

01-01-1970 00:00:00 UTC+01
1970-01-01 00:00:00 UTC+01
1970-01-01 00:00:00 XXX+01
1970-01-01 00:00:00 HAHA+01
1970-01-01 00:00:00 Pancakes+01

将被解释为POSIX style timezones,其中1表示格林威治以西：

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

那些甚至带来警告：

One should be wary that the POSIX-style time zone feature can lead to silently accepting bogus input,since there is no check on the reasonableness of the zone abbreviations. For example,SET TIMEZONE TO FOOBAR0 will work,leaving the system effectively using a rather peculiar abbreviation for UTC. 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.

注意西与东的差异.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!