上一篇我们介绍 PostgreSQL 中常用的数学函数和字符函数。现在我们继续学习常用的日期时间函数以及类型转换函数。

日期时间函数

PostgreSQL 提供了以下日期和时间运算的算术运算符。

PostgreSQL 还提供了大量用于日期和时间数据处理的函数。

计算时间间隔

age(timestamp, timestamp) 函数用于计算两个时间点之间的间隔，age(timestamp) 函数用于计算当前日期的凌晨 12 点到该时间点之间的间隔。

SELECT age(timestamp '2020-12-31', timestamp '2020-01-01'), age(timestamp '2020-01-01');
age            |age          |
---------------|-------------|
11 mons 30 days|2 mons 2 days|

2020 年 12 月 31 日到 2020 年 01 月 01 日之间有 11 个月零 30 天；今天（2020 年 03 月 03 日）距离 2020 年 01 月 01 日已经过了 2 个月零 2 天。

获取时间中的信息

date_part(text, timestamp) 和 extract(field from timestamp) 函数用于获取日期时间中的某一部分，例如年份、月份、小时等；date_part(text, interval) 和 extract(field from interval) 函数用于获取时间间隔中的某一部分。

SELECT date_part('year', timestamp '2020-03-03 20:38:40'), extract(year from timestamp '2020-03-03 20:38:40'),
       date_part('month', interval '1 years 5 months'), extract(month from interval '1 years 5 months');
date_part|date_part|date_part|date_part|
---------|---------|---------|---------|
2020|     2020|        5|        5|

通过返回字段的标题可以看出，extract 函数实际上也是调用了 date_part 函数，只是参数方式不同。这两个函数支持获取的信息包括：

• century，世纪；

• day，对于 timestamp，返回月份中的第几天；对于 interval，返回天数；

• decade，年份除以 10；

• dow，星期天（0）到星期六（6）；

• doy，一年中的第几天，（1 - 365/366）；

• epoch，对于 timestamp with time zone，返回从1970-01-01 00:00:00 UTC 到该时间的秒数；对于 date 和 timestamp，返回本地时间的 1970-01-01 00:00:00 到该时间的秒数；对于 interval，返回以秒数表示的该时间间隔；

• hour，小时（1 - 23）；

• isodow，ISO 8601 标准中的星期一（1）到星期天（7）；

• isoyear，ISO 8601 标准定义的日期所在的年份。每年从包含 1 月 4 日的星期一开始，2017 年 01 月 01 日属于 2016 年；

• microseconds，微秒，包含秒和小数秒在内的数字乘以 1000000；

• millennium，千年；

• milliseconds，毫秒，包含秒和小数秒在内的数字乘以 1000；

• minute，分钟，（0 - 59）；

• month，月份；

• quarter，季度，（1 - 4）；

• second，秒数，包含小数秒；

• timezone，UTC 时区，单位为秒；

• timezone_hour，UTC 时区中的小时部分；

• timezone_minute，UTC 时区中的分钟部分；

• week，ISO 8601 标准中的星期几，每年从第一个星期四所在的一周开始；

• year，年份。

截断日期/时间

date_trunc(field, source [, time_zone ]) 函数用于将 timestamp、timestamp with time zone、date、time 或者 interval 数据截断到指定的精度。

SELECT date_trunc('year', timestamp '2020-03-03 20:38:40'),
       date_trunc('day', timestamptz '2020-03-03 20:38:40+00', 'Asia/Shanghai'),
       date_trunc('hour', interval '2 days 3 hours 40 minutes');
date_trunc         |date_trunc         |date_trunc     |
-------------------|-------------------|---------------|
2020-01-01 00:00:00|2020-03-04 00:00:00|2 days 03:00:00|

date_trunc 函数支持以下截断精度：

• microseconds

• milliseconds

• second

• minute

• hour

• day

• week

• month

• quarter

• year

• decade

• century

• millennium

创建日期/时间

make_date(year int, month int, day int) 函数用于创建一个日期：

SELECT make_date(2020, 03, 15);
make_date |
----------|
2020-03-15|

make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) 函数通过指定年、月、日等信息创建一个时间间隔。

SELECT make_interval(days => 1, hours => 5);
make_interval |
--------------|
1 day 05:00:00|

make_time(hour int, min int, sec double precision) 函数通过指定小时、分钟和秒数创建一个时间。

SELECT make_time(1, 2, 30.5);
  make_time|
-----------|
01:02:30.5|

make_timestamp(year int, month int, day int, hour int, min int, sec double precision) 函数通过指定年、月、日、时、分、秒创建一个时间戳。

