SQL 数据分析 · 指南 · 2026年10月30日 · 11 分钟阅读

SQL 日期函数：提取年月日、按周按月截断、加减间隔与计算日期差

四类日期函数几乎能覆盖分析师遇到的所有任务。EXTRACT 从日期里取出一个部分（年、月、小时、星期几）。date_trunc 把时间戳向下取整到所在日、周或月的开始，按时间段分组就靠它。时间间隔（+ INTERVAL '2 hours'）把日期往前或往后挪。相减得到两个日期之间的时长。掌握这四样，大多数按时间统计的报表你都能写出来。

本文的每个例子都在 Nomto SQL 课程使用的航空公司数据库上运行：2026 年 7 月 12 日至 9 月 10 日的 33,121 个航班和 262,788 笔订单，时间均为莫斯科时间。查询使用 PostgreSQL 语法，在 DuckDB 里运行（Nomto SQL 练习场背后就是 DuckDB），每张结果表都是真实输出。PostgreSQL 和 DuckDB 函数名不同的地方，文中会特别说明。

DATE、TIMESTAMP 和 INTERVAL

干活的主要是三种类型：

在航空公司数据里，flights.scheduled_departure、flights.actual_departure 和 bookings.book_date 都是时间戳。要把时间戳变成日期，做类型转换即可：scheduled_departure::date（或者标准写法 CAST(scheduled_departure AS DATE)）。两种写法在 PostgreSQL 和 DuckDB 里都能用。

8 月头三天的每日航班数：

SELECT scheduled_departure::date AS day,
       COUNT(*) AS flights
FROM flights
WHERE scheduled_departure >= '2026-08-01'
  AND scheduled_departure <  '2026-08-04'
GROUP BY 1
ORDER BY 1;

注意这个筛选条件：「大于等于第一天的开始，小于最后一天的下一天的开始」。它直接作用在时间戳上，不用对列套任何函数，数据库也因此能用上索引。如果写 BETWEEN '2026-08-01' AND '2026-08-03'，范围会停在 8 月 3 日零点，悄悄丢掉几乎一整天。

EXTRACT：取出年、月、小时或星期几

EXTRACT(part FROM value) 以数字形式返回其中一个部分：

SELECT flight_no,
       scheduled_departure,
       EXTRACT(year  FROM scheduled_departure) AS yr,
       EXTRACT(month FROM scheduled_departure) AS mon,
       EXTRACT(day   FROM scheduled_departure) AS d,
       EXTRACT(hour  FROM scheduled_departure) AS hr,
       EXTRACT(dow   FROM scheduled_departure) AS dow
FROM flights
WHERE flight_id = 1;

2026 年 7 月 12 日是星期日，dow 返回 0：在 PostgreSQL 和 DuckDB 里，dow 都是从 0（星期日）到 6（星期六）。如果想要星期一 = 1、星期日 = 7，就用 isodow。其他常用的部分还有：quarter、week（ISO 周数）、minute 和 epoch（自 1970 年起的秒数，或者一段间隔的秒数，下文会讲）。

典型用法是按日期的某个部分分组。起飞最繁忙的几个小时：

SELECT EXTRACT(hour FROM scheduled_departure) AS hour,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY flights DESC
LIMIT 5;

按星期几统计，星期一排在最前：

SELECT EXTRACT(isodow FROM scheduled_departure) AS iso_dow,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY 1;

在这份航班计划里，星期五最清闲，星期日最忙。要注意这段时间不是整数周，所以有些星期几会比别的多出现一次。想公平比较，就除以区间内每个星期几出现的次数。

PostgreSQL 与 DuckDB 的差异。 两者都支持 EXTRACT(part FROM x) 和函数形式 date_part('part', x)。PostgreSQL 14+ 的 EXTRACT 返回 numeric，date_part 返回 double precision；DuckDB 返回整数。DuckDB 还有 year(x)、hour(x)、dayname(x)、monthname(x) 这类简写，PostgreSQL 没有。

date_trunc：按日、周或月分组

EXTRACT(month ...) 对每一年的 7 月都返回 7，数据跨年时就会出错。要按日历上的时间段分组，用 date_trunc('unit', value) 把时间戳向下取整到时间段的开始：

SELECT date_trunc('month', book_date) AS month,
       COUNT(*) AS bookings,
       ROUND(SUM(total_amount)) AS revenue
