PostgreSQL 时间/日期函数和操作符（千字长文）

PostgreSQL 时间/日期函数和操作符：从入门到实战

在开发过程中，时间与日期的处理几乎是每个项目都无法回避的环节。无论是用户注册时间记录、订单创建时间比对，还是定时任务调度，都离不开对时间数据的精准操作。PostgreSQL 作为功能强大的开源数据库，其对时间/日期的支持堪称行业标杆。今天，我们就来深入聊聊 PostgreSQL 时间/日期函数和操作符，帮助你轻松应对各种时间相关需求。

什么是时间/日期数据类型？

在开始函数和操作符之前，先了解 PostgreSQL 支持的基本时间类型，它们就像“时间容器”，决定了你能存储什么精度的时间数据。

  • DATE：仅存储年月日，例如 2024-04-05
  • TIME：仅存储时分秒，例如 14:30:25
  • TIMESTAMP：同时包含日期和时间，例如 2024-04-05 14:30:25
  • TIMESTAMPTZ：带时区的时间戳，例如 2024-04-05 14:30:25+08
  • INTERVAL：表示时间间隔，比如 1 day 2 hours

💡 小贴士：使用 TIMESTAMPTZ 是最佳实践，尤其在涉及多地区用户时，它能自动处理时区转换，避免“时间错乱”的尴尬。

常用时间/日期函数详解

PostgreSQL 提供了丰富的内置函数来处理时间数据。我们从最常用的开始，一步步拆解。

获取当前时间

-- 获取当前时间戳（含时区）
SELECT NOW();

-- 获取当前日期（仅年月日）
SELECT CURRENT_DATE;

-- 获取当前时间（仅时分秒）
SELECT CURRENT_TIME;

✅ 注释：NOW() 是最常用函数，返回当前会话时间，包括时区信息。在日志记录、审计字段中非常实用。

时间加减运算

时间加减是处理日期的核心操作。你可以用 + 和 - 操作符，配合 INTERVAL 来实现。

-- 给当前时间加 3 天
SELECT NOW() + INTERVAL '3 days';

-- 给订单创建时间减 1 个月
SELECT order_date - INTERVAL '1 month' 
FROM orders 
WHERE order_id = 1001;

-- 加 2 小时 30 分钟
SELECT '2024-04-05 10:00:00'::TIMESTAMP + INTERVAL '2 hours 30 minutes';

✅ 注释：INTERVAL '3 days' 是标准写法，支持多种单位：year, month, day, hour, minute, second 等。这种写法清晰且不易出错。

时间比较与区间判断

在实际业务中，我们经常需要判断某个时间是否在某个区间内，比如“最近 7 天的订单”。

-- 查询最近 7 天内的订单
SELECT *
FROM orders
WHERE order_date >= NOW() - INTERVAL '7 days'
  AND order_date < NOW();

-- 判断某个时间是否在某个区间内（使用 BETWEEN）
SELECT *
FROM events
WHERE event_time BETWEEN '2024-04-01 00:00:00' AND '2024-04-30 23:59:59';

✅ 注释：BETWEEN 是闭区间，包含两端。建议用 >= 和 < 更精确，避免边界问题。比如 order_date < NOW() 比 BETWEEN 更安全。

时间格式化与提取

很多时候，我们需要把时间以特定格式输出，比如“2024年4月5日”或“14:30”。

-- 将时间格式化为中文格式
SELECT TO_CHAR(NOW(), 'YYYY年MM月DD日 HH24:MI:SS') AS formatted_time;

-- 提取年、月、日、小时
SELECT 
    EXTRACT(YEAR FROM NOW()) AS year,
    EXTRACT(MONTH FROM NOW()) AS month,
    EXTRACT(DAY FROM NOW()) AS day,
    EXTRACT(HOUR FROM NOW()) AS hour;

✅ 注释：TO_CHAR 使用格式化模板，YYYY 表示四位年份，MM 为两位月份，HH24 表示 24 小时制。EXTRACT 用于从时间中“抽取出”某个部分，就像从时间胶囊里取出某一段记忆。

