MySQL中日期和时间戳的转换说白了就是字符、DATE、TIMESTAMP三种形态之间来回倒腾但凡是写过数据导入脚本或者报表查询的人多少都在这上面吃过亏。我自己处理过的一批订单数据源文件里日期格式五花八门有斜杠的、有点号分隔的还有末尾带AM/PM的第一次导入直接失败后来用STR_TO_DATE逐条清洗才搞定。这篇文章不绕弯子直接讲清楚字符怎么转成DATE和TIMESTAMP、日期时间怎么转回指定格式的字符串以及转换过程中最容易踩的隐式类型转换、索引失效、时区偏差这些坑适合正在写迁移脚本、接口入参校验或者排查慢SQL的开发者。1. 先分清DATE和TIMESTAMP底层字段决定转换这条路怎么走1.1 DATE、TIMESTAMP、DATETIME的存储差异对比很多人之所以在转换上报错不是函数用错了而是根本不知道自己往什么类型里写数据。MySQL里跟日期时间相关的常用类型有三个DATE、DATETIME、TIMESTAMP。DATE只有3字节存储范围是1000-01-01到9999-12-31只能表示年月日时分秒一律是零DATETIME占8字节能同时存日期和时间TIMESTAMP也存日期加时间但它的存储方式和前两者完全不同——内部是一个从1970年1月1日开始计算的整数时间戳对外显示成日期时间时会带上当前会话的time_zone时区设置。这三种类型日常用起来最直观的差异集中在存储字节、日期范围、时区影响这几项上。下面这个表是我做表结构评审时经常贴在文档里的对照在这里也放一份省得大家来回翻手册。字段类型存储字节日期范围时区影响默认值生成DATE3字节1000-01-01 到 9999-12-31无不支持 CURRENT_TIMESTAMPDATETIME8字节1000-01-01 00:00:00 到 9999-12-31 23:59:59无MySQL 5.6.5 支持默认值TIMESTAMP4字节1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC有显示受 time_zone 影响强烈依赖 CURRENT_TIMESTAMP注意TIMESTAMP不是DATETIME的别名它的本质是整数UTC时间读出来的时候再根据时区换算成字符串。这就是为什么有时候你往TIMESTAMP列里塞了一个日期字符串过段时间再看发现多了或者少了几个小时。1.2 选错类型后你会在转换上多花多少时间选错类型带来的转换成本是隐形的。我见过一个后台管理系统用户登录时间用了DATETIME字段设计没问题但另一边记录设备上报数据时用了TIMESTAMP结果设备端、服务端、数据库三层处于两个不同时区每次展示时间都要在应用层做加减还时不时差半小时——因为部分历史数据写入时客户端时区漂移了。这不是日期转换函数的问题是字段选型时就埋了雷。所以我在设计表结构时有两条很简单的经验业务层面的时间比如订单创建时间、用户生日、活动开始时间用DATETIME需要跨时区同步的设备事件、日志时间戳用TIMESTAMP并配合统一时区写入。选对了类型后面写转换逻辑能省一半力气。如果你现在已经有一张表类型选得不合适先别急着改库优先在SQL查询层把读出来的结果转成自己需要的格式等数据迁移窗口再做彻底调整。2. 字符转日期STR_TO_DATE格式串是唯一的翻译官2.1 标准ISO格式能直接转就不用麻烦STR_TO_DATE标准的ISO格式字符串可以直接转不需要任何格式串。比如2024-03-15可以直接拿给DATE2024-03-15 14:30:00可以直接拿给TIMESTAMP或DATETIME。具体到SQL里最简单的做法是把字符串字面量直接塞进列里MySQL会自动完成解析INSERT INTO t_event(event_time) VALUES (2024-03-15 14:30:00);同样SELECT DATE(2024-03-15 14:30:00)会返回2024-03-15SELECT TIMESTAMP(2024-03-15 14:30:00)返回完整的日期时间。像20240315这种紧凑写法也能识别SELECT DATE(20240315)得到2024-03-15。这些标准写法在绝大多数场景够用但要注意一个边界字符串里一旦混入其他字符比如2024-03-15 14:30少了几秒、2024 03 15用空格代替短横线MySQL的自动识别就会时灵时不灵。我的建议是凡是入库前的日期字符串第一选择永远是写成YYYY-MM-DD或YYYY-MM-DD HH:MM:SS这样连格式串都省了代码最干净。2.2 STR_TO_DATE的格式符规则与严格匹配要求一旦字符串不是标准格式MySQL就指望STR_TO_DATE给它翻译。STR_TO_DATE(str, format)的第二个参数是格式串它的规则和DATE_FORMAT完全对称输入方和输出方用同一套占位符。常用占位符我整理了一份平时写转换逻辑时照着查就行。占位符含义示例%Y四位数年份2024%y两位数年份24%m两位数月份01-1203%c月份数字1-123%d两位数日01-3115%e日数字1-315%H24小时制小时00-2314%i分钟00-5930%s秒00-5945%f微秒6位123456%b月份英文缩写Mar%M月份英文全称March%pAM/PMPM格式串和输入字符串必须严格对齐一个字符都不能差。比如2024/03/15要配%Y/%m/%d15.Mar.2024要配%d.%b.%Y微秒串要配%Y-%m-%d %H:%i:%s.%f写成这样SELECT STR_TO_DATE(2024/03/15, %Y/%m/%d); SELECT STR_TO_DATE(15.Mar.2024, %d.%b.%Y); SELECT STR_TO_DATE(2024-03-15 10:30:45.123456, %Y-%m-%d %H:%i:%s.%f);这里面有个容易被忽略的细节%m和%d严格要求输入位置有两位数字2024-3-5配上%Y-%m-%d就会解析失败或产生警告因为3和5前面缺了前导零。此时可以改用%c和%e这两个占位符接受不带补零的数字。另外%y是两位数年份MySQL对它的解释规则是00到69当作2000年到2069年70到99当作1970年到1999年。把240315解析成2024-03-15没问题但解析700101就会变成1970-01-01历史数据清洗时容易在这上面栽跟头。2.3 用CAST和CONVERT兜底能处理的范围其实很窄CAST和CONVERT能做的事情有限但胜在简洁。SELECT CAST(2024-03-15 AS DATE)可以SELECT CAST(2024-03-15 14:30:00 AS DATETIME)也可以CAST(2024-03-15 14:30:00 AS DATE)会直接丢掉时间部分只留下日期SELECT CAST(2024-03-15 AS DATE); SELECT CAST(2024-03-15 14:30:00 AS DATETIME); SELECT CAST(2024-03-15 14:30:00 AS DATE); -- 结果是 2024-03-15不过它们的解析规则比较死板字符串必须是MySQL能识别的标准日期格式。写成2024/03/15这种带斜杠的、或者2024年03月15日这种自然语言格式CAST直接报错或者返回异常。所以我的习惯是标准格式用CAST非标准格式统一走STR_TO_DATE不要在CAST上一个格式一个格式地去试浪费时间还容易漏。提示CAST不支持直接转成TIMESTAMP类型显式的CAST(2024-03-15 14:30:00 AS TIMESTAMP)在MySQL里是不合法的。要把字符串变成TIMESTAMP实际做法是写入TIMESTAMP列时由MySQL自动转换或者先用STR_TO_DATE得到日期时间再赋值给TIMESTAMP字段。3. 日期时间转字符DATE_FORMAT和UNIX_TIMESTAMP各管一段3.1 DATE_FORMAT输出格式报表字符串一次成型字符转日期用STR_TO_DATE反过来日期转字符就轮到DATE_FORMAT上场。它的格式符和STR_TO_DATE是同一套输出端用起来非常顺手SELECT DATE_FORMAT(2024-03-15 14:30:00, %Y-%m-%d %H:%i:%s); SELECT DATE_FORMAT(2024-03-15 14:30:00, %Y%m%d); SELECT DATE_FORMAT(2024-03-15 14:30:00, %Y年%m月%d日);报表场景里我最常用的是这两种一种是想按分钟对齐的时间串格式%Y-%m-%d %H:%i另一种是给下游接口用的紧凑日期格式%Y%m%d。比如导给外部系统的文件名经常拼成order_20240315.csv直接在SQL里用CONCAT把DATE_FORMAT结果拼进去省得在应用层再处理一遍字符串。这里有个经验DATE_FORMAT是纯展示函数它不会改变原有字段值所以尽量不要在WHERE条件里用DATE_FORMAT过滤数据——那样既浪费CPU又让索引失效后面第4章会详细说。输出端随便用输入端慎用。3.2 UNIX_TIMESTAMP与FROM_UNIXTIME数字时间戳的往返除了转成格式化字符串日期时间还经常要转成数字时间戳给APP端或其他服务传参。两个函数要配合着用UNIX_TIMESTAMP把日期字符串或日期时间变成整数秒FROM_UNIXTIME把整数秒变回日期时间SELECT UNIX_TIMESTAMP(2024-03-15 14:30:00); SELECT FROM_UNIXTIME(1710505800);FROM_UNIXTIME返回的字符串受当前会话的time_zone影响同一个整数秒在UTC会话里和08:00会话里显示的时间不一样。如果只是给自己看一眼没问题但要做跨系统对接就得统一认知标准。我处理跨时区接口时的做法是服务端统一返回UNIX整数秒客户端拿到后自己按本地时区格式化。这样做的好处是整数没有时区歧义不会出现你传过来的是北京时间还是UTC时间这种争论。如果你的数据要落到报表库里供多部门使用那还是老老实实存成字符串或日期时间不要在库里大量保存整数时间戳否则SQL一坨接一坨全是FROM_UNIXTIME查询时还得时时惦记时区。3.3 数字和字符混排时MySQL会先转谁日期时间、字符串、数字三类值放在同一个表达式里时MySQL有一套隐式转换规则数字和字符串比较字符串会尽量转成数字日期时间和字符串比较字符串会尽量转成日期时间。这个方向很多人搞反以为字符串一定都变成数字。举个例子接口传参经常是UNIX秒数的字符串比如1710505800。你写WHERE event_ts 1710505800和WHERE event_ts 1710505800结果通常一样因为字符串能安全转成数字。但如果参数格式不干净比如带了个空格或者逗号MySQL转数字失败时并不会直接报错而是把它转成0然后拿0去和所有行比较查询结果直接崩。这种问题最难排查因为表面上看不出类型转换有什么错就是结果不对。所以涉及数字时间戳的字段我的建议是应用层传参时就明确用数字类型不要在SQL里依赖隐式转换。日期字段则相反传入标准字符串让MySQL自动转成日期再比较比你自己在SQL里写UNIX_TIMESTAMP(字段)要稳妥得多。4. 隐式转换的暗坑MySQL替你猜格式时经常两头挨打4.1 字符串和日期列做等值比较查得到查不到只差一秒这是我在线上排障时遇到最多的一类问题。表里create_time是DATETIME存的是2024-03-15 10:30:00业务方传过来一个查询参数2024-03-15SQL写成WHERE create_time 2024-03-15结果查不到这一行。原因很简单MySQL把字符串2024-03-15转换成了2024-03-15 00:00:00再和列里的2024-03-15 10:30:00做等值比较当然不相等。这不是数据丢了是转换后的值不在同一秒上。很多人遇到这个问题第一反应是转成DATE再比较于是改成WHERE DATE(create_time) 2024-03-15这样能查出数据但代价是索引失效。正确做法是写成范围查询让字符串常量在右边裸列在左边既利用了索引又保证语义正确WHERE create_time 2024-03-15 00:00:00 AND create_time 2024-03-16 00:00:00提示BETWEEN 2024-03-15 00:00:00 AND 2024-03-15 23:59:59也能查到当天数据但边界处理不如上面的左闭右开写法干净。尤其是涉及微秒的列23:59:59.999会被漏掉用下一天凌晨更保险。4.2 函数套在索引列上转换写得顺手索引就不干活上一节提到的WHERE DATE(create_time) 2024-03-15能查出数据可数据量一大就暴露问题。DATE()把create_time这个索引列整个包了起来MySQL无法用BTree索引有序查找只能老老实实全表扫描把所有行的值先算一遍DATE()再比较。其实从转换的角度理解就通了对一行数据做函数加工等于每一行都要执行一次日期转字符再比较的隐式操作成本和直接扫全表差不多。优化方案就是4.1里的范围查询左边保持create_time原样右边的字符串由MySQL转成日期常量这样索引能正常走范围扫描。我自己检查慢SQL时有个习惯先看WHERE条件里有没有日期时间列被函数包住像DATE()、DATE_FORMAT()、YEAR()、MONTH()这些一旦出现就考虑重写。MySQL 8.0支持函数索引可以给DATE(create_time)建索引但常规的BTree索引方案里范围查询永远是最简单可靠的选择。4.3 连接时区和sql_mode隐式转换之外的隐形变量还有一个常见的八小时误差问题不在SQL函数层面而在连接参数层面。比如Java连接MySQL时JDBC串里serverTimezone配置得不对驱动拿到TIMESTAMP列的值后会按驱动默认时区做一次换算结果程序里显示的时间比数据库里少八小时或多八小时。很多人误以为是数据库存错了反复查转换函数其实只要把连接串的serverTimezone和数据库实际时区对齐问题立刻消失。sql_mode也直接影响日期转换的结果。老库很多没开严格模式2024-13-45这种非法日期插入时会自动变成0000-00-00并给出警告而MySQL 8.0默认的严格模式会直接报错。这种差异会导致同一个转换脚本在两个环境跑出不一样的结果。我的习惯是在所有环境统一显式设置sql_mode并把NO_ZERO_DATE、STRICT_TRANS_TABLES这些项对齐从源头杜绝零日期混进业务表。5. 让转换服务于业务导入清洗、报表分组与触发器冗余5.1 批量导入时的脏数据清洗实际项目里最脏的日期数据都来自CSV和Excel一个列里混着好几种格式直接INSERT必挂。我处理过一个供应商文件里面既有2024/03/15也有15-Mar-2024还有20240315不洗根本没法入库。我的清洗思路是分两步先用CASE WHEN判断格式类型再统一交给STR_TO_DATE转换。比如先把斜杠替换成短横线让大部分数据落进标准格式剩下特殊格式逐个加分支INSERT INTO clean_table(event_date) SELECT STR_TO_DATE( CASE WHEN dt LIKE %/% THEN REPLACE(dt, /, -) WHEN dt LIKE %-% THEN dt ELSE NULL END, %Y-%m-%d ) FROM raw_table;这种写法的好处是逻辑清晰每一条脏数据能对应到具体分支。如果清洗过程有异常数据我最喜欢用的排查方式是先把SELECT部分跑一遍不急着INSERT看转换结果里哪些行是NULL再回头补分支。记住一条原则转换前的原值要保留一列不要直接覆盖否则洗坏了想恢复只能重新导文件。5.2 报表按周、按月、按季度分组转换函数和分组字段的取舍报表统计是DATE_FORMAT最典型的应用场景。按月份分组最直接的写法是SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) FROM t_order GROUP BY month;按季度可以配合YEAR和QUARTERCONCAT(YEAR(create_time), -Q, QUARTER(create_time))得到2024-Q1这种标签。按ISO周数分组则用%x-%v组合它遵循周一作为一周第一天、跨年按周归属的规则比%U这种以周日为起点的分组更符合国际惯例。问题出在大表上GROUP BY里对create_time使用转换函数每条分组都要执行一次日期转字符的计算数据量一旦上千万这个分组维度就会成为慢查询的源头。更稳的做法是建一个生成列把月份字符串在写入时就算好再对这个生成列建索引ALTER TABLE t_order ADD COLUMN month_str VARCHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(create_time, %Y-%m)) STORED; ALTER TABLE t_order ADD INDEX idx_month(month_str);这样报表SQL只GROUP BY month_str不用每次查询都现转。这个方案我在一个订单报表项目里实测过月维度聚合从原来的十几秒降到了两秒内。不过要注意生成列占用额外存储只针对明确的高频查询维度加别把每个可能的格式都加一列。5.3 触发器写冗余字符串列这招要慎用有些人为了让查询快一点会在表里同时存一个create_time和display_time字符串字段再用BEFORE INSERT触发器生成格式化值比如CREATE TRIGGER trg_event_fmt BEFORE INSERT ON t_event FOR EACH ROW SET NEW.display_time DATE_FORMAT(NEW.event_time, %Y-%m-%d %H:%i:%s);这个方案能跑但我不推荐在核心业务表里用。原因是触发器逻辑藏得深排查问题时大家往往盯着SQL本身压根想不到还有一个自动生成的冗余字段而且只要显示格式一变就得改触发器还要同步更新历史存量数据非常被动。如果真要冗余格式化字段生成列比触发器干净得多——它是表结构的一部分逻辑肉眼可见支持索引也不需要额外维护触发器。我在实际项目里只在一种情况下见过触发器方案成立下游系统强制要求直接读一个字符串字段且不允许改查询逻辑比如某些老旧报表平台。这种情况可以直接用生成列替代触发器效果一样维护成本低很多。除此之外我更推荐查询时用DATE_FORMAT现算等真实出现性能瓶颈再考虑冗余。6. 时区、边界值与存储规范转换之前先把底子打好6.1 time_zone参数同一个时间戳换个时区就变脸TIMESTAMP列的时区特性是日期时间转换里最容易被误解的一块。给你一个可复现的实验先在会话里把time_zone设为UTC插入一条记录再切换会话时区再看同一行。SET time_zone 00:00; INSERT INTO t_event(event_time) VALUES (2024-03-15 14:30:00); SET time_zone 08:00; SELECT event_time FROM t_event;这时候你会发现查询结果显示的是2024-03-15 22:30:00。但同表里如果有DATETIME列插入同样的值切换时区后它纹丝不动。原因就是TIMESTAMP内部存的是UTC整数读出来时按当前会话时区换算DATETIME则原样存储原样展示和时区无关。跨时区业务的处理标准我建议定成应用层、数据库、缓存全部统一存UTC读取时在展示层按用户时区转换。需要显式转换时用CONVERT_TZ比手工加减时区差靠谱SELECT DATE_FORMAT(CONVERT_TZ(event_time, 00:00, 08:00), %Y-%m-%d %H:%i:%s) FROM t_event;CONVERT_TZ的第一个参数可以是TIMESTAMP列也可以是DATETIME值但要注意它只做时区换算不会帮你改变存储类型。手工加减小时的方法我见过不少翻车案例夏令时一出现就全乱套能用函数就别自己算。6.2 2038年、闰秒与RangeTIMESTAMP的边界兜底TIMESTAMP的存储范围上限是2038-01-19 03:14:07 UTC这就是俗称的2038问题。和2000年问题不同它不是软件不识别两位年份而是4字节整数存满了。实际影响体现在写入环节字符串2039-01-01 00:00:00用STR_TO_DATE解析本身没问题因为解析结果是DATETIME但把它插入TIMESTAMP列时严格模式直接报Out of range宽松模式可能变成零日期。所以排查这类报错时先确认目标列是不是TIMESTAMP再确认字符串有没有超范围两步就能定位。也有一个好消息MySQL的DATETIME范围到9999年业务上涉及未来长远日期比如排产计划、债券到期日、会员到期时间的字段用DATETIME完全避开2038问题。闰秒在MySQL里基本不用考虑它不支持23:59:60这种值现实业务中也很少有系统会精确到闰秒咱们按正常日期时间处理即可。6.3 存储规范少在应用层手动拼接日期字符串最后聊聊我在多轮项目实战后总结的几条存储规范不算标准答案但照着做能减少一大半日期转换纠纷。第一库内统一时区连接串的serverTimezone必须和数据库时区显式对齐不要依赖系统默认值也不要靠驱动会自动处理这种侥幸心理。第二列类型选型遵循前面说的原则业务时间用DATETIME跨时区事件用TIMESTAMP。第三应用层不要手动拼日期字符串再塞给数据库尤其不要用YYYY年MM月DD日、MM/DD/YYYY这种本地化格式入库一切非标准格式在进SQL前先洗成ISO标准串。第四点是我特别想强调的查询结果要展示给用户时格式转换尽量放在SQL的DATE_FORMAT里做或者放在前端做不要在服务端代码里写一堆手撕字符串的逻辑。你把格式转换集中在SQL里后续改格式只改一个地方排查问题时也只需要盯着SQL不用在Java、Python、数据库三层之间来回跳。最后分享点实在的体会日期转换这个主题看手册永远觉得简单写代码才知道坑在哪。我自己最深的体会是转换逻辑写得最多的不是日常查询而是数据迁移脚本。迁移脚本里我习惯先把所有日期字符串统一成ISO格式再进行类型转换因为只要有两条记录格式不一致整批导入就会中断另外千万别在WHERE子句里写DATE_FORMAT(create_time, %Y-%m-%d) 2024-03-15这种代码它看起来无害实际上数据量一大就慢得让人怀疑人生用一个范围查询就全解决了。日期时间转换这件事功能够用、规则就那几条但每一处都值得较真。