SELECT make_timestamp(2020, 3, 15, 8, 20, 23.5);
make_timestamp       |
---------------------|
2020-03-15 08:20:23.5|

make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ]) 函数通过指定年、月、日、时、分、秒创建一个带时区的时间戳。如果没有指定时区，使用当前时区。

SELECT make_timestamptz(2020, 3, 15, 8, 20, 23.5);
make_timestamptz        |
------------------------|
2020-03-15 08:20:23.5+08|

to_timestamp(double precision) 函数将 Unix 时间戳（自从 1970-01-01 00:00:00+00 以来的秒数）转换为 PostgreSQL 时间戳数据。

SELECT to_timestamp(1583152349);
to_timestamp          |
----------------------|
2020-03-02 20:32:29+08|

获取系统时间

PostgreQL 提供了大量用于获取系统当前日期和时间的函数，例如 current_date、current_time、current_timestamp、clock_timestamp()、localtimestamp、now()、statement_timestamp() 等；同时还支持延迟语句执行的 pg_sleep() 等函数。

时区转换

AT TIME ZONE 运算符用于将 timestamp without time zone、timestamp with time zone 以及 time with time zone 转换为指定时区中的时间。

SELECT TIMESTAMP '2020-03-03 20:38:40' AT TIME ZONE 'Asia/Shanghai',
TIMESTAMP WITH TIME ZONE '2020-03-03 20:38:40-05:00' AT TIME ZONE 'Asia/Shanghai',
TIME WITH TIME ZONE '20:38:40-05:00' AT TIME ZONE 'Asia/Shanghai';
timezone              |timezone           |timezone   |
----------------------|-------------------|-----------|
2020-03-03 20:38:40+08|2020-03-04 09:38:40|09:38:40+08|

timezone(zone, timestamp) 函数等价于 SQL 标准中的 timestamp AT TIME ZONE zone。

📝还有一些关于日期时间的函数，可以参考官方文档。

类型转换函数

类型转换函数用于将数据从一种类型转换为另一种类型。

CAST 函数

CAST ( expr AS data_type ) 函数用于将 expr 转换为 data_type 数据类型；PostgreSQL 类型转换运算符（::）也可以实现相同的功能。例如：

SELECT CAST ('15' AS INTEGER), '2020-03-15'::DATE;
int4|date      |
----|----------|
15|2020-03-15|

如果数据无法转换为指定的类型，将会返回错误：

SELECT CAST ('A15' AS INTEGER);
SQL 错误 [22P02]: 错误: 无效的类型 integer 输入语法: "A15"
  位置：14

to_date 函数

to_date(string, format) 函数用于将字符串 string 按照 format 格式转换为日期类型。

SELECT to_date('2020/03/15','YYYY/MM/DD');
to_date   |
----------|
2020-03-15|

其中，YYYY 代表 4 位数的年；MM 代表两位数的月；DD 代表两位数的日。更多的格式选项可以参考官方文档。

to_timestamp 函数

to_timestamp(string, format) 函数用于将字符串 string 按照 format 格式转换为 timestamp with time zone 类型。

SELECT to_timestamp('2020-03-15 19:08:00.678', 'YYYY-MM-DD HH24:MI:SS.MS');
to_timestamp              |
--------------------------|
2020-03-15 19:08:00.678+08|

其中，HH24 表示 24 小时制的小时；MI 表示分钟；SS 表示秒数；MS 表示毫秒数。

to_char 函数

to_char(expre, format) 函数用于将 timestamp、interval、integer、double precision 或者 numeric 类型的值转换为指定格式的字符串。

SELECT to_char(current_timestamp, 'HH24:MI:SS'),
       to_char(interval '5h 12m 30s', 'HH12:MI:SS'),
       to_char(-125.8, '999D99');
to_char |to_char |to_char|
--------|--------|-------|
21:30:22|05:12:30|-125.80|

其中，格式中的 9 代表数字位；D 代表小数点。关于数字的格式化选项可以参考官方文档。

to_number 函数

to_number(string, format) 函数用于将字符串转换为数字。

SELECT to_number('￥125.8', 'L999D9');
to_number|
---------|
125.8|

其中，格式字符串中的 L 表示本地货币符号。

隐式类型转换

除了显式使用类型转换函数或运算符之外，很多时候 PostgreSQL 会自动执行数据类型的隐式转换。

SELECT 1+'2', 'todo: '||current_timestamp;
?column?|?column?                          |
--------|----------------------------------|
3|todo:2020-03-09 21:49:49.370621+08|

下一篇我们将会介绍 PostgreSQL 中的子查询（Subquery）。