日期函数

导学

日期函数是业务 SQL 中使用频率最高的函数类别之一。计算年龄、统计月度报表、判断有效期……本节系统梳理 MySQL 5.7 的核心日期函数。

定义

日期函数：用于获取当前时间、提取日期分量、进行日期计算和格式化的函数。

获取当前时间

NOW、CURDATE、CURTIME、UNIX_TIMESTAMP

当前数据状态：

假设当前系统时间为 2026-06-10 15:30:00。

操作语句：

SELECT
    NOW() AS current_datetime,
    CURDATE() AS current_date,
    CURTIME() AS current_time,
    UNIX_TIMESTAMP() AS current_unix_ts,
    UNIX_TIMESTAMP('2026-06-10 15:30:00') AS specific_unix_ts;

操作后的数据状态：

current_datetime	current_date	current_time	current_unix_ts	specific_unix_ts
2026-06-10 15:30:00	2026-06-10	15:30:00	1718011800	1718011800

结果解读：

NOW() 返回当前日期时间 2026-06-10 15:30:00
CURDATE() 返回当前日期 2026-06-10
CURTIME() 返回当前时间 15:30:00
UNIX_TIMESTAMP() 返回当前时间的 Unix 时间戳（秒）
UNIX_TIMESTAMP('2026-06-10 15:30:00') 将指定时间转为时间戳

NOW() 和 SYSDATE() 的区别：NOW() 在一条 SQL 执行开始时确定，语句内多次调用返回相同值；SYSDATE() 每次调用都获取实时时间，可能导致主从复制问题。推荐用 NOW()。

NOW() 与 SYSDATE() 行为差异演示

SELECT
    NOW() AS first_now,
    SLEEP(2),
    NOW() AS second_now,
    SYSDATE() AS sysdate_after_sleep;

结果：

first_now	second_now	sysdate_after_sleep
2026-06-10 15:30:00	2026-06-10 15:30:00	2026-06-10 15:30:02

结果解读：SLEEP(2) 暂停 2 秒后，NOW() 两次返回的值相同（语句开始时间），而 SYSDATE() 返回了实时时间（晚了 2 秒）。在主从复制场景下，SYSDATE() 可能导致主库和从库执行结果不一致。

提取日期分量

YEAR、MONTH、DAY、DAYOFWEEK、WEEKDAY、QUARTER

当前数据状态：

以下用常量演示日期分量提取：

SELECT
    '2024-01-15' AS event_date,
    YEAR('2024-01-15') AS year_val,
    MONTH('2024-01-15') AS month_val,
    DAY('2024-01-15') AS day_val,
    DAYOFWEEK('2024-01-15') AS day_of_week,
    WEEKDAY('2024-01-15') AS weekday_val,
    QUARTER('2024-01-15') AS quarter_val
UNION ALL
SELECT
    '2024-06-10',
    YEAR('2024-06-10'),
    MONTH('2024-06-10'),
    DAY('2024-06-10'),
    DAYOFWEEK('2024-06-10'),
    WEEKDAY('2024-06-10'),
    QUARTER('2024-06-10');

操作后的数据状态：

event_date	year_val	month_val	day_val	day_of_week	weekday_val	quarter_val
2024-01-15	2024	1	15	2	0	1
2024-06-10	2024	6	10	2	0	2

结果解读：

YEAR、MONTH、DAY 分别提取年、月、日
DAYOFWEEK：周日=1，周一=2，... 周六=7。2024-01-15 是周一，所以为 2
WEEKDAY：周一=0，周二=1，... 周日=6。2024-01-15 是周一，所以为 0
QUARTER：返回季度，1 月属于第 1 季度，6 月属于第 2 季度

日期计算

DATE_ADD、DATE_SUB、DATEDIFF、TIMESTAMPDIFF、LAST_DAY

当前数据状态：

以下用常量演示日期计算：

SELECT
    '2024-01-15' AS start_date,
    '2024-06-10' AS end_date,
    DATE_ADD('2024-01-15', INTERVAL 10 DAY) AS plus_10_days,
    DATE_SUB('2024-06-10', INTERVAL 1 MONTH) AS minus_1_month,
    '2024-01-15' + INTERVAL 10 DAY AS operator_plus_10,
    DATEDIFF('2024-06-10', '2024-01-15') AS days_diff,
    TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-06-10') AS months_diff,
    LAST_DAY('2024-01-15') AS last_day_of_month
UNION ALL
SELECT
    '2024-03-01',
    '2024-03-31',
    DATE_ADD('2024-03-01', INTERVAL 10 DAY),
    DATE_SUB('2024-03-31', INTERVAL 1 MONTH),
    '2024-03-01' + INTERVAL 10 DAY,
    DATEDIFF('2024-03-31', '2024-03-01'),
    TIMESTAMPDIFF(MONTH, '2024-03-01', '2024-03-31'),
    LAST_DAY('2024-03-01');

