Postgres UTC日期格式和时代转型，符号反转 [英] Postgres UTC date format & epoch cast, sign inversion

问题描述

有人能解释这个符号反转吗，我在这里迷路了……

  SELECT EXTRACT（EPOCH FROM '01 -01-1970 00:00:00 UTC + 01'::带时区的时间戳）
  

= > 3600

  SELECT EXTRACT（EPOCH FROM'1970-01-01 00：00：00 + 01':: timestamp with time区域）
  

=> -3600

Postgres 8.3.14

解决方案

此

  1970-01-01 00：00：00 + 01 
  

是具有+1小时偏移量的ISO 8601时间戳，并且+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样式的时区，其中+1表示格林威治的 west ：

PostgreSQL将接受POSIX风格的时区规范，格式为STDoffset或STDoffsetDST，其中STD是区域缩写，offset是以西数小时为单位的数字偏移量来自UTC

，甚至带有警告：

应该警惕的是，POSIX样式的时区功能可能导致静默接受虚假输入，因为没有检查时区缩写的合理性。例如，将时区设置为FOOBAR0 将起作用，从而使系统有效地使用UTC的一个特有缩写。要记住的另一个问题是，在POSIX时区名称中，正偏移量用于格林威治以西的位置。 PostgreSQL在其他任何地方都遵循ISO-8601约定，正时区偏移量位于格林威治以东。

请注意西方与东方的区别。 / p>

Could someone explain me this sign inversion, i'm lost here...

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

解决方案

This

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

is an ISO 8601 timestamp with a +1 hour offset and +1 means east of Greenwich. The offsets in these

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

will be interpreted as POSIX style timezones where +1 means west of Greenwich:

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 those even come with a warning:

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.

Note the west versus east difference.

这篇关于Postgres UTC日期格式和时代转型，符号反转的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！