41 日期函数与 COALESCE

将日期按时间桶分组、提取字段，并用 COALESCE 提供默认值。

PostgreSQL 提供了丰富的日期和时间运算。date_trunc 将时间截断到指定精度；extract 提取单个字段；interval 允许你加减时间间隔；date_part、age、to_char、to_date 涵盖其余需求。COALESCE 虽然并非日期专用，但很适合与日期逻辑搭配，用来为可能为空的值指定默认值。

本章要点

current_date、current_timestamp、now()、clock_timestamp()
date_trunc(unit, ts)：截断到年、月、日、小时等精度
extract(field FROM ts)：提取年、月、星期几等字段
interval 运算与格式化
使用 COALESCE 为 NULL 提供回退值

示例

示例 41.1 日期函数与 COALESCE

-- "Now" functions
SELECT current_date AS today, current_timestamp AS now_tz, clock_timestamp() AS wall_clock;

-- date_trunc: truncate to a precision
SELECT date_trunc('month', current_timestamp) AS month_start,
       date_trunc('day',   current_timestamp) AS day_start,
       date_trunc('hour',  current_timestamp) AS hour_start;

-- Interval arithmetic
SELECT current_date + interval '7 days'    AS in_a_week,
       current_date - interval '1 month'   AS a_month_ago,
       current_timestamp + interval '90 minutes' AS in_90m;

-- Difference between two timestamps as an interval
SELECT age(timestamptz '2025-01-01', timestamptz '2024-03-15') AS gap;

-- extract: pull a field
SELECT extract(year      FROM current_date) AS year,
       extract(month     FROM current_date) AS month,
       extract(dow       FROM current_date) AS day_of_week,  -- 0=Sunday
       extract(isodow    FROM current_date) AS iso_dow,      -- 1=Monday
       extract(week      FROM current_date) AS iso_week;

-- Formatting and parsing
SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS TZ') AS formatted,
       to_date('2024-01-15', 'YYYY-MM-DD')                    AS parsed;

-- COALESCE: first non-NULL
SELECT COALESCE(NULL, NULL, 'default') AS c1,
       COALESCE(NULL, 42)              AS c2,
       COALESCE(due_at, current_timestamp + interval '7 days') AS due_or_default
FROM tasks_example
ORDER BY id;

date_trunc('month', ts) 是分析中按月“分桶”的标准方式。结果是该月第一天午夜的 timestamp（或 timestamptz）。使用 extract(isodow FROM ts) 按星期几分组，使用 date_trunc('week', ts) 按日历周分组，或者使用 date_bin（PostgreSQL 14+）划分固定宽度的时间桶。

extract 返回 numeric（较早版本中为 double precision）。dow 字段以星期日为 0；isodow 以星期一为 1（与 ISO 8601 一致）。如果拿不准，isodow 更符合大多数“星期一是一周的开始”的约定。

age(end, start) 返回按年、月、日归一化的时间间隔，适合用于显示（“3 年、2 个月”）。如果需要原始差值，可以直接减去两个 timestamptz 值，得到以日、小时和秒表示的时间间隔。

COALESCE 按从左到右的顺序检查参数，并返回第一个非 NULL 值。它是处理“默认值”的标准工具：COALESCE(user.display_name, user.email, 'Anonymous')。

运行示例

运行方式：

$ psql -f source/date-functions-and-coalesce.sql postgres
    today    |          now_tz             |       wall_clock
-------------+-----------------------------+-----------------------------
 2026-xx-xx  | 2026-xx-xx xx:xx:xx.xxxxxx+00 | 2026-xx-xx xx:xx:xx.xxxxxx+00
(1 row)
 ...

常见陷阱

now() 返回事务开始时间。在长事务中，每次调用都会返回相同的值。需要真实的挂钟时间时，请使用 clock_timestamp()。
混用 timestamp 和 timestamptz 时，会静默使用会话的时区。请明确指定：在类型转换或显示时使用 at time zone 'UTC'，并尽可能将数据存储为 timestamptz。
date_trunc 返回与输入相同类型的值——date_trunc('day', timestamptz) 返回的是 timestamptz，而不是 date。如果需要 date，请进行类型转换。
在 PostgreSQL 17 中，extract(epoch FROM ts) 返回自 Unix 纪元起经过的 numeric 秒数；date_part('epoch', ts) 返回 double precision。与使用 Unix 时间的代码互操作时，这一点很有用。
COALESCE 使用短路求值，因此，当更早的参数非 NULL 时，后续参数中的有副作用表达式不会被求值。

提示

对于“下个月的第一天”，可以使用 date_trunc('month', current_date) + interval '1 month'。对于“本月最后一天”，可以使用 date_trunc('month', current_date) + interval '1 month' - interval '1 day'。这些习惯用法在报表查询中随处可见。

动手练习

计算当前年份的第一天（date_trunc('year', current_date)）。将时间戳格式化为 ISO 8601：to_char(current_timestamp, 'YYYY-MM-DD"T"HH24:MI:SSOF')。在针对 tasks_example 的 SELECT 查询中使用 COALESCE，显示 due_at 或 ’no deadline’。

41 日期函数与 COALESCE

本章要点

示例

运行示例

常见陷阱

下载