提要:在本教程中,我们将向您介绍PostgreSQL TIMESTAMP 数据类型,并告诉你如何使用一些方便的函数更有效地处理时间戳数据。

时间戳简介

PostgreSQL 提供两种存储时间戳的数据类型: 不带时区的 TIMESTAMP 和带时区的 TIMESTAMPTZ

TIMESTAMP 数据类型可以同时存储日期和时间,但它不存储时区。这意味着,当修改了数据库服务器所在的时区时,它里面存储的值不会改变。

TIMESTAMPTZ 数据类型在存储日期和时间的同时还能正确处理时区。PostgreSQL 使用 UTC 值来存储 TIMESTAMPTZ 数据。在向 TIMESTAMPTZ 字段插入值的时候,PostgreSQL 会自动将值转换成 UTC 值,并保存到表里。当从一个 TIMESTAMPTZ 字段查询数据的时候,PostgreSQL 会把存储在其中的 UTC 值转换成数据库服务器、用户或当前连接所在的时区。

TIMESTAMPTIMESTAMPTZ 都使用8字节存储空间。如下所示:

SELECT
      typname,
      typlen
FROM
      pg_type
WHERE
      typname ~ '^timestamp';
   typname   | typlen
-------------+--------
 timestamp   |      8
 timestamptz |      8
(2 rows)

重要提示,TIMESTAMPTZ 并不会存储时区,它只是了 UTC 值,然后会和当前时区进行转换。

时间戳示例

首先,创建一个含有 TIMESTAMPTIMESTAMPTZ 的表:

CREATE TABLE timestamp_demo (ts TIMESTAMP, tstz TIMESTAMPTZ);

接下来,将数据库服务器的时区设置为 America/Los_Angeles

SET timezone = 'America/Los_Angeles';

顺便说一句,可以使用 SHOW TIMEZONE 语句来显示当前时区:

SHOW TIMEZONE;
    TimeZone
----------------
 Asia/Shang_Hai
(1 行记录)

然后,插入一些示例数据:

INSERT INTO timestamp_demo (ts, tstz)
VALUES
 (
 '2016-06-22 19:10:25-07',
 '2016-06-22 19:10:25-07'
 );

最后,查询:

SELECT
 ts
FROM
 timestampz_demo;
         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 19:10:25-07
(1 row)

再把时区设置为 America/New_York

SET timezone = 'America/New_York';

再查询一次,结果如下:

         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 22:10:25-04
(1 row)

TIMESTAMP 类型字段的值不变,而 TIMESTAMPTZ 类型字段的值变成了当前时区下的时间。

时间戳函数

获取当前时间

使用 NOW() 函数来获取当前时间:

SELECT NOW();
              now
-------------------------------
 2018-01-11 16:51:55.706649+08
(1 行记录)

或者使用 CURRENT_TIMESTAMP

SELECT CURRENT_TIMESTAMP;
       current_timestamp
-------------------------------
 2018-01-11 16:53:03.874548+08
(1 行记录)

如果只需要时间部分,不需要日期部分,使用 CURRENT_TIME

SELECT CURRENT_TIME;
    current_time
--------------------
 16:54:33.802691+08
(1 行记录)

提示,CURRENT_TIMESTAMPCURRENT_TIME 返回的值都包含时区。

时区转换

SELECT CURRENT_TIMESTAMP AS CST,  timezone('America/New_York', CURREN
T_TIMESTAMP) AS NYT;

结果:

              cst              |            nyt
-------------------------------+----------------------------
 2018-01-11 16:58:08.610978+08 | 2018-01-11 03:58:08.610978
(1 行记录)