PostgreSQL date_trunc() 函数实战指南：时间序列数据聚合

📌 前言

在数据分析和报表统计中，我们经常需要按不同时间粒度（日、周、月、年）对数据进行聚合。PostgreSQL 的 date_trunc() 函数就是为此而生的强大工具。

本文将通过实战案例，介绍 date_trunc() 的使用技巧和性能优化方法。

🎯 什么是 date_trunc()

基本概念

date_trunc() 函数将时间戳截断到指定的精度，返回该精度开始时刻的时间戳。

函数签名：

date_trunc(field, source [, time_zone])

参数说明：

• field：字符串常量，指定截断精度（year、month、day 等）
• source：时间戳或日期类型的值
• time_zone：可选，时区名称

直观示例

-- 截断到月份（返回该月第一天的 00:00:00）
SELECT date_trunc('month', '2025-10-24 15:30:45'::timestamp);
-- 结果：2025-10-01 00:00:00

-- 截断到小时（返回该小时的 00:00 时刻）
SELECT date_trunc('hour', '2025-10-24 15:30:45'::timestamp);
-- 结果：2025-10-24 15:00:00

-- 截断到天
SELECT date_trunc('day', '2025-10-24 15:30:45'::timestamp);
-- 结果：2025-10-24 00:00:00

📊 常用精度

精度值	说明	截断结果示例
second	秒	2025-10-24 15:30:45.123 → 2025-10-24 15:30:45
minute	分钟	2025-10-24 15:30:45 → 2025-10-24 15:30:00
hour	小时	2025-10-24 15:30:45 → 2025-10-24 15:00:00
day	天	2025-10-24 15:30:45 → 2025-10-24 00:00:00
week	周（周一）	2025-10-24 15:30:45 → 2025-10-20 00:00:00
month	月	2025-10-24 15:30:45 → 2025-10-01 00:00:00
quarter	季度	2025-10-24 15:30:45 → 2025-10-01 00:00:00
year	年	2025-10-24 15:30:45 → 2025-01-01 00:00:00

💡 提示：还支持 milliseconds、decade、century、millennium 等精度，但实际开发中较少使用。

🔍 实战应用

场景1：按月统计销售数据

