企业官网建设流程全解析

做后端的人，几乎天天要和日期打交道。订单创建时间、用户注册时间、日志时间戳，到了报表阶段全部要格式化输出。MySQL里做这件事最常用的是DATE_FORMAT，但如果你只会抄一句%Y-%m-%d %H:%i:%s，那迟早要在某个凌晨被拉起来处理线上问题。这篇文章是这个系列里关于日期格式化的一篇，我会把常用的格式符、字符串和日期的双向转换、时间差计算、时区坑、还有格式化引发的索引问题一次讲透，适合所有在业务里写过日期SQL的人。

1. 日期格式化的第一道坎：先搞清楚字段里到底存的是什么

1.1 数据类型决定了你能用什么姿势格式化

很多人一上来就写DATE_FORMAT，结果发现字段是VARCHAR，或者反过来字段是TIMESTAMP但程序里一直当字符串处理，两边口径对不上。MySQL里和日期相关的字段类型主要有DATE、DATETIME、TIMESTAMP三种，它们的区别不只是精度：

• DATE只存年月日，范围是1000-01-01到9999-12-31；
• DATETIME存年月日时分秒，不带时区概念，存进去是什么样子，查出来就是什么样子；
• TIMESTAMP底层按UTC存储，展示时受time_zone参数影响。

如果业务表里用的是VARCHAR来存日期，那就更麻烦。字符串的排序规则和日期完全不同，'2024-1-5'和'2024-01-05'在人的眼里是同一天，但在字符串比较、GROUP BY分组里并不是一回事。

我见过不少团队为了图省事，在Excel导入时把日期列直接导成了VARCHAR，当时看没啥问题，等到了做月报、做同比环比的时候，各种莫名其妙的数据就出来了。所以动手格式化之前，先花一分钟确认字段类型，这是排查所有日期问题的第一步。

1.2 隐式转换：MySQL悄悄把类型换掉了

日期字符串和日期类型的比较，MySQL会做隐式转换。比如：

SELECT * FROM orders WHERE create_time > '2024-01-01';

这里MySQL会把'2024-01-01'转成日期再比较，一般没问题。但如果字段本身是VARCHAR，而你又跟日期字面量比较，MySQL会尝试把字段值转成日期，一旦有脏数据转失败，这一行就会直接卡住，甚至导致整个查询报错。

还有一类隐式转换藏得更深。比如：

SELECT * FROM users WHERE register_date = '2024-01-05';

当register_date是DATETIME类型时，MySQL的规则是：字段是日期类型，字符串会被转成日期，所以这里实际比较的是2024-01-05 00:00:00。如果你的业务本意是“1月5日一整天”，这个条件就漏掉了当天后面的23小时59分59秒。

这种问题在报表里最致命，统计出来的数据总是差几条，查半天发现是等号边界没卡对。后文我会专门讲范围查询怎么处理这种边界。

1.3 报表对不上时先查这四个地方

如果你遇到“格式化出来结果不对”的情况，不要急着怀疑函数写错，按这个顺序排查：

1. 字段类型是不是日期类型，还是字符串；
2. 连接数据库的字符集和排序规则；
3. 数据库time_zone与会话time_zone；
4. SQL_MODE里有没有开启宽松模式。

这几个因素单独看都不起眼，组合起来却能制造出非常隐蔽的异常。比如字符集不一致，可能导致日期字符串里的横杠在某些字符集下被当成特殊字符处理；time_zone不对，TIMESTAMP字段查出来会整体偏移几小时；SQL_MODE里的ALLOW_INVALID_DATES则可能让'2024-02-30'这种非法日期在宽松模式下被存进去，后面格式化时就乱了。

2. DATE_FORMAT格式符全景：背熟这张表，基本不会写错

2.1 最常用的组合和它的含义

DATE_FORMAT的核心是把日期类型按你指定的格式输出成字符串。格式串由一个个%开头的格式符组成。

例如：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

结果类似：

2025-01-14 16:08:33

这个组合是全套系统里最常用的标准格式：四位年份、两位月份、两位日期、24小时制小时、两位分钟、两位秒。只要统一用它，在同一个团队内导出日志、生成报表，格式基本不会打架。

2.2 容易混淆的格式符对照表

以下是我整理的一份格式符对照表，重点标记了平时最容易用混的几个：

2.3 分组统计的典型SQL写法

按天分组统计是报表里最常见的需求：

SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS day, COUNT(*) AS order_cnt FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01' GROUP BY day ORDER BY day;