操作后的数据状态：

start_date	end_date	plus_10_days	minus_1_month	operator_plus_10	days_diff	months_diff	last_day_of_month
2024-01-15	2024-06-10	2024-01-25	2024-05-10	2024-01-25	147	4	2024-01-31
2024-03-01	2024-03-31	2024-03-11	2024-02-29	2024-03-11	30	0	2024-03-31

结果解读：

DATE_ADD(start_date, INTERVAL 10 DAY)：2024-01-15 加 10 天 = 2024-01-25
DATE_SUB(end_date, INTERVAL 1 MONTH)：2024-03-31 减 1 个月 = 2024-02-29（闰年自动处理）
start_date + INTERVAL 10 DAY：运算符写法，效果与 DATE_ADD 相同
DATEDIFF(end_date, start_date)：两个日期之间的天数差，2024-01-15 到 2024-06-10 相差 147 天
TIMESTAMPDIFF(MONTH, start_date, end_date)：计算完整的月数差，2024-01-15 到 2024-06-10 为 4 个完整月
LAST_DAY(start_date)：返回当月最后一天，2024-01-15 对应 2024-01-31

格式化

DATE_FORMAT、STR_TO_DATE

当前数据状态：

以下用常量演示日期格式化：

SELECT
    '2024-01-15 09:30:00' AS meeting_time,
    DATE_FORMAT('2024-01-15 09:30:00', '%Y年%m月%d日') AS cn_date,
    DATE_FORMAT('2024-01-15 09:30:00', '%Y-%m-%d %H:%i:%s') AS standard_format,
    DATE_FORMAT('2024-01-15 09:30:00', '%W') AS weekday_name
UNION ALL
SELECT
    '2024-06-10 14:45:30',
    DATE_FORMAT('2024-06-10 14:45:30', '%Y年%m月%d日'),
    DATE_FORMAT('2024-06-10 14:45:30', '%Y-%m-%d %H:%i:%s'),
    DATE_FORMAT('2024-06-10 14:45:30', '%W');

操作后的数据状态：

meeting_time	cn_date	standard_format	weekday_name
2024-01-15 09:30:00	2024年01月15日	2024-01-15 09:30:00	Monday
2024-06-10 14:45:30	2024年06月10日	2024-06-10 14:45:30	Monday

结果解读：

DATE_FORMAT(meeting_time, '%Y年%m月%d日') 将日期格式化为中文年月日格式
DATE_FORMAT(meeting_time, '%Y-%m-%d %H:%i:%s') 输出标准格式
DATE_FORMAT(meeting_time, '%W') 返回英文星期名称

STR_TO_DATE 字符串转日期

SELECT
    STR_TO_DATE('15-01-2024', '%d-%m-%Y') AS parsed_date,
    STR_TO_DATE('2024/06/10', '%Y/%m/%d') AS parsed_date2;

结果：

parsed_date	parsed_date2
2024-01-15	2024-06-10

结果解读：STR_TO_DATE 按照指定格式将字符串解析为日期类型。第一个例子中 %d-%m-%Y 匹配 15-01-2024，第二个例子中 %Y/%m/%d 匹配 2024/06/10。

常用格式符：

格式符	含义	示例
`%Y`	四位年份	2024
`%m`	两位月份	01
`%d`	两位日期	15
`%H`	24小时制	15
`%i`	分钟	30
`%s`	秒	00
`%W`	星期名称	Monday

SQL 综合示例

场景一：计算精确年龄

当前数据状态：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2),
    birth_date DATE
);

INSERT INTO employees (emp_name, dept, score, birth_date) VALUES
('大翔', '技术部', 100, '2000-12-31'),
('白歌', '技术部', NULL, '2000-01-01');

假设当前日期为 2024-01-01。

当前表数据：

emp_id	emp_name	dept	score	birth_date
1	大翔	技术部	100	2000-12-31
2	白歌	技术部	NULL	2000-01-01

操作语句：

SELECT
    emp_id,
    emp_name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS exact_age,
    YEAR(CURDATE()) - YEAR(birth_date) AS wrong_age
FROM employees;

操作后的数据状态：

emp_id	emp_name	birth_date	exact_age	wrong_age
1	大翔	2000-12-31	23	24
2	白歌	2000-01-01	24	24

结果解读：

TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) 计算完整的年数差。大翔 2000-12-31 出生，在 2024-01-01 时只有 23 岁（生日还没到）
YEAR(CURDATE()) - YEAR(birth_date) 的简单计算得到 24，是错误的，因为它没有考虑月份和日期

场景二：查询本月注册的用户

当前数据状态：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2),
    created_at DATETIME
);

INSERT INTO employees (emp_name, dept, score, created_at) VALUES
('大翔', '技术部', 100, '2024-06-01 10:00:00'),
('白歌', '技术部', NULL, '2024-06-15 14:30:00');

