在 Vertica 中使用时区

Vertica 使用公共域 tz 数据库（时区数据库），其中包含表示全球各地当地时间历史记录的代码和数据。此数据库通过将世界划分为多个时区来对时区和夏令时数据进行组织，这些时区的时钟都一致采用晚于 POSIX 时期 (1970-01-01 00:00:00 UTC) 的时间戳。每个时区都有一个唯一的标识符。标识符通常遵循 区域/位置的约定，其中区域是大陆或海洋，而位置是该区域内的特定位置——例如，非洲/开罗、美洲/纽约和太平洋/火奴鲁鲁。

Vertica 在每个节点上使用 TZ 环境变量（如果已设置）作为默认的当前时区。否则，Vertica 将使用操作系统时区。

TZ 变量可由操作系统在登录期间设置（请参见/etc/profile、/etc/profile.d 或 /etc/bashrc），或由用户在 .profile、.bashrc 或 .bash-profile 中设置。 TZ 必须在启动 Vertica 时在每个节点上设置为相同的值。

以下命令将会返回数据库的当前时区：

=> SHOW TIMEZONE;
   name   |     setting
----------+------------------
 timezone | America/New_York
(1 row)

还可以使用 SET TIME ZONE 为单个会话设置时区。

日期/时间数据的转换和存储

数据库没有默认时区。TIMESTAMPTZ (TIMESTAMP WITH TIMEZONE) 数据从当前的本地时间转换而来，并以 GMT/UTC（格林威治标准时间/协调世界时）形式进行存储。

在使用 TIMESTAMPTZ 数据时，数据将恢复为当前本地时区，此时区与存储数据的本地时区可能不同。这种转换会考虑夏令时（夏季时间），具体取决于决定夏令时开始和结束的年份和日期。

TIMESTAMP WITHOUT TIMEZONE 数据则按原样存储时间戳，并完全按原样检索时间戳。将忽略当前时区。对于 TIME WITHOUT TIMEZONE 而言同样如此。但对于 TIME WITH TIMEZONE (TIMETZ)，当前时区设置将与给定的时间一同存储，并且在检索时使用该时区。

查询数据/时间数据

TIMESTAMPTZ 在输入和输出时均使用当前时区，如以下示例所示：

=> CREATE TEMP TABLE s (tstz TIMESTAMPTZ);=> SET TIMEZONE TO 'America/New_York';
=> INSERT INTO s VALUES ('2009-02-01 00:00:00');
=> INSERT INTO s VALUES ('2009-05-12 12:00:00');
=> SELECT tstz AS 'Local timezone', tstz AT TIMEZONE 'America/New_York' AS 'America/New_York',
   tstz AT TIMEZONE 'GMT' AS 'GMT' FROM s;
     Local timezone     |  America/New_York   |         GMT
------------------------+---------------------+---------------------
 2009-02-01 00:00:00-05 | 2009-02-01 00:00:00 | 2009-02-01 05:00:00
 2009-05-12 12:00:00-04 | 2009-05-12 12:00:00 | 2009-05-12 16:00:00
(2 rows)

“Local timezone”列中的 -05 显示了以 EST 时区显示的数据，而 -04 则指示 EDT 时区。另外两列显示了指定时区的 TIMESTAMP WITHOUT TIMEZONE。

下一个示例显示了将当前时区更改为 GMT 会发生什么情况：

=> SET TIMEZONE TO 'GMT';=> SELECT tstz AS 'Local timezone', tstz AT TIMEZONE 'America/New_York' AS
   'America/New_York', tstz AT TIMEZONE 'GMT' as 'GMT' FROM s;
     Local timezone     |  America/New_York   |         GMT
------------------------+---------------------+---------------------
 2009-02-01 05:00:00+00 | 2009-02-01 00:00:00 | 2009-02-01 05:00:00
 2009-05-12 16:00:00+00 | 2009-05-12 12:00:00 | 2009-05-12 16:00:00
(2 rows)

“Local timezone”列中的 +00 指示 TIMESTAMPTZ 显示为 GMT 时区。

使用 TIMESTAMPTZ 字段记录事件的方法将会捕获事件的 GMT（表示为本地时区的形式）。之后，它允许通过设置本地时区或指定明确的 AT TIMEZONE 语句，轻松地转换为其他任何时区。

以下示例显示了 TIMESTAMP WITHOUT TIMEZONE 字段在 Vertica 中的使用方法。

=> CREATE TEMP TABLE tnoz (ts TIMESTAMP);=> INSERT INTO tnoz VALUES('2009-02-01 00:00:00');
=> INSERT INTO tnoz VALUES('2009-05-12 12:00:00');
=> SET TIMEZONE TO 'GMT';
=> SELECT ts AS 'No timezone', ts AT TIMEZONE 'America/New_York' AS
   'America/New_York', ts AT TIMEZONE 'GMT' AS 'GMT' FROM tnoz;
      No timezone    |    America/New_York    |          GMT
---------------------+------------------------+------------------------
 2009-02-01 00:00:00 | 2009-02-01 05:00:00+00 | 2009-02-01 00:00:00+00
 2009-05-12 12:00:00 | 2009-05-12 16:00:00+00 | 2009-05-12 12:00:00+00
(2 rows)

时间戳末尾的 +00 指示，该设置是以 GMT 形式（当前时区）表示的 TIMESTAMP WITH TIMEZONE。“America/New_York”列显示了记录时间的时刻对应的 GMT 设置（假定您读取“America/New_York”时区的正常时钟）。这表明，America/New_York 时区的午夜时分对应 GMT 时区的凌晨 5 点。

“GMT”列显示了 GMT 时间（假定输入数据在 GMT 时区捕获）。

如果没有将时区设为 GMT，而是使用其他时区，例如 America/New_York，则结果以 America/New_York 时区显示为 -05 和 -04，表明该时区与 GMT 之间的时差。

=> SET TIMEZONE TO 'America/New_York';
=> SHOW TIMEZONE;
    name   |     setting
 ----------+------------------
  timezone | America/New_York
 (1 row)
=> SELECT ts AS 'No timezone', ts AT TIMEZONE 'America/New_York' AS
   'America/New_York', ts AT TIMEZONE 'GMT' AS 'GMT' FROM tnoz;
      No timezone    |    America/New_York    |          GMT
---------------------+------------------------+------------------------
 2009-02-01 00:00:00 | 2009-02-01 00:00:00-05 | 2009-01-31 19:00:00-05
 2009-05-12 12:00:00 | 2009-05-12 12:00:00-04 | 2009-05-12 08:00:00-04
(2 rows)

在这种情况下，最后一列需要引起注意，因为数据是在 GMT 时区捕获，返回的是纽约时间。

另请参阅