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

Question

我有一个表，其中有一列的数据类型为 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系统视图的utc_offset列定义为 UTC 偏移量（正表示格林威治以东）。

但在 POSIX 样式的时区规范中，偏移部分是不同的：

。要记住的另一个问题是，在 POSIX 时区名称中，格林威治以西的位置使用正偏移量。在其他任何地方，PostgreSQL都遵循ISO-8601惯例，即正时区偏移量位于格林威治以东。

因此，不应使用偏移量（作为间隔）或 POSIX 样式的时区规范，而应使用：

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

简而言之，这就是缩写和全名之间的区别：缩写表示与 UTC 的特定偏移量，而许多全名表示本地夏令时规则，因此有两个可能的 UTC 偏移量。

更复杂的是，一些司法管辖区使用相同的时区缩写来表示不同时间的不同UTC偏移量;例如，在莫斯科，MSK在某些年份表示UTC+3，在其他年份表示UTC+4。PostgreSQL根据它们在指定日期的含义（或最近的含义）来解释这些缩写;但是，与上面的 EST 示例一样，这不一定与该日期的当地民事时间相同。

但最简单的解决方案是使用 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