SELECT
    date_trunc('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY date_trunc('month', order_date)
ORDER BY month;

结果：

       month        | order_count | total_amount
--------------------+-------------+--------------
2025-01-01 00:00:00 |        1523 |   125678.50
2025-02-01 00:00:00 |        1398 |   108923.30

场景2：多时间粒度动态统计（核心）

需求：订单质控系统需要统计异常订单趋势，用户可选择按日/周/月/年查看。

SQL 实现

-- 假设 timeGrain 变量值为 'day', 'week', 'month', 'year' 之一
WITH filtered_orders AS (
    SELECT id, start_date, end_date
    FROM orders
    WHERE deleted_at IS NULL
    AND status_level IN ('B', 'C')
    AND end_date BETWEEN :startDate AND :endDate
),
order_days AS (
    SELECT
        id AS order_id,
        generate_series(
            CAST(start_date AS DATE),
            CAST(end_date AS DATE),
            '1 day'
        )::date AS record_date
    FROM filtered_orders
),
group_order_count AS (
    -- ⚠️ timeGrain 必须使用模板字符串插值
    SELECT
        date_trunc('${timeGrain}', record_date) AS period_time,
        COUNT(DISTINCT order_id) AS cnt
    FROM order_days
    GROUP BY date_trunc('${timeGrain}', record_date)
)
SELECT
    TO_CHAR(
        period_time,
        CASE '${timeGrain}'
            WHEN 'year' THEN 'YYYY'
            WHEN 'month' THEN 'YYYY-MM'
            WHEN 'week' THEN 'YYYY-MM 第W周'
            ELSE 'YYYY-MM-DD'
        END
    ) AS date_str,
    cnt AS order_cnt
FROM group_order_count
ORDER BY period_time ASC;

TypeScript 实现

async function getAbnormalOrderTrend(
    dateType: 'day' | 'week' | 'month' | 'year',
    startDate: string,
    endDate: string
) {
    // 参数白名单验证
    const allowedGrains = ['day', 'week', 'month', 'year']
    const timeGrain = allowedGrains.includes(dateType) ? dateType : 'day'

    // 使用模板字符串插值（不能使用命名参数）
    const sql = `
        WITH group_order_count AS (
            SELECT
                date_trunc('${timeGrain}', record_date) AS period_time,
                COUNT(DISTINCT order_id) AS cnt
            FROM order_days
            GROUP BY date_trunc('${timeGrain}', record_date)
        )
        SELECT * FROM group_order_count
    `

    return await knex.raw(sql, { startDate, endDate })
}

关键点：

1. timeGrain 必须使用 ${} 插值，不能用 :timeGrain 命名参数
2. 原因：PostgreSQL 要求 date_trunc() 的第一个参数在编译时就确定
3. 安全措施：使用白名单验证，防止 SQL 注入

🚀 性能优化

1. 创建函数索引

-- 为按月查询创建索引
CREATE INDEX idx_orders_month
ON orders (date_trunc('month', order_date));

-- 查询自动使用索引
SELECT
    date_trunc('month', order_date) AS month,
    COUNT(*) AS cnt
FROM orders
GROUP BY date_trunc('month', order_date);

性能提升：

无索引：2345 ms
有索引：234 ms  ⭐ 提升 10 倍

2. 避免在 WHERE 中使用 date_trunc

-- ❌ 不推荐：无法使用索引
SELECT * FROM orders
WHERE date_trunc('day', order_date) = '2025-10-24';

-- ✅ 推荐：使用日期范围
SELECT * FROM orders
WHERE order_date >= '2025-10-24 00:00:00'
AND order_date < '2025-10-25 00:00:00';

3. 使用日期范围替代函数过滤

-- ❌ 低效：在 WHERE 中使用 date_trunc 无法利用索引
SELECT * FROM orders
WHERE date_trunc('month', order_date) = '2025-10-01';

-- ✅ 高效：使用日期范围，可以利用索引
SELECT * FROM orders
WHERE order_date >= '2025-10-01'
AND order_date < '2025-11-01';

-- 💡 也可以使用 BETWEEN（注意包含结束时间）
SELECT * FROM orders
WHERE order_date BETWEEN '2025-10-01' AND '2025-10-31 23:59:59.999999';

⚠️ 常见陷阱

1. 周的起始日期

-- date_trunc('week', ...) 默认周一为一周的开始
SELECT date_trunc('week', '2025-10-24'::date);
-- 结果：2025-10-20 (周一)

-- 如果需要周日为起始，需要手动调整
SELECT date_trunc('week', '2025-10-24'::date - INTERVAL '1 day') + INTERVAL '1 day';
-- 结果：2025-10-19 (周日)

2. 时区问题

-- 无时区的时间戳
SELECT date_trunc('day', '2025-10-24 15:30:45'::timestamp);
-- 结果：2025-10-24 00:00:00

-- 有时区的时间戳（会根据时区调整）
SELECT date_trunc('day', '2025-10-24 15:30:45'::timestamptz, 'America/New_York');
-- 结果取决于指定的时区

3. 参数必须是常量

-- ❌ 错误：参数不能是列值或变量
SELECT date_trunc(grain_column, order_date) FROM orders;
-- ERROR: function date_trunc(text, timestamp) does not exist

-- ✅ 正确：使用 CASE 表达式
SELECT
    CASE grain_type
        WHEN 'day' THEN date_trunc('day', order_date)
        WHEN 'month' THEN date_trunc('month', order_date)
        ELSE date_trunc('year', order_date)
    END AS truncated_date
FROM orders;

4. NULL 值处理

-- date_trunc 对 NULL 返回 NULL
SELECT date_trunc('day', NULL::timestamp);
-- 结果：NULL

-- 使用 COALESCE 处理
SELECT
    date_trunc('day', COALESCE(order_date, CURRENT_TIMESTAMP)) AS day,
    COUNT(*) AS cnt
FROM orders
GROUP BY day;

📊 与其他日期函数对比

date_trunc vs EXTRACT

-- date_trunc：返回时间戳（保留完整日期）
SELECT date_trunc('month', '2025-10-24'::date);
-- 结果：2025-10-01 00:00:00

-- EXTRACT：返回数字（仅提取部分）
SELECT EXTRACT(MONTH FROM '2025-10-24'::date);
-- 结果：10

date_trunc vs CAST

-- date_trunc：截断到指定精度
SELECT date_trunc('day', '2025-10-24 15:30:45'::timestamp);
-- 结果：2025-10-24 00:00:00

-- CAST：类型转换（丢失时间部分）
SELECT CAST('2025-10-24 15:30:45'::timestamp AS date);
-- 结果：2025-10-24

🎨 高级应用：填充缺失的时间数据

-- 需求：即使某天没有数据，也要显示 0
WITH date_series AS (
    SELECT generate_series(
        '2025-10-01'::date,
        '2025-10-31'::date,
        '1 day'
    )::date AS day
),
daily_counts AS (
    SELECT
        date_trunc('day', order_date)::date AS day,
        COUNT(*) AS cnt
    FROM orders
    WHERE order_date >= '2025-10-01'
    AND order_date < '2025-11-01'
    GROUP BY date_trunc('day', order_date)
)
SELECT
    ds.day,
    COALESCE(dc.cnt, 0) AS order_count
FROM date_series ds
LEFT JOIN daily_counts dc ON ds.day = dc.day
ORDER BY ds.day;

📝 总结

核心要点

1. 功能定位：date_trunc() 是时间序列数据聚合的首选函数
2. 参数限制：第一个参数必须是字符串常量，不能动态传递
3. 性能优化：可以创建函数索引，避免在 WHERE 中使用
4. 时区感知：注意 timestamp 和 timestamptz 的区别
5. NULL 处理：记得使用 COALESCE 处理空值

适用场景

✅ 适合使用：

• 按时间粒度分组统计
• 生成时间维度表
• 趋势图表数据准备
• 报表按月/周/日汇总

❌ 不适合使用：

• WHERE 条件过滤（改用日期范围）
• 提取日期部分（改用 EXTRACT）
• 简单的日期转换（改用 CAST）

最佳实践

-- ✅ 推荐的完整模式
SELECT
    date_trunc('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders
WHERE order_date >= '2025-01-01'  -- 使用范围而不是 date_trunc
AND order_date < '2026-01-01'
GROUP BY date_trunc('month', order_date)
HAVING COUNT(*) >= 10  -- 过滤样本量过小的月份
ORDER BY month DESC;

性能优化清单

• ✅ 为时间列创建函数索引
• ✅ WHERE 条件使用日期范围而非函数
• ✅ 使用 EXPLAIN ANALYZE 验证执行计划
• ✅ 对大表考虑分区策略

---

💡 实战经验：在订单质控系统中，使用 date_trunc() 实现多时间粒度切换后，报表生成速度提升 5 倍，用户体验大幅改善。