postgresql - 将时间戳转换为Postgres中特定时区的时间戳

Question

我有一个表，其中有一列的数据类型为 timestamp without time zone ，我试图在给定时区将其转换为 timestamp without time zone。

这些是我的 Postgres 9.3 设置

select current_setting('TIMEZONE');
 current_setting
-----------------
 Hongkong

select * from  pg_timezone_names where name = 'Hongkong';
   name   | abbrev | utc_offset | is_dst
----------+--------+------------+--------
 Hongkong | HKT    | 08:00:00   | f

这是我将其转换为 HKT 的操作:

-- this SQL gives me what I expected
select '2015-01-05 12:00:00'::timestamp without time zone
at time zone 'UTC'
at time zone 'HKT';
---------------------
 2015-01-05 20:00:00

-- Shouldn't this produce the same result with the above one?
-- How do I make this work?
-- Don't tell me to change it to UTC-08:00 ...
select '2015-01-05 12:00:00'::timestamp without time zone
at time zone 'UTC'
at time zone 'UTC+08:00';
---------------------
 2015-01-05 04:00:00 -- WHY?

Answer 1

这背后的原因是为什么你真的不应该在 PostgreSQL 中使用时区偏移量(除非你确切地知道你在做什么)。

时区'UTC+08:00'是POSIX风格的时区规范，'Hongkong'是准确的时区名称， 'HKT' 是(其中之一)它的缩写。

pg_timezone_names 系统 View 的utc_offset 列是defined是 UTC 的偏移量(正数表示格林威治的东)。

但是在POSIX风格的时区规范中，偏移部分是different :

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

因此，您应该使用:

• 时区缩写，如果您想自己处理夏令时规则，
• 准确的时区名称，其他任何地方(首选)。

In short, this is the difference between abbreviations and full names: abbreviations represent a specific offset from UTC, whereas many of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets.

To complicate matters, some jurisdictions have used the same timezone abbreviation to mean different UTC offsets at different times; for example, in Moscow MSK has meant UTC+3 in some years and UTC+4 in others. PostgreSQL interprets such abbreviations according to whatever they meant (or had most recently meant) on the specified date; but, as with the EST example above, this is not necessarily the same as local civil time on that date.

但最简单的解决方案是使用timestamp with time zone:您已经将TimeZone设置为'Hongkong'，所以timestamp with time zone 值将以该时区显示给您的 (PostgreSQL) 客户端。

set time zone 'Hongkong';

select current_setting('TimeZone') TimeZone,
       dt original,
       dt AT TIME ZONE 'UTC' AT TIME ZONE 'UTC+08:00' "UTC+08:00",
       dt AT TIME ZONE 'UTC' AT TIME ZONE 'UTC+8' "UTC+8",
       dt AT TIME ZONE 'UTC' AT TIME ZONE 'UTC-8' "UTC-8",
       dt AT TIME ZONE 'UTC' AT TIME ZONE INTERVAL '+08:00' "INTERVAL +08:00",
       dt AT TIME ZONE 'UTC' AT TIME ZONE 'HKT' "HKT",
       dt AT TIME ZONE 'UTC' AT TIME ZONE 'Hongkong' "Hongkong",
       dt AT TIME ZONE 'UTC' "with time zone"
from (values (timestamp '2015-01-05 12:00:00')) v(dt);

-- TIMEZONE | ORIGINAL            | UTC+08:00
-- ---------+---------------------+--------------------
-- Hongkong | 2015-01-05 12:00:00 | 2015-01-05 04:00:00

-- UTC+8               | UTC-8               | INTERVAL +08:00
-- --------------------+---------------------+--------------------
-- 2015-01-05 04:00:00 | 2015-01-05 20:00:00 | 2015-01-05 20:00:00

-- HKT                 | HONGKONG            | WITH TIME ZONE
-- --------------------+---------------------+-----------------------
-- 2015-01-05 20:00:00 | 2015-01-05 20:00:00 | 2015-01-05 20:00:00+08

SQLFiddle