PostgreSQL - 日期/时间函数与运算符

PostgreSQL - 现代日期/时间函数和运算符

处理日期和时间是几乎每个应用程序中的关键任务。PostgreSQL 为此目的提供了一套强大而全面的数据类型、函数和运算符。本指南涵盖了处理时间数据的现代最佳实践。

最佳实践：使用时区感知类型

PostgreSQL 有两种主要的时间戳类型：timestamp（或 timestamp without time zone）和 timestamptz（或 timestamp with time zone）。

• timestamp：存储日期和时间，但没有时区信息。其值在没有外部上下文的情况下是模糊的。
• timestamptz：这是大多数应用程序的推荐类型。它在内部存储 UTC 时间戳。当您插入值时，它会根据会话的时区转换为 UTC。当您选择它时，它会转换回会话的时区。这使其在全球应用程序中具有健壮性。

获取当前时间

PostgreSQL 提供了多个函数来获取当前时间，每个函数都有特定的范围：

函数	描述	返回类型
NOW()	返回当前事务的开始时间。该值在事务内是稳定的。	timestamptz
CURRENT_TIMESTAMP	NOW() 的标准 SQL 等价物。	timestamptz
CURRENT_DATE	仅返回当前日期。	date
CURRENT_TIME	仅返回带时区的当前时间。	timetz
clock_timestamp()	返回实际的当前时间（挂钟时间）。其值即使在单个 SQL 命令中也会改变。	timestamptz

示例：NOW() 和 clock_timestamp() 之间的区别。

SELECT NOW(), pg_sleep(2), NOW();
-- 两次 NOW() 调用都将返回完全相同的时间戳。

SELECT clock_timestamp(), pg_sleep(2), clock_timestamp();
-- 第二个 clock_timestamp() 将比第一个晚大约 2 秒。

INTERVAL 的日期/时间算术

您可以使用 INTERVAL 类型对日期/时间值执行算术运算。这提供了一种清晰易读的方式来添加或减去时间段。

-- 获取从现在起 7 天后的时间戳
SELECT NOW() + INTERVAL '7 days';

-- 获取 3 个月前的日期
SELECT CURRENT_DATE - INTERVAL '3 months';

-- 一个更复杂的间隔
SELECT NOW() + INTERVAL '1 year 2 months 3 days 4 hours 5 minutes';

您还可以减去两个 timestamp 值以获得表示差异的 INTERVAL。

SELECT timestamp '2023-10-27 10:00:00' - timestamp '2023-10-26 08:30:00';

-- 结果：
--      interval
-- -------------------
--  1 day 01:30:00

提取信息：EXTRACT 和 DATE_PART

EXTRACT 函数允许您从日期/时间值中提取特定字段（如年、月、日、小时）。DATE_PART 是 PostgreSQL 的历史等价物。

语法：EXTRACT(field FROM source)

SELECT
    EXTRACT(YEAR FROM NOW()) AS current_year,
    EXTRACT(MONTH FROM NOW()) AS current_month,
    EXTRACT(DAY FROM NOW()) AS current_day,
    EXTRACT(DOW FROM NOW()) AS day_of_week; -- 0=星期日, 6=星期六

这对于聚合非常有用，例如，按年份的月份对销售数据进行分组。

-- 假设的销售表
-- SELECT EXTRACT(MONTH FROM sale_date) as sale_month, SUM(amount)
-- FROM sales
-- GROUP BY sale_month;

日期截断：DATE_TRUNC

DATE_TRUNC 是一个强大的函数，用于将时间戳“对齐”到特定精度。这对于将数据分组到时间桶中非常有用。

语法：DATE_TRUNC('field', source)

SELECT
    '2023-10-27 14:45:10'::timestamptz AS original_ts,
    DATE_TRUNC('hour', '2023-10-27 14:45:10'::timestamptz) AS truncated_to_hour,
    DATE_TRUNC('day', '2023-10-27 14:45:10'::timestamptz) AS truncated_to_day;

结果：

       original_ts       |   truncated_to_hour    |    truncated_to_day
-------------------------+------------------------+------------------------
 2023-10-27 14:45:10+XX | 2023-10-27 14:00:00+XX | 2023-10-27 00:00:00+XX

一个常见用例是创建用户注册的每日时间序列。

-- 假设的用户表
-- SELECT DATE_TRUNC('day', created_at) AS signup_date, COUNT(*)
-- FROM users
-- GROUP BY signup_date
-- ORDER BY signup_date;

格式化日期显示：TO_CHAR

虽然您应该始终将日期存储为原生日期/时间类型，但您通常需要将它们格式化为字符串以用于报告或 UI。TO_CHAR 是用于此目的的函数，它使用模板模式。

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS iso_format;
-- 结果: '2023-10-27 14:50:30'

SELECT TO_CHAR(NOW(), 'Day, DD Month YYYY') AS readable_format;
-- 结果: 'Friday   , 27 October 2023'

其他有用函数

• AGE(timestamp, timestamp)：计算两个时间戳之间的间隔，以人类可读的年、月、日组合表示。
• AGE(timestamp)：计算给定时间戳与当前事务开始时间 (NOW()) 之间的间隔。

SELECT AGE(timestamp '2023-10-27', timestamp '1990-04-10');
-- 结果: 33 years 6 mons 17 days