PostgreSQL 教程: timestamp 时间戳数据类型

八月 23, 2023

摘要：在本教程中，您将了解 PostgreSQL 时间戳数据类型，包括timestamp和timestamptz。您还将学习如何使用一些方便的函数来更有效地处理时间戳数据。

PostgreSQL 时间戳简介

PostgreSQL Timestamp

PostgreSQL 为您提供了两种用于处理时间戳的时间数据类型：

  • timestamp：没有时区的时间戳。
  • timestamptz：带有时区的时间戳。

timestamp数据类型允许您存储日期和时间。但是，它没有任何时区数据。这意味着当您更改数据库服务器的时区时，存储在数据库中的时间戳值不会自动更改。

timestamptz数据类型是带有时区的时间戳。timestamptz数据类型是时区感知的日期和时间数据类型。

PostgreSQL 以 UTC 值存储timestamptz。

  • 当您向timestamptz列中插入值时，PostgreSQL 会将该timestamptz值转换为 UTC 值并将该 UTC 值存储在表中。
  • 当您从数据库查询timestamptz值时，PostgreSQL 会将 UTC 值转换回数据库服务器、用户或当前数据库连接设置的时区的时间值。

请注意， timestamp和timestamptz都使用 8 个字节来存储时间戳值，如以下查询所示：

SELECT
      typname,
      typlen
FROM
      pg_type
WHERE
      typname ~ '^timestamp';

   typname   | typlen
-------------+--------
 timestamp   |      8
 timestamptz |      8
(2 rows)

请务必注意，timestamptz值存储为 UTC 值。PostgreSQL 不存储任何该timestamptz值带有的时区数据。

PostgreSQL 时间戳示例

让我们看一下使用timestamp和timestamptz 的示例，以更好地理解 PostgreSQL 如何处理它们。

首先，创建一个表，包含两列，分别为timestamp和timestamptz类型。

CREATE TABLE timestamp_demo (
    ts TIMESTAMP, 
    tstz TIMESTAMPTZ
);

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

SET timezone = 'America/Los_Angeles';

顺便说一句，您可以使用SHOW TIMEZONE命令查看当前时区：

SHOW TIMEZONE;

      TimeZone
---------------------
 America/Los_Angeles
(1 row)

然后，插入一个新行到timstamp_demo 表中：

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

之后，从timestamp和timestamptz列查询数据。

SELECT
   ts, tstz
FROM
   timestamp_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';
SELECT
	ts,
	tstz
FROM
	timestamp_demo;

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

timestamp列中的值不会改变，但timestamptz列中的值会调整为新的'America/New_York'时区。

一般来说，使用timestamptz数据类型来存储时间戳数据是一个很好的做法。

PostgreSQL 时间戳函数

为了有效地处理时间戳数据，PostgreSQL 提供了一些方便的函数，如下所示：

获取当前时间

要获取当前时间戳，请使用如下的NOW()函数：

SELECT NOW();

              now
-------------------------------
 2016-06-22 20:44:52.134125-07
(1 row)

或者您可以使用CURRENT_TIMESTAMP：

SELECT CURRENT_TIMESTAMP;

要获取没有日期的当前时间，您可以使用CURRENT_TIME：

       timetz
--------------------
 20:49:04.566025-07
(1 row)

请注意，CURRENT_TIMESTAMP和CURRENT_TIME都会返回带时区的当前时间。

要获取字符串格式的时间，请使用timeofday()函数。

SELECT TIMEOFDAY();

              timeofday
-------------------------------------
 Wed Jun 22 20:51:12.632420 2016 PDT

在时区之间转换

要将时间戳转换为另一个时区，请使用timezone(zone, timestamp) 函数。

SHOW TIMEZONE;

      TimeZone
---------------------
 America/Los_Angeles
(1 row)

当前时区是America/Los_Angeles。

要将时间值2016-06-01 00:00转换为America/New_York时区，请使用timezone()函数，如下：

SELECT timezone('America/New_York','2016-06-01 00:00');

      timezone
---------------------
 2016-06-01 03:00:00
(1 row)

请注意，我们将时间戳作为字符串传递给timezone()函数，PostgreSQL 将其隐式转换为timestamptz。最好将时间戳值显式转换为timestamptz数据类型，如下所示：

SELECT timezone('America/New_York','2016-06-01 00:00'::timestamptz);

在本教程中，您了解了 PostgreSQL 时间戳数据类型，并学习了如何使用一些有用的函数来操作时间戳值。

搜索

分类

标签