PostGreSQL:时间戳时区问题

时间|日期类型

PostGreSQL数据库内置的时间类型如下，注意到：内置的时间类型被分为了with time zone-带时区、without time zone-不带时区两种类型，

time、timestamp和interval都可以接受一个可选的精度值 p（取值：0-6），这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界。

时间|日期类型可接受的输入格式

在实际使用中，对于一个时间类型，我们通常很关心如何为此种类型的字段做插入、更新操作，那么就要考虑：PostGreSQL可以接受什么样的输入格式？对于PGSQL来讲，日期和时间的输入可以接受几乎任何合理的格式，包括 ISO 8601、SQL-兼容的、传统POSTGRES的和其他的形式。

如下表所示，PG官方推荐使用ISO 8601格式的时间、日期字符串输入。

时间戳:输入格式|当前时间

时间戳输入格式

相比上面的部分，本文更加关心与时间戳相关的内容。

时间戳类型的有效输入：由一个日期和时间的串接组成，后面跟着一个可选的时区，因此，以下两种输入都是有效的（都遵循ISO 8601标准），

1999-01-08 04:05:06
1999-01-08 04:05:06 -8:00

SQL标准通过“+”或者“-”符号的存在以及时间后面的时区偏移来区分timestamp without time zone和timestamp with time zone文字。因此，根据标准，

①TIMESTAMP '2004-10-19 10:23:54'：是一个timestamp without time zone；

②TIMESTAMP '2004-10-19 10:23:54+02'：是一个timestamp with time zone。

获取当前时间戳

我们先来获取一个当前时间戳，PGSQL提供了以下两种方式来获取，

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

注意CURRENT前缀、LOCAL前缀函数之间的区别：

CURRENT_TIME和CURRENT_TIMESTAMP传递带有时区的值；

LOCALTIME和LOCALTIMESTAMP传递的值不带时区。

接着看一下官方给的例子，

SELECT CURRENT_TIME;
结果：14:39:53.662522-05SELECT CURRENT_DATE;
结果：2001-12-23SELECT CURRENT_TIMESTAMP;
结果：2001-12-23 14:39:53.662522-05SELECT CURRENT_TIMESTAMP(2);
结果：2001-12-23 14:39:53.66-05SELECT LOCALTIMESTAMP;
结果：2001-12-23 14:39:53.662522

以2023-09-09 11:16:56.745139+08为例，表示：2023年09月09日，上午11时16分56.745139秒，+08东八区。

时间|日期输出格式

时间|日期格式化也是我们做项目开发、数据库操作避不开的一个问题，PostGreSQL的时间/日期类型的输出格式可以设成四种风格之一： ISO 8601、SQL（Ingres）、传统的POSTGRES（Unix的date格式）或 German 。缺省是ISO格式。

以下为4种格式的例子，

根据PG官方描述，实现数据格式化操作的方式有两种，

①日期/时间风格可以由用户使用SET datestyle命令选取，在postgresql.conf配置文件里的参数DateStyle设置或者在服务器或客户端的PGDATESTYLE环境变量里设置。

②格式化函数to_char（见第 9.8 节）也可以作为一个更灵活的方式来格式化日期/时间输出。

我们来了解一下第②种。

数据类型格式化函数

PostgreSQL格式化函数提供一套强大的工具用于把各种数据类型（日期/时间、整数、浮点、数字）转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。

可以看到：通过格式化函数，可以实现时间戳和字符串之间的互转操作，而对于格式化字符串的format控制参数模板串，可参见：9.8. 数据类型格式化函数 (postgres.cn)，表9.25.

例如：我们要将当前时间戳转换为类似于：yyyy-MM-dd hh:mm:ss这样的字符串格式（24小时制），那么SQL语句为：


SELECT to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')--输出
2023-09-09 11:29:02

接着，我们再将上面的输出字符串转换为时间戳，

SELECT to_timestamp('2023-09-09 11:29:02', 'YYYY-MM-DD HH24:MI:SS')--
2023-09-09 11:29:02+08

关于时区

PG官方对于时区的问题解释如下，并不建议使用带时区的类型，

PostgreSQL努力在典型使用中与SQL标准的定义相兼容。但SQL标准在日期和时间类型和功能上有一些奇怪的混淆。两个显而易见的问题是：

尽管date类型与时区没有联系，而time类型却可以有。然而，现实世界的时区只有在与时间和日期都关联时才有意义，因为偏移（时差）可能因为实行类似夏时制这样的制度而在一年里有所变化。

缺省的时区会指定一个到UTC的数字常量偏移（时差）

我们建议在使用时区的时候，使用那些同时包含日期和时间的日期/时间类型。我们不建议使用类型 time with time zone （尽管PostgreSQL出于遗留应用以及与SQL标准兼容性的考虑支持这个类型）。 PostgreSQL假设你用于任何类型的本地时区都只包含日期或时间。

关于UTC和ISO 8601的概念以及作用，可参考：时间标准基础知识UTC和ISO8601。

查看数据库当前使用的时区，