PostgreSQL技术问答43 - Date&Time 日期和时间-上本文探讨了在Postgres中，对于时间处理的相
本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：
文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。
本文讨论的内容是PostgreSQL中对于日期和时间的相关支持和内容。这部分的内容内容比较多，笔者可能会分为两个部分进行探讨。本文是相关内容的上半部分，是比较基础和常规的内容。在下半部分，会讨论一些相对比较扩展和高级的内容，也建议读者还是去了解一下。
Postgres中，如何实现和支持日期和时间的处理
Postgres技术文档中，有关于时间处理的内容主要在下面的链接页面中：
www.postgresql.org/docs/16/fun…
通过查阅这些相关技术文档，笔者觉得，Postgres对于时间和日期的支持还是比较完善和强大的，不仅仅是简单的获得当前时间或者进行字符串格式的转换，还有很多相关的技术，可以很好的满足各种各样的业务方面的需求。但主要的问题是，它的很多实现和处理，都是针对以英语为母语的应用的模式，对于中文和相关语境的支持并不是很好，这个就比较遗憾了。
首先从数据类型上而言，Postgres将时间相关的信息，分为date、time、timestamp和interval四种类型：
- date: 就是日期类型或者以字符串形式表达的日期
- time: 时间，和字符串形式的时间
- timestamp: 就是时间戳，一个完整的时间信息
- interval: 时间间隔，这是一个比较独特的数据类型，在postgres中它可以参与时间的计算
基于以上的数据类型，从形式上而言，Postgres对于时间和日期处理方面的支持，大致可以分为操作符和函数两种大的类型。其中，操作符就是直接使用我们都比较熟悉的数学运算符，但逻辑上来支持时间数据和信息的计算；而函数一般用于时间类型相关的处理。通用的相关时间信息和处理，主要包括下面一些操作：
- 获取当前的时间
- 获取过去或者未来的时间，就是基于某个特定时间(包括当前时间)和时间间隔，计算相对的未来或者过去时间点
- 计算两个时间之间的差异，并用时间间隔或者字符串表达
- 时间数据类型，和表示信息(格式化的时间字符串)之间的转换
- 从完整的时间信息中，提取特性的属性信息(如年、月、日、周日等等)，这通常是业务统计的需求
- 时区信息和相关的处理
- 延时或者定时操作和控制
这些内容，其实也是所有编程系统，对于时间进行操作和处理的一般方法论。这些操作，再结合比较丰富的数据类型和表现形式，postgres在时间信息的处理上，表现出来的功能特性，给人的感觉就非常灵活和强大。
下面我们就根据这些操作的分类，分别进行讨论。
如何获取当前时间
获取当前时间，是一个非常常见的应用需求。Postgres提供了很多种方式，可以获取和展示当前时间，它们有一些细微的差别，用于不同的场景当中。下面是一些简单的例子：
-- 时间戳，字符串形式
defaultdb=> SELECT clock_timestamp(), timeofday();
clock_timestamp | timeofday
-------------------------------+-------------------------------------
2024-09-02 09:14:17.376077+00 | Mon Sep 02 09:14:17.376078 2024 GMT
(1 row)
-- 语句和事务时间
defaultdb=> select statement_timestamp(),transaction_timestamp();
statement_timestamp | transaction_timestamp
-------------------------------+-------------------------------
2024-09-02 09:09:45.366577+00 | 2024-09-02 09:09:45.366577+00
(1 row)
-- 系统变量， 默认带时区，可以指定精度
defaultdb=> SELECT current_date, current_time, current_timestamp, current_timestamp(2) ;
current_date | current_time | current_timestamp | current_timestamp
--------------+--------------------+-------------------------------+---------------------------
2024-09-02 | 09:07:20.869468+00 | 2024-09-02 09:07:20.869468+00 | 2024-09-02 09:07:20.87+00
(1 row)
-- localtimestemp， 不带时区信息
defaultdb=> SELECT LOCALTIMESTAMP;
localtimestamp
----------------------------
2024-09-02 09:06:10.869189
(1 row)
-- now函数, now时间戳
defaultdb=> SELECT now(), TIMESTAMP 'now';
now | timestamp
-------------------------------+----------------------------
2024-09-02 09:11:23.570569+00 | 2024-09-02 09:11:23.570569
(1 row)
这里有一个小技巧，就是通过查看使用字符串表示的时间或者时间戳的时候，可以从其尾部是否附带时区(如+00表示UTC)，来判断这是否是一个带有时区信息的时间，也可以判断该时间附带的时区信息。
如何创建时间/日期/间隔/时间戳
在Postgres中，有很多种方式，可以从数据和信息中，创建时间/日期/间隔/时间戳，它们通常使用makexxx方法：
-- 创建日期
make_date(2013, 7, 15) → 2013-07-15
-- 创建时间
make_time(8, 15, 23.5) → 08:15:23.5
-- 创建时间戳
make_timestamp(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5
-- 基于时区创建时间戳
make_timestamptz(2013, 7, 15, 15, 15, 23.5, 'Asia/Shanghai') → 2013-07-15 07:15:23.5+00
-- 创建时间间隔
make_interval(years=>1,days => 10) → 1 year 10 days
-- 格式化字符串转换
defaultdb=> SELECT TO_TIMESTAMP('2023-10-02 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp;
timestamp
------------------------
2023-10-02 14:30:00+00
(1 row)
可以看到，大体有两种模式。一个是来自时间构成的数值，一个是来自时间字符串。前者所使用的函数，有其规范和格式，需要注意使用的参数和对应关系；后者本质上是使用一个模板字符串，来进行时间信息和其表示字符串之间进行的相互转换，这里涉及到两个方法，就是to_timestamp和to_char，关于模板字符串的使用，我们在后面的章节有更详细的探讨。
如何从时间对象中提取某些属性或者内容
在日常应用中，我们经常遇到需要从一个完整的时间信息中，提取一部分特定的信息，如日期，某种形式的时间表示等等，这时就需要将一个完整的时间信息进行解构。在Postgres中，通常使用extract函数来操作和实现，下面是一些常见的例子：
defaultdb=> with T(n,t) as (values (now(), '2023-03-11 17:43:17.436'))
SELECT EXTRACT(CENTURY FROM n ) c1 , EXTRACT(CENTURY FROM t::TIMESTAMP) c2,
EXTRACT(HOUR FROM n ) h1 , EXTRACT(HOUR FROM t::TIMESTAMP) h2
from T;
c1 | c2 | h1 | h2
----+----+----+----
21 | 21 | 2 | 17
我们可以将一个时间戳(字符串)看成一个时间对象，那么例如年份、小时数等等，就可以看成它的属性，extract就可以用于提取这些属性，它们可以使用一些关键字来指定属性的标识，相当的强大和丰富：
- Millennium： 千年，如当前(2024年)是3
- CENTURY: 世纪，如当前(2024)是21
- DECADE: 世纪中的十年，如2023年的十年份是202
- YEAR: 年份，如2023
- ISOYEAR： ISO纪年方式，它和标准年稍有差别(基于周纪年)，和公历年不完全一致
- QUARTER： 一年中的季度序号，1~4
- MONTH: 月份，一年中的月份序号，1~12
- WEEK：在年中的周数,1~53
- DAY: 月份中的天序号，1~31
- DOY: 年中的天序号，1~366
- DOW: 星期中的天，0~6，周日到周六
- ISODOW： ISO周天，1~7，周一到周日
- HOUR: 小时，一天内的小时数，0~23
- MINUTE: 分钟，小时内的分钟数，0~59
- SECOND： 秒，分钟内的秒数, 0~59
- Milliseconds： 毫秒，秒中的毫秒值
- Microseconds： 微秒，毫秒中的微秒值
- EPOCH: 获取指定时间距离标准起始时间(1970-1-1)过去的秒数
前面的例子，都是从一个时间戳，转换成为一个时间对象，然后提取相关的熟悉。extract函数，还支持另外一种形式，就是从一个时间间隔的表达字符串中，来进行提取，如下面的例子：
-- 直接使用字符串
defaultdb=> SELECT EXTRACT(DAY FROM INTERVAL '19 days 3 minute');
extract
---------
19
(1 row)
-- 转换类型
defaultdb=> with I (i) as ( values ('19 days 3 minute'))
SELECT EXTRACT(DAY FROM i::INTERVAL ) days,
EXTRACT(HOUR FROM i::INTERVAL ) hours,
EXTRACT(MINUTE FROM i::INTERVAL ) minutes from I;
days | hours | minutes
------+-------+---------
19 | 0 | 3
(1 row)
我们可以看到，extract函数的调用，有其相应的语法规则，并且可以处理TimeStamp和Interval两者类型的信息(字符串形式)。
在PG技术文档中，还提到有一个类似的函数date_part，也可以实现类型的功能。笔者简单研究了一下，初步的感觉就是和extract相比，两者都可以实现相同的功能。主要差异就是调用的方式，一个是子句形式，一个是标准的参数方式，不知道为什么这样设计。下面是一个简单的测试用例，读者可以体会一下：
defaultdb=> SELECT
EXTRACT(HOURS FROM INTERVAL '4 hours 3 minutes'),
date_part('hour', INTERVAL '4 hours 3 minutes');
extract | date_part
---------+-----------
4 | 4
(1 row)
如何将一个时间，转换成为数值形式的时间戳
在很多应用和计算过程当中，都需要使用数值形式，来对时间进行计算。方法是，取一个标准的起始时间，然后计算时间戳距离此时间点过去的时间数值，得到的数字，就可以来表示时间。在很多系统，如Unix中，都是使用这种处理方式，Postgres也沿用这个模式。它们设置的时间基点，就是1970-1-1，所有时间戳，都可以表示成为距离此基点过去的数值，单位为秒，数据类型可以是双精度或者浮点型(依据实现精度和范围)。由于数值计算的优势，我们也有理由认为，在系统内部，这些时间，也都是以数值形式进行存储和计算的，表示出来的时间戳字符串，只不过是方便人类理解而已。这里顺便提一下，在nodejs中，这个情况有点特殊，它的Date.now()方法结果是一个长整型，单位是ms。
所以，一个字符串形式的时间戳，如何转换成为对应的数值呢？
Postgres中，通常使用extract结合epoch来实现这一需求。epoch是“纪元”的意思，它用于获取某一个时间，距离标准起始时间(1970-1-1)过去的秒数(双精度类型)。也可以结合Interval类型计算以时间间隔字符串标识的时间区间的秒数。如：
-- 特定时间
defaultdb=> SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-03-11 17:43:17.436');
extract
-------------------
1678556597.436000
(1 row)
-- 当前时间
defaultdb=> SELECT EXTRACT(EPOCH FROM NOW());
extract
-------------------
1724988499.777206
(1 row)
-- 时间间隔
defaultdb=> SELECT EXTRACT(EPOCH FROM INTERVAL '2 day 10 hours');
extract
---------------
208800.000000
(1 row)
所有的时间，都可以简单用一个数值来表示，非常容易管理和计算。这个解决方案看起来很完美？在某些方面确实是这样的，但实际上还有很多工程和现实方面的问题。例如，在真实世界的时间中，还有闰秒的问题，所以，通过这种方法计算出来的数值，和真实的数值是有差异的，可能有些时间点被丢弃了(想象我们怎么表示闰秒)，有些时间点其实是重复的。当然，一般情况下，这种处理并不会太大的影响普通业务的应用，在绝大多数场景中，是可以被接受的，但作为开发者，我们应该知晓并理解这个情况。
如何计算有间隔的时间
在应用中，经常会需要计算和某个时间，间隔一段时间的另一个时间，如当前时间5天后的时间。下面是一个简单明了的示例：
-- 指定时间基点
defaultdb=> SELECT TIMESTAMP '2023-10-01 12:00:00' + INTERVAL '2 days 3 hours';
?column?
---------------------
2023-10-03 15:00:00
(1 row)
-- 将来时间
defaultdb=> SELECT NOW() + INTERVAL '2 days 3 hours';
?column?
-------------------------------
2024-09-01 06:37:31.581509+00
(1 row)
-- 过去的时间
defaultdb=> SELECT NOW() - INTERVAL '10 days';
?column?
-------------------------------
2024-08-20 03:46:22.957945+00
(1 row)
这里面有几个值得注意的地方：
- 时间计算的基点，可以是当前时间，使用now()获取，类型是一个时间戳
- 也可以是一个字符串形式表达的时间戳
- 时间间隔，是一个字符串形式的Interval
- 两者可以直接使用操作符进行计算，加或者减，也显示出Postgres操作符的灵活和强大
- 理论上，我们都可以将所有时间都转换成为时间整数进行计算，但这种方式更加直观并容易理解
除了简单直观的操作符之外，Postgres还提供了date_add和date_subtract来实现类似的功能，这些函数可能的附加的特性，就是对于时区计算的支持，有兴趣的读者可以查阅文档自行了解。
如何基于时间计算间隔
PG提供了一个age函数，用于计算两个时间之间的间隔，并以年龄的形式呈现出来。本质上，是将两个时间点的时间差，使用interval字符串的形式表达出来。
-- age函数
defaultdb=> SELECT age(timestamp '2015-01-15', timestamp '1972-12-28');
age
------------------
42 years 18 days
(1 row)
defaultdb=> SELECT age(timestamp '1973-01-25');
age
------------------------
51 years 7 mons 3 days
(1 row)
-- 操作符
defaultdb=> SELECT (timestamp '2015-01-15')- (timestamp '1972-12-28');
?column?
------------
15358 days
(1 row)
defaultdb=> SELECT (timestamp '1972-12-28') - (timestamp '2015-01-15');
?column?
-------------
-15358 days
(1 row)
-- 调整显示
defaultdb=> SELECT justify_interval ((timestamp '1972-12-28') - (timestamp '2015-01-15'));
justify_interval
----------------------------
-42 years -7 mons -28 days
(1 row)
如果有两个参数，age函数会计算两个参数日期之间的间隔；如果有一个参数，将会计算当前时间和参数日期之间的间隔。输出的结果是一个语义化的字符串，能够显示年、月、日的数量。
虽然名字叫做age，但其实这个函数有非常广泛的应用场景，所有需要对快速表示两个时间之间间隔的操作，都可以用这个方式来进行处理。比如可以显示人员或者用户的年龄，两个日期之间的间隔时间，事件过去的时间，历史报告和分析中时间的表述等等。
除了age函数之外，还有就是可以非常简单的使用操作符“-”来计算两个时间之间的间隔。
这里顺便提一下，当计算间隔的时候，通常有一个问题，就是它的结果，可能就是一个简单的时间间隔(如15338 days这种形式)，这时可以使用justify系列函数，可以对这个结果进行“调整”，转换成为更易读的形式，这些函数包括：
- justify_days ( interval ) → interval 调整日期显示
- justify_hours ( interval ) → interval 时间显示
- justify_interval ( interval ) → interval 综合调整
关于时区是怎样处理的
在时间的相关处理中，特别是对于在全球使用的应用而言，有一个比较麻烦的问题，就是需要考虑时区对于时间处理的影响。
笔者理解，默认情况下，所有的时间戳和生成时间，都是世界标准时(UTC)的，所以我们看到的时间戳，都带有时区的信息，但为+00时区，就是标准时。上面的例子中创建时间戳时，可以指定时区，但系统存储和记录时，会将其转换成为标准时的时间。
如果要在使用中，指定带有时区信息的时间戳，一般需要结合 "timestamp with time zone/ at time zone"等标识 和时区字符串标识进行操作。下面是一些示例：
-- 带有时区的时间，显示为标准时间
defaultdb=> select timestamp '2001-02-16 20:38:40' at time zone 'America/Denver';
timezone
------------------------
2001-02-17 03:38:40+00
(1 row)
-- 将一个时区时间，转换为另一个时区的时间
defaultdb=> select timestamp with time zone '2001-02-16 20:38:40-05'
at time zone 'America/Denver';
timezone
---------------------
2001-02-16 18:38:40
(1 row)
-- 只转换时间
defaultdb=> select time with time zone '05:34:17-05' at time zone 'UTC';
timezone
-------------
10:34:17+00
(1 row)
有一些函数，本身是支持时区参数的，在处理过程中也基于基于时区信息进行计算。如下面的例子：
date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')
→ 2001-02-16 13:00:00+00
这个例子中，需要对一个时间戳截断到天。使用date_trunc函数，这个函数有一个带时区处理的版本(三个参数)。这个处理的过程是：
- 原始时间戳， 02-16 20:38，标准时
- 指定时区转换时，使用时区为悉尼，+11
- 转换到悉尼时间，为 02-17 07:38
- 截断日期时间，为悉尼时间 02-17 00:00
- 再转换为UTC，为标准时 02-16 13:00
- 最终处理的结果为： 2001-02-16 13:00:00+00
Postgres中，可以使用标准字符串形式来表示时区，常见的时区和字符串包括：
- Asia/Shanghai 中国标准时CST，+08
- America/New_York 美国东部时间EDT -04
- US/Pacific PST 美国太平洋时间PST，-08
- Europe/London 英国标准时, +01
- Europe/Moscow 莫斯科标准时MST, +03
- Asia/Tokyo 日本标准时JST，+09
- Australia/Sydney 澳大利亚东部标准时AEST， +11
这些信息，可以在系统视图 pg_timezone_names中， 查询得到。
小结
本文探讨了在Postgres中，对于时间处理的相关操作，包括常见的操作场景，如获取当前时间，计算两个时间的间隔，基于基础时间和间隔，计算过去或者将来的时间点，并讨论和展示了常见的函数和使用方式。
转载自:https://juejin.cn/post/7410980982377529398