迹忆客 专注技术分享

当前位置:主页 > 学无止境 > 数据库 > PostgreSQL >

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

作者:迹忆客 最近更新:2023/05/09 浏览次数:

本文将讨论 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。要了解有关时间戳格式和表示的更多信息,请访问这里

转载请发邮件至 1244347461@qq.com 进行申请,经作者同意之后,转载请以链接形式注明出处

本文地址:

相关文章

在 PSQL 中运行 SQL 文件

发布时间:2023/03/20 浏览次数:178 分类:数据库

本文解释了如何直接从终端/命令行或 psql shell 运行 SQL 文件。为此,你需要指定主机名、端口、用户名和数据库名称。

在 PostgreSQL 中使用循环

发布时间:2023/03/20 浏览次数:124 分类:PostgreSQL

在 PL/SQL 中,你可能需要在 Postgres 中使用循环。我们可以使用 FOR 和 WHILE 语句来创建循环。

扫一扫阅读全部技术教程

社交账号
  • https://www.github.com/onmpw
  • qq:1244347461

最新推荐

教程更新

热门标签

扫码一下
查看教程更方便