Marico's space
处理日期和时间这件事，看起来很简单，但当你盯着一个时间戳字段发愁，不知道为什么"最近30天"的查询结果总是莫名其妙时，就会意识到它其实相当棘手。日期和时间函数对于数据分析、报表生成以及任何涉及时间序列事件追踪的应用来说都至关重要——但不同数据库引擎之间的细微差异，连经验丰富的开发者也会栽跟头。
今天这篇文章，我们就来好好聊聊 PostgreSQL 和 MySQL 这两大主流数据库中最实用的日期/时间函数，全部都配上真实场景的例子。学完之后，你就知道怎么自信地按时间范围筛选、计算时长、按周期分组数据，以及避开那些最常见的坑了。
说实话，日期时间处理是我在代码审查中最常发现问题的领域之一。很多看似正确的查询，运行起来却要么性能拉胯，要么结果根本不对。所以掌握这些基础概念真的很有必要。
想想看有多少查询都跟时间有关："查看最近7天内的订单"、"计算用户成为会员的时长"、"按月份统计营收"。几乎所有有价值的业务问题都有一个时间维度。
真正的挑战在于，不同的 SQL 数据库并不讲同一套"日期方言"。PostgreSQL 有 DATE_TRUNC 和 AGE，MySQL 有 DATE_FORMAT 和 TIMESTAMPDIFF，标准 SQL 则给了我们 EXTRACT 和 CURRENT_TIMESTAMP。知道哪些函数可用、各自有什么差异，能帮你省下好几个小时的调试时间。
我们整个教程会用一个统一的示例：一个电商数据库，包含 customers（用户）和 orders（订单）两张表。
-- 示例表结构
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
joined_at TIMESTAMP NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
placed_at TIMESTAMP NOT NULL,
shipped_at TIMESTAMP,
total_amount DECIMAL(10,2)
);
最基本的需求："现在几点了？"主流数据库都支持这些函数，但语法略有不同：
-- PostgreSQL
SELECT NOW(); -- 带时区的时间戳
SELECT CURRENT_TIMESTAMP; -- 等同于NOW()，符合SQL标准
SELECT CURRENT_DATE; -- 仅日期
SELECT CURRENT_TIME; -- 仅时间
-- MySQL
SELECT NOW(); -- 当前日期时间
SELECT CURDATE(); -- 仅日期
SELECT CURTIME(); -- 仅时间
SELECT UTC_TIMESTAMP(); -- UTC时间（这个很实用！）
实战场景：查询今天的所有订单。
-- PostgreSQL
SELECT order_id, placed_at, total_amount
FROM orders
WHERE placed_at::date = CURRENT_DATE;
-- MySQL
SELECT order_id, placed_at, total_amount
FROM orders
WHERE DATE(placed_at) = CURDATE();
小贴士：把时间戳转成日期（或者用
DATE()函数）会强制数据库在比较前丢弃时间部分。否则placed_at = CURRENT_DATE这种写法会失败——因为时间戳2026-04-28 14:33:00和日期2026-04-28根本不相等。
有时候你只需要从时间戳里取出年份、月份或者小时。EXTRACT 是符合 SQL 标准的做法，PostgreSQL 和 MySQL 都支持：
-- 标准SQL语法 — PostgreSQL和MySQL通用
SELECT
order_id,
placed_at,
EXTRACT(YEAR FROM placed_at) AS order_year,
EXTRACT(MONTH FROM placed_at) AS order_month,
EXTRACT(DOW FROM placed_at) AS day_of_week, -- 0=周日（PostgreSQL）
EXTRACT(HOUR FROM placed_at) AS order_hour
FROM orders;
实战场景：按月分组统计订单，观察月度营收趋势。
SELECT
EXTRACT(YEAR FROM placed_at) AS yr,
EXTRACT(MONTH FROM placed_at) AS mo,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue
FROM orders
GROUP BY yr, mo
ORDER BY yr, mo;
MySQL 专属的快捷函数：MySQL 还提供了一些专用的提取函数，用起来更直观：
-- MySQL专用快捷函数
SELECT
YEAR(placed_at) AS order_year,
MONTH(placed_at) AS order_month,
DAY(placed_at) AS order_day,
HOUR(placed_at) AS order_hour,
DAYNAME(placed_at) AS weekday_name -- 比如 "Tuesday"
FROM orders;
加减时间是常见需求："最近30天的所有订单"、"未来7天内即将到期的订阅"等等。
-- PostgreSQL: 用INTERVAL关键字
SELECT * FROM orders
WHERE placed_at >= NOW() - INTERVAL '30 days';
-- MySQL: 用DATE_SUB()或者INTERVAL语法
SELECT * FROM orders
WHERE placed_at >= NOW() - INTERVAL 30 DAY;
-- MySQL另一种写法：
SELECT * FROM orders
WHERE placed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);
INTERVAL 支持多种时间单位：
-- PostgreSQL示例
NOW() + INTERVAL '1 year'
NOW() - INTERVAL '3 months'
NOW() + INTERVAL '2 hours 30 minutes'
'2026-01-01'::date + INTERVAL '90 days'
-- MySQL示例
DATE_ADD(NOW(), INTERVAL 1 YEAR)
DATE_ADD(NOW(), INTERVAL -3 MONTH)
DATE_ADD(shipped_at, INTERVAL 14 DAY) -- 预计送达日期
实战场景：标记发货超过14天但没有后续跟进记录的订单。
-- PostgreSQL
SELECT order_id, customer_id, shipped_at
FROM orders
WHERE shipped_at IS NOT NULL
AND shipped_at < NOW() - INTERVAL '14 days';
两个日期相差多少天？用户成为会员多久了？这些问题需要日期差值函数来解决。
-- MySQL: DATEDIFF返回天数差
SELECT
order_id,
placed_at,
shipped_at,
DATEDIFF(shipped_at, placed_at) AS days_to_ship
FROM orders
WHERE shipped_at IS NOT NULL;
TIMESTAMPDIFF 更灵活——你可以指定返回的单位：
-- MySQL: 自行指定单位
SELECT
c.customer_id,
c.name,
c.joined_at,
TIMESTAMPDIFF(YEAR, c.joined_at, NOW()) AS years_as_customer,
TIMESTAMPDIFF(MONTH, c.joined_at, NOW()) AS months_as_customer,
TIMESTAMPDIFF(DAY, c.joined_at, NOW()) AS days_as_customer
FROM customers c;
PostgreSQL 有一个优雅的 AGE() 函数，返回人类可读的间隔：
-- PostgreSQL
SELECT
customer_id,
name,
joined_at,
AGE(NOW(), joined_at) AS customer_age, -- 比如 "2 years 3 mons 12 days"
EXTRACT(DAY FROM AGE(NOW(), joined_at)) AS days_as_customer
FROM customers;
实战场景：找出注册超过1年但最近90天内没有任何订单的用户（适合做唤醒营销的目标群体）。
-- PostgreSQL
SELECT
c.customer_id,
c.name,
c.joined_at,
MAX(o.placed_at) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.joined_at < NOW() - INTERVAL '1 year'
GROUP BY c.customer_id, c.name, c.joined_at
HAVING MAX(o.placed_at) < NOW() - INTERVAL '90 days'
OR MAX(o.placed_at) IS NULL;
DATE_TRUNC 是 PostgreSQL 的一大神器，专门用于按时间段分组数据。它会把时间戳向下取整到指定周期的起始点：
SELECT DATE_TRUNC('month', NOW());
-- 结果: 2026-04-01 00:00:00+00
SELECT DATE_TRUNC('week', NOW());
-- 结果: 2026-04-27 00:00:00+00（本周第一天，周一）
SELECT DATE_TRUNC('year', NOW());
-- 结果: 2026-01-01 00:00:00+00
支持的时间精度包括：microseconds、milliseconds、second、minute、hour、day、week、month、quarter、year、decade、century。
实战场景：生成周报，按周对齐统计营收：
-- PostgreSQL
SELECT
DATE_TRUNC('week', placed_at) AS week_start,
COUNT(*) AS orders,
SUM(total_amount) AS revenue,
AVG(total_amount) AS avg_order_value
FROM orders
WHERE placed_at >= NOW() - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', placed_at)
ORDER BY week_start;
在 MySQL 中，可以用 DATE_FORMAT 近似实现：
-- MySQL: 用DATE_FORMAT按周分组
SELECT
DATE_FORMAT(placed_at, '%Y-%u') AS year_week, -- 比如 "2026-17"
COUNT(*) AS orders,
SUM(total_amount) AS revenue
FROM orders
WHERE placed_at >= DATE_SUB(NOW(), INTERVAL 12 WEEK)
GROUP BY DATE_FORMAT(placed_at, '%Y-%u')
ORDER BY year_week;
原始时间戳对用户不友好。需要在报表和接口中做格式化：
-- PostgreSQL: TO_CHAR
SELECT TO_CHAR(placed_at, 'Month DD, YYYY') AS display_date; -- "April 28, 2026"
SELECT TO_CHAR(placed_at, 'YYYY-MM-DD HH24:MI') AS display_dt; -- "2026-04-28 14:33"
SELECT TO_CHAR(placed_at, 'Day') AS weekday; -- "Tuesday"
-- MySQL: DATE_FORMAT
SELECT DATE_FORMAT(placed_at, '%M %d, %Y') AS display_date; -- "April 28, 2026"
SELECT DATE_FORMAT(placed_at, '%Y-%m-%d %H:%i') AS display_dt; -- "2026-04-28 14:33"
SELECT DATE_FORMAT(placed_at, '%W') AS weekday; -- "Tuesday"
1. 对索引列使用函数会让查询性能崩掉
如果 placed_at 上有索引，下面这种写法就完全用不上索引：
-- ❌ 对列套函数会阻止索引生效
WHERE DATE(placed_at) = CURDATE() -- MySQL
WHERE DATE_TRUNC('day', placed_at) = ... -- PostgreSQL
正确做法是用范围比较，让列保持原样：
-- ✅ 范围过滤 — placed_at上的索引可以正常使用
WHERE placed_at >= '2026-04-28 00:00:00'
AND placed_at < '2026-04-29 00:00:00'
-- 或者动态写法：
WHERE placed_at >= CURRENT_DATE
AND placed_at < CURRENT_DATE + INTERVAL '1 day'
2. 忽略时区问题
PostgreSQL 的 NOW() 返回的是 timestamptz（带时区）。如果你的应用存的是 UTC 时间，但数据库会话用的是本地时区，结果可能会大相径庭。一定要明确指定：
-- PostgreSQL: 强制使用UTC
WHERE placed_at >= NOW() AT TIME ZONE 'UTC' - INTERVAL '24 hours'
在 MySQL 中，需要 UTC 时间时用 UTC_TIMESTAMP() 而不是 NOW()，这样不受会话设置影响。
3. NULL 的 shipped_at 会让时长计算出岔子
如果 shipped_at 是 NULL（订单还没发货），用 DATEDIFF 或直接相减都会返回 NULL——不是报错，就是静默的 NULL 传播。一定要做处理：
-- MySQL
SELECT
order_id,
COALESCE(DATEDIFF(shipped_at, placed_at), 'Not yet shipped') AS fulfillment_days
FROM orders;
4. DATEDIFF 的参数顺序因数据库而异
DATEDIFF(结束日期, 开始日期) → 结束在开始之后则为正DATEDIFF(单位, 开始日期, 结束日期) → 注意单位在最前面！结束日期 - 开始日期在数据库之间切换的开发者经常在这里翻车。
在 SQL 中处理日期是一项通用技能，几乎对你写的每一条分析或报表查询都有帮助。需要记住的核心概念：
NOW() / CURRENT_TIMESTAMP 获取当前时刻；用 CURRENT_DATE 获取今天的日期EXTRACT() 提取特定日期部分（年、月、周几）——符合 SQL 标准INTERVAL 做日期运算——WHERE 子句中保持索引列干净，不要套函数DATEDIFF / TIMESTAMPDIFF，PostgreSQL 用 AGE() 或直接相减来处理时长计算DATE_TRUNC 是按周/月/季度分组的好帮手DATE_FORMAT，PostgreSQL 用 TO_CHAR 来格式化日期展示日期函数看起来平平无奇，但威力其实被大大低估了。一旦你熟练掌握了 DATE_TRUNC 做时间序列聚合，或者用 TIMESTAMPDIFF 做用户群组分析，你的 SQL 查询就能从"能用"进化到"真正有洞察力"。
欢迎在评论区留言——你平时最常用哪种日期/时间查询模式？很想看看大家都在解决什么样的实际问题！如果觉得这篇文章有用，转发给身边正在跟"最近30天"筛选条件较劲的同事吧。