按月分组则把格式串换成%Y-%m，按年换成%Y，按周的话可以用%Y-W%u：

SELECT DATE_FORMAT(create_time, '%Y-W%u') AS week, COUNT(*) FROM orders GROUP BY week ORDER BY week;

注意排序这里有个隐蔽的坑：GROUP BY后面的别名，在MySQL里可以直接用，但ORDER BY最好和GROUP BY的表达式保持一致。如果你ORDER BY用的是原始时间字段，在ONLY_FULL_GROUP_BY模式下要么报错，要么排序结果和分组后的字符串顺序不一致。分组后的字段已经是格式化后的字符串，直接按字符串排序即可，日期格式标准的字符串排序等于时间排序。

3. 反向操作：STR_TO_DATE把字符串精准变成日期

3.1 为什么不要只靠DATE()函数截断

很多字符串转日期的需求，比如接收前端传来的'2024-01-05 12:30:00'，有人习惯直接DATE('2024-01-05 12:30:00')取日期部分。这个操作在严格模式下容易踩坑，因为DATE()函数的隐式转换规则在不同版本、不同SQL_MODE下并不完全一致。

更推荐的做法是用STR_TO_DATE，它让你主动声明字符串的结构，而不是把判断交给MySQL的猜测逻辑。比如：

SELECT STR_TO_DATE('2024-01-05 12:30:45', '%Y-%m-%d %H:%i:%s');

返回的结果就是一个标准的DATETIME：

2024-01-05 12:30:45

3.2 匹配规则：格式符必须和字符串结构一一对应

STR_TO_DATE的第二个参数格式串，必须和第一个参数字符串的结构精确匹配。分隔符也要一致，'2024-01-05'用%Y/%m/%d去解析，结果就是NULL：

SELECT STR_TO_DATE('2024-01-05', '%Y/%m/%d'); -- 返回NULL

很多新手在这里吃了亏，以为是数据有问题，其实是格式串里的斜杠和字符串里的横杠对不上。反过来也一样：

SELECT STR_TO_DATE('2024/01/05', '%Y/%m/%d');

如果格式串对不上，MySQL会返回NULL并附一个warning。想看到具体告警信息，可以执行：

SHOW WARNINGS;

3.3 批量导入和用户输入场景下的实用技巧

从Excel或CSV导入日期时，用户可能给你一堆五花八门的格式：2024/1/5、2024-01-05、20240105。这种情况下可以用STR_TO_DATE配合CASE WHEN做多格式兼容。

比如同时兼容两种常用格式：

SELECT CASE WHEN col LIKE '%-%' THEN STR_TO_DATE(col, '%Y-%m-%d') WHEN col LIKE '%/%' THEN STR_TO_DATE(col, '%Y/%m/%d') ELSE NULL END AS parsed_date FROM import_tmp;

这里只是示例，具体还是要根据你实际收到的数据格式调整。打印出来的解析结果一旦有NULL，优先检查原始字符串里有没有多余空格、全角字符、或者是Excel把日期序列号当成字符串导出来了。

3.4 时间戳互转：别被13位毫秒时间戳坑了

时间戳格式化也是老生常谈。UNIX_TIMESTAMP可以把日期转成秒级整数，FROM_UNIXTIME则把秒级整数转回DATETIME：

SELECT UNIX_TIMESTAMP('2024-01-05 12:00:00'); -- 1704434400 SELECT FROM_UNIXTIME(1704434400); -- 2024-01-05 12:00:00

但如果前端传过来的是13位毫秒时间戳，直接传给FROM_UNIXTIME就会出问题。13位数字超出了MySQL秒级时间戳的正常范围，结果要么是NULL，要么是一个完全离谱的日期。

处理方式很简单，先除以1000：

SELECT FROM_UNIXTIME(1704434400000 / 1000);

需要注意的是，除以1000得到的是带小数点的数字，旧版本MySQL对FROM_UNIXTIME处理小数会有精度问题，稳妥起见可以先用FLOOR取整再转换：

SELECT FROM_UNIXTIME(FLOOR(1704434400000 / 1000));

4. 日期加减与时间差：算对“最近7天”没那么简单

4.1 DATE_ADD、DATE_SUB与INTERVAL表达式

日期的加减运算，最常用的就是DATE_ADD和DATE_SUB：

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE); SELECT DATE_ADD('2024-01-05', INTERVAL '1-2' YEAR_MONTH);

