我的个人技术笔记

日期时间处理

7 min read

日期时间处理

MySQL 提供了丰富的日期时间函数，可以灵活地进行获取、提取、运算、格式化和转换。下面按功能分类总结，并附上常用的计算场景和示例。

1. 获取当前日期和时间

函数说明
NOW()当前日期时间（语句开始时确定）
CURDATE()当前日期
CURTIME()当前时间
SYSDATE()执行时的实时日期时间
UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP()返回 UTC 时间
SELECT NOW();                     -- 2026-05-26 14:30:00
SELECT CURDATE();                 -- 2026-05-26
SELECT CURTIME();                 -- 14:30:00
SELECT UTC_TIMESTAMP();           -- 2026-05-26 06:30:00

2. 提取日期/时间分量

函数说明
YEAR(date), MONTH(date), DAY(date)年、月、日
HOUR(time), MINUTE(time), SECOND(time)时、分、秒
QUARTER(date)季度（1-4）
MONTHNAME(date), DAYNAME(date)月份名、星期名
DAYOFWEEK(date)星期几（1=周日,7=周六）
WEEKDAY(date)星期几（0=周一,6=周日）
DAYOFYEAR(date)一年中的第几天
WEEK(date[,mode])周数
EXTRACT(unit FROM date)灵活提取，如 YEAR_MONTH
SELECT YEAR('2026-05-26');         -- 2026
SELECT MONTHNAME('2026-05-26');    -- May
SELECT DAYOFWEEK('2026-05-26');    -- 3 (周二)
SELECT WEEKDAY('2026-05-26');      -- 1 (周二)
SELECT EXTRACT(YEAR_MONTH FROM '2026-05-26'); -- 202605

3. 日期/时间运算

函数说明
DATE_ADD(date, INTERVAL expr unit)日期加
DATE_SUB(date, INTERVAL expr unit)日期减
ADDDATE(date, INTERVAL expr unit) 或 ADDDATE(date, days)与上面相同（两用法）
SUBDATE(date, INTERVAL expr unit) 或 SUBDATE(date, days)相减
date + INTERVAL expr unit / date - INTERVAL expr unit直接运算符
DATEDIFF(date1, date2)日期差（天数）
TIMEDIFF(time1, time2)时间差
TIMESTAMPDIFF(unit, dt1, dt2)按单位返回差值
PERIOD_ADD(period, n)给 YYYYMM 加 n 个月
PERIOD_DIFF(p1, p2)两个 YYYYMM 相差月数

常用 unit：MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
复合 unit：YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND 等。

SELECT DATE_ADD('2026-05-26', INTERVAL 1 MONTH);   -- 2026-06-26
SELECT DATE_SUB('2026-05-26', INTERVAL 10 DAY);    -- 2026-05-16
SELECT '2026-05-26' + INTERVAL 1 YEAR;             -- 2027-05-26
SELECT DATEDIFF('2026-06-01', '2026-05-26');       -- 6
SELECT TIMESTAMPDIFF(MONTH, '2025-01-15', '2026-05-26'); -- 16
SELECT PERIOD_DIFF(202605, 202412);                -- 5

4. 格式化与解析

函数说明
DATE_FORMAT(date, format)日期/时间按格式输出
TIME_FORMAT(time, format)仅格式化时间
STR_TO_DATE(str, format)字符串 → 日期时间
GET_FORMAT(type, region)预定义格式

常用格式符：%Y(4位年), %y(2位年), %m(月), %d(日), %H(24小时), %i(分), %s(秒), %W(星期名), %M(月份名) 等。

SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s'); -- 2026年05月26日 14:30:00
SELECT STR_TO_DATE('26/05/2026', '%d/%m/%Y');        -- 2026-05-26

5. 构造与类型转换

函数说明
MAKEDATE(year, dayofyear)由年和第几天生成日期
MAKETIME(h, m, s)生成时间
DATE(expr)截取日期部分
TIME(expr)截取时间部分
TIMESTAMP(expr) / TIMESTAMP(d, t)返回 datetime
FROM_UNIXTIME(ts[, format])Unix 时间戳 → 日期
UNIX_TIMESTAMP([date])日期 → Unix 时间戳
CONVERT_TZ(dt, from_tz, to_tz)时区转换
CAST(expr AS DATETIME)类型转换
SELECT MAKEDATE(2026, 146);           -- 2026-05-26
SELECT DATE('2026-05-26 14:30:00');  -- 2026-05-26
SELECT FROM_UNIXTIME(1760000000);     -- 转换时间戳
SELECT UNIX_TIMESTAMP('2026-05-26'); -- 对应时间戳
SELECT CONVERT_TZ('2026-05-26 12:00:00','+00:00','+08:00'); -- 时区转换（需时区表）

