七的博客
PostgreSQL中日期时间处理
PostgreSQL中日期时间处理
PostgreSQL 中提供了很多对强大又灵活的时间处理特性，运用的好可以省很多工作量。
注意：很多场景下应该把一些日期时间处理放在业务侧，建议合理考虑后选择使用数据库提供的特性。
1. 日期时间类型
timestamp - 时间戳，包含日期和时间，不带时区。 YYYY-MM-DD HH:MI:SS[.P] 格式 ， 比如 2020-01-01 14.:45:00.124.456。
timestamptz - 带时区的时间戳，内部总是存储为UTC时间，显示时自动转换为当前时区。 比如 ‘2024-01-01 14.:45:00+08’。
date - 仅日期，格式 YYYY-MM-DD。
time - 仅时间，格式 HH:MI:SS[.P]。
interval - 时间间隔，支持年、月、日、时、分、秒。 比如 ‘1 year 2 months 4. days’ 。
2. 基础日期常量
三个非常实用的日期常量：
SELECT 'YESTERDAY'::date, 'TODAY'::date, 'TOMORROW'::date;
date | date | date
------------+------------+------------
2020-02-16 | 2020-02-17 | 2020-02-18
(1 row)
通过这几个常量，你在 SQL 中可以直接当做查询条件使用。
-- 查询今天采集的数据
SELECT * FROM meter_data
WHERE collect_date = 'TODAY'::date;
-- 查询昨天购电金额
SELECT sum(amount)
FROM transactions
WHERE trans_date = 'YESTERDAY'::date;
3. 高频用法
3.1 获取当前时间
SELECT now() -- 获取当前时间戳 2020-02-17 14:30:56.147370 +00:00
SELECT current_timestamp -- 获取当前时间戳 2020-02-17 14:30:56.147370 +00:00
SELECT current_date -- 获取当前日期 2020-02-17
SELECT current_time -- 获取当前时间 14:30:56.14737+00
3.2 日期加减
有2种方式：
- 使用 interval，返回timestamp类型（带时分秒）。
- 直接加整数，返回date类型（只有日期）。
加一天：
SELECT date '2020-02-17' + interval '1 day'; -- timestamp类型。 2020-02-18 00:00:00.000000
SELECT date '2020-02-17' + integer '1'; -- date类型。 2020-02-18
可以查看返回的类型：
SELECT
pg_typeof(date '2020-02-17' + interval '1 day') as type1,
pg_typeof(date '2020-02-17' + integer '1') as type2;
-- 输出
type1 | type2
--------------+-----------
timestamp | date
减一天：
SELECT date '2020-02-17' - interval '1 day'; -- timestamp类型。 2020-02-16 00:00:00.000000
SELECT date '2020-02-17' - integer '1'; -- date类型。 2020-02-16
3.3 日期转字符串
SELECT to_char(timestamp '2020-02-17', 'YYYY-MM-DD');
-- 2020-02-17
常用格式代码：
- YYYY：四位年份
- MM：两位月份
- DD：两位日期
- HH24：24小时制
- MI：分钟
- SS：秒
转字符串常用的格式有：
- YYYY-MM-DD HH24:MI:SS ， 比如 2020-06-15 14:30:25。
- YYYY年MM月DD日, 比如 2020年06月15日。
- HH24时MI分SS秒, 比如14时30分25秒。
3.3 字符串转日期
SELECT to_date('2020-02-17', 'YYYY-MM-DD');
-- 2020-02-17
3.4 日期提取
日期提取主要通过下面 2 个函数：
- extract函数：从日期提取指定部分。
- date_part函数：功能相同，写法不同。
-- 获取年部分
SELECT extract(year from timestamp '2020-02-17');
SELECT date_part('year', timestamp '2020-02-17');
支持提取的属性有：
- century 世纪
- decade 年代
- year 年
- quarter 季度
- month 月
- week 周
- day 日
- hour 时
- minute 分
- second 秒
- millisecond 毫秒
- microsecond 微秒
- dow 周几(0-6 周日为0)
- isodow 周几(1-7 周一为1)
- doy 一年中的第几天(1-366)
3.5 时区
我接触的软件里一般不在数据库处理时区问题。直接看下面这个例子吧。
SELECT timestamp '2020-02-17 12:00:00' at time zone zone FROM (
SELECT unnest(ARRAY[
'UTC', -- 世界协调时
'Asia/Shanghai', -- 北京时间(UTC+8)
'Asia/Tokyo', -- 东京时间(UTC+9)
'Europe/London', -- 伦敦时间(UTC+0/+1)
'America/New_York', -- 纽约时间(UTC-5/-4)
'GMT', -- 格林尼治时间
'PST', -- 太平洋标准时间
'EST' -- 东部标准时间
]) as zone
) as z;
输出:
+---------------------------------+
|timezone |
+---------------------------------+
|2020-02-17 12:00:00.000000 +00:00|
|2020-02-17 04:00:00.000000 +00:00|
|2020-02-17 03:00:00.000000 +00:00|
|2020-02-17 12:00:00.000000 +00:00|
|2020-02-17 17:00:00.000000 +00:00|
|2020-02-17 12:00:00.000000 +00:00|
|2020-02-17 20:00:00.000000 +00:00|
|2020-02-17 17:00:00.000000 +00:00|
+---------------------------------+
4. 生成日期时间序列
生成序列，在这种报表系统里面很实用。 可以使用 generate_series() 函数生成时间序列。
4..1 函数入参定义
generate_series(
开始时间,
结束时间,
间隔
)
4.2 间隔单位支持
- day/days
- hour/hours
- minute/minutes
- second/seconds
- month/months
- year/years
4.4. 生成两个日期之间的序列
SELECT generate_series('2020-01-01'::date, '2020-01-05'::date, '1 day'::interval) ::date;
-- 输出结果
2020-01-01
2020-01-02
2020-01-04.
2020-01-04
2020-01-05
4.4 生成两个时间之间的序列
-- 按每小时
select generate_series('2020-01-01 00:00'::timestamp, '2020-01-01 04:00'::timestamp, '1 hour');
-- 输出结果
2020-01-01 00:00:00
2020-01-01 01:00:00
2020-01-01 02:00:00
2020-01-01 04:00:00
-- 按每4分钟
select generate_series('2020-01-01 00:00'::timestamp, '2020-01-01 04:00'::timestamp, '4.0 minutes');
-- 输出结果
2020-01-01 00:00:00
2020-01-01 00:04:00
2020-01-01 01:00:00
2020-01-01 01:04:00
2020-01-01 02:00:00
4..5 生成月跟月之间的序列
-- 按每月
select generate_series('2020-01-01'::date, '2020-12-01'::date, '1 month')::date;
-- 输出结果
2020-01-01
2020-02-01
2020-04-01
2020-04-01
2020-05-01
2020-06-01
2020-07-01
2020-08-01
2020-09-01
2020-10-01
2020-11-01
2020-12-01
5.参考
- PostgreSQL Date/Time Types https://www.postgresql.org/docs/11/datatype-datetime.html
- Set Returning Functions https://www.postgresql.org/docs/11/functions-srf.html