FROM bookings
GROUP BY 1
ORDER BY 1;

这里 6 月和 8 月都不是完整的月份：订单从 6 月 17 日开始，数据在 8 月 11 日截止。比较不同时间段之前，一定要先看看第一天和最后一天。

按周也是一样，date_trunc('week', ...) 在 PostgreSQL 和 DuckDB 里都以星期一作为一周的开始：

SELECT date_trunc('week', scheduled_departure) AS week,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY 1
LIMIT 5;

第一周只包含 7 月 12 日星期日这一天，所以只有 558 个航班。其他单位还有：'day'、'hour'、'quarter'、'year'。

对时间戳做 date_trunc，返回的还是时间戳。如果报表里想要干净的日期，再加上 ::date。注意，没有数据的时间段根本不会出现在结果里；如果输出里需要每一天或每个月（包括空的），就要先生成一张日历表，做法见日期序列指南。

加减时间间隔

给时间戳加上或减去一个 INTERVAL，就能把它挪动：

SELECT flight_no,
       scheduled_departure,
       scheduled_departure + INTERVAL '2 hours' AS plus_2h,
       scheduled_departure - INTERVAL '1 day'   AS minus_1d,
       CAST(scheduled_departure AS DATE) + 7    AS week_later
FROM flights
WHERE flight_id = 1;

给 DATE 加一个普通整数就是加天数，两个数据库都如此。对时间戳则要用间隔。

按月计算要小心，因为每个月的天数不同。1 月 31 日加一个月不会溢出到 3 月，而是停在 2 月的最后一天：

SELECT DATE '2026-01-31' + INTERVAL '1 month' AS a;

同样的思路可以求出某个月的最后一天：月初，加一个月，再减一天。

SELECT d,
       date_trunc('month', d) + INTERVAL '1 month' - INTERVAL '1 day' AS month_end,
       last_day(d) AS ld
FROM (SELECT DATE '2026-02-10' AS d);

last_day 在 DuckDB（以及 MySQL）里有，PostgreSQL 里没有；date_trunc 的写法到处都能用。

「最近 N 天」这类筛选也靠间隔来写。这份数据的快照时间是 2026 年 8 月 11 日 18:00，所以要用这个时刻代替 now()：WHERE book_date >= TIMESTAMP '2026-08-11 18:00' - INTERVAL '7 days'。在实时数据库上，now() 和 current_date 在 PostgreSQL 和 DuckDB 里都能用。

计算两个日期之间的天数和分钟数

两个时间戳相减得到一个间隔。起飞延误最久的航班：

SELECT flight_no,
       scheduled_departure,
       actual_departure,
       actual_departure - scheduled_departure AS delay,
       EXTRACT(epoch FROM actual_departure - scheduled_departure) / 60 AS delay_min
FROM flights
WHERE actual_departure IS NOT NULL
ORDER BY delay DESC
LIMIT 3;

04:37:00 这样的间隔读起来很直观，但没法求平均做成 KPI，也没法和数字比较。EXTRACT(epoch FROM interval) 把它变成秒数；除以 60 得到分钟，除以 3600 得到小时。这种写法在 PostgreSQL 和 DuckDB 里表现一致，是最稳妥的习惯。每月的平均延误：

SELECT date_trunc('month', scheduled_departure)::date AS month,
       COUNT(*) AS departed,
       ROUND(AVG(EXTRACT(epoch FROM actual_departure - scheduled_departure) / 60), 1) AS avg_delay_min
FROM flights
WHERE actual_departure IS NOT NULL
GROUP BY 1
ORDER BY 1;

要算整天数，就让两个日期相减。在两个数据库里，DATE - DATE 都返回整数天数。乘客提前多久订票？下面每一行是订单里的一个航段：

SELECT b.book_ref,
       b.book_date,
       f.scheduled_departure,
       f.scheduled_departure::date - b.book_date::date AS days_ahead
FROM bookings b
JOIN tickets t         USING (book_ref)
JOIN ticket_flights tf USING (ticket_no)
JOIN flights f         USING (flight_id)
ORDER BY b.book_ref, f.scheduled_departure
LIMIT 5;

再用 CASE 把天数分档，就得到一份提前预订时长的报表：