INTERVAL后面可以跟年、季、月、周、天、时、分、秒等，甚至可以组合'1-2' YEAR_MONTH表示1年2个月。需要提醒的是，DATE_ADD的INTERVAL如果为负数，效果等同于DATE_SUB，但写出来不直观，不建议反着用。

还有一个反直觉的点：DATE_ADD遇到月末会自动进位。比如DATE_ADD('2024-01-31', INTERVAL 1 MONTH)，结果不是“不存在的2月31日”，而是3月2日。做账单周期、会员有效期这类偏业务的日期计算时，一定要先确认口径是否能接受这种进位。

4.2 DATEDIFF与TIMESTAMPDIFF的区别

DATEDIFF只计算日期部分的差值，时间部分直接忽略：

SELECT DATEDIFF('2024-01-10 23:59:59', '2024-01-05 00:00:00'); -- 结果是5

哪怕中间隔了接近6天，DATEDIFF依然只按“日期差”来算。TIMESTAMPDIFF则更精细，可以指定单位，并且按实际时间差计算：

SELECT TIMESTAMPDIFF(HOUR, '2024-01-05 23:00:00', '2024-01-06 01:00:00'); -- 结果是2

TIMESTAMPDIFF的单位非常灵活，DAY、HOUR、MINUTE、SECOND都支持。实际业务里，判断“订单是否超时”用TIMESTAMPDIFF而不是DATEDIFF，因为分钟和秒的差异才是关键；而计算“两个日期相隔几天”用DATEDIFF就够了，反而更直观。

4.3 查“最近7天”时先统一口径

“最近7天”这四个字在业务侧可能有完全不同的含义。如果你直接写：

WHERE create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)

那包含了当前时刻开始往回推的整整168小时，而如果只想要最近7个自然日：

WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)

1天和0天的差异，关键在于要不要把“今天”算进去。这个口径问题我在实际项目里见过不止一次：产品说最近7天，开发写得是往前数7天，测试又在日历上点了7个格子，三个人三种算法，最后对不上账。

建议团队里定一个通用函数或通用SQL片段，所有“最近N天”都走同一套口径，业务口径有变化时只改一处，比每个人各写各的靠谱得多。

4.4 连续登录和同期对比的场景

计算连续登录天数时，时间差和格式化经常同时出现。比如先按用户和日期去重，再计算相邻日期差是否为1天。这里格式化就派上了用场：

SELECT user_id, DATE_FORMAT(login_time, '%Y-%m-%d') AS login_day FROM login_log GROUP BY user_id, login_day;

有了不含时间的日期字符串，后续做前后两天差值的判断就非常干净。上个月同期、上周同期这类需求，本质上是日期加减，先把基准日期算出来，再和格式化后的分组结果关联，SQL会清晰很多。

5. 时区与连接参数：报表结果对不上时先查这里

5.1 MySQL的time_zone机制：全局与会话

MySQL的时区设置分为全局级别和会话级别：

SELECT @@global.time_zone; SELECT @@session.time_zone;

如果time_zone是SYSTEM，表示跟随操作系统时区；也可以设置成具体的偏移量或时区名，比如：

SET time_zone = '+08:00';

时区主要影响NOW()、CURDATE()这些函数，以及TIMESTAMP类型的读写。如果你在一个时区写入TIMESTAMP，换了会话时区再去查，显示出来的本地时间会不一样。

5.2 TIMESTAMP和DATETIME在时区下的表现差异

TIMESTAMP从存储层面来讲记录的是UTC时间，展示的时候会根据会话时区转换成对应本地时间。DATETIME则没有这层转换，它存的是什么就显示什么。

这就导致了一个经典问题：数据库和应用程序服务器在不同机房或不同时区时，TIMESTAMP字段用DATE_FORMAT格式化出来的字符串，会比对端程序期望的本地时间差几个小时。而DATETIME虽然不随会话变，但如果写入时用的是程序本地时间，丢了时区信息，将来迁移或做多区域展示时一样会乱。

我的建议是：单区域业务优先用DATETIME，需要做全球多时区展示的业务，存时间戳或TIMESTAMP，并且在展示层统一格式化，不要在SQL里混合处理。

5.3 连接串参数和SQL_MODE的隐形影响

除了数据库本身的设置，应用程序连接串里的时区参数也会影响拿到的时间值。很多连接驱动读取时间时，如果没有明确指定时区，会以驱动默认时区解析，和数据库的时区不一致时就会偏掉几小时。