6. 其他实用函数

函数说明
LAST_DAY(date)该月最后一天
TO_DAYS(date)从公元0年算起的天数
FROM_DAYS(n)天数 → 日期
TIME_TO_SEC(time)时间 → 秒数
SEC_TO_TIME(seconds)秒数 → 时间
TO_SECONDS(expr)从公元0年算起的秒数
SELECT LAST_DAY('2026-05-26');             -- 2026-05-31
SELECT TO_DAYS('2026-05-26');              -- 739759
SELECT SEC_TO_TIME(3661);                  -- 01:01:01

常用日期时间计算方法

1. 计算两个日期相差天数/月数/年数

-- 相差天数
SELECT DATEDIFF('2026-06-01', '2026-05-26');   -- 6
 
-- 相差月数（忽略日差）
SELECT TIMESTAMPDIFF(MONTH, '2025-01-15', '2026-05-26'); -- 16
 
-- 相差年数（常用于年龄）
SELECT TIMESTAMPDIFF(YEAR, '1990-05-15', CURDATE());     -- 36

2. 计算精确年龄（过完生日才算一岁）

SELECT TIMESTAMPDIFF(YEAR, birth, CURDATE()) 
FROM users;
-- 或使用公式
SELECT (YEAR(CURDATE())-YEAR(birth)) - 
       (DATE_FORMAT(CURDATE(),'%m%d') < DATE_FORMAT(birth,'%m%d'))
FROM users;

3. 本月第一天、最后一天

-- 月初
SELECT DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY);
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01');                     -- 字符串形式
 
-- 月末
SELECT LAST_DAY(CURDATE());                                     -- 2026-05-31

4. 上月最后一天、下月第一天

-- 上月最后一天
SELECT LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH));
 
-- 下月第一天
SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY);

5. 本周一（以周一为起始）

SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY);
-- 若当前 2026-05-26 是周二，得 2026-05-25（周一）

6. 本周日、下周一

-- 本周日
SELECT DATE_ADD(CURDATE(), INTERVAL (6 - WEEKDAY(CURDATE())) DAY);
 
-- 下一个周一（如果今天是周一，则返回下周一的日期）
SELECT DATE_ADD(CURDATE(), INTERVAL (7 - WEEKDAY(CURDATE())) DAY);

7. 简单判断工作日（跳过周六日）

-- 给定日期 @d 的下一个工作日（不考虑节假日）
SELECT CASE DAYOFWEEK(@d)
         WHEN 7 THEN DATE_ADD(@d, INTERVAL 2 DAY)   -- 周六 -> 周一
         WHEN 1 THEN DATE_ADD(@d, INTERVAL 1 DAY)   -- 周日 -> 周一
         ELSE DATE_ADD(@d, INTERVAL 1 DAY)
       END AS next_workday;

8. 季度第一天、季度最后一天

-- 当前季度的第一天
SELECT MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL (QUARTER(CURDATE())-1)*3 MONTH;
 
-- 当前季度最后一天
SELECT LAST_DAY(MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE())*3 MONTH - INTERVAL 1 MONTH);

9. 判断闰年（某年2月有29天）

SELECT DAYOFMONTH(LAST_DAY(CONCAT(@year,'-02-01'))) = 29 AS is_leap;

10. 计算两个时间点的小时/分钟差（如工作时长）

SELECT TIMESTAMPDIFF(MINUTE, '2026-05-26 09:00:00', '2026-05-26 18:30:00') / 60 AS work_hours;
-- 9.5 小时
SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, start_time, end_time)) AS duration;

提示

  • 进行月份加减时，若目标月没有该日（如1月31日加1个月），MySQL 会自动调整到该月最后一天（结果为2月28/29日）。
  • 时区转换函数 CONVERT_TZ 依赖系统时区表，使用时需确保 mysql.time_zone 表已填充数据（可用 mysql_tzinfo_to_sql 加载），否则会返回 NULL。
  • 直接使用 + INTERVAL 语法与 DATE_ADD 功能等价，写法更简洁。

掌握上述函数与计算方法，基本能覆盖绝大部分业务场景中的日期时间处理需求。

Graph View