# 日期函数与 COALESCE

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

---

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 本章要点 {#learn}

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

## 示例 {#example}

```sql {num="41.1" caption="日期函数与 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')`。

## 运行示例 {#run}

运行方式：

```text
$ 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)
 ...
```

## 常见陷阱 {#pitfalls}

- `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 时，后续参数中的有副作用表达式不会被求值。

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

> [!NOTE] 动手练习
> 计算当前年份的第一天（`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'。

## 下载 {#downloads}

SQL 文件：[date-functions-and-coalesce.sql](/examples/source/date-functions-and-coalesce.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)