这类问题在代码里极难排查，因为数据库命令行查出来的时间是对的，程序接口返回的时间却是错的。定位方法一般是找一个已知时间戳，分别用命令行、ORM日志、连接驱动直查三个路径去跑，对比结果。哪个和期望不一致，问题就在哪一层。检查顺序可以按：数据库时区 → 连接串参数 → ORM到SQL的传输转换。

SQL_MODE也要留个心眼。宽松模式下某些非法日期字符串可以被写入，格式化时虽然不会报错，但结果会匪夷所思，比如'2024-02-30'被转成'2024-03-01'之类的进位。生产体系里尽量用严格模式，从源头卡住脏数据。

5.4 快速排查脚本

遇到线上时间不对，先在数据库里执行以下三条SQL，能快速缩小范围：

SELECT NOW(); SELECT @@session.time_zone, @@global.time_zone; SELECT CREATE_TIME, DATE_FORMAT(CREATE_TIME, '%Y-%m-%d %H:%i:%s') FROM your_table LIMIT 1;

如果第一条和应用程序所在机器的当前时间不一致，说明时区配置有问题；如果第一条一致但第三条查出来的值和程序里拿到的值不一致，问题大概率出在连接串参数上。

6. 性能自查：格式化之前，先想想索引还走不走

6.1 函数包裹索引列，索引直接失效

DATE_FORMAT用起来很方便，但如果在WHERE条件里对索引列套函数，索引就废了。这是一个很典型的写法：

SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-01-05';

看起来没毛病，但数据库必须先对每一行的create_time做格式化，然后再和字符串比较，原来的索引完全用不上，全表扫描没得跑。

换成范围查询后，优化器可以走索引：

SELECT * FROM orders WHERE create_time >= '2024-01-05 00:00:00' AND create_time < '2024-01-06 00:00:00';

这种写法不仅在性能上更优，语义上也更准确，天然包含了“1月5日一整天”这个完整区间，正好解决了前面提到的等号边界问题。

6.2 用EXPLAIN确认执行计划

光靠猜是没用的，写完后用EXPLAIN看一下：

EXPLAIN SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-01-05';

如果type列显示ALL，说明全表扫描。改成范围查询后，type应该变成range，key列能看到你用到的索引名。我在实际项目里见过因为这种格式化写法导致一个大表查询从毫秒级退化成秒级的案例，数据量大了以后差距非常明显。

ORDER BY和GROUP BY里的格式化同样要留意。如果只是对结果集做格式化展示，影响的是CPU；如果GROUP BY直接用了格式化表达式，并且行数很大，那就要评估是否要把格式化移到应用层去做了。

6.3 写入侧设计：尽量别把日期当字符串存

很多格式化问题的根源，其实在表结构设计阶段就埋下了。用VARCHAR存储日期，写入的时候不做校验，想怎么存就怎么存，查询的时候再靠各种转换去补救，最后性能和正确性双双受损。

设计表字段时的几条建议：

• 只有日期用DATE，日期加时间用DATETIME或TIMESTAMP；
• 默认值优先用DEFAULT CURRENT_TIMESTAMP，让数据库生成时间，不要靠应用层拼接字符串；
• 如果确实要存格式化好的日期字符串，考虑加一个生成列或用函数索引，避免每次查询都做全表转换。

6.4 团队规范里的几条实用约定

这些年踩过的坑总结下来，我在团队里定过几条关于日期格式化的小规矩，效果不错：

• 所有接口对外输出的时间统一用yyyy-MM-dd HH:mm:ss，跨时区再加时区后缀；
• 时间字段命名统一增加_at后缀，看到名字就知道类型；
• 禁止在WHERE条件里对索引列使用DATE_FORMAT，一律改写为范围查询；
• 日期字符串入库前必须过STR_TO_DATE，不允许直接塞VARCHAR；
• 所有统计SQL必须包含明确的开始和结束时间边界，禁止用等号卡某一天。

规矩不多，但每一条都能对应到我前面讲的某个真实事故。日期格式化看似简单，真正让团队受伤的从来不是date_format这个函数本身，而是那些容易被忽略的类型、时区、边界和索引问题。

我个人这两年最深的体会是：格式化本身只是最后一公里的展示问题，前面任何一环的类型和口径不统一，最后都会以“日期对不上”的形式集中爆发。写SQL之前多花一分钟确认字段类型和业务口径，比出了事故再排查要划算太多。