迹忆客 专注技术分享

本文将讨论 PostgreSQL 中的时间戳类型并展示它们的区别。

PostgreSQL 中的时间戳

在 PostgreSQL 中，有两种类型的时间戳。

1. 没有时区的时间戳
2. 带时区的时间戳

第一个存储本地日期。例如，假设现在是 24 小时系统时钟中的 11.00。

因此，这将存储为 11.00。如果它保存在数据库中，比如远程数据库中，并且有人从 CST 时区拉出这一行，他仍会将其视为 11.00。

然而，这不是真正的时间。查看该时间所需的 CST 将转换为 CST 时区。

由于它不存储有关时区的任何信息，因此无法在不同时区进一步确定。

第二种方法解决了这个问题。因此，每当你在数据库中推送时间戳时，它都会从主机系统中提取时区并将时区与时间戳一起保存。

假设我们在 GMT+6 时区。这是时间戳的演示。

SELECT now() as "System Time",
now()::timestamp as "postgres Time",
now() AT TIME ZONE 'GMT' as "time without zone",
now() AT TIME ZONE 'CST' as "time without zone",
now()::timestamp at TIME ZONE 'GMT' as "Timestamp GMT",
now()::timestamp at TIME ZONE 'CST' as "Timestamp CST" ;

这里，第一列包含系统时间，第二列包含将时间转换为不带时区的时间戳后的时间戳。

输出：

          System Time          |       postgres Time        |     time without zone      |     time without zone      |         Timestamp GMT         |         Timestamp CST
-------------------------------+----------------------------+----------------------------+----------------------------+-------------------------------+-------------------------------
 2022-03-15 10:19:05.432758+06 | 2022-03-15 10:19:05.432758 | 2022-03-15 04:19:05.432758 | 2022-03-14 22:19:05.432758 | 2022-03-15 16:19:05.432758+06 | 2022-03-15 22:19:05.432758+06
(1 row)

PostgreSQL 中带和不带时区的时间戳之间的区别

让我们创建一个表，看看它如何存储时间戳，以及如何在 PostgreSQL 中使用无时区和有时区。

首先，将你的 psql 控制台连接到 Postgres，然后运行以下 SQL 命令来创建如下表：

CREATE TABLE Times(
    id INT PRIMARY KEY NOT NULL,
    time_without_zone TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
    time_with_zone TIMESTAMP WITH TIME ZONE DEFAULT now()
);

现在，用一些条目填充表。

INSERT INTO Times(id) VALUES(1);
INSERT INTO Times(id) VALUES(2);
INSERT INTO Times(id) VALUES(3);
INSERT INTO Times(id) VALUES(4);
INSERT INTO Times(id) VALUES(5);
INSERT INTO Times(id) VALUES(6);
INSERT INTO Times(id) VALUES(7);

输出：

postgres=# select * from times;
 id |     time_without_zone      |        time_with_zone
----+----------------------------+-------------------------------
  1 | 2022-03-15 10:29:03.52078  | 2022-03-15 10:29:03.52078+06
  2 | 2022-03-15 10:29:03.52564  | 2022-03-15 10:29:03.52564+06
  3 | 2022-03-15 10:29:03.526723 | 2022-03-15 10:29:03.526723+06
  4 | 2022-03-15 10:29:03.527775 | 2022-03-15 10:29:03.527775+06
  5 | 2022-03-15 10:29:03.528865 | 2022-03-15 10:29:03.528865+06
  6 | 2022-03-15 10:29:03.529941 | 2022-03-15 10:29:03.529941+06
  7 | 2022-03-15 10:29:05.045774 | 2022-03-15 10:29:05.045774+06
(7 rows)

postgres=#

如你所见，time_with_zone 列存储带有 GMT+06 时区的时间。时间可以转换为任何其他时区，因为 psql 将知道列中时间的基数。

这是一个示例输出，它显示了如果你尝试在没有时区类型的列上插入带有时区的时间戳会发生什么。

INSERT INTO Times(id,time_without_zone,time_with_zone) VALUES(9,'2022-03-15 10:29:05.045774+06','2022-03-15 10:29:05.045774+06');

输出：

postgres=# select * from times where id=9;
 id |     time_without_zone      |        time_with_zone
----+----------------------------+-------------------------------
  9 | 2022-03-15 10:29:05.045774 | 2022-03-15 10:29:05.045774+06
(1 row)

如你所见，psql 只是在 time_without_zone 列中删除了 +06。

如果要查看 Postgres 中所有可用的时区，可以运行以下 SQL 命令：

postgres=# SELECT name FROM pg_timezone_names;
               name
----------------------------------
 Africa/Abidjan
 Africa/Accra
 Africa/Addis_Ababa
 Africa/Algiers
 Africa/Asmara
 Africa/Asmera
 Africa/Bamako
 Africa/Bangui
 Africa/Banjul
 Africa/Bissau
 -- More --

此处，系统在 GMT+6 时区运行。如果要将 Postgres 时区更改为其他时区，可以运行以下命令：

postgres=# SET TIMEZONE='UTC';
SET

如果你看到上表的数据，你会看到带有时区的列已成功转换为 UTC。

postgres=# select * from times;
 id |     time_without_zone      |        time_with_zone
----+----------------------------+-------------------------------
  1 | 2022-03-15 10:29:03.52078  | 2022-03-15 04:29:03.52078+00
  2 | 2022-03-15 10:29:03.52564  | 2022-03-15 04:29:03.52564+00
  3 | 2022-03-15 10:29:03.526723 | 2022-03-15 04:29:03.526723+00
  4 | 2022-03-15 10:29:03.527775 | 2022-03-15 04:29:03.527775+00
  5 | 2022-03-15 10:29:03.528865 | 2022-03-15 04:29:03.528865+00
  6 | 2022-03-15 10:29:03.529941 | 2022-03-15 04:29:03.529941+00
  7 | 2022-03-15 10:29:05.045774 | 2022-03-15 04:29:05.045774+00
  9 | 2022-03-15 10:29:05.045774 | 2022-03-15 04:29:05.045774+00
(8 rows)

你可以看到 time_without_zone 保持不变，但 time_with_zone 从 GMT+06 转换为 UTC。要了解有关时间戳格式和表示的更多信息，请访问这里。