做MySQL开发的朋友十有八九都被日期时间字段折磨过。平时业务表里有各种来源的数据前端传的2024/01/15、Excel导出的20240115、接口给的2024-01-15 10:23:45甚至还有2024年1月15日这种带着中文的格式。想让这些字符串规规矩矩落到DATE或TIMESTAMP列里中间踩过的坑比想象中多得多。很多人一开始觉得不就是一个转换函数的事吗真上手之后才发现字符到DATE和TIMESTAMP的相互转换牵扯到隐式转换、严格模式、时区偏移、索引失效每一个都能让线上告警响半天。这篇文章不打算把官方文档复读一遍而是把我实际处理过的数据清洗、接口对接、报表查询场景里的经验整理出来。看完之后你至少能搞明白DATE和TIMESTAMP到底差在哪、字符串怎么安全转成两种类型、两者之间怎么互相转、遇到脏数据该怎么兜底。不管你是刚接触MySQL的新手还是已经写了几年SQL的开发者后面这些细节应该都能派上用场。1. 先认识DATE和TIMESTAMP看似同门性格迥异1.1 一张表看懂两种类型的区别很多人直到吃了亏才意识到DATE和TIMESTAMP不是同一个东西的两种叫法。DATE只存日期不存时间格式是YYYY-MM-DDTIMESTAMP存日期和时间格式是YYYY-MM-DD HH:MM:SS。但它们的差异远不止多几个字符这么简单。对比项DATETIMESTAMPDATETIME顺带对比存储范围1000-01-01 至 9999-12-311970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC1000-01-01 00:00:00 至 9999-12-31 23:59:59存储空间3字节4字节8字节受时区影响否是随session时区自动换算否默认值自动更新否支持DEFAULT CURRENT_TIMESTAMP和ON UPDATE支持适用场景生日、交易日期日志时间、订单创建时间带时间的通用记录最坑的就是时区。TIMESTAMP在存储时会被转成UTC读取时再按当前会话的时区转回来。就是说同一行数据你在A时区的客户端看到的可能是10:00换到B时区连接查看就变成了18:00。DATE和DATETIME没有这个脾气存进去是什么就是什么。另外老生常谈的2038年问题也是TIMESTAMP特有的因为它的底层存储是和UNIX时间戳对应的4字节整数一到2038年1月19日凌晨3点14分07秒就溢出了。业务如果会跨很久远的时间选型时就要慎重。1.2 隐式转换最容易被忽视的隐患MySQL是门松散的语言。你往DATE列里插一个字符串它不会直接报错而是先按默认格式猜测猜不出来才报错。这个猜的过程就是隐式转换。比如下面这条INSERTINSERT INTO t_order(order_date, order_ts) VALUES (2024/01/15, 2024-01-15 10:23:45);第一列是DATE类型MySQL默认期望YYYY-MM-DD你用2024/01/15这种斜杠格式严格模式下直接报Incorrect date value但在非严格模式下它可能把数据存成0000-00-00或者给出一个warning。等到查询的时候才发现这张表里莫名其妙出现了一堆零日期。同样的道理也出现在WHERE条件里。如果你在字符串字段和DATETIME字段之间做比较MySQL会把字符串转成日期再比较整个字段上的索引通常就用不上了。这些坑都不是函数写错导致的而是类型理解不到位导致的。1.3 我的选型原则既然DATETIME那么宽松为什么还要用TIMESTAMP我个人目前的原则很简单如果业务时间需要跟随时区自动换算比如全球多地域的订单系统就用TIMESTAMP如果只是记录一个绝对时刻固定展示给国内用户直接用DATETIME更省心。很多团队为了省4字节存储选TIMESTAMP结果被时区折腾得够呛。2. 字符转DATE最常用的三种姿势2.1 CAST与CONVERT标准写法但格式挑剔字符串转DATE第一反应通常是CAST。SELECT CAST(2024-01-31 AS DATE) AS d1; SELECT CONVERT(2024-01-31, DATE) AS d2;两条语句的效果一样都是把符合YYYY-MM-DD格式的字符串转成DATE类型。这里有个先说透的规则MySQL对字符串默认日期格式的识别范围非常死板它接受-做分隔符也接受YYYYMMDD这种紧凑写法但不太能接受/和空格等乱七八糟的组合。比如CAST(20240131 AS DATE)能正常返回2024-01-31但CAST(2024/01/31 AS DATE)可能直接报错。我最初接手一个数据中台项目时上游给的全是yyyyMMdd字符串CAST倒是能对付一旦遇到2024-1-5这种不补零的写法CAST就抓瞎了。所以我的建议是CAST和CONVERT只适合格式本来就规整的字符串别指望它做清洗。2.2 STR_TO_DATE格式自由的转换主力如果你手头的日期字符串格式五花八门STR_TO_DATE才是正主。它的用法是传入两个参数待转换的字符串以及对应的格式描述符。SELECT STR_TO_DATE(2024/01/31, %Y/%m/%d) AS d1; SELECT STR_TO_DATE(20240131, %Y%m%d) AS d2; SELECT STR_TO_DATE(2024-01-31 10:23:45, %Y-%m-%d %H:%i:%s) AS dt;常见格式符有这些格式符含义示例%Y四位年份2024%y两位年份24%m两位月份01%c月份不补零1%d两位日期31%e日期不补零5%H24小时制13%h12小时制01%i分钟23%s秒45%pAM/PMAMSTR_TO_DATE的另一个特点是它返回的其实是一个DATETIME类型。如果格式串里没有时间部分时间默认为00:00:00。正因为这样它既能用于转DATE列也能用于转TIMESTAMP列后面我会细说。2.3 特殊日期字符串的容错处理现实中总有一些数据源日期字段混杂了2024.01.15、2024年1月15日、2024-1-5 10:23这种不规则写法。这时候我一般分两步走。第一步看能不能通过REPLACE统一分隔符。比如把.和/统一替换成-SELECT STR_TO_DATE( REPLACE(REPLACE(2024.01.15, ., -), /, -), %Y-%m-%d ) AS d;第二步遇到中文日期就别硬用REPLACE了老老实实用字符串函数拼出标准格式SELECT STR_TO_DATE( CONCAT( SUBSTRING_INDEX(2024年1月15日, 年, 1), -, SUBSTRING_INDEX(SUBSTRING_INDEX(2024年1月15日, 年, -1), 月, 1), -, SUBSTRING_INDEX(SUBSTRING_INDEX(2024年1月15日, 月, -1), 日, 1) ), %Y-%m-%d ) AS d;这种方式看起来繁琐但在清洗历史数据时确实有效。我建议不要把这种复杂逻辑塞到业务SQL里反复写而是建一个清洗函数或者干脆在导入阶段就用Python等外部程序处理好再入库。2.4 无效日期的拦截与兜底字符串转日期最大的麻烦不是格式而是格式对但日期不存在。比如2025-02-30纯手工输入完全有可能出现。使用STR_TO_DATE时MySQL对无效日期会返回NULL而不是报错SELECT STR_TO_DATE(2025-02-30, %Y-%m-%d); -- 返回 NULL但如果你用的是CAST(2025-02-30 AS DATE)在严格模式下可能会直接抛Incorrect date value非严格模式下则可能变成0000-00-00。这种行为受sql_mode影响很大。我的习惯是在真正写入表之前先用SELECT加CASE WHEN把每一天数check一遍非法数据单独拎出来记日志或者打回上游别让它进正式表。SELECT raw_date, CASE WHEN STR_TO_DATE(raw_date, %Y-%m-%d) IS NULL THEN 非法日期 ELSE STR_TO_DATE(raw_date, %Y-%m-%d) END AS checked_date FROM temp_raw;判断逻辑不复杂但能避免事后查数时被一堆0000-00-00干扰。3. 字符转TIMESTAMP比DATE多了时间牵扯出更多门道3.1 先分清TIMESTAMP的两个含义这个必须单独说。MySQL里的TIMESTAMP是一种日期时间类型而日常开发中常说的时间戳往往指1706682600这种从1970年1月1日算起的UNIX时间戳数字。很多人把这两者搞混导致转换函数也用错。如果说的是MySQL的TIMESTAMP类型那字符串转换和前面思路一致如果说的是UNIX时间戳数字那你要用的是UNIX_TIMESTAMP和FROM_UNIXTIME这一对函数。3.2 TIMESTAMP()与STR_TO_DATE组合字符串转TIMESTAMP类型最稳妥的办法还是先用STR_TO_DATE得到一个DATETIME值再用TIMESTAMP()函数把它显式转成TIMESTAMP。SELECT TIMESTAMP(2024-01-31 10:23:45) AS ts1; SELECT TIMESTAMP(STR_TO_DATE(2024/01/31 10:23:45, %Y/%m/%d %H:%i:%s)) AS ts2;TIMESTAMP()还支持两个参数第二个参数是时间增量会加在第一个参数后面。这个特性在计算某天某时的场景很好用SELECT TIMESTAMP(2024-01-31, 10:23:45) AS ts;需要注意如果原字符串只有日期没有时间转成TIMESTAMP后时间部分是00:00:00。这在按天统计时往往没问题但如果业务上要求当天结束的时间你用00:00:00就会把所有当天数据排除在边界外得手动拼23:59:59。3.3 UNIX_TIMESTAMP与FROM_UNIXTIME的互转前几年经常有人做接口对接上游把时间以UNIX时间戳的形式传过来比如1706682600。你直接往TIMESTAMP列里插这个数字MySQL会把它当成一个数字隐式转换后常常变成稀奇古怪的日期。正确做法是用FROM_UNIXTIMESELECT FROM_UNIXTIME(1706682600) AS dt;反过来想把日期时间转成UNIX时间戳就用UNIX_TIMESTAMPSELECT UNIX_TIMESTAMP(2024-01-31 10:30:00) AS unix_ts;这里有个大坑FROM_UNIXTIME的返回值会跟着时区走。比如北京时间下午三点对应UTC时间早上七点存的UNIX时间戳数字是同一个但FROM_UNIXTIME在不同时区连接下显示出来的字符串不一样。这个问题经常在跨时区协作时爆发A看到的是15:00B看到的是07:00两人对着同一张表争论半天。如果想让展示结果稳定一个简单办法是先固定连接时区或者在查询时显式指定SET time_zone 08:00; SELECT FROM_UNIXTIME(1706682600) AS dt;3.4 时区这个隐形变量时区问题不只是影响FROM_UNIXTIME它还会影响TIMESTAMP类型的实际存储。MySQL在TIMESTAMP列存储时会把它转成UTC读取时再转回当前session的时区。所以同一行TIMESTAMP数据在不同时区的数据库连接下读出来的值是不一样的。遇到这个问题先检查两个变量SELECT global.time_zone, session.time_zone, NOW();如果time_zone是SYSTEM实际上参考的是操作系统时区这就更隐蔽了。我记得有一次测试库和正式库部署在不同地域的服务器上同样一条SQL查出来的订单时间差了好几个小时查了半天才定位到时区变量不一致。处理思路有两种一种是全局约定所有连接串里统一加时区参数比如连接池的初始化SQL里执行SET time_zone 08:00另一种是查数时用官方推荐的方式把TIMESTAMP转成DATE之前先确认参考时区否则转换结果本身就是错的。4. DATE与TIMESTAMP相互转换精确控制每一步4.1 DATE转TIMESTAMPDATE只存日期转成TIMESTAMP时时间部分自动补00:00:00。写法有这么几种SELECT CAST(2024-01-31 AS DATETIME) AS dt1; SELECT TIMESTAMP(2024-01-31) AS dt2;如果是在DATE列上操作直接传入列名即可。需要注意的是CAST成DATETIME之后如果你真要往TIMESTAMP列里写实际上MySQL接受日期时间值会自动处理。大多数情况下CAST(date_col AS DATETIME)已经能满足需求。但有的时候你想把日期转成当天某个业务时间点比如凌晨2点CAST就搞不定了。更实用的写法是SELECT DATE_ADD(CAST(2024-01-31 AS DATETIME), INTERVAL 2 HOUR) AS ts;或者更直接SELECT TIMESTAMP(2024-01-31, 02:00:00) AS ts;这种需求在排班、日报、结算场景里很常见我建议直接记这两条。4.2 TIMESTAMP转DATETIMESTAMP转DATE要简单得多时间部分会被截断SELECT CAST(2024-01-31 10:23:45 AS DATE) AS d1; SELECT DATE(2024-01-31 10:23:45) AS d2;这两条都会返回2024-01-31。注意这里的动作是截断不是四舍五入更不是向下取整。10:23:45会被直接丢掉不会影响日期部分也就没有跨天的问题因为日期部分本来就是独立的。真正容易出错的是TIMESTAMP偏移计算后再转DATE。比如要统计昨天创建的所有订单有人会写成SELECT DATE(created_ts) CURDATE() - INTERVAL 1 DAY这个写法本身没问题但如果在千万级数据表上这么查DATE(created_ts)会导致索引失效。下一条我会说索引问题这里先记住不要在索引列上套函数。4.3 输出时用DATE_FORMAT统一格式DATE和TIMESTAMP相互转换最终目的往往是为了显示成某种字符串或者对齐成某种粒度。DATE_FORMAT是最常用的输出工具它能把DATE或TIMESTAMP都格式化成你想要的字符串SELECT DATE_FORMAT(2024-01-31 10:23:45, %Y-%m-%d %H:%i:%s) AS f1; SELECT DATE_FORMAT(2024-01-31, %Y年%m月%d日) AS f2; SELECT DATE_FORMAT(2024-01-31 10:23:45, %Y-%m-%d) AS f3;如果你只想保留日期部分除了DATE()还能用DATE_FORMAT输出纯日期字符串。但要注意DATE()返回的是DATE类型DATE_FORMAT返回的是字符串。这区别在排序、分组、做关联时会有微妙影响尽量不要混用。5. 实战复盘清洗一张全是脏日期的表5.1 先看脏数据长什么样有一次我接手一个历史订单接入项目上游从旧系统导出一张CSV日期列直接以文本形式存储。打开一看情况比预想中更乱2024/01/15斜杠分隔2024-01-15 10:23:45标准时间20240115102345纯数字串2024年1月15日中文日期空字符串、0000-00-00无效占位不要指望生产环境的数据都规规矩矩越老越乱的系统越要做足清洗预案。5.2 清洗思路与SQL实现我的做法是先把原始数据导入临时表保留原始字符串列然后新增两个目标列用一条UPDATE语句做转换。注意要分开处理格式避免一条STR_TO_DATE走天下。UPDATE temp_order SET order_date CASE WHEN raw_date REGEXP ^[0-9]{4}/[0-9]{1,2}/[0-9]{1,2}$ THEN STR_TO_DATE(raw_date, %Y/%m/%d) WHEN raw_date REGEXP ^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2} [0-9]{2}:[0-9]{2}:[0-9]{2}$ THEN STR_TO_DATE(raw_date, %Y-%m-%d %H:%i:%s) WHEN raw_date REGEXP ^[0-9]{14}$ THEN STR_TO_DATE(raw_date, %Y%m%d%H%i%s) WHEN raw_date REGEXP ^[0-9]{4}年[0-9]{1,2}月[0-9]{1,2}日$ THEN STR_TO_DATE( CONCAT( SUBSTRING_INDEX(raw_date, 年, 1), -, SUBSTRING_INDEX(SUBSTRING_INDEX(raw_date, 年, -1), 月, 1), -, SUBSTRING_INDEX(SUBSTRING_INDEX(raw_date, 月, -1), 日, 1) ), %Y-%m-%d ) ELSE NULL END, order_ts TIMESTAMP( CASE WHEN raw_date REGEXP ^[0-9]{4}/[0-9]{1,2}/[0-9]{1,2}$ THEN STR_TO_DATE(raw_date, %Y/%m/%d) WHEN raw_date REGEXP ^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2} [0-9]{2}:[0-9]{2}:[0-9]{2}$ THEN STR_TO_DATE(raw_date, %Y-%m-%d %H:%i:%s) WHEN raw_date REGEXP ^[0-9]{14}$ THEN STR_TO_DATE(raw_date, %Y%m%d%H%i%s) WHEN raw_date REGEXP ^[0-9]{4}年[0-9]{1,2}月[0-9]{1,2}日$ THEN STR_TO_DATE( CONCAT( SUBSTRING_INDEX(raw_date, 年, 1), -, SUBSTRING_INDEX(SUBSTRING_INDEX(raw_date, 年, -1), 月, 1), -, SUBSTRING_INDEX(SUBSTRING_INDEX(raw_date, 月, -1), 日, 1) ), %Y-%m-%d ) ELSE NULL END ) WHERE raw_date IS NOT NULL AND raw_date ;这段SQL不短但逻辑很直观每一种格式一个分支能匹配就转匹配不了就置NULL绝不留下半吊子数据。看到这里你会发现清洗脏日期的核心不是某个函数多神通广大而是先判断格式再选对应的格式符。如果你用的是MySQL 8.0也可以考虑先把中文日期里的年、月、日替换成-再用一次STR_TO_DATE但注意月份和日期如果不补零替换后可能会是2024-1-15这时候格式符要用%Y-%c-%e或者先补零再转换反而更容易乱。5.3 校验清洗结果清洗完成不能直接认为万事大吉。我会跑三组验证第一检查空值率。清洗前后空值数量是否在预期范围内SELECT COUNT(*) AS total, COUNT(order_date) AS valid_date, COUNT(order_ts) AS valid_ts, SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) AS null_date FROM temp_order;第二抽样对比。取几条记录人工核对尤其是有时间部分的20240115102345防止格式符写错导致小时分钟错位。第三看日期范围是否合理SELECT MIN(order_date), MAX(order_date) FROM temp_order;如果某天突然出现9999-12-31或者1000-01-01多半是原数据里有占位符被当成了正常日期需要回头查。6. 常见问题与避坑实录6.1 报错定位速查表我自己日常遇到的报错和现象整理成表放在这里遇到对应情况可以直接对照报错或现象常见原因处理方式Incorrect date value字符串格式和期望格式不符改用STR_TO_DATE匹配真实格式Data truncation: Incorrect datetime value非法日期如2025-02-30转换前先判断NULL插入成功但出现0000-00-00非严格模式下隐式转换失败设置STRICT_TRANS_TABLES或提前校验查询结果相差数小时TIMESTAMP时区不一致统一连接时区检查session.time_zone索引未生效查询变慢索引列上用了DATE()等函数改写为范围条件避免函数包列2038年以后的数据报错TIMESTAMP类型范围限制换DATETIME存储字符串转数字后变成乱码忘了用FROM_UNIXTIME数值时间戳必须先转再落库6.2 索引列上别做函数转换这是我最想强调的一点。很多人喜欢在WHERE条件里写WHERE DATE(create_time) 2024-01-31逻辑完全正确但性能一塌糊涂。因为索引列被DATE()函数包裹之后MySQL没办法用B树的顺序特性只能全表扫描。更好的写法是范围条件WHERE create_time 2024-01-31 00:00:00 AND create_time 2024-02-01 00:00:00哪怕create_time是TIMESTAMP类型这样写也能命中索引。如果一定要按天分组统计在GROUP BY里先用DATE()聚合一次倒还好因为聚合本来就要扫数据但过滤条件必须避免函数包列。6.3 一点存储习惯上的建议最后聊个设计层面的经验。很多项目里日期字段用VARCHAR存储理由是上游就是这么给的。结果就是查询时每多一个转换索引就多报废一个而且脏数据的排查难度成倍上升。我的建议很直接业务表里能用DATE、DATETIME、TIMESTAMP存的时间一律不要用字符串存。如果实在要兼容历史数据也要在入口处转成日期类型再落到正式表。查询展示要什么格式最后用DATE_FORMAT输出就行。这样单一职责既好查又好维护。个人在实际操作中最深的感受是MySQL日期转换的难点从来不是函数记不住而是你搞不清当前数据的真实形态。是字符串是DATE还是TIMESTAMP决定了你该用STR_TO_DATE、CAST还是TIMESTAMP()也决定了你会不会掉进隐式转换的坑。下次再遇到日期格式报错先别急着翻函数列表把数据的每一种格式都列出来再针对性地写转换分支问题往往瞬间就清楚了。