SELECT CASE WHEN days < 7  THEN '0-6 days'
            WHEN days < 14 THEN '7-13 days'
            WHEN days < 30 THEN '14-29 days'
            ELSE '30+ days' END AS booked_ahead,
       COUNT(*) AS segments
FROM (
  SELECT f.scheduled_departure::date - b.book_date::date AS days
  FROM bookings b
  JOIN tickets t         USING (book_ref)
  JOIN ticket_flights tf USING (ticket_no)
  JOIN flights f         USING (flight_id)
) s
GROUP BY 1
ORDER BY MIN(days);

CASE 分档的更多写法见 SQL CASE WHEN 用法。

date_diff、DATEDIFF 和 age

不同数据库提供的简写各不相同，复制来的查询也最容易在这里出错：

PostgreSQL 没有 date_diff 和 DATEDIFF，DuckDB 没有 to_char。age 返回按日历计算的间隔：age(TIMESTAMP '2026-08-11 18:00', TIMESTAMP '2026-06-17 00:00') 在 DuckDB 里得到 1 month 24 days 18:00:00（PostgreSQL 显示为 1 mon 24 days 18:00:00）。

DuckDB 的 date_diff 有个陷阱：它数的是跨过了多少个边界，而不是过去了多少个完整的单位：

SELECT date_diff('day', TIMESTAMP '2026-07-12 23:50', TIMESTAMP '2026-07-13 00:10') AS dd;

二十分钟，算作一「天」。如果要的是日历天数（10 号订票、24 号出发），这正是你想要的。如果要的是经过的时间（延误超过一天了吗？），就先算出间隔，再用 EXTRACT(epoch ...)。

常见错误

• 跨年时按 EXTRACT(month ...) 分组。 2025 年 7 月和 2026 年 7 月会落进同一组。改用 date_trunc('month', ...)。
• 对时间戳用 BETWEEN。 上限是最后一天开始时的零点。改用 >= 开始 AND < 下一段的开始。
• 在 WHERE 里给列套函数。 WHERE date_trunc('day', scheduled_departure) = '2026-08-01' 结果没错，但数据库必须对每一行都算一遍函数。对原始列写范围条件是等价的，而且能用上索引。
• 直接对间隔求平均做 KPI。 先用 EXTRACT(epoch ...) 换算成分钟或小时，数字才好读、好比较。
• 在快照数据上用 now()。 训练数据和历史数据有自己的「今天」。把快照时刻直接写进查询。
• 忘了时区。 这份数据里的所有时间都是莫斯科时间，存为不带时区的普通时间戳。在真实数据里，提取小时之前，先确认列是 timestamp 还是 timestamptz，以及事件是按哪个时区记录的。

日期通常和分组、累计求和一起出现，见 GROUP BY 和 HAVING 的区别和 SQL 累计求和。文本列有自己的一套工具，见 SQL 字符串函数。

常见问题

SQL 怎么从时间戳里只取日期？

做类型转换：scheduled_departure::date 或 CAST(scheduled_departure AS DATE)，两种写法在 PostgreSQL 和 DuckDB 里都能用。date_trunc('day', ...) 也能去掉时间部分，但保留时间戳类型。

EXTRACT 和 date_trunc 有什么区别？

EXTRACT 以数字形式返回日期的一个部分（7 月返回 7）。date_trunc 返回向下取整到时间段开始的完整时间戳（2026-07-01 00:00:00）。研究季节性规律（「哪个小时最忙？」）用 EXTRACT，做时间序列（「每月订单数」）用 date_trunc。

PostgreSQL 里怎么计算两个日期相差几天？

两个日期相减：end_date - start_date 返回整数。如果列是时间戳，要算日历天数就先转换（t2::date - t1::date），要算带小数的经过天数就用 EXTRACT(epoch FROM t2 - t1) / 86400。

date_trunc(‘week’) 从星期一还是星期日开始？

在 PostgreSQL 和 DuckDB 里都从星期一开始。如果需要以星期日开始的周，就把日期挪一下：date_trunc('week', d + INTERVAL '1 day') - INTERVAL '1 day'。

PostgreSQL 有 DATEADD 吗？

没有。改为加一个间隔：order_date + INTERVAL '3 days'，或者对 DATE 列直接 + 3。

其他语言： English, Español, Português (Brasil), Tiếng Việt