搞数据库的同行肯定都跟日期时间字段打过交道。不管你是做报表、写订单系统还是搞数据仓库MySQL里的日期时间类型几乎躲不开。但越是基础的东西越容易出幺蛾子字符串转日期格式不对、默认值设置报错、时区导致数据“变脸”、按天分组统计时缺日期……这些坑我全踩过。这篇就系统梳理一下MySQL的日期时间类型从类型选型到函数实操再到常见问题排查把能省的弯路都给你趟平。这篇内容适合刚入门的开发也适合干了几年想系统查漏补缺的同行可以直接当成实操手册来用。1. MySQL日期时间类型全景梳理1.1 五种内置类型DATE、TIME、DATETIME、TIMESTAMP、YEARMySQL中的日期时间类型说来说去就是这五个DATE、TIME、DATETIME、TIMESTAMP、YEAR。很多人第一眼看到会以为DATETIME和TIMESTAMP差不多实则差异不小。先看各自管什么DATE只保存日期格式是YYYY-MM-DD比如2024-06-18不关心时分秒。TIME只保存时间格式是HH:MM:SS比如14:30:00还可以带小数秒比如14:30:00.123456。DATETIME日期时间格式YYYY-MM-DD HH:MM:SS比如2024-06-18 14:30:00范围很大从公元1000年到9999年。TIMESTAMP也是日期时间但本质是时间戳存的是从1970-01-01 00:00:00 UTC到指定时刻的秒数或者微秒数取决于小数位范围比DATETIME小得多只能到2038年。YEAR只存年份格式YYYY可以缩写为两位但建议用四位。这里最容易被人忽略的是TIME类型很多人以为它就是存个HH:MM:SS实际上它还能表示负值和大于24小时的值。比如你记录某个任务的耗时时长超过了24小时TIME类型可以直接存36:30:00这在一些统计场景里非常实用。另外MySQL 8.0.19之后TIME、DATETIME、TIMESTAMP都支持小数秒精度可以指定到微秒级别也就是DATETIME(6)、TIME(3)这种写法。做高频交易或埋点统计时这个特性非常有用。1.2 各类型存储长度与取值范围搞清楚了各自含义再看底层存储和边界值因为这直接关系到你建表时的选型和容量规划。直接上表类型存储长度范围默认格式YEAR1字节1901~2155或0000YYYYDATE3字节1000-01-01 ~ 9999-12-31YYYY-MM-DDTIME3字节加小数秒时额外增加-838:59:59 ~ 838:59:59HH:MM:SSDATETIME8字节加小数秒时额外增加1000-01-01 00:00:00 ~ 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SSTIMESTAMP4字节加小数秒时额外增加1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTCYYYY-MM-DD HH:MM:SS注意几个关键点TIMESTAMP的范围限制。2038年问题在MySQL里是真实存在的。如果你的系统要存几十年后的数据比如保险、养老金业务千万别用TIMESTAMP老老实实选DATETIME。TIME类型有正负。它能表示 “-838小时59分59秒” 到 “838小时59分59秒”实际上可以理解为“相对时间偏移量”不仅仅是当天几点。0值的支持。MySQL默认允许日期时间类型存储0000-00-00这种“零值”但在严格模式下会被禁止。这个我们在第4章细说因为这里藏着一个高频报错。还有一点版本变化。MySQL 5.6.4之前DATETIME是8字节TIMESTAMP是4字节5.6.4之后如果加了小数秒存储长度会动态增加。而在MySQL 8.0中DATETIME的存储变了不再像以前那样用8字节固定存储而是在不同小数秒精度下会占用5~8字节不等。细节不用记死但你要知道一个原则不要为了省几个字节去用TIMESTAMP除非你有明确的时区自动转换需求。2. 怎么选日期时间类型选型实战2.1 DATETIME vs TIMESTAMP经典抉择每次建表只要是带时间的字段都逃不过DATETIME和TIMESTAMP二选一。很多新手直接选DATETIME理由是“范围大”也有一部分老系统还在用TIMESTAMP理由是“自动更新方便”。到底该怎么选我把决策要点拆成三条需求范围。你的业务数据会不会超过2038年会果断DATETIME。数据不会超过两个都行。时区行为。TIMESTAMP是UTC时间戳查询时MySQL会依据会话时区自动转换显示。如果你的业务需要多时区支持比如全球部署、跨国订单TIMESTAMP可以省去你手动转换的麻烦。反过来如果只在单一时区用DATETIME不会“偷偷改时间”更好排查。自动初始化与更新。TIMESTAMP在MySQL 5.6.5之前一个表里只允许一个字段默认使用CURRENT_TIMESTAMP并在更新时自动刷新。现在虽然放宽了但如果你依赖自动更新最好显式写清楚。我的建议很直白90%的业务场景直接用DATETIME。原因很简单现代服务器存储不缺那4个字节而DATETIME没有2038问题也没有隐式时区转换带来的“灵异事件”。时区问题交给应用层处理或者在查询时用CONVERT_TZ()显式转换都比依赖TIMESTAMP的隐式行为更可控。2.2 时区处理TIMESTAMP的自动转换与DATETIME的“老实”时区是最容易让人抓狂的点。我见过一个真实案例有人在两台不同时区的服务器上查询同一张表发现TIMESTAMP字段差了8个小时排查半天发现是MySQL的时区配置不一致。TIMESTAMP的行为逻辑插入的时候会将当前会话时区下的时间转换成UTC存储查询的时候再根据当前会话时区把UTC转回本地时间。所以它是“会变”的。DATETIME就“老实”多了你存的是什么查出来就是什么不带你转换。因此如果你用TIMESTAMP务必检查会话时区。查看命令很简单SELECT global.time_zone, session.time_zone;如果结果是SYSTEM或00:00并且你的服务器是东八区可能就会对不上。最稳妥的方式是在连接初始化时指定时区比如JDBC连接串上加serverTimezoneAsia/Shanghai或者启动参数加--default-time-zone08:00。而如果需要格式化显示MySQL也提供了CONVERT_TZ(dt, from_tz, to_tz)函数比如SELECT CONVERT_TZ(2024-06-18 14:30:00, 08:00, 00:00); -- 结果2024-06-18 06:30:00注意时区表不是默认加载的如果报错说时区表为空需要执行mysql_tzinfo_to_sql命令导入操作系统的时区数据。2.3 业务场景推荐表聊完原理给一个可以直接抄的选型表业务场景推荐类型理由用户注册时间、订单创建时间DATETIME范围广无时区隐式转换业务可读性好日志表记录时间DATETIME(6)支持微秒方便排查同一秒内多条日志全局消息或需要按UTC存储的时间TIMESTAMP多时区业务下自动转换仅记录年份如毕业年份YEAR省空间任务耗时统计可能超过一天TIME支持大于24小时的值业务有效期截止日如优惠券到期DATE只需日期不需要具体时刻这张表不是绝对标准但按这个方向选基本不会踩大坑。如果你的表数据量极大要按时间做分区建议统一用DATETIME做分区键避免TIMESTAMP的2038问题在未来某一天突然成为瓶颈。3. 日期时间类型的核心操作与函数实战3.1 获取当前日期时间NOW、CURDATE、CURTIME、SYSDATE的区别这些函数是日常写SQL用到最频繁的。但很多人不知道NOW和SYSDATE有细微差别。NOW()返回的是语句开始执行时的日期时间。SYSDATE()返回的是函数被调用的那一刻的日期时间。在一条慢SQL里如果后面还有SYSDATE它可能会比NOW更接近实际执行时间导致结果不一致。更关键的是SYSDATE会破坏查询的复制和缓存一致性主从复制时容易出问题。所以官方也建议尽量用NOW而不是SYSDATE。常用写法SELECT NOW(); -- 2024-06-18 14:30:00 SELECT CURDATE(); -- 2024-06-18 SELECT CURTIME(); -- 14:30:00 SELECT UTC_TIMESTAMP(); -- 2024-06-18 06:30:00 (UTC时间)如果只需要当前时间戳还可以用UNIX_TIMESTAMP()SELECT UNIX_TIMESTAMP(); -- 1718706600秒级时间戳反过来把时间戳转成日期时间SELECT FROM_UNIXTIME(1718706600); -- 2024-06-18 14:30:003.2 日期格式化与字符串互转DATE_FORMAT、STR_TO_DATE、CAST/CONVERT这个板块是重灾区。网上经常有人问“日期转字符串怎么转”“字符串转日期报错怎么办”。先说格式化输出。用DATE_FORMAT(date, format)比如SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 结果2024-06-18 14:30:00常见的格式符有%Y四位年份%y两位年份%m两位月份01-12%d两位日01-31%H24小时制小时00-23%i分钟00-59%s秒00-59%W星期名称Monday%j一年中的第几天001-366再说字符串转日期。核心函数是STR_TO_DATE(str, format)。它跟DATE_FORMAT正好互为逆操作SELECT STR_TO_DATE(2024-06-18 14:30:00, %Y-%m-%d %H:%i:%s); -- 结果2024-06-18 14:30:00如果你的字符串格式符合MySQL默认的日期格式也可以直接用CASTSELECT CAST(2024-06-18 AS DATE); SELECT CAST(2024-06-18 14:30:00 AS DATETIME);需要注意的是CAST转换要求字符串本身是标准的YYYY-MM-DD或YYYY-MM-DD HH:MM:SS格式如果不是老老实实用STR_TO_DATE并指定格式否则会返回NULL或报错。还有个小技巧如果你不知道输入的字符串具体长什么样可以先试探性地提取年月日再用STR_TO_DATE转换。比如SELECT STR_TO_DATE(18/06/2024 14:30, %d/%m/%Y %H:%i);这种场景在清洗外部导入的数据时特别常见尤其是CSV里的日期格式五花八门。3.3 日期计算与偏移DATE_ADD、DATE_SUB、DATEDIFF、TIMESTAMPDIFF日期加减和差值计算是另一大需求。比如“查询最近7天的订单”“计算两个日期相隔多少天”。DATE_ADD和DATE_SUB用法一致一个加一个减。看看例子SELECT DATE_ADD(2024-06-18, INTERVAL 1 DAY); -- 2024-06-19 SELECT DATE_ADD(2024-06-18, INTERVAL 1 MONTH); -- 2024-07-18 SELECT DATE_SUB(2024-06-18, INTERVAL 2 WEEK); -- 2024-06-04注意INTERVAL后面的单位很丰富包括MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR甚至混合表达式INTERVAL 1:30 MINUTE_SECOND。如果只想取整数差值DATEDIFF(date1, date2)返回两个日期之间相差的天数只算日期部分忽略时间。TIMESTAMPDIFF(unit, datetime1, datetime2)返回指定单位的差值单位可以是SECOND、MINUTE、HOUR、DAY、MONTH、YEAR。示例SELECT DATEDIFF(2024-06-18, 2024-06-01); -- 17 SELECT TIMESTAMPDIFF(HOUR, 2024-06-18 08:00:00, 2024-06-18 14:30:00); -- 6注意DATEDIFF是两个参数顺序相减第一个减第二个。而TIMESTAMPDIFF也是第二个减第一个容易记混自己写的时候多测一下。此外还有LAST_DAY(date)返回该月的最后一天DATE_FORMAT结合%j可以计算一年中的第几天。这些都是写报表SQL的好帮手。3.4 提取日期部分YEAR、MONTH、DAY、HOUR等有时候你不需要完整的日期时间只需要某一部分比如统计某个月的订单量、按小时聚合埋点数据。这时用YEAR()、MONTH()、DAY()、HOUR()这些函数直接提取SELECT YEAR(2024-06-18 14:30:00); -- 2024 SELECT MONTH(2024-06-18 14:30:00); -- 6 SELECT DAY(2024-06-18 14:30:00); -- 18 SELECT HOUR(2024-06-18 14:30:00); -- 14还有DAYOFWEEK()1周日7周六、DAYOFYEAR()、WEEK()、QUARTER()等。如果想按周聚合WEEK(date)或YEARWEEK(date)会更方便。这里分享一个实用技巧按天分组统计时不要直接用DATE(datetime_col)因为这样会导致索引失效。更优的做法是用范围查询比如SELECT DATE(create_time) AS day, COUNT(*) FROM orders WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-30 00:00:00 GROUP BY day;如果索引设计得当这种写法能走索引范围扫描比GROUP BY DATE(create_time)靠谱得多。4. 建表与默认值常见坑与正确姿势4.1 设置默认值为CURRENT_TIMESTAMP建表时很多开发希望给创建时间字段自动填当前时间于是直接在DEFAULT里写CURRENT_TIMESTAMP这是完全没问题的。CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), created_at DATETIME DEFAULT CURRENT_TIMESTAMP );在MySQL 5.6.5之前TIMESTAMP是唯一支持DEFAULT CURRENT_TIMESTAMP的类型DATETIME不行。但在5.6.5之后和MySQL 8.0里DATETIME也可以了所以别再说“只有TIMESTAMP能自动填充”了。如果你需要“插入时填默认值更新时不改变”上面这种写法就够。如果你想在每次更新记录时自动刷新这个时间为当前时间需要加ON UPDATE CURRENT_TIMESTAMP。4.2 不允许默认值为0MySQL的sql_mode与严格模式这个坑非常典型。新手建表时可能写CREATE TABLE demo ( dt DATETIME DEFAULT 0000-00-00 00:00:00 );结果执行的时候报错Invalid default value for dt。原因就是当前sql_mode里带有NO_ZERO_DATE和STRICT_TRANS_TABLES禁止使用零日期作为默认值。查看当前模式SELECT sql_mode;如果看到NO_ZERO_DATE要么把默认值改成合法日期比如1970-01-01 00:00:00要么修改sql_mode。但我不建议随意删掉NO_ZERO_DATE因为零日期很容易在业务层引发判断混乱尤其是对接其他语言时0000-00-00会被解析成非法时间。这里插一句如果你的项目遇到了“mysql设置默认值为0”这类搜索词绝大多数指的就是这个问题。正确做法是不要在业务表里使用零日期作为默认值改成NULL更合理。字段设计为created_at DATETIME DEFAULT NULL查询时再用IFNULL处理。4.3 自动更新列ON UPDATE CURRENT_TIMESTAMP有时候我们希望update_time字段在每次行更新时自动变为当前时间省去手动UPDATE的麻烦。写法如下CREATE TABLE orders ( id INT PRIMARY KEY, status TINYINT, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );注意这个自动更新只会在行的其他字段发生变更时触发。如果你执行UPDATE orders SET status status WHERE id 1即使值没变MySQL通常也会更新取决于是否开启binlog_row_image这个细节在排查“为什么update_time没变”时可能有用。还有一点MySQL 8.0.13之后DEFAULT子句和ON UPDATE子句支持表达式但多数场景用CURRENT_TIMESTAMP就够了。5. 实战案例字符串转日期、按日期分组统计、连续日期补齐5.1 场景一清洗日志表把字符串转成日期假设有一个日志表原始数据是从文件导入的日期字段是VARCHAR格式千奇百怪CREATE TABLE raw_logs ( id INT, time_str VARCHAR(50) );里面可能有2024-06-18 14:30:00也可能有2024/06/18 14:30甚至06-18-2024 2:30 PM。这时候用STR_TO_DATE逐个清洗UPDATE raw_logs SET time_str DATE_FORMAT( STR_TO_DATE(time_str, %d/%m/%Y %h:%i %p), %Y-%m-%d %H:%i:%s ) WHERE time_str LIKE %/%/% AND time_str LIKE %PM%;一般清洗策略是先判断格式再用对应的格式字符串转换。更稳妥的做法是直接新增一列parsed_time DATETIME然后分批UPDATE这样即使出错也不会影响原数据。ALTER TABLE raw_logs ADD COLUMN parsed_time DATETIME; UPDATE raw_logs SET parsed_time STR_TO_DATE(time_str, %Y-%m-%d %H:%i:%s) WHERE time_str REGEXP ^[0-9]{4}-[0-9]{2}-[0-9]{2};注意STR_TO_DATE如果转换失败会返回NULL不会报错除非在严格模式下。所以清洗后一定要自查看看有多少NULL值再决定怎么处理。5.2 场景二按天统计订单量并补齐缺失日期做数据报表经常遇到要按天统计每单量但有些天没有订单结果缺行。比如6月1日有单6月2日没有直接GROUP BY只能得到有订单的日期。解决思路是“造”一个完整的日期序列再左连接统计结果。可以借助MySQL的递归CTE8.0支持WITH RECURSIVE date_range AS ( SELECT 2024-06-01 AS day UNION ALL SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM date_range WHERE day 2024-06-30 ) SELECT d.day, COUNT(o.order_id) AS order_cnt FROM date_range d LEFT JOIN orders o ON DATE(o.create_time) d.day GROUP BY d.day ORDER BY d.day;这里的DATE(o.create_time)会阻止索引但小报表数据量可接受。如果数据量大可以换成o.create_time d.day AND o.create_time DATE_ADD(d.day, INTERVAL 1 DAY)利用索引。5.3 场景三计算两个日期之间的自然天数或工作日数计算两个日期相差几天DATEDIFF直接搞定。但要算工作日MySQL没有内置函数得自己写。这里提供一个简化版思路DELIMITER // CREATE FUNCTION workday_diff(start_date DATE, end_date DATE) RETURNS INT DETERMINISTIC BEGIN DECLARE diff INT DEFAULT 0; DECLARE d DATE; SET d start_date; WHILE d end_date DO IF DAYOFWEEK(d) BETWEEN 2 AND 6 THEN SET diff diff 1; END IF; SET d DATE_ADD(d, INTERVAL 1 DAY); END WHILE; RETURN diff; END // DELIMITER ;这个函数简单粗暴但适合数据量不大或临时统计场景。如果要考虑法定节假日就得引入节假日表了。记住MySQL存储过程写多了会拖慢维护效率能用SQL完成就别上函数。6. 常见问题与排查技巧实录6.1 ERROR 1067Invalid default value for create_time这个报错在章节4.2已经提过再补充一个排查思路。当你执行建表语句遇到ERROR 1067时查SELECT sql_mode;看是否有NO_ZERO_DATE和STRICT_TRANS_TABLES。找所有写上DEFAULT 0000-00-00 00:00:00的字段改成合法默认值或者用DEFAULT NULL。如果确实要兼容老系统可以在会话级别临时改模式SET SESSION sql_mode ALLOW_INVALID_DATES但这个操作别用在生产上。另外一个相关报错是ERROR 1292错误值截断也是因为插入的字符串不合法比如月份写成13日期写成32严格模式下直接拒绝。6.2 查询结果日期显示乱码或乱跳时区问题如果你的连接字符串没有指定serverTimezone或者MySQL的time_zone参数与程序所在时区不一致查询TIMESTAMP字段时就会出现“日期差8小时”的情况。排查步骤先执行SELECT NOW(), UTC_TIMESTAMP();看数据库认为当前本地时间和UTC差多少。再看global.time_zone, session.time_zone确认会话时区。如果是JDBC连接检查连接串是否有serverTimezoneAsia/Shanghai或serverTimezoneGMT%2B8。如果是在Linux服务器上用命令行连MySQL检查系统时区设置并在MySQL配置文件的[mysqld]段加一行default-time-zone08:00然后重启服务。注意修改default-time-zone会影响所有客户端。如果你有跨时区需求建议按会话设置而不是全局改死。6.3 日期索引失效的几种可能日期字段加了索引但查询还是慢先看是不是对字段套了函数。比如WHERE DATE(create_time) 2024-06-18这样写即使create_time有索引也无法使用。因为对字段做了函数运算破坏了索引有序性。正确写法WHERE create_time 2024-06-18 00:00:00 AND create_time 2024-06-19 00:00:00;同理WHERE YEAR(create_time)2024也是索引杀手应该改成create_time 2024-01-01 AND create_time 2025-01-01。还有一种情况字段是VARCHAR里面存的是2024-06-18你拿create_time 2024-06-18比较虽然MySQL会自动把字符串转成日期但可能因为字符集或排序规则问题导致索引失效。最好让日期字段用DATE或DATETIME类型别用字符串。6.4 快速速查表日期时间相关函数与最佳实践最后给一张我自己整理的速查表方便日常排查需求推荐写法注意事项当前日期时间NOW()比SYSDATE更稳定少用SYSDATE当前日期CURDATE()等效于DATE(NOW())当前UTC时间戳UNIX_TIMESTAMP()秒级秒级时间戳转日期FROM_UNIXTIME(ts)注意会话时区日期格式化DATE_FORMAT(dt, %Y-%m-%d)注意格式符大小写含义字符串转日期STR_TO_DATE(str, %Y-%m-%d)格式不匹配返回NULL日期加减DATE_ADD(dt, INTERVAL 1 DAY)单位可用MONTH/YEAR等两个日期相差天数DATEDIFF(d1, d2)d1减d2两个日期相差小时TIMESTAMPDIFF(HOUR, d1, d2)d2减d1当月最后一天LAST_DAY(dt)常用于月度账单时区转换CONVERT_TZ(dt, from, to)需要加载时区表按天分组范围条件代替DATE(col)保住索引这张表里的每一条都是我实际项目中验证过的。尤其“按天分组”这一条别偷懒用函数包字段数据量一大就会很痛苦。个人体会日期时间类型看似基础但往往是数据准确性的根基。我踩过最深的坑就是开发环境没问题、生产环境差8小时的时区问题查了两天才发现是连接串没带serverTimezone。从那以后我建表时会特别约定所有时间字段统一用DATETIME所有接口传输统一用字符串或时间戳所有涉及跨时区的转换都在应用层做。虽然看起来“笨”但维护成本最低。最后再分享一个常被忽视的小技巧写SQL的时候日期字面量尽量用标准格式比如2024-06-18或2024-06-18 14:30:00不要用2024/6/18这种写法。虽然MySQL有时候能隐式识别但依赖隐式转换是不靠谱的尤其在不同版本、不同sql_mode下行为可能不一样。规规矩矩写标准格式能省掉很多莫名其妙的兼容性问题。