日期和时间戳函数

日期和时间戳无处不在：订单、登录、事件、账单。在 本课我们将了解如何从日期（年、月……）中提取部分， 将其截断到一定粒度（月、周……），计算间隔 和差异，并进行不同时期的比较。

基本类型

PostgreSQL 的区别：

• DATE — 仅日期，格式 YYYY-MM-DD；
• TIMESTAMP — 日期+时间，无时区；
• TIMESTAMPTZ — 日期+时间+时区；
• INTERVAL — 句点（例如 '3 days'、'2 hours 30 minutes'）。

在我们的 ecommerce 数据集中：

EXTRACT — 阅读一部分

EXTRACT(part FROM date) 提取一个数字：

SELECT EXTRACT(YEAR  FROM ordered_at) AS year,
       EXTRACT(MONTH FROM ordered_at) AS month,
       EXTRACT(DOW   FROM ordered_at) AS day_of_week  -- 0=domenica
FROM   orders;

最常用的部分：YEAR、MONTH、DAY、HOUR、MINUTE、DOW（天） 第 0-6 周），DOY（一年中的某一天）。

DATE_TRUNC — 向下舍入

DATE_TRUNC('part', date) 将日期“截断”到一定粒度。它是 按月、周、小时聚合的必要：

-- Tutti gli ordini fatti in "marzo 2025" cadono nello stesso bucket:
SELECT DATE_TRUNC('month', ordered_at) AS month,
       COUNT(*) AS order_count
FROM   orders
GROUP BY month
ORDER BY month;

有效部分：'year'、'quarter'、'month'、'week'、'day'、 'hour'、'minute'、'second'。

为了可读性，当不需要时间时，人们经常转换为 DATE ： DATE_TRUNC('month', ordered_at)::date。

差异和间隔

减去两个 TIMESTAMP 会产生 INTERVAL：

SELECT id,
       shipped_at - ordered_at AS wait_time
FROM   orders
WHERE  shipped_at IS NOT NULL;

与 INTERVAL 文字的比较：

WHERE  shipped_at - ordered_at > INTERVAL '1 day'

向日期添加天数：

SELECT signed_up_on + INTERVAL '30 days' AS trial_expiration
FROM   customers;

尝试一下

复习练习