假设当前日期为 2024-06-10。

当前表数据：

emp_id	emp_name	dept	score	created_at
1	大翔	技术部	100	2024-06-01 10:00:00
2	白歌	技术部	NULL	2024-06-15 14:30:00

推荐写法（可利用索引）：

SELECT
    emp_id,
    emp_name,
    created_at
FROM employees
WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
  AND created_at < DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01');

操作后的数据状态：

emp_id	emp_name	created_at
1	大翔	2024-06-01 10:00:00
2	白歌	2024-06-15 14:30:00

结果解读：

DATE_FORMAT(NOW(), '%Y-%m-01') 得到本月第一天 2024-06-01
DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01') 得到下个月第一天 2024-07-01
使用 >= 和 < 的范围查询可以利用 created_at 列上的索引

避免写法（函数作用于列，索引失效）：

-- 不推荐：会导致索引失效
SELECT * FROM employees WHERE MONTH(created_at) = 6;

场景三：查询最近 7 天内的订单

当前数据状态：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2),
    created_at DATETIME
);

INSERT INTO employees (emp_name, dept, score, created_at) VALUES
('大翔', '技术部', 100, '2026-06-03 10:00:00'),
('白歌', '技术部', NULL, '2026-06-10 09:00:00');

假设当前日期为 2026-06-10。

当前表数据：

emp_id	emp_name	dept	score	created_at
1	大翔	技术部	100	2026-06-03 10:00:00
2	白歌	技术部	NULL	2026-06-10 09:00:00

操作语句：

SELECT
    emp_id,
    emp_name,
    created_at,
    DATEDIFF(NOW(), created_at) AS days_ago
FROM employees
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

操作后的数据状态：

emp_id	emp_name	created_at	days_ago
1	大翔	2026-06-03 10:00:00	7
2	白歌	2026-06-10 09:00:00	0

结果解读：

DATE_SUB(NOW(), INTERVAL 7 DAY) 计算 7 天前的日期时间
created_at >= '2026-06-03 ...' 筛选出最近 7 天内的订单
白歌的订单是 2026-06-10，距今 0 天
DATEDIFF(NOW(), created_at) 显示每条订单距今多少天

场景四：生成月度报表

当前数据状态：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2),
    sale_date DATE
);

INSERT INTO employees (emp_name, dept, score, sale_date) VALUES
('大翔', '技术部', 100, '2024-01-05'),
('白歌', '技术部', NULL, '2024-01-20');

当前表数据：

emp_id	emp_name	dept	score	sale_date
1	大翔	技术部	100	2024-01-05
2	白歌	技术部	NULL	2024-01-20

操作语句：

SELECT
    DATE_FORMAT(sale_date, '%Y-%m') AS month,
    COUNT(*) AS sale_count,
    SUM(score) AS total_amount
FROM employees
GROUP BY DATE_FORMAT(sale_date, '%Y-%m')
ORDER BY month;

操作后的数据状态：

month	sale_count	total_amount
2024-01	2	100.00

结果解读：DATE_FORMAT(sale_date, '%Y-%m') 将日期按年月分组，统计每月的销售笔数和总金额。注意 score 为 NULL 的记录不参与 SUM 计算，所以总金额为 100.00。

常见误区

误区	正解
`MONTH(created_at) = 6` 可以用索引	函数作用于列会导致索引失效，应改写为范围条件。
`DATEDIFF` 返回带符号的月数/年数	`DATEDIFF` 只返回天数差。计算年月差用 `TIMESTAMPDIFF`。
`NOW()` 和 `SYSDATE()` 一样	`NOW()` 语句内固定，`SYSDATE()` 实时变化。主从复制场景用 `NOW()`。

面试考点

Q：如何查询"最近 30 天"的数据？

WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)。注意这是包含第 30 天的，如需精确到时分秒，根据业务调整。

Q：YEAR(CURDATE()) - YEAR(birth_date) 计算年龄有什么问题？

不考虑月份和日期。例如 2000-12-31 出生，在 2024-01-01 时，公式算 24 岁，实际只有 23 岁。正确做法是用 TIMESTAMPDIFF(YEAR, birth_date, CURDATE())。

Q：为什么 WHERE MONTH(created_at) = 1 AND YEAR(created_at) = 2024 不是好写法？

函数作用于 created_at 列，导致索引失效（无法使用 created_at 的索引）。应改写为 WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'。

小结

NOW() 获取当前日期时间，CURDATE() 获取当前日期
DATE_ADD / DATE_SUB 进行日期加减，DATEDIFF / TIMESTAMPDIFF 计算差值
避免在 WHERE 中对日期列使用函数，会导致索引失效
精确年龄用 TIMESTAMPDIFF(YEAR, birth, CURDATE())

下一章引子：掌握了基础查询和函数，接下来进入 SQL 的进阶领域——多表关联查询。