MySQL 里处理日期和时间转换你只需要吃透这几个函数就够用了处理 MySQL 里的日期时间说难不难说简单也真的容易翻车。我见过很多刚入门的朋友数据表里存了一个2024-06-15 14:30:00要查某天的数据就直接拿字符串去查不出来还一脸懵也有人从 Excel 导数据日期是2024/06/15或者15/06/2024这种格式往 MySQL 里一插直接报错或者变成0000-00-00。这些问题归根结底就是没有搞清楚 MySQL 里“日期时间类型”“字符串”“时间戳”三者之间怎么转。我最早做报表系统的时候为了从一张历史表里把某个时间段的记录捞出来被STR_TO_DATE()和DATE_FORMAT()折腾了好几个晚上。后来花时间把 MySQL 里常用的日期时间转换函数系统梳理了一遍才真正觉得这块知识通了。这篇就围绕STR_TO_DATE()展开把相关的转换函数、使用场景、容易踩的坑一次讲清楚。这篇内容适合谁看日常工作里要跟日期时间打交道的后端开发、数据分析师、运维同学以及正在学 MySQL 的初学者。你看完之后至少能解决这几个问题字符串怎么变成日期、日期怎么变成字符串、时间戳和日期怎么互转、两个时间相减怎么得到时分秒、为什么你的日期查询条件总是查不到数据。1. 为什么日期时间转换总是出错先搞懂 MySQL 里的三种时间形态很多人用不好日期函数根子不在函数本身而是没理解 MySQL 里日期时间信息有三种存在形态而且它们之间不会自动替你完成所有转换。1.1 字符串、DATE/DATETIME、时间戳三者之间的天然差异第一种形态是字符串比如2024-06-15、2024/06/15 14:30:00。它就是我们肉眼看到的文本本身没有任何“日期”含义MySQL 不会因为你写了一个像日期的字符串就把当日期处理。第二种是真正的日期时间类型在 MySQL 里常见的有DATE只存年月日、DATETIME年月日时分秒、TIME时分秒、TIMESTAMP带时区概念的时间戳类型。这类数据在 MySQL 内部有固定的存储格式可以直接参与日期运算比如比较大小、加减天数、计算差值。第三种是 Unix 时间戳通常是一个整数代表从1970-01-01 00:00:00 UTC到某个时间点经过的秒数。日常操作里出问题最多的地方就是在这三种形态之间切换的时候。比如你写WHERE create_time 2024-06-15如果create_time是DATETIME类型这个条件往往查不到数据因为2024-06-15会被转成2024-06-15 00:00:00而你的数据可能是2024-06-15 14:30:00。这种隐式转换很坑人但是理解了类型差异之后就会知道正确写法应该是用范围查询或者日期函数来处理。1.2 为什么推荐“显式转换”而不是依赖隐式转换MySQL 允许在某些情况下自动做类型转换比如把字符串2024-06-15直接跟 DATE 列比较MySQL 会尝试帮你转。但这恰恰是隐患所在。首先隐式转换的规则比较隐蔽不同版本之间可能存在细微差异。其次一旦字符串格式不是 MySQL 能自动识别的样子比如15/06/2024MySQL 就转不了结果可能是一个警告加0000-00-00。最麻烦的是隐式转换容易让索引失效。我在实际优化慢查询的时候见过不少 SQL 因为WHERE date_col some_string这种写法导致全表扫描。所以做日期时间处理我的习惯是所有转换都写成显式调用函数代码可读性好行为也可预期。这就是为什么STR_TO_DATE()、DATE_FORMAT()这些函数你必须熟练掌握它们是你在三种形态之间自由切换的“翻译官”。2. STR_TO_DATE() 函数深度拆解字符串如何变成规范的日期时间STR_TO_DATE()是整个 MySQL 日期时间转换体系里最核心的函数之一。它的作用就是按照你指定的格式把一个字符串解析成DATE、DATETIME或者TIME类型。这个函数在数据清洗、数据导入、接口对接这些场景里出场率极高。2.1 核心语法format 格式串就是你的“翻译规则”它的语法很简单STR_TO_DATE(str, format)第一个参数是你手里有的字符串第二个参数是格式串告诉 MySQL 这个字符串里的每一部分分别代表什么。格式串里最常用的几个格式符我先列出来后面实战都会用到%Y四位的年份比如2024%y两位的年份比如24%m两位的月份比如06%c月份可以是1到12不强制补零%d两位的日期比如15%e日期可以是1到31不强制补零%H24 小时制的小时00到23%i分钟00到59%s秒00到59%pAM 或 PM%h12 小时制的小时01到12%T时间等同于%H:%i:%s%j一年中的第几天001到366比如下面这个例子把字符串2024/06/15 14:30:00转成 DATETIMESELECT STR_TO_DATE(2024/06/15 14:30:00, %Y/%m/%d %H:%i:%s); -- 输出2024-06-15 14:30:00格式串里的斜杠是字面量跟字符串里的斜杠一一对应。如果字符串里是-格式串里也要写-比如%Y-%m-%d。2.2 实际场景一从 Excel 导入的数据把2024/06/15变成标准日期这种场景太常见了。Excel 里导出的日期经常是2024/06/15或者2024年6月15日这种五花八门的格式。你直接往 MySQL 表里插很可能得到一堆0000-00-00。我用一个实际例子演示。假设有一张临时表tmp_import里面有个字段raw_date存的是2024/06/15这种文本。清洗的时候这样写UPDATE tmp_import SET clean_date STR_TO_DATE(raw_date, %Y/%m/%d);如果raw_date是2024年6月15日这种格式格式串就这样写SELECT STR_TO_DATE(2024年6月15日, %Y年%c月%e日); -- 输出2024-06-15这里%c和%e不要求补零所以6和15都能正确解析。这个特性在解析用户输入、第三方接口返回的数据时特别有用因为对方给的格式你没法控制只能靠格式串去适配。2.3 实际场景二前端传来的MM-DD-YYYY怎么安全入库有一些前端组件或者老旧系统日期格式是美式的06/15/2024。这种格式 MySQL 默认不会正确识别。用STR_TO_DATE()可以完成月、日、年的位置互换SELECT STR_TO_DATE(06/15/2024, %m/%d/%Y); -- 输出2024-06-15这个案例能直观说明为什么STR_TO_DATE()不能用一个固定的格式去套所有输入你必须先知道输入字符串的日期部分分别是什么含义然后像拼图一样把格式串写对。顺手一提如果字符串里带上了时间部分比如06/15/2024 14:30:00格式串可以写成%m/%d/%Y %H:%i:%s返回的就是 DATETIME。2.4 使用 STR_TO_DATE() 最容易被坑的几个点格式串写错了MySQL 不会报错而是返回NULL加一个警告。注意是NULL warning不是报错中断。这个特性在实际使用中很容易让人忽略。如果字符串里是2024-6-5格式串写成%Y-%m-%d会得到NULL因为%m期望两位数而6是一位数。这时候要用%Y-%c-%e。如果字符串里的月份是英文缩写比如Jun 15 2024格式串要用%b %d %Y。如果字符串里带了前后空格建议先用TRIM()清理一下否则也可能解析失败。我经常在数据清洗脚本里加一步检查把解析结果为NULL的行单独捞出来看原始值SELECT raw_date FROM tmp_import WHERE STR_TO_DATE(raw_date, %Y/%m/%d) IS NULL AND raw_date IS NOT NULL;这一步能帮你快速找到脏数据不至于让坏数据悄悄混进正式表。3. 日期转字符串、字符串转日期、时间戳互转一套全家桶讲明白STR_TO_DATE()解决了“字符串到日期”的方向但实际工作中还有大量反向需求日期类型怎么转成指定格式的字符串时间戳怎么变成日期日期怎么变成时间戳这些单靠一个函数是不够的我把常用的一套整理出来。3.1 日期和时间格式化的主力DATE_FORMAT()如果说STR_TO_DATE()是把字符串变成日期那DATE_FORMAT()就是它的镜像操作把日期时间类型或者可以被隐式转换的字符串变成指定格式的字符串。SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 输出当前的日期时间字符串如 2024-06-15 14:30:00这个函数最常见的用处是报表导出。比如数据表里存的是2024-06-15 14:30:00领导要的 Excel 里只想要2024年06月15日那就这样写SELECT DATE_FORMAT(create_time, %Y年%m月%d日) AS create_date FROM orders;另一个高频场景是按天分组统计。表里存的是 DATETIME统计每天的单量需要先转成日期字符串再分组SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) FROM orders GROUP BY day;这里有个细节GROUP BY day里直接用了别名MySQL 是允许的但有些团队规范可能不推荐为了保险可以写成GROUP BY DATE_FORMAT(create_time, %Y-%m-%d)。DATE_FORMAT()的格式符和STR_TO_DATE()基本一致所以只要记住一套格式串规则正反两个方向都能覆盖了。3.2 用 CAST 和 CONVERT 做简单、快速的类型转换如果需要转换的字符串本身已经是标准的YYYY-MM-DD或YYYY-MM-DD HH:MM:SS格式其实不需要绕一大圈去拼格式串直接用CAST()或者CONVERT()更省事。SELECT CAST(2024-06-15 AS DATE); -- 输出2024-06-15 SELECT CAST(2024-06-15 14:30:00 AS DATETIME); -- 输出2024-06-15 14:30:00 SELECT CONVERT(2024-06-15, DATE); -- 输出2024-06-15反过来把日期转成字符串也可以用CAST()SELECT CAST(NOW() AS CHAR); -- 输出2024-06-15 14:30:00字符串形式但是CAST()有一个局限它只能识别 MySQL 默认支持的YYYY-MM-DD这种标准格式。如果你拿2024/06/15去CAST(... AS DATE)不同版本表现可能不一样有些环境下会得到NULL。所以我的经验是标准格式用 CAST/CONVERT非标准格式一定要用 STR_TO_DATE() 指定格式串。3.3 Unix 时间戳与日期时间的互相转换现在很多系统接口之间传时间都用 Unix 时间戳比如1718433000这种十位整数。MySQL 里有两个函数专门处理这个转换。时间戳转日期时间用FROM_UNIXTIME()SELECT FROM_UNIXTIME(1718433000); -- 输出2024-06-15 14:30:00取决于你当前的时区设置日期时间转时间戳用UNIX_TIMESTAMP()SELECT UNIX_TIMESTAMP(2024-06-15 14:30:00); -- 输出1718433000这里有个时区的坑必须提醒。FROM_UNIXTIME()的结果会受 MySQL 会话的time_zone参数影响。如果你的应用服务器和数据库时区设置不一致同一个时间戳转出来的日期时间可能跟预期差几个小时。团队协作时最好统一约定所有环境都使用同一个时区否则排查问题的时候你会怀疑人生。我在公司里就遇到过一起事故两个服务部署在不同时区的机器上一个写入的时间戳是 UTC8 的时间另一个服务用的时候没注意时区换算导致对账数据差了一个小时。最后查了半天根因就是时区配置不统一。3.4 从 DATE/DATETIME 里提取年、月、日、时、分、秒转换之外还有一类高频需求是“从完整日期时间中抽取某一部分”。比如按年统计、按月统计、只取时间部分做排班。MySQL 提供了一批提取函数YEAR(date)取年份MONTH(date)取月份DAY(date)/DAYOFMONTH(date)取日HOUR(time)取小时MINUTE(time)取分钟SECOND(time)取秒DAYOFWEEK(date)星期几注意 1 是周日DAYOFYEAR(date)一年中的第几天举个例子统计 2024 年每个月下单的用户数SELECT MONTH(create_time) AS month, COUNT(DISTINCT user_id) AS user_cnt FROM orders WHERE YEAR(create_time) 2024 GROUP BY month ORDER BY month;这类函数配合DATE_FORMAT()使用几乎能覆盖所有报表场景中对日期时间字段的加工需求。唯一要提醒的是在WHERE条件里对日期字段使用函数比如WHERE YEAR(create_time) 2024很可能导致索引失效。数据量小无所谓数据量大了建议改成范围条件WHERE create_time 2024-01-01 AND create_time 2025-01-01。4. 日期时间的运算差值计算、加减天数的正确姿势转换函数解决的是“格式”问题但工作中还有一类“计算”问题两个日期差几天、两个时间段差多少分钟、某个时间点加 3 天是几号。这部分我也一并梳理掉因为日期转换和日期运算经常一起出现。4.1 计算两个日期相差多少天DATEDIFF()DATEDIFF()返回两个日期之间相差的天数语法是DATEDIFF(expr1, expr2)结果是expr1 - expr2的天数。SELECT DATEDIFF(2024-06-15, 2024-06-01); -- 输出14你要注意它只比较日期部分即使参数里带了时间也会被忽略。比如SELECT DATEDIFF(2024-06-15 23:59:59, 2024-06-15 00:00:00); -- 输出0这两个时间点虽然相差几乎一天但日期部分相同所以结果是 0。这个行为经常让人困惑但你看文档就能发现它是故意的DATEDIFF()只处理日期部分。如果你需要精确到秒的差值得用TIMESTAMPDIFF()。4.2 计算两个时间相差多少秒、分钟、小时TIMESTAMPDIFF()TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)是更强大的差值函数unit可以指定SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR。注意参数顺序跟DATEDIFF()是反过来的TIMESTAMPDIFF的结果是expr2 - expr1。计算两个时间相差多少秒SELECT TIMESTAMPDIFF(SECOND, 2024-06-15 14:30:00, 2024-06-15 14:35:20); -- 输出320计算两个时间相差几分钟SELECT TIMESTAMPDIFF(MINUTE, 2024-06-15 14:30:00, 2024-06-15 15:00:00); -- 输出30热词里提到的“时间相减得到分秒”用这个函数特别方便。比如想得到HH:MM:SS格式的差值可以先把总秒数算出来再用SEC_TO_TIME()转成时间格式SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, start_time, end_time)) AS duration FROM task_log;假如某个任务从09:00:00跑到10:15:30这条 SQL 输出的duration就是01:15:30特别直观。4.3 日期加减天数、月份DATE_ADD() 和 DATE_SUB()DATE_ADD(date, INTERVAL expr unit)用于在日期时间上加上一个间隔DATE_SUB()就是减法。也可以用date INTERVAL expr unit这种运算符写法。SELECT DATE_ADD(2024-06-15, INTERVAL 30 DAY); -- 输出2024-07-15 SELECT DATE_ADD(2024-06-15, INTERVAL 1 MONTH); -- 输出2024-07-15 SELECT 2024-06-15 14:30:00 INTERVAL 2 HOUR; -- 输出2024-06-15 16:30:00这个函数的应用场景太多了。比如你要查最近 7 天产生的订单SELECT * FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 7 DAY);再比如你要算用户的会员到期日下单时间 31 天。DATE_ADD配合INTERVAL就能轻松完成。你还可以用负数的INTERVAL达到减法效果但为了可读性建议减法用DATE_SUB()不要写成DATE_ADD(date, INTERVAL -1 DAY)。这里特别提醒一个小坑DATE_ADD(2024-01-31, INTERVAL 1 MONTH)的结果是什么MySQL 返回的是2024-02-292024 年是闰年。如果当年不是闰年则是2024-02-28。MySQL 在处理月末日期溢出时会自动截断到当月最后一天不会像某些语言那样抛异常。做账单、订阅续费之类业务时要留意这个行为是否符合你的业务预期。4.4 求两个日期里的最大、最小值和今天所属周期热词里有“取三个日期中最大的”MySQL 提供了GREATEST()和LEAST()两个函数虽然它们不是专门的日期函数但可以直接传日期参数SELECT GREATEST(2024-06-15, 2024-06-01, 2024-07-01); -- 输出2024-07-01注意GREATEST()返回的是参数列表里最大的那个值不是“最晚/最早”这种语义。如果参数里有NULL结果是NULL业务上要注意做空值保护。另外还有一个高频需求获取当前日期、当前时间、今天还剩多少秒。最常用的几个SELECT NOW(); -- 当前日期时间 2024-06-15 14:30:00 SELECT CURDATE(); -- 当前日期 2024-06-15 SELECT CURTIME(); -- 当前时间 14:30:00这几个函数在生成默认值、写日志表、做定时任务判断时经常用到。5. 日期时间转换中的常见坑与排查技巧这一节把我这些年实际踩过、帮别人排查过的坑集中列一下做成一个速查表顺便聊聊排查思路。5.1 常见问题速查表现象可能原因解决方案STR_TO_DATE()返回NULL格式串跟字符串不匹配或者有前导空格核对格式符使用TRIM()清理用%c、%e兼容不补零的月日查询WHERE create_time 2024-06-15查不到数据DATETIME 字段包含了时分秒隐式转换后2024-06-15变成00:00:00改用范围查询或WHERE DATE(create_time) 2024-06-15注意大表可能索引失效FROM_UNIXTIME()结果跟预期差几个小时数据库会话时区和业务预期时区不一致检查time_zone参数统一设置为业务时区DATEDIFF()结果跟自己心算不一样DATEDIFF()只比较日期部分忽略时间精确到秒的差值用TIMESTAMPDIFF(SECOND, ...)导入的数据出现0000-00-00源数据格式 MySQL 无法识别用STR_TO_DATE()指定格式串先清洗再入库用日期函数处理大表很慢函数包装了索引列导致索引失效改成范围查询写法例如和组合DATE_FORMAT()格式化出来是 NULL第一个参数不是合法日期类型先确认参数到底是什么类型必要时先用CAST()或STR_TO_DATE()转一次5.2 排查案例复盘一个“日期格式化后全是 NULL”的坑有一次同事找我说DATE_FORMAT()怎么在他那边全是NULL。我过去看了下 SQL是这样写的SELECT DATE_FORMAT(create_time, %Y-%m-%d) FROM user_log;看字段类型是VARCHAR里面的值是2024-06-15 14:30:00看起来很正常。问题出在有些行的字符串末尾带着空格甚至藏着不可见的字符DATE_FORMAT()无法解析这种情况下的字符串返回了NULL。后来的解决方式是先用STR_TO_DATE()把字符串显式转成日期类型再做格式化SELECT DATE_FORMAT(STR_TO_DATE(TRIM(create_time), %Y-%m-%d %H:%i:%s), %Y-%m-%d) FROM user_log;这里也印证了一个原则类型转换能显式写就不要依赖隐式行为。字符串就是字符串先转成 DATE/DATETIME再用日期函数处理思路会清晰很多。5.3 排查工具和调试技巧排查日期问题时我一般会分三步走。第一步先看原始数据长什么样。把字段里CHAR_LENGTH、HEX()打出来看看确认有没有隐藏字符SELECT id, CHAR_LENGTH(raw_date), HEX(raw_date) FROM tmp_import LIMIT 10;如果HEX()里出现20就是空格出现0D、0A可能是回车换行这些都会导致解析失败。第二步把解析失败的样例单独捞出来。用STR_TO_DATE(...) IS NULL作为过滤条件反向找到异常数据重点看它们的格式规律。第三步分步验证格式串。临时在SELECT里只取一段内容测试比如先看%Y解析对不对再看%m、%d逐段缩小问题范围。这种排查方法虽然看起来笨但非常有效尤其面对脏数据时一步一步验证比瞎猜快得多。6. 存储过程里处理日期时间能不能套用这些函数最后再讲一个热词里反复出现的场景MySQL 存储过程中处理日期时间。很多人写存储过程做定时统计、数据归档结果卡在日期时间转换上。存储过程里用的日期函数跟普通 SQL 完全一样没有特殊语法。唯一的区别是你要把变量声明清楚。比如写一个按天归档的存储过程判断“今天”的数据DELIMITER $$ CREATE PROCEDURE archive_daily() BEGIN DECLARE v_today DATE; DECLARE v_start DATETIME; DECLARE v_end DATETIME; SET v_today CURDATE(); SET v_start v_today; SET v_end DATE_ADD(v_today, INTERVAL 1 DAY); INSERT INTO orders_archive SELECT * FROM orders WHERE create_time v_start AND create_time v_end; DELETE FROM orders WHERE create_time v_start AND create_time v_end; END$$ DELIMITER ;这个例子里我刻意用了“大于等于 start 且小于 end”的写法而不是用DATE(create_time) v_today。原因前面说过范围写法能走索引性能更有保障。这也是我写存储过程处理日期数据时最常用的一种模式。存储过程里还经常遇到拼接字符串生成时间区间的情况比如生成上个月的第一天和最后一天。MySQL 没有直接函数返回“上个月第一天”但可以组合出来SET v_first_day DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), %Y-%m-01); SET v_last_day LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH));LAST_DAY()返回一个日期所在月份的最后一天非常实用。比如LAST_DAY(2024-02-01)会返回2024-02-29闰年不用自己判断平年闰年省了很多麻烦。我用存储过程做数据归档已经三四年了个人体会是存储过程里所有日期时间相关的字段进出都强制走显式转换。因为存储过程往往要跑很久中间如果产生隐式转换的问题排查成本比在普通 SQL 里高得多。写在最后的一个经验关于 MySQL 日期时间转换我最后分享一个自己的习惯写 SQL 之前先问自己字段是什么类型再决定用哪个函数。如果源数据是字符串非标准格式直接上STR_TO_DATE()。如果源数据是 DATE/DATETIME想要输出成指定格式文本用DATE_FORMAT()。如果是标准格式字符串赶时间可以用CAST()或CONVERT()。遇到 Unix 时间戳用FROM_UNIXTIME()和UNIX_TIMESTAMP()。做差值运算整天差用DATEDIFF()精确差值用TIMESTAMPDIFF()。需要加一天加一个月用DATE_ADD()/DATE_SUB()注意月末行为。这一套组合拳打下来我这些年处理过的跟日期时间相关的需求基本都能稳稳拿下。你如果正在被 MySQL 的日期时间转换困扰照着这里面的思路和代码去试大概率能直接解决问题。如果还有更特殊的场景建议先查官方文档对格式符的定义再拿几条真实数据测试别急着写进正式逻辑。数据无小事日期时间尤其如此。