时间差计算：INTERVAL 与 AGE 函数

当你需要计算两个时间之间的差距时，AGE 函数非常方便。

-- 计算两个时间的间隔（返回 INTERVAL 类型）
SELECT AGE('2024-04-10 14:30:00', '2024-04-05 10:00:00');

-- 计算用户注册至今的天数
SELECT EXTRACT(DAYS FROM AGE(NOW(), user_created_at)) AS days_since_register
FROM users
WHERE user_id = 101;

✅ 注释：AGE(end_time, start_time) 返回从 start_time 到 end_time 的时间差。注意参数顺序是 end, start，别搞反了。EXTRACT(DAYS FROM ...) 用于从 INTERVAL 中提取天数，适合做“用户活跃天数”统计。

常见时间/日期操作场景

下面通过几个真实业务场景，展示 PostgreSQL 时间/日期函数和操作符的实战价值。

场景一：自动计算订单超时时间

假设订单在创建后 24 小时内未支付则自动关闭。

-- 创建订单时设置超时时间
INSERT INTO orders (order_id, create_time, expire_time)
VALUES (1001, NOW(), NOW() + INTERVAL '24 hours');

-- 查询已超时的订单
SELECT order_id
FROM orders
WHERE expire_time < NOW();

✅ 注释：expire_time 字段存储了订单的过期时间，通过 NOW() + INTERVAL 动态生成。查询时只需与当前时间比较即可，逻辑清晰，性能高。

场景二：按周统计用户活跃

需要统计每周的新增用户数量。

-- 按周分组统计新增用户
SELECT 
    DATE_TRUNC('week', created_at) AS week_start,
    COUNT(*) AS new_users
FROM users
WHERE created_at >= NOW() - INTERVAL '6 months'
GROUP BY DATE_TRUNC('week', created_at)
ORDER BY week_start;

✅ 注释：DATE_TRUNC('week', ...) 会将时间截断到本周的起始日（星期一）。这在生成周报、月报时非常有用，能轻松实现“按周聚合”。

常见陷阱与注意事项

虽然 PostgreSQL 的时间功能强大，但初学者容易踩坑：

  1. 时区问题：使用 TIMESTAMP 时，若未指定时区，可能返回本地时间，导致跨时区问题。建议统一使用 TIMESTAMPTZ。
  2. 字符串转时间：使用 ::TIMESTAMP 转换时，确保字符串格式正确。例如 2024-04-05 14:30:00 可以，但 2024/04/05 会报错。
  3. 区间边界：BETWEEN 包含两端，若要精确控制“最近 7 天”，建议用 >= NOW() - INTERVAL '7 days' AND < NOW()。
  4. 性能优化：在时间字段上建立索引，尤其是 WHERE 子句中频繁使用时间比较时。

总结与建议

PostgreSQL 时间/日期函数和操作符功能强大、语法清晰，是处理时间数据的利器。从 NOW() 到 AGE()，从 INTERVAL 到 TO_CHAR，每一个函数都有明确的用途。掌握它们，不仅能提升开发效率，还能避免常见的时间逻辑错误。

📌 小结：

  • 优先使用 TIMESTAMPTZ 存储时间
  • 用 INTERVAL 进行时间加减
  • 用 EXTRACT 和 DATE_TRUNC 提取或分组时间
  • 用 TO_CHAR 格式化输出
  • 注意时区和区间边界问题

在实际项目中，建议将时间相关的逻辑封装成函数或视图，提升代码可维护性。只要多练习，你会发现，处理时间问题，其实就像整理一个有序的文件夹，井井有条，毫不混乱。

延伸思考

你是否遇到过“凌晨 0 点”订单数据异常的问题？这往往是因为时区转换未处理好。下次遇到类似问题，不妨用 AT TIME ZONE 显式转换时区，比如：

SELECT NOW() AT TIME ZONE 'Asia/Shanghai';

让时间真正“归位”。