尧图网站建设 
做业务系统久了会发现一个规律只要表里有created_at、updated_at、expire_at、paid_at这类字段后面就绕不开 MySQL 日期时间操作函数。无论是按天统计订单、计算会员还剩几天、判断订单是否超时、生成周报月报还是排查“为什么这条数据昨天还在今天不见了”最后都会落到NOW()、DATE_FORMAT()、DATE_ADD()、TIMESTAMPDIFF()这些函数上。很多人装完 MySQL、连上客户端之后第一反应是学增删改查但真正做项目时日期时间函数的使用频率往往比JOIN还高。我写这篇内容不是把官方文档抄一遍而是把这几年在真实业务里反复用到的日期时间函数按“类型选择、当前时间、格式化解析、加减差值、提取构造、时区时间戳、业务场景、性能排查”串起来。你如果是刚学 MySQL 的新手可以把它当成一份能直接抄的 SQL 模板如果你已经写过不少报表也可以重点看后面的索引失效、时区坑、月末加月、周模式这些容易翻车的地方。文中示例默认以 MySQL 8.0 为主5.7 也大多通用个别函数差异我会单独说明。1. 先搞清日期时间类型和函数地图1.1 DATE、TIME、DATETIME、TIMESTAMP、YEAR 到底怎么选日期时间函数用得好不好第一步不是背函数而是先看字段类型。类型选错函数再熟也会别扭。MySQL 常见的日期时间类型有DATE、TIME、DATETIME、TIMESTAMP、YEAR它们占用的空间、范围、时区行为都不一样。DATE只存日期格式是YYYY-MM-DD范围从1000-01-01到9999-12-31适合生日、账单日、统计日期这类不需要具体时刻的字段。TIME只存时间范围是-838:59:59到838:59:59除了HH:MM:SS还能表示时间间隔所以它不只是“一天内的时间”。DATETIME存日期加时间格式是YYYY-MM-DD HH:MM:SS范围从1000-01-01 00:00:00到9999-12-31 23:59:59它不随时区变化存进去是什么取出来就是什么。TIMESTAMP也存日期加时间但它内部按 UTC 存储检索时按会话时区转换传统范围是1970-01-01 00:00:01UTC 到2038-01-19 03:14:07UTC这也是常说的 2038 问题来源。YEAR只存年份范围是 1901 到 2155以及 0000。我的选择习惯很直接业务发生时间、支付时间、创建时间如果系统只在一个时区运行用DATETIME最省心如果天然跨时区或者需要自动记录最后更新时间可以用TIMESTAMP但得接受 2038 上限和时区转换。会员到期时间、长期活动结束时间我更倾向DATETIME因为不想在 2038 年给自己埋雷。统计日期可以单独拆一个DATE字段或者在查询时用DATE(created_at)提取但后者会影响索引后面会细说。提示MySQL 8.0 默认启用严格模式插入2025-02-30这种不存在的日期通常会直接报错而不是悄悄变成零值。老系统里如果见过0000-00-00多半是早期非严格模式留下的历史包袱。1.2 日期时间函数的五类地图MySQL 日期时间操作函数看起来多其实可以分成五类。第一类取当前时间比如NOW()、CURDATE()、CURTIME()、UTC_TIMESTAMP()、SYSDATE()。第二类做格式化和解析比如DATE_FORMAT()、STR_TO_DATE()、TIME_FORMAT()、GET_FORMAT()。第三类做加减和差值比如DATE_ADD()、DATE_SUB()、ADDDATE()、SUBDATE()、DATEDIFF()、TIMEDIFF()、TIMESTAMPDIFF()、PERIOD_DIFF()。第四类做提取、截断和构造比如YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()、EXTRACT()、DATE()、TIME()、LAST_DAY()、MAKEDATE()、MAKETIME()。第五类处理时间戳和时区比如UNIX_TIMESTAMP()、FROM_UNIXTIME()、CONVERT_TZ()、TIMESTAMP()。分类之后你查函数就不是死记硬背而是先问自己要干什么。要“现在几点”就找第一类要把2025-03-18变成20250318就找第二类要算“七天后”就找第三类要取“这个月最后一天”就找第四类要把整数时间戳转成可读时间就找第五类。实际写 SQL 时经常是混合使用比如按周统计就是YEARWEEK()加DATE_FORMAT()加GROUP BY。1.3 为什么日期函数容易踩坑隐式转换和时区日期函数最常见的坑不是语法而是隐式转换。MySQL 很“宽容”WHERE created_at 2025-03-18这种写法在DATETIME字段上会把字符串转成2025-03-18 00:00:00于是你只能匹配到零点整那一秒而不是全天。很多人第一次查“今天的数据”发现只有凌晨那条就是因为把日期当成了完整时间。正确做法是用范围created_at 2025-03-18 AND created_at 2025-03-19。另一个坑是时区。NOW()返回的是当前会话时区的本地时间UTC_TIMESTAMP()返回 UTC 时间UNIX_TIMESTAMP()又和时区解释有关。数据库服务器、连接会话、应用服务器如果时区不一致就会出现“写入是 10 点读出来是 2 点”的现象。我的经验是跨时区系统统一用 UTC 存DATETIME展示层再按用户时区转换单时区系统可以本地时间存DATETIME但连接初始化时固定time_zone不要依赖机器默认值。2. 取当前时间与格式化解析2.1 NOW、CURDATE、CURTIME、UTC_TIMESTAMP 怎么选取当前时间是高频操作。NOW()返回当前日期和时间格式YYYY-MM-DD HH:MM:SS它还可以带精度比如NOW(3)返回毫秒NOW(6)返回微秒。CURDATE()等价于CURRENT_DATE只返回日期CURTIME()等价于CURRENT_TIME只返回时间。UTC_TIMESTAMP()返回 UTC 日期时间不带时区转换。SYSDATE()也返回当前时间但它和NOW()有个关键区别NOW()在同一条 SQL 语句开始时取值整条语句里多次调用结果一致SYSDATE()是实时取值同一条语句里可能出现不同结果。做主从复制时SYSDATE()可能带来不确定性所以我基本只用NOW()和UTC_TIMESTAMP()。SELECT NOW(), NOW(3), CURDATE(), CURTIME(), UTC_TIMESTAMP(), SYSDATE();如果你要记录“数据创建时间”建表时可以用DEFAULT CURRENT_TIMESTAMP。如果还要自动记录更新时间可以加ON UPDATE CURRENT_TIMESTAMP。但注意TIMESTAMP和DATETIME都支持这个默认值传统TIMESTAMP还会受时区影响。我的习惯是创建时间、更新时间用DATETIME默认值写CURRENT_TIMESTAMP应用层不手动传减少误差。2.2 DATE_FORMAT 与 STR_TO_DATE格式化与反向解析DATE_FORMAT(date, format)把日期时间转成指定格式的字符串。常见格式符里%Y是四位年%y是两位年%m是两位月%c是数字月%M是英文月名%d是两位日%e是数字日%H是 24 小时制小时%i是分钟%s和%S都是秒%T是HH:MM:SS%r是 12 小时制%W是英文星期名%w是星期几数字%u是周数且周一为第一天%U是周数且周日为第一天。SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s) AS full_time, DATE_FORMAT(NOW(), %Y%m) AS year_month, DATE_FORMAT(NOW(), %Y-%m-%d %H:%i) AS minute_time, DATE_FORMAT(NOW(), %W %M %Y) AS en_time;STR_TO_DATE(str, format)是反向操作把字符串按格式解析成日期时间。它常用于导入外部数据比如 CSV 里日期是18/03/2025你可以写成STR_TO_DATE(18/03/2025, %d/%m/%Y)。如果格式不匹配它会返回NULL并产生警告严格模式下某些非法日期还会报错。排查时可以用SHOW WARNINGS;看具体原因。SELECT STR_TO_DATE(2025-03-18 14:30:00, %Y-%m-%d %H:%i:%s) AS dt, STR_TO_DATE(18/03/2025, %d/%m/%Y) AS d, STR_TO_DATE(20250318, %Y%m%d) AS compact_d;2.3 格式化中的注意点与实操心得最容易写错的是分钟。%m是月份%i才是分钟所以%Y-%m-%d %H:%m:%s会把分钟位置显示成月份例如2025-03-18 14:03:00看起来像分钟其实是三月。这个错误很隐蔽尤其在只看结果不核对格式时。我一般写完格式化会拿一条真实数据对比年、月、日、时、分、秒是否都对。第二个注意点是DATE_FORMAT()返回字符串不是日期类型。如果你在WHERE里写DATE_FORMAT(created_at, %Y-%m) 2025-03字段上的索引基本用不上。更好的写法是范围created_at 2025-03-01 AND created_at 2025-04-01。如果一定要按月分组可以把DATE_FORMAT放在SELECT和GROUP BY里但WHERE尽量用原始日期列。第三个注意点是语言和区域设置。%M、%W返回英文名通常和lc_time_names有关但生产环境不建议依赖这个做展示展示层用前端或应用格式化更稳。SQL 里格式化应该服务于统计和排查而不是最终 UI。3. 日期加减与差值计算INTERVAL、DATEDIFF、TIMESTAMPDIFF3.1 DATE_ADD、DATE_SUB、ADDDATE、SUBDATE 的写法日期加减的核心是DATE_ADD(date, INTERVAL expr unit)和DATE_SUB(date, INTERVAL expr unit)。INTERVAL后面的单位可以是MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR等。ADDDATE和SUBDATE是简写既可以接INTERVAL也可以直接接天数。比如DATE_ADD(NOW(), INTERVAL 1 DAY)是明天同一时刻DATE_SUB(NOW(), INTERVAL 7 DAY)是七天前。SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS tomorrow, DATE_SUB(NOW(), INTERVAL 7 DAY) AS last_week, ADDDATE(2025-03-18, INTERVAL 1 MONTH) AS next_month, SUBDATE(2025-03-31, 1) AS yesterday;月末加月是一个经典场景。DATE_ADD(2025-01-31, INTERVAL 1 MONTH)在 MySQL 里会返回2025-02-28因为 2 月没有 31 日它会调整到月末。这个行为大多数时候符合直觉但你如果做财务结算需要明确“到期日”到底按月末还是按原日调整。更稳的做法是先算下个月第一天再减一天或者用LAST_DAY()验证结果。复合单位也很有用比如INTERVAL 1:30 HOUR_MINUTE表示 1 小时 30 分钟INTERVAL 2 10:30 DAY_HOUR表示 2 天 10 小时 30 分钟。写复合单位时字符串和单位要匹配否则容易报错。3.2 DATEDIFF、TIMEDIFF、TIMESTAMPDIFF 的边界DATEDIFF(expr1, expr2)只比较日期部分返回expr1 - expr2的天数。它忽略时间所以DATEDIFF(2025-03-18 23:59:59, 2025-03-17 00:00:00)返回 1。这个函数适合算“相差几天”比如会员到期还有几天、订单创建到今天多少天。TIMEDIFF(expr1, expr2)返回两个时间或日期时间之间的时间差结果类型是TIME范围受TIME类型限制。如果差值超过838:59:59会截断所以它更适合算同一天内或较短的时间差比如通话时长、任务耗时。TIMESTAMPDIFF(unit, start, end)更灵活返回end - start的整数差值单位可以是SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR。注意参数顺序是start, end不是end, start写反了会得到负数。它按完整单位计算比如TIMESTAMPDIFF(MONTH, 2025-01-31, 2025-02-28)返回 0因为还没满一个月TIMESTAMPDIFF(DAY, 2025-03-01, 2025-03-18)返回 17。SELECT DATEDIFF(2025-03-18, 2025-03-01) AS diff_days, TIMEDIFF(2025-03-18 18:00:00, 2025-03-18 09:30:00) AS diff_time, TIMESTAMPDIFF(HOUR, 2025-03-18 09:30:00, 2025-03-18 18:00:00) AS diff_hours, TIMESTAMPDIFF(DAY, 2025-03-01, 2025-03-18) AS diff_days_2;PERIOD_DIFF(period1, period2)专门算YYYYMM或YYMM格式的月份差比如PERIOD_DIFF(202503, 202501)返回 2。PERIOD_ADD(period, n)给YYYYMM加月份。做月报时这两个函数比字符串拼接更稳。3.3 计算工作日、自然月、月末这些复杂差值业务里很少只算自然日。比如请假天数要排除周末合同周期要按月账单周期要按自然月。MySQL 没有直接的工作日函数但可以组合算。简单办法是生成日期序列然后用DAYOFWEEK()过滤周日和周六。DAYOFWEEK()返回 1 到 7其中 1 是周日7 是周六WEEKDAY()返回 0 到 6其中 0 是周一6 是周日。我更喜欢WEEKDAY()因为周一到周五是 0 到 4判断WEEKDAY(d) 5就是工作日。WITH RECURSIVE days AS ( SELECT DATE(2025-03-01) AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM days WHERE d 2025-03-31 ) SELECT COUNT(*) AS workdays FROM days WHERE WEEKDAY(d) 5;自然月差值可以用TIMESTAMPDIFF(MONTH, start, end)但它不处理“同一天才算满月”的细节。如果合同是 1 月 31 日到 2 月 28 日算一个月就需要自己写逻辑比如比较DAY(start)和LAST_DAY(end)。月末函数LAST_DAY(date)返回该月最后一天配合DATE_ADD可以算出“下个月最后一天”。SELECT LAST_DAY(2025-02-15) AS last_day_feb, LAST_DAY(DATE_ADD(2025-01-31, INTERVAL 1 MONTH)) AS next_month_last_day, DATE_SUB(DATE_ADD(2025-03-01, INTERVAL 1 MONTH), INTERVAL 1 DAY) AS month_end;4. 提取、截断、构造与周月季函数4.1 YEAR、MONTH、DAY、EXTRACT 怎么用提取函数很直观。YEAR(date)、MONTH(date)、DAY(date)、HOUR(time)、MINUTE(time)、SECOND(time)、MICROSECOND(time)分别取各个部分。DAYOFMONTH()和DAY()同义DAYOFWEEK()、DAYOFYEAR()、WEEKDAY()取星期相关。EXTRACT(unit FROM date)是更统一的写法比如EXTRACT(YEAR FROM NOW())、EXTRACT(MONTH FROM NOW())、EXTRACT(DAY FROM NOW())。它还可以取WEEK、QUARTER、YEAR_MONTH这类组合单位。SELECT YEAR(NOW()) AS y, MONTH(NOW()) AS m, DAY(NOW()) AS d, HOUR(NOW()) AS h, MINUTE(NOW()) AS mi, SECOND(NOW()) AS s, EXTRACT(QUARTER FROM NOW()) AS q, EXTRACT(YEAR_MONTH FROM NOW()) AS ym;DATE(date)提取日期部分TIME(date)提取时间部分。它们常用于把DATETIME截断到天。比如DATE(created_at)就是创建日期。DATE()放在SELECT里没问题但放在WHERE里会导致无法直接使用普通索引后面性能部分会讲替代方案。4.2 LAST_DAY、DAYNAME、MONTHNAME、WEEK、QUARTERLAST_DAY(date)返回当月最后一天做月报、账单周期、月末对账非常实用。DAYNAME(date)返回英文星期名MONTHNAME(date)返回英文月名。WEEK(date, mode)返回周数WEEKOFYEAR(date)等价于WEEK(date, 3)即周一为第一天且周数范围 1 到 53。QUARTER(date)返回季度 1 到 4。YEARWEEK(date, mode)返回年和周组合适合按周分组。SELECT LAST_DAY(2025-03-18) AS last_day, DAYNAME(2025-03-18) AS day_name, MONTHNAME(2025-03-18) AS month_name, WEEK(2025-03-18, 1) AS week_1, WEEKOFYEAR(2025-03-18) AS week_of_year, QUARTER(2025-03-18) AS quarter, YEARWEEK(2025-03-18, 1) AS year_week;周模式很容易出错。WEEK()的mode决定了周从周日还是周一开始、第一周怎么算。做国际化报表时最好在团队内统一一个模式比如mode1或mode3并在 SQL 注释里写清楚。YEARWEEK(date, 1)常用于按周统计但跨年周可能把 12 月 31 日归到下一年的第 1 周展示时要跟产品确认。4.3 MAKEDATE、MAKETIME、SEC_TO_TIME、TIME_TO_SEC构造类函数用于把零散数字拼成日期时间。MAKEDATE(year, dayofyear)根据年份和一年中的第几天返回日期比如MAKEDATE(2025, 77)返回2025-03-18。MAKETIME(hour, minute, second)根据时分秒返回时间。SEC_TO_TIME(seconds)把秒数转成HH:MM:SSTIME_TO_SEC(time)把时间转成秒数。做耗时统计时这两个函数很方便。SELECT MAKEDATE(2025, 77) AS made_date, MAKETIME(14, 30, 0) AS made_time, SEC_TO_TIME(5400) AS sec_to_time, TIME_TO_SEC(01:30:00) AS time_to_sec;TIMESTAMP(date, time)可以把日期和时间合并成日期时间ADDTIME()和SUBTIME()可以做时间加减。TO_DAYS(date)返回从公元 0 年开始的天数FROM_DAYS(n)反向转换。这些函数在老系统里常见但新项目用DATE_ADD和DATEDIFF更直观。5. 时间戳、时区与特殊值处理5.1 TIMESTAMP 和 DATETIME 的时区差异DATETIME不存时区存什么读什么。TIMESTAMP存 UTC 秒读的时候按会话时区转换。举个实际例子会话时区是08:00你插入2025-03-18 10:00:00到TIMESTAMP字段它内部换算成 UTC 的2025-03-18 02:00:00另一个会话时区是00:00读出来就是2025-03-18 02:00:00。而DATETIME字段两个会话读出来都是2025-03-18 10:00:00。这个差异决定了选型。如果你的系统只服务一个时区用DATETIME少很多麻烦。如果服务多个时区又希望数据库自动转换可以用TIMESTAMP但要注意 2038 上限。会员到期时间、活动结束时间、订单过期时间这些可能超过 2038 年的字段我建议用DATETIME把时区转换交给应用层。SELECT global.time_zone, session.time_zone, NOW(), UTC_TIMESTAMP(), UNIX_TIMESTAMP();5.2 UNIX_TIMESTAMP 与 FROM_UNIXTIME 的整数时间UNIX_TIMESTAMP()无参时返回当前 Unix 时间戳也就是从 1970-01-01 00:00:00 UTC 到现在的秒数。带日期参数时把日期时间解释成会话时区的时间再转成 UTC 秒数。FROM_UNIXTIME(timestamp)反过来把 Unix 秒数按会话时区转成日期时间。它们常用于和外部系统对接比如接口传秒级时间戳数据库里存DATETIME。SELECT UNIX_TIMESTAMP(2025-03-18 10:00:00) AS ts, FROM_UNIXTIME(1742263200) AS dt, FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())) AS round_trip;UNIX_TIMESTAMP返回整数FROM_UNIXTIME默认返回秒级也可以带小数秒。注意UNIX_TIMESTAMP受时区影响同一字符串在不同会话时区下结果不同。跨系统对接时最好明确时间戳是秒还是毫秒很多前端默认毫秒MySQL 默认秒差 1000 倍就会得到离谱日期。5.3 零值、NULL、2038 问题与夏令时零值0000-00-00是 MySQL 历史包袱。MySQL 8.0 默认NO_ZERO_DATE、NO_ZERO_IN_DATE、STRICT_TRANS_TABLES开启插入非法日期会报错。老系统如果关闭严格模式可能把非法日期转成0000-00-00后续DATE_FORMAT、DATEDIFF都会出现奇怪结果。排查时先看SELECT sql_mode;。NULL 处理也要小心。日期字段允许 NULL 时DATE_ADD(NULL, INTERVAL 1 DAY)返回 NULLDATEDIFF(NULL, NOW())也返回 NULL。做统计时可以用IFNULL或COALESCE给默认值但业务上最好明确这个时间到底允许不允许为空。如果允许为空查询条件里要写IS NULL或IS NOT NULL不要用 NULL。2038 问题主要影响传统TIMESTAMP。如果系统要存 2040 年的到期时间用TIMESTAMP会溢出。DATETIME范围到 9999 年适合长期日期。夏令时问题在跨时区系统里更常见某些地区一年会切换两次时间TIMESTAMP自动转换时可能出现重复小时或不存在的时刻。解决思路通常是统一 UTC 存储展示层按地区处理。6. 业务场景模板统计、分桶、到期、超时、留存6.1 按日、周、月统计与补零按日统计是最常见需求。假设订单表orders有created_at DATETIME按日统计订单数和 GMVSELECT DATE(created_at) AS stat_date, COUNT(*) AS order_cnt, SUM(amount) AS gmv FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01 GROUP BY DATE(created_at) ORDER BY stat_date;如果想按周统计可以用YEARWEEK(created_at, 1)。mode1表示周一为第一天周数范围 0 到 53。展示时可以用MIN(DATE(created_at))取该周第一天但跨周数据要小心。SELECT YEARWEEK(created_at, 1) AS year_week, MIN(DATE(created_at)) AS week_start, COUNT(*) AS order_cnt, SUM(amount) AS gmv FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01 GROUP BY YEARWEEK(created_at, 1) ORDER BY year_week;按月统计可以用DATE_FORMAT(created_at, %Y-%m)但WHERE仍然用范围。补零是报表常见需求MySQL 8.0 可以用递归 CTE 生成日期序列再左连接统计结果。WITH RECURSIVE date_series AS ( SELECT DATE(2025-03-01) AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_series WHERE d 2025-03-31 ), daily AS ( SELECT DATE(created_at) AS stat_date, COUNT(*) AS order_cnt, SUM(amount) AS gmv FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01 GROUP BY DATE(created_at) ) SELECT ds.d AS stat_date, IFNULL(daily.order_cnt, 0) AS order_cnt, IFNULL(daily.gmv, 0) AS gmv FROM date_series ds LEFT JOIN daily ON ds.d daily.stat_date ORDER BY ds.d;6.2 会员到期、订单超时、过期清理会员到期查询通常用expire_at范围。比如查未来 7 天内到期的会员SELECT id, user_id, expire_at FROM member WHERE expire_at NOW() AND expire_at DATE_ADD(NOW(), INTERVAL 7 DAY);订单超时判断可以算创建时间和当前时间差SELECT id, user_id, created_at, TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS pending_minutes FROM orders WHERE status 0 AND created_at DATE_SUB(NOW(), INTERVAL 30 MINUTE);过期清理不要一次性删太多。大表删除容易锁表、拖慢主从建议分批DELETE FROM orders WHERE status 0 AND created_at DATE_SUB(NOW(), INTERVAL 2 HOUR) LIMIT 1000;循环执行直到影响行数为 0。注意DELETE条件里created_at是原始列能用索引不要写成DATE_SUB(created_at, INTERVAL 2 HOUR) NOW()。6.3 留存与活跃计算留存计算常用“首次登录日期”加“后续登录日期”。下面是一个简化版日留存模板WITH first_login AS ( SELECT user_id, MIN(DATE(login_time)) AS first_dt FROM login_log GROUP BY user_id ), login_daily AS ( SELECT DISTINCT user_id, DATE(login_time) AS login_dt FROM login_log ) SELECT f.first_dt, COUNT(DISTINCT f.user_id) AS new_users, COUNT(DISTINCT CASE WHEN DATEDIFF(l.login_dt, f.first_dt) 1 THEN l.user_id END) AS day1, COUNT(DISTINCT CASE WHEN DATEDIFF(l.login_dt, f.first_dt) 7 THEN l.user_id END) AS day7 FROM first_login f LEFT JOIN login_daily l ON f.user_id l.user_id GROUP BY f.first_dt ORDER BY f.first_dt;这里用DATEDIFF只比日期忽略时间正好符合留存按天定义。如果按小时活跃可以用TIMESTAMPDIFF(HOUR, ...)或直接按DATE_FORMAT(login_time, %Y-%m-%d %H)分组。7. 性能与常见问题排查7.1 函数包字段导致索引失效这是日期查询最大的性能杀手。比如SELECT * FROM orders WHERE DATE(created_at) 2025-03-18;即使created_at有索引MySQL 也可能无法直接使用因为它要对每一行计算DATE(created_at)。正确写法是范围SELECT * FROM orders WHERE created_at 2025-03-18 AND created_at 2025-03-19;同理WHERE YEAR(created_at) 2025也不如created_at 2025-01-01 AND created_at 2026-01-01。如果确实需要按函数结果查询可以考虑生成列加索引或者 MySQL 8.0.13 以上的函数索引ALTER TABLE orders ADD COLUMN created_date DATE GENERATED ALWAYS AS (DATE(created_at)) STORED, ADD INDEX idx_created_date (created_date);生成列会占用空间但查询时可以直接用created_date 2025-03-18。函数索引写法是CREATE INDEX idx_created_date ON orders ((DATE(created_at)));但使用前要确认版本和业务写入模式。7.2 常见错误速查表现象常见原因处理方式查某天数据只有零点一条用比较DATETIME改用当天和次日分钟显示成月份格式符写成%m分钟用%i月份用%mTIMESTAMPDIFF结果是负数参数顺序写反确认是start, end按周统计跨年错乱周模式不统一统一mode如YEARWEEK(date, 1)CONVERT_TZ返回 NULL时区表未导入导入mysql.time_zone相关表或改用应用层转换TIMESTAMP存 2040 年失败2038 上限长期时间改用DATETIMESTR_TO_DATE返回 NULL格式不匹配SHOW WARNINGS;看警告核对格式符NOW()和SYSDATE()结果不同取值时机不同主从环境优先用NOW()7.3 时区与格式的排查技巧排查日期问题我通常按这个顺序第一看SELECT sql_mode;确认严格模式第二看SELECT global.time_zone, session.time_zone, NOW(), UTC_TIMESTAMP();确认时区第三看字段类型是DATETIME还是TIMESTAMP第四看查询条件是否用了范围第五看EXPLAIN是否命中索引。EXPLAIN是排查性能的硬手段。执行EXPLAIN SELECT * FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01;关注type、key、rows、Extra。如果type是ALL说明全表扫描如果key为 NULL说明没用索引。对比DATE(created_at)写法很容易看出差异。对于大表日期范围查询还要注意范围太大时优化器可能选择全表扫描这时可以加更精确的条件或做分区。8. 从建表到报表一套可复现的实操路线8.1 建表与样本数据先准备一张订单表字段包含创建时间、支付时间、过期时间。注意created_at用DATETIME默认CURRENT_TIMESTAMP并加索引。CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, paid_at DATETIME NULL, expire_at DATETIME NULL, KEY idx_created_at (created_at), KEY idx_status_created (status, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入一些样本数据覆盖 3 月不同日期INSERT INTO orders (user_id, amount, status, created_at, paid_at, expire_at) VALUES (1001, 99.00, 1, 2025-03-01 10:00:00, 2025-03-01 10:05:00, 2025-03-01 10:30:00), (1002, 199.00, 1, 2025-03-01 15:20:00, 2025-03-01 15:25:00, 2025-03-01 15:50:00), (1003, 59.00, 0, 2025-03-02 09:10:00, NULL, 2025-03-02 09:40:00), (1001, 299.00, 1, 2025-03-08 11:00:00, 2025-03-08 11:02:00, 2025-03-08 11:30:00), (1004, 129.00, 1, 2025-03-18 20:00:00, 2025-03-18 20:01:00, 2025-03-18 20:30:00), (1005, 49.00, 0, 2025-03-18 21:30:00, NULL, 2025-03-18 22:00:00);8.2 日报、周报、月报 SQL日报直接按天分组SELECT DATE(created_at) AS stat_date, COUNT(*) AS order_cnt, SUM(amount) AS gmv, SUM(CASE WHEN status 1 THEN 1 ELSE 0 END) AS paid_cnt FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01 GROUP BY DATE(created_at) ORDER BY stat_date;周报用YEARWEEKSELECT YEARWEEK(created_at, 1) AS year_week, MIN(DATE(created_at)) AS week_start, MAX(DATE(created_at)) AS week_end, COUNT(*) AS order_cnt, SUM(amount) AS gmv FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01 GROUP BY YEARWEEK(created_at, 1) ORDER BY year_week;月报用DATE_FORMATSELECT DATE_FORMAT(created_at, %Y-%m) AS year_month, COUNT(*) AS order_cnt, SUM(amount) AS gmv FROM orders WHERE created_at 2025-01-01 AND created_at 2026-01-01 GROUP BY DATE_FORMAT(created_at, %Y-%m) ORDER BY year_month;如果数据量大月报可以改成范围条件加预聚合表比如每天凌晨跑一次汇总表报表查汇总表避免每次扫全量订单。8.3 参数校验与结果核对写完 SQL 不要直接丢给业务。我一般做三步核对。第一用SELECT NOW(), CURDATE(), time_zone;确认环境时间第二用少量样本 data 手工算一遍比如 3 月 18 日有两条订单日报结果应该是 2第三用EXPLAIN看索引是否命中特别是WHERE created_at ... AND created_at ...这种范围。对于STR_TO_DATE和DATE_FORMAT可以用往返测试SELECT DATE_FORMAT(STR_TO_DATE(2025-03-18, %Y-%m-%d), %Y-%m-%d) AS round_trip;如果结果和输入一致说明格式符没写错。对于时区可以在两个不同time_zone会话里查同一个TIMESTAMP字段观察是否转换。对于周数可以拿年初、年末各一条数据测试YEARWEEK是否符合产品定义。我个人在实际操作中的体会是日期时间函数本身不难难的是边界和一致性。把类型选对、时区统一、WHERE用范围、格式化只在展示层做大部分问题都能提前避开。剩下的小坑比如分钟写成%m、TIMESTAMPDIFF参数写反、月末加月调整到 28 号只要在测试环境拿真实数据跑一遍基本都能现形。 MySQL日期时间函数全指南：类型选择、格式化、加减与时区性能
做业务系统久了会发现一个规律只要表里有created_at、updated_at、expire_at、paid_at这类字段后面就绕不开 MySQL 日期时间操作函数。无论是按天统计订单、计算会员还剩几天、判断订单是否超时、生成周报月报还是排查“为什么这条数据昨天还在今天不见了”最后都会落到NOW()、DATE_FORMAT()、DATE_ADD()、TIMESTAMPDIFF()这些函数上。很多人装完 MySQL、连上客户端之后第一反应是学增删改查但真正做项目时日期时间函数的使用频率往往比JOIN还高。我写这篇内容不是把官方文档抄一遍而是把这几年在真实业务里反复用到的日期时间函数按“类型选择、当前时间、格式化解析、加减差值、提取构造、时区时间戳、业务场景、性能排查”串起来。你如果是刚学 MySQL 的新手可以把它当成一份能直接抄的 SQL 模板如果你已经写过不少报表也可以重点看后面的索引失效、时区坑、月末加月、周模式这些容易翻车的地方。文中示例默认以 MySQL 8.0 为主5.7 也大多通用个别函数差异我会单独说明。1. 先搞清日期时间类型和函数地图1.1 DATE、TIME、DATETIME、TIMESTAMP、YEAR 到底怎么选日期时间函数用得好不好第一步不是背函数而是先看字段类型。类型选错函数再熟也会别扭。MySQL 常见的日期时间类型有DATE、TIME、DATETIME、TIMESTAMP、YEAR它们占用的空间、范围、时区行为都不一样。DATE只存日期格式是YYYY-MM-DD范围从1000-01-01到9999-12-31适合生日、账单日、统计日期这类不需要具体时刻的字段。TIME只存时间范围是-838:59:59到838:59:59除了HH:MM:SS还能表示时间间隔所以它不只是“一天内的时间”。DATETIME存日期加时间格式是YYYY-MM-DD HH:MM:SS范围从1000-01-01 00:00:00到9999-12-31 23:59:59它不随时区变化存进去是什么取出来就是什么。TIMESTAMP也存日期加时间但它内部按 UTC 存储检索时按会话时区转换传统范围是1970-01-01 00:00:01UTC 到2038-01-19 03:14:07UTC这也是常说的 2038 问题来源。YEAR只存年份范围是 1901 到 2155以及 0000。我的选择习惯很直接业务发生时间、支付时间、创建时间如果系统只在一个时区运行用DATETIME最省心如果天然跨时区或者需要自动记录最后更新时间可以用TIMESTAMP但得接受 2038 上限和时区转换。会员到期时间、长期活动结束时间我更倾向DATETIME因为不想在 2038 年给自己埋雷。统计日期可以单独拆一个DATE字段或者在查询时用DATE(created_at)提取但后者会影响索引后面会细说。提示MySQL 8.0 默认启用严格模式插入2025-02-30这种不存在的日期通常会直接报错而不是悄悄变成零值。老系统里如果见过0000-00-00多半是早期非严格模式留下的历史包袱。1.2 日期时间函数的五类地图MySQL 日期时间操作函数看起来多其实可以分成五类。第一类取当前时间比如NOW()、CURDATE()、CURTIME()、UTC_TIMESTAMP()、SYSDATE()。第二类做格式化和解析比如DATE_FORMAT()、STR_TO_DATE()、TIME_FORMAT()、GET_FORMAT()。第三类做加减和差值比如DATE_ADD()、DATE_SUB()、ADDDATE()、SUBDATE()、DATEDIFF()、TIMEDIFF()、TIMESTAMPDIFF()、PERIOD_DIFF()。第四类做提取、截断和构造比如YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()、EXTRACT()、DATE()、TIME()、LAST_DAY()、MAKEDATE()、MAKETIME()。第五类处理时间戳和时区比如UNIX_TIMESTAMP()、FROM_UNIXTIME()、CONVERT_TZ()、TIMESTAMP()。分类之后你查函数就不是死记硬背而是先问自己要干什么。要“现在几点”就找第一类要把2025-03-18变成20250318就找第二类要算“七天后”就找第三类要取“这个月最后一天”就找第四类要把整数时间戳转成可读时间就找第五类。实际写 SQL 时经常是混合使用比如按周统计就是YEARWEEK()加DATE_FORMAT()加GROUP BY。1.3 为什么日期函数容易踩坑隐式转换和时区日期函数最常见的坑不是语法而是隐式转换。MySQL 很“宽容”WHERE created_at 2025-03-18这种写法在DATETIME字段上会把字符串转成2025-03-18 00:00:00于是你只能匹配到零点整那一秒而不是全天。很多人第一次查“今天的数据”发现只有凌晨那条就是因为把日期当成了完整时间。正确做法是用范围created_at 2025-03-18 AND created_at 2025-03-19。另一个坑是时区。NOW()返回的是当前会话时区的本地时间UTC_TIMESTAMP()返回 UTC 时间UNIX_TIMESTAMP()又和时区解释有关。数据库服务器、连接会话、应用服务器如果时区不一致就会出现“写入是 10 点读出来是 2 点”的现象。我的经验是跨时区系统统一用 UTC 存DATETIME展示层再按用户时区转换单时区系统可以本地时间存DATETIME但连接初始化时固定time_zone不要依赖机器默认值。2. 取当前时间与格式化解析2.1 NOW、CURDATE、CURTIME、UTC_TIMESTAMP 怎么选取当前时间是高频操作。NOW()返回当前日期和时间格式YYYY-MM-DD HH:MM:SS它还可以带精度比如NOW(3)返回毫秒NOW(6)返回微秒。CURDATE()等价于CURRENT_DATE只返回日期CURTIME()等价于CURRENT_TIME只返回时间。UTC_TIMESTAMP()返回 UTC 日期时间不带时区转换。SYSDATE()也返回当前时间但它和NOW()有个关键区别NOW()在同一条 SQL 语句开始时取值整条语句里多次调用结果一致SYSDATE()是实时取值同一条语句里可能出现不同结果。做主从复制时SYSDATE()可能带来不确定性所以我基本只用NOW()和UTC_TIMESTAMP()。SELECT NOW(), NOW(3), CURDATE(), CURTIME(), UTC_TIMESTAMP(), SYSDATE();如果你要记录“数据创建时间”建表时可以用DEFAULT CURRENT_TIMESTAMP。如果还要自动记录更新时间可以加ON UPDATE CURRENT_TIMESTAMP。但注意TIMESTAMP和DATETIME都支持这个默认值传统TIMESTAMP还会受时区影响。我的习惯是创建时间、更新时间用DATETIME默认值写CURRENT_TIMESTAMP应用层不手动传减少误差。2.2 DATE_FORMAT 与 STR_TO_DATE格式化与反向解析DATE_FORMAT(date, format)把日期时间转成指定格式的字符串。常见格式符里%Y是四位年%y是两位年%m是两位月%c是数字月%M是英文月名%d是两位日%e是数字日%H是 24 小时制小时%i是分钟%s和%S都是秒%T是HH:MM:SS%r是 12 小时制%W是英文星期名%w是星期几数字%u是周数且周一为第一天%U是周数且周日为第一天。SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s) AS full_time, DATE_FORMAT(NOW(), %Y%m) AS year_month, DATE_FORMAT(NOW(), %Y-%m-%d %H:%i) AS minute_time, DATE_FORMAT(NOW(), %W %M %Y) AS en_time;STR_TO_DATE(str, format)是反向操作把字符串按格式解析成日期时间。它常用于导入外部数据比如 CSV 里日期是18/03/2025你可以写成STR_TO_DATE(18/03/2025, %d/%m/%Y)。如果格式不匹配它会返回NULL并产生警告严格模式下某些非法日期还会报错。排查时可以用SHOW WARNINGS;看具体原因。SELECT STR_TO_DATE(2025-03-18 14:30:00, %Y-%m-%d %H:%i:%s) AS dt, STR_TO_DATE(18/03/2025, %d/%m/%Y) AS d, STR_TO_DATE(20250318, %Y%m%d) AS compact_d;2.3 格式化中的注意点与实操心得最容易写错的是分钟。%m是月份%i才是分钟所以%Y-%m-%d %H:%m:%s会把分钟位置显示成月份例如2025-03-18 14:03:00看起来像分钟其实是三月。这个错误很隐蔽尤其在只看结果不核对格式时。我一般写完格式化会拿一条真实数据对比年、月、日、时、分、秒是否都对。第二个注意点是DATE_FORMAT()返回字符串不是日期类型。如果你在WHERE里写DATE_FORMAT(created_at, %Y-%m) 2025-03字段上的索引基本用不上。更好的写法是范围created_at 2025-03-01 AND created_at 2025-04-01。如果一定要按月分组可以把DATE_FORMAT放在SELECT和GROUP BY里但WHERE尽量用原始日期列。第三个注意点是语言和区域设置。%M、%W返回英文名通常和lc_time_names有关但生产环境不建议依赖这个做展示展示层用前端或应用格式化更稳。SQL 里格式化应该服务于统计和排查而不是最终 UI。3. 日期加减与差值计算INTERVAL、DATEDIFF、TIMESTAMPDIFF3.1 DATE_ADD、DATE_SUB、ADDDATE、SUBDATE 的写法日期加减的核心是DATE_ADD(date, INTERVAL expr unit)和DATE_SUB(date, INTERVAL expr unit)。INTERVAL后面的单位可以是MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR等。ADDDATE和SUBDATE是简写既可以接INTERVAL也可以直接接天数。比如DATE_ADD(NOW(), INTERVAL 1 DAY)是明天同一时刻DATE_SUB(NOW(), INTERVAL 7 DAY)是七天前。SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS tomorrow, DATE_SUB(NOW(), INTERVAL 7 DAY) AS last_week, ADDDATE(2025-03-18, INTERVAL 1 MONTH) AS next_month, SUBDATE(2025-03-31, 1) AS yesterday;月末加月是一个经典场景。DATE_ADD(2025-01-31, INTERVAL 1 MONTH)在 MySQL 里会返回2025-02-28因为 2 月没有 31 日它会调整到月末。这个行为大多数时候符合直觉但你如果做财务结算需要明确“到期日”到底按月末还是按原日调整。更稳的做法是先算下个月第一天再减一天或者用LAST_DAY()验证结果。复合单位也很有用比如INTERVAL 1:30 HOUR_MINUTE表示 1 小时 30 分钟INTERVAL 2 10:30 DAY_HOUR表示 2 天 10 小时 30 分钟。写复合单位时字符串和单位要匹配否则容易报错。3.2 DATEDIFF、TIMEDIFF、TIMESTAMPDIFF 的边界DATEDIFF(expr1, expr2)只比较日期部分返回expr1 - expr2的天数。它忽略时间所以DATEDIFF(2025-03-18 23:59:59, 2025-03-17 00:00:00)返回 1。这个函数适合算“相差几天”比如会员到期还有几天、订单创建到今天多少天。TIMEDIFF(expr1, expr2)返回两个时间或日期时间之间的时间差结果类型是TIME范围受TIME类型限制。如果差值超过838:59:59会截断所以它更适合算同一天内或较短的时间差比如通话时长、任务耗时。TIMESTAMPDIFF(unit, start, end)更灵活返回end - start的整数差值单位可以是SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR。注意参数顺序是start, end不是end, start写反了会得到负数。它按完整单位计算比如TIMESTAMPDIFF(MONTH, 2025-01-31, 2025-02-28)返回 0因为还没满一个月TIMESTAMPDIFF(DAY, 2025-03-01, 2025-03-18)返回 17。SELECT DATEDIFF(2025-03-18, 2025-03-01) AS diff_days, TIMEDIFF(2025-03-18 18:00:00, 2025-03-18 09:30:00) AS diff_time, TIMESTAMPDIFF(HOUR, 2025-03-18 09:30:00, 2025-03-18 18:00:00) AS diff_hours, TIMESTAMPDIFF(DAY, 2025-03-01, 2025-03-18) AS diff_days_2;PERIOD_DIFF(period1, period2)专门算YYYYMM或YYMM格式的月份差比如PERIOD_DIFF(202503, 202501)返回 2。PERIOD_ADD(period, n)给YYYYMM加月份。做月报时这两个函数比字符串拼接更稳。3.3 计算工作日、自然月、月末这些复杂差值业务里很少只算自然日。比如请假天数要排除周末合同周期要按月账单周期要按自然月。MySQL 没有直接的工作日函数但可以组合算。简单办法是生成日期序列然后用DAYOFWEEK()过滤周日和周六。DAYOFWEEK()返回 1 到 7其中 1 是周日7 是周六WEEKDAY()返回 0 到 6其中 0 是周一6 是周日。我更喜欢WEEKDAY()因为周一到周五是 0 到 4判断WEEKDAY(d) 5就是工作日。WITH RECURSIVE days AS ( SELECT DATE(2025-03-01) AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM days WHERE d 2025-03-31 ) SELECT COUNT(*) AS workdays FROM days WHERE WEEKDAY(d) 5;自然月差值可以用TIMESTAMPDIFF(MONTH, start, end)但它不处理“同一天才算满月”的细节。如果合同是 1 月 31 日到 2 月 28 日算一个月就需要自己写逻辑比如比较DAY(start)和LAST_DAY(end)。月末函数LAST_DAY(date)返回该月最后一天配合DATE_ADD可以算出“下个月最后一天”。SELECT LAST_DAY(2025-02-15) AS last_day_feb, LAST_DAY(DATE_ADD(2025-01-31, INTERVAL 1 MONTH)) AS next_month_last_day, DATE_SUB(DATE_ADD(2025-03-01, INTERVAL 1 MONTH), INTERVAL 1 DAY) AS month_end;4. 提取、截断、构造与周月季函数4.1 YEAR、MONTH、DAY、EXTRACT 怎么用提取函数很直观。YEAR(date)、MONTH(date)、DAY(date)、HOUR(time)、MINUTE(time)、SECOND(time)、MICROSECOND(time)分别取各个部分。DAYOFMONTH()和DAY()同义DAYOFWEEK()、DAYOFYEAR()、WEEKDAY()取星期相关。EXTRACT(unit FROM date)是更统一的写法比如EXTRACT(YEAR FROM NOW())、EXTRACT(MONTH FROM NOW())、EXTRACT(DAY FROM NOW())。它还可以取WEEK、QUARTER、YEAR_MONTH这类组合单位。SELECT YEAR(NOW()) AS y, MONTH(NOW()) AS m, DAY(NOW()) AS d, HOUR(NOW()) AS h, MINUTE(NOW()) AS mi, SECOND(NOW()) AS s, EXTRACT(QUARTER FROM NOW()) AS q, EXTRACT(YEAR_MONTH FROM NOW()) AS ym;DATE(date)提取日期部分TIME(date)提取时间部分。它们常用于把DATETIME截断到天。比如DATE(created_at)就是创建日期。DATE()放在SELECT里没问题但放在WHERE里会导致无法直接使用普通索引后面性能部分会讲替代方案。4.2 LAST_DAY、DAYNAME、MONTHNAME、WEEK、QUARTERLAST_DAY(date)返回当月最后一天做月报、账单周期、月末对账非常实用。DAYNAME(date)返回英文星期名MONTHNAME(date)返回英文月名。WEEK(date, mode)返回周数WEEKOFYEAR(date)等价于WEEK(date, 3)即周一为第一天且周数范围 1 到 53。QUARTER(date)返回季度 1 到 4。YEARWEEK(date, mode)返回年和周组合适合按周分组。SELECT LAST_DAY(2025-03-18) AS last_day, DAYNAME(2025-03-18) AS day_name, MONTHNAME(2025-03-18) AS month_name, WEEK(2025-03-18, 1) AS week_1, WEEKOFYEAR(2025-03-18) AS week_of_year, QUARTER(2025-03-18) AS quarter, YEARWEEK(2025-03-18, 1) AS year_week;周模式很容易出错。WEEK()的mode决定了周从周日还是周一开始、第一周怎么算。做国际化报表时最好在团队内统一一个模式比如mode1或mode3并在 SQL 注释里写清楚。YEARWEEK(date, 1)常用于按周统计但跨年周可能把 12 月 31 日归到下一年的第 1 周展示时要跟产品确认。4.3 MAKEDATE、MAKETIME、SEC_TO_TIME、TIME_TO_SEC构造类函数用于把零散数字拼成日期时间。MAKEDATE(year, dayofyear)根据年份和一年中的第几天返回日期比如MAKEDATE(2025, 77)返回2025-03-18。MAKETIME(hour, minute, second)根据时分秒返回时间。SEC_TO_TIME(seconds)把秒数转成HH:MM:SSTIME_TO_SEC(time)把时间转成秒数。做耗时统计时这两个函数很方便。SELECT MAKEDATE(2025, 77) AS made_date, MAKETIME(14, 30, 0) AS made_time, SEC_TO_TIME(5400) AS sec_to_time, TIME_TO_SEC(01:30:00) AS time_to_sec;TIMESTAMP(date, time)可以把日期和时间合并成日期时间ADDTIME()和SUBTIME()可以做时间加减。TO_DAYS(date)返回从公元 0 年开始的天数FROM_DAYS(n)反向转换。这些函数在老系统里常见但新项目用DATE_ADD和DATEDIFF更直观。5. 时间戳、时区与特殊值处理5.1 TIMESTAMP 和 DATETIME 的时区差异DATETIME不存时区存什么读什么。TIMESTAMP存 UTC 秒读的时候按会话时区转换。举个实际例子会话时区是08:00你插入2025-03-18 10:00:00到TIMESTAMP字段它内部换算成 UTC 的2025-03-18 02:00:00另一个会话时区是00:00读出来就是2025-03-18 02:00:00。而DATETIME字段两个会话读出来都是2025-03-18 10:00:00。这个差异决定了选型。如果你的系统只服务一个时区用DATETIME少很多麻烦。如果服务多个时区又希望数据库自动转换可以用TIMESTAMP但要注意 2038 上限。会员到期时间、活动结束时间、订单过期时间这些可能超过 2038 年的字段我建议用DATETIME把时区转换交给应用层。SELECT global.time_zone, session.time_zone, NOW(), UTC_TIMESTAMP(), UNIX_TIMESTAMP();5.2 UNIX_TIMESTAMP 与 FROM_UNIXTIME 的整数时间UNIX_TIMESTAMP()无参时返回当前 Unix 时间戳也就是从 1970-01-01 00:00:00 UTC 到现在的秒数。带日期参数时把日期时间解释成会话时区的时间再转成 UTC 秒数。FROM_UNIXTIME(timestamp)反过来把 Unix 秒数按会话时区转成日期时间。它们常用于和外部系统对接比如接口传秒级时间戳数据库里存DATETIME。SELECT UNIX_TIMESTAMP(2025-03-18 10:00:00) AS ts, FROM_UNIXTIME(1742263200) AS dt, FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())) AS round_trip;UNIX_TIMESTAMP返回整数FROM_UNIXTIME默认返回秒级也可以带小数秒。注意UNIX_TIMESTAMP受时区影响同一字符串在不同会话时区下结果不同。跨系统对接时最好明确时间戳是秒还是毫秒很多前端默认毫秒MySQL 默认秒差 1000 倍就会得到离谱日期。5.3 零值、NULL、2038 问题与夏令时零值0000-00-00是 MySQL 历史包袱。MySQL 8.0 默认NO_ZERO_DATE、NO_ZERO_IN_DATE、STRICT_TRANS_TABLES开启插入非法日期会报错。老系统如果关闭严格模式可能把非法日期转成0000-00-00后续DATE_FORMAT、DATEDIFF都会出现奇怪结果。排查时先看SELECT sql_mode;。NULL 处理也要小心。日期字段允许 NULL 时DATE_ADD(NULL, INTERVAL 1 DAY)返回 NULLDATEDIFF(NULL, NOW())也返回 NULL。做统计时可以用IFNULL或COALESCE给默认值但业务上最好明确这个时间到底允许不允许为空。如果允许为空查询条件里要写IS NULL或IS NOT NULL不要用 NULL。2038 问题主要影响传统TIMESTAMP。如果系统要存 2040 年的到期时间用TIMESTAMP会溢出。DATETIME范围到 9999 年适合长期日期。夏令时问题在跨时区系统里更常见某些地区一年会切换两次时间TIMESTAMP自动转换时可能出现重复小时或不存在的时刻。解决思路通常是统一 UTC 存储展示层按地区处理。6. 业务场景模板统计、分桶、到期、超时、留存6.1 按日、周、月统计与补零按日统计是最常见需求。假设订单表orders有created_at DATETIME按日统计订单数和 GMVSELECT DATE(created_at) AS stat_date, COUNT(*) AS order_cnt, SUM(amount) AS gmv FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01 GROUP BY DATE(created_at) ORDER BY stat_date;如果想按周统计可以用YEARWEEK(created_at, 1)。mode1表示周一为第一天周数范围 0 到 53。展示时可以用MIN(DATE(created_at))取该周第一天但跨周数据要小心。SELECT YEARWEEK(created_at, 1) AS year_week, MIN(DATE(created_at)) AS week_start, COUNT(*) AS order_cnt, SUM(amount) AS gmv FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01 GROUP BY YEARWEEK(created_at, 1) ORDER BY year_week;按月统计可以用DATE_FORMAT(created_at, %Y-%m)但WHERE仍然用范围。补零是报表常见需求MySQL 8.0 可以用递归 CTE 生成日期序列再左连接统计结果。WITH RECURSIVE date_series AS ( SELECT DATE(2025-03-01) AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_series WHERE d 2025-03-31 ), daily AS ( SELECT DATE(created_at) AS stat_date, COUNT(*) AS order_cnt, SUM(amount) AS gmv FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01 GROUP BY DATE(created_at) ) SELECT ds.d AS stat_date, IFNULL(daily.order_cnt, 0) AS order_cnt, IFNULL(daily.gmv, 0) AS gmv FROM date_series ds LEFT JOIN daily ON ds.d daily.stat_date ORDER BY ds.d;6.2 会员到期、订单超时、过期清理会员到期查询通常用expire_at范围。比如查未来 7 天内到期的会员SELECT id, user_id, expire_at FROM member WHERE expire_at NOW() AND expire_at DATE_ADD(NOW(), INTERVAL 7 DAY);订单超时判断可以算创建时间和当前时间差SELECT id, user_id, created_at, TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS pending_minutes FROM orders WHERE status 0 AND created_at DATE_SUB(NOW(), INTERVAL 30 MINUTE);过期清理不要一次性删太多。大表删除容易锁表、拖慢主从建议分批DELETE FROM orders WHERE status 0 AND created_at DATE_SUB(NOW(), INTERVAL 2 HOUR) LIMIT 1000;循环执行直到影响行数为 0。注意DELETE条件里created_at是原始列能用索引不要写成DATE_SUB(created_at, INTERVAL 2 HOUR) NOW()。6.3 留存与活跃计算留存计算常用“首次登录日期”加“后续登录日期”。下面是一个简化版日留存模板WITH first_login AS ( SELECT user_id, MIN(DATE(login_time)) AS first_dt FROM login_log GROUP BY user_id ), login_daily AS ( SELECT DISTINCT user_id, DATE(login_time) AS login_dt FROM login_log ) SELECT f.first_dt, COUNT(DISTINCT f.user_id) AS new_users, COUNT(DISTINCT CASE WHEN DATEDIFF(l.login_dt, f.first_dt) 1 THEN l.user_id END) AS day1, COUNT(DISTINCT CASE WHEN DATEDIFF(l.login_dt, f.first_dt) 7 THEN l.user_id END) AS day7 FROM first_login f LEFT JOIN login_daily l ON f.user_id l.user_id GROUP BY f.first_dt ORDER BY f.first_dt;这里用DATEDIFF只比日期忽略时间正好符合留存按天定义。如果按小时活跃可以用TIMESTAMPDIFF(HOUR, ...)或直接按DATE_FORMAT(login_time, %Y-%m-%d %H)分组。7. 性能与常见问题排查7.1 函数包字段导致索引失效这是日期查询最大的性能杀手。比如SELECT * FROM orders WHERE DATE(created_at) 2025-03-18;即使created_at有索引MySQL 也可能无法直接使用因为它要对每一行计算DATE(created_at)。正确写法是范围SELECT * FROM orders WHERE created_at 2025-03-18 AND created_at 2025-03-19;同理WHERE YEAR(created_at) 2025也不如created_at 2025-01-01 AND created_at 2026-01-01。如果确实需要按函数结果查询可以考虑生成列加索引或者 MySQL 8.0.13 以上的函数索引ALTER TABLE orders ADD COLUMN created_date DATE GENERATED ALWAYS AS (DATE(created_at)) STORED, ADD INDEX idx_created_date (created_date);生成列会占用空间但查询时可以直接用created_date 2025-03-18。函数索引写法是CREATE INDEX idx_created_date ON orders ((DATE(created_at)));但使用前要确认版本和业务写入模式。7.2 常见错误速查表现象常见原因处理方式查某天数据只有零点一条用比较DATETIME改用当天和次日分钟显示成月份格式符写成%m分钟用%i月份用%mTIMESTAMPDIFF结果是负数参数顺序写反确认是start, end按周统计跨年错乱周模式不统一统一mode如YEARWEEK(date, 1)CONVERT_TZ返回 NULL时区表未导入导入mysql.time_zone相关表或改用应用层转换TIMESTAMP存 2040 年失败2038 上限长期时间改用DATETIMESTR_TO_DATE返回 NULL格式不匹配SHOW WARNINGS;看警告核对格式符NOW()和SYSDATE()结果不同取值时机不同主从环境优先用NOW()7.3 时区与格式的排查技巧排查日期问题我通常按这个顺序第一看SELECT sql_mode;确认严格模式第二看SELECT global.time_zone, session.time_zone, NOW(), UTC_TIMESTAMP();确认时区第三看字段类型是DATETIME还是TIMESTAMP第四看查询条件是否用了范围第五看EXPLAIN是否命中索引。EXPLAIN是排查性能的硬手段。执行EXPLAIN SELECT * FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01;关注type、key、rows、Extra。如果type是ALL说明全表扫描如果key为 NULL说明没用索引。对比DATE(created_at)写法很容易看出差异。对于大表日期范围查询还要注意范围太大时优化器可能选择全表扫描这时可以加更精确的条件或做分区。8. 从建表到报表一套可复现的实操路线8.1 建表与样本数据先准备一张订单表字段包含创建时间、支付时间、过期时间。注意created_at用DATETIME默认CURRENT_TIMESTAMP并加索引。CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, paid_at DATETIME NULL, expire_at DATETIME NULL, KEY idx_created_at (created_at), KEY idx_status_created (status, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入一些样本数据覆盖 3 月不同日期INSERT INTO orders (user_id, amount, status, created_at, paid_at, expire_at) VALUES (1001, 99.00, 1, 2025-03-01 10:00:00, 2025-03-01 10:05:00, 2025-03-01 10:30:00), (1002, 199.00, 1, 2025-03-01 15:20:00, 2025-03-01 15:25:00, 2025-03-01 15:50:00), (1003, 59.00, 0, 2025-03-02 09:10:00, NULL, 2025-03-02 09:40:00), (1001, 299.00, 1, 2025-03-08 11:00:00, 2025-03-08 11:02:00, 2025-03-08 11:30:00), (1004, 129.00, 1, 2025-03-18 20:00:00, 2025-03-18 20:01:00, 2025-03-18 20:30:00), (1005, 49.00, 0, 2025-03-18 21:30:00, NULL, 2025-03-18 22:00:00);8.2 日报、周报、月报 SQL日报直接按天分组SELECT DATE(created_at) AS stat_date, COUNT(*) AS order_cnt, SUM(amount) AS gmv, SUM(CASE WHEN status 1 THEN 1 ELSE 0 END) AS paid_cnt FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01 GROUP BY DATE(created_at) ORDER BY stat_date;周报用YEARWEEKSELECT YEARWEEK(created_at, 1) AS year_week, MIN(DATE(created_at)) AS week_start, MAX(DATE(created_at)) AS week_end, COUNT(*) AS order_cnt, SUM(amount) AS gmv FROM orders WHERE created_at 2025-03-01 AND created_at 2025-04-01 GROUP BY YEARWEEK(created_at, 1) ORDER BY year_week;月报用DATE_FORMATSELECT DATE_FORMAT(created_at, %Y-%m) AS year_month, COUNT(*) AS order_cnt, SUM(amount) AS gmv FROM orders WHERE created_at 2025-01-01 AND created_at 2026-01-01 GROUP BY DATE_FORMAT(created_at, %Y-%m) ORDER BY year_month;如果数据量大月报可以改成范围条件加预聚合表比如每天凌晨跑一次汇总表报表查汇总表避免每次扫全量订单。8.3 参数校验与结果核对写完 SQL 不要直接丢给业务。我一般做三步核对。第一用SELECT NOW(), CURDATE(), time_zone;确认环境时间第二用少量样本 data 手工算一遍比如 3 月 18 日有两条订单日报结果应该是 2第三用EXPLAIN看索引是否命中特别是WHERE created_at ... AND created_at ...这种范围。对于STR_TO_DATE和DATE_FORMAT可以用往返测试SELECT DATE_FORMAT(STR_TO_DATE(2025-03-18, %Y-%m-%d), %Y-%m-%d) AS round_trip;如果结果和输入一致说明格式符没写错。对于时区可以在两个不同time_zone会话里查同一个TIMESTAMP字段观察是否转换。对于周数可以拿年初、年末各一条数据测试YEARWEEK是否符合产品定义。我个人在实际操作中的体会是日期时间函数本身不难难的是边界和一致性。把类型选对、时区统一、WHERE用范围、格式化只在展示层做大部分问题都能提前避开。剩下的小坑比如分钟写成%m、TIMESTAMPDIFF参数写反、月末加月调整到 28 号只要在测试环境拿真实数据跑一遍基本都能现形。