MySQL实用系列：日期格式化详解与常见用法

为什么日期格式化在 MySQL 里总是高频需求

在实际开发里，日期时间字段几乎无处不在：订单创建时间、支付时间、登录时间、统计周期、日报时间、数据分区时间。表里存的是 DATE、DATETIME、TIMESTAMP 这类原始值，但到了查询层、报表层、接口层，往往需要把它们变成更适合人阅读或更适合分组统计的形式。

MySQL 的日期格式化，核心并不复杂，真正容易出问题的地方主要有三个：

1. 不清楚不同日期类型的语义区别，导致格式化结果和预期不一致。
2. 只会把日期“显示出来”，不会把格式化用于查询、统计、分组和清洗。
3. 在 WHERE、GROUP BY 里滥用函数，影响索引命中和查询性能。

所以这个主题不能只讲一个 DATE_FORMAT() 函数就结束。真正实用的写法，应该包括格式化规则、常见业务写法、性能注意点，以及和 STR_TO_DATE()、UNIX_TIMESTAMP()、FROM_UNIXTIME() 的配合使用。

---

先分清 MySQL 里几种常见日期时间类型

在讲格式化之前，先把类型边界说明白，否则很多结果看起来“像 bug”，其实只是类型本身的行为不同。

DATE

只保存日期，不保存时间部分。

例如：

'2026-03-10'

适合生日、结算日、出库日期这类只关心“哪一天”的场景。

TIME

只保存时间，不保存日期部分。

例如：

'15:30:45'

适合营业时段、时长表达、某天内的时间点。

DATETIME

保存完整日期和时间，常见格式：

'2026-03-10 15:30:45'

它更像“字面值时间”，不带时区转换语义。业务系统里大量使用它来保存创建时间、更新时间。

TIMESTAMP

也是日期时间类型，但它和时区有关系。MySQL 在存储和读取 TIMESTAMP 时，会受到当前会话时区影响。

这意味着：

• 同一条数据在不同时区下读取，显示值可能不同
• 做跨时区系统时，需要特别谨慎

YEAR

只保存年份。

例如：

2026

用得不多，但在少数按年度归档、统计的场景会遇到。

---

日期格式化最常用的核心函数：DATE_FORMAT()

MySQL 中最常用的日期格式化函数是 DATE_FORMAT()。

语法如下：

DATE_FORMAT(date, format)

• date：要格式化的日期或日期时间值
• format：格式模板，使用 %Y、%m、%d 这类占位符

示例：

SELECT DATE_FORMAT('2026-03-10 15:30:45', '%Y-%m-%d %H:%i:%s') AS formatted_time;

结果：

2026-03-10 15:30:45

这个例子看起来像没变化，但它展示的是最标准、最完整的格式输出方式。

---

MySQL 日期格式化常用占位符

下面这些占位符，是实际开发里最常用的部分。没有必要机械背全，但这几个必须熟悉。

占位符	含义	示例
%Y	四位年份	2026
%y	两位年份	26
%m	两位月份，01-12	03
%c	月份，1-12	3
%d	两位日期，01-31	10
%e	日期，1-31	10
%H	24 小时制小时，00-23	15
%h	12 小时制小时，01-12	03
%i	分钟，00-59	30
%s	秒，00-59	45
%T	24 小时制时间	15:30:45
%r	12 小时制时间	03:30:45 PM
%W	星期名称	Tuesday
%w	星期数字，0-6，0 表示周日	2
%j	一年中的第几天，001-366	069
%M	月份英文名称	March
%b	月份英文缩写	Mar

这里有几个特别容易混淆：

• %m 是月份
• %i 才是分钟
• %d 是日
• %H 是 24 小时制小时

很多人第一次写都会把分钟写成 %m，结果把月份重复输出了。

例如下面就是错误写法：

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

这条语句里的 %m 会输出“月份”，不是分钟。

正确写法应该是：

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

---

最常见的格式化场景

格式化为年月日

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS date_str;

结果类似：

2026-03-10

适合页面列表、导出文件、日报字段展示。

格式化为年月日时分秒

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

这是最通用的一种输出格式，接口日志、操作记录、后台表格里经常用它。

格式化为紧凑数字形式

SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%s') AS compact_time;

结果类似：

20260310153045

适合生成文件名后缀、流水号时间片段、批次号。

格式化为中文风格日期

SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分%s秒') AS cn_time;

结果类似：

2026年03月10日 15时30分45秒

这种通常用于报表导出、打印单据、面向用户的展示。

只取年月

SELECT DATE_FORMAT(NOW(), '%Y-%m') AS ym;

适合月度统计、账单周期、归档展示。

只取时分秒

SELECT DATE_FORMAT(NOW(), '%H:%i:%s') AS hms;

适合日志片段、营业时间展示、定时任务监控。

---

业务里更实用的写法，不只是“显示”

真正的实用系列，重点不在把时间显示成字符串，而在于如何把日期格式化用进业务查询。

---

场景一：按天统计订单数量

先准备一张订单表示例。

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(64) NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_created_at (created_at),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

如果要按天统计订单数，常见写法是：

SELECT
    DATE_FORMAT(created_at, '%Y-%m-%d') AS stat_day,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d')
ORDER BY stat_day;

这个写法语义清楚，适合报表查询、小规模统计，结果类似：

stat_day	order_count
2026-03-08	120
2026-03-09	156
2026-03-10	143

但这里要注意一个问题：GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d') 是对字段做函数运算，统计类 SQL 通常没问题，但如果再叠加复杂筛选和大数据量，性能要评估。

---

场景二：按月汇总销售额

SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS stat_month,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY stat_month;

这个写法非常常见，用于：

• 月度营收报表
• 月度活跃统计
• 月度新增用户数
• 月度退款趋势

如果你需要的是“自然月”，这种写法就很合适。

---

场景三：按小时统计访问量

比如用户访问日志表：

CREATE TABLE access_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NULL,
    ip VARCHAR(64) NOT NULL,
    uri VARCHAR(255) NOT NULL,
    method VARCHAR(16) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

按小时统计请求量：

SELECT
    DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') AS stat_hour,
    COUNT(*) AS pv
FROM access_log
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00')
ORDER BY stat_hour;

这个结果特别适合做折线图横坐标。

---

场景四：从日期时间里提取独立部分

有时候并不需要完整格式化字符串，只想单独取年、月、日、小时。这时候有两种方式：

用 DATE_FORMAT()

SELECT
    DATE_FORMAT(created_at, '%Y') AS year_num,
    DATE_FORMAT(created_at, '%m') AS month_num,
    DATE_FORMAT(created_at, '%d') AS day_num,
    DATE_FORMAT(created_at, '%H') AS hour_num
FROM orders;

用专用提取函数

SELECT
    YEAR(created_at) AS year_num,
    MONTH(created_at) AS month_num,
    DAY(created_at) AS day_num,
    HOUR(created_at) AS hour_num
FROM orders;

从可读性上说，提取单个部分时，用专用函数通常更直观：

• YEAR()
• MONTH()
• DAY()
• HOUR()
• MINUTE()
• SECOND()

它们和 DATE_FORMAT() 并不冲突，只是用途不同。

---

场景五：把字符串转成日期再格式化

很多旧系统、外部接口、Excel 导入数据，并不是直接给你 DATETIME，而是字符串，比如：

'20260310'

或者：

'2026/03/10 15:30:45'

这时候不能直接格式化，要先转成日期类型。MySQL 提供了 STR_TO_DATE()。

语法如下：

STR_TO_DATE(str, format)

示例一：把 20260310 转成日期

SELECT STR_TO_DATE('20260310', '%Y%m%d') AS dt;

结果：

2026-03-10

示例二：把斜杠格式时间转成标准时间

SELECT STR_TO_DATE('2026/03/10 15:30:45', '%Y/%m/%d %H:%i:%s') AS dt;

然后再继续格式化：

SELECT DATE_FORMAT(
    STR_TO_DATE('2026/03/10 15:30:45', '%Y/%m/%d %H:%i:%s'),
    '%Y-%m-%d'
) AS formatted_date;

---

场景六：时间戳和格式化之间的互转

很多系统会把时间保存成 Unix 时间戳。MySQL 中常见两个函数：

• UNIX_TIMESTAMP()：日期转时间戳
• FROM_UNIXTIME()：时间戳转日期时间

日期转时间戳

SELECT UNIX_TIMESTAMP('2026-03-10 15:30:45') AS ts;

时间戳转标准时间

SELECT FROM_UNIXTIME(1768097445) AS dt;

时间戳直接格式化输出

SELECT FROM_UNIXTIME(1768097445, '%Y-%m-%d %H:%i:%s') AS formatted_dt;

这在处理：

• 前端传来的秒级时间戳
• 日志系统中的时间戳
• 第三方平台接口字段

时特别常见。

---

DATE()、TIME()、CAST() 和 DATE_FORMAT() 的区别

很多人习惯所有场景都用 DATE_FORMAT()，其实并不总是最佳选择。

DATE()

只取日期部分。

SELECT DATE('2026-03-10 15:30:45');

结果：

2026-03-10

TIME()

只取时间部分。

SELECT TIME('2026-03-10 15:30:45');

结果：

15:30:45

CAST()

可以做类型转换。

SELECT CAST('2026-03-10 15:30:45' AS DATE);

结果：

2026-03-10

DATE_FORMAT()

更偏向“按模板输出字符串”。

SELECT DATE_FORMAT('2026-03-10 15:30:45', '%Y年%m月%d日');

结果：

2026年03月10日

可以这样理解：

• 只想截取日期部分：优先考虑 DATE()
• 只想截取时间部分：优先考虑 TIME()
• 想转成另一种数据类型：用 CAST()
• 想自定义显示样式：用 DATE_FORMAT()

---

日期格式化和查询条件，最容易踩的性能坑

这是最重要的实战点之一。

很多人写查询条件时喜欢这样写：

SELECT *
FROM orders
WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2026-03-10';

这条 SQL 逻辑上没有问题，但性能上通常不好。因为你对索引字段 created_at 做了函数计算，MySQL 往往无法高效利用普通索引。

如果 created_at 上有索引，更推荐写成范围查询：

SELECT *
FROM orders
WHERE created_at >= '2026-03-10 00:00:00'
  AND created_at < '2026-03-11 00:00:00';

这种写法的优势很直接：

1. 语义准确，表示某一天内的数据
2. 更容易命中 created_at 索引
3. 对大表更友好

同理，如果查某个月的数据，不要这样写：

SELECT *
FROM orders
WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-03';

建议改成：

SELECT *
FROM orders
WHERE created_at >= '2026-03-01 00:00:00'
  AND created_at < '2026-04-01 00:00:00';

一个很实用的原则是：

DATE_FORMAT() 更适合用于 SELECT 输出和 GROUP BY 展示，不适合直接作为高频筛选条件去包裹索引列。

---

按天去重、分组时的几个实用技巧

查询某天有哪些下单用户

SELECT DISTINCT user_id
FROM orders
WHERE created_at >= '2026-03-10 00:00:00'
  AND created_at < '2026-03-11 00:00:00';

统计每天的去重用户数

SELECT
    DATE_FORMAT(created_at, '%Y-%m-%d') AS stat_day,
    COUNT(DISTINCT user_id) AS user_count
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d')
ORDER BY stat_day;

查询每个月第一天和最后一天

可以配合日期函数：

SELECT
    DATE_FORMAT(NOW(), '%Y-%m-01') AS first_day,
    LAST_DAY(NOW()) AS last_day;

LAST_DAY() 很实用，做月报、账期、结算窗口时经常能用上。

---

周、季度、年等更进一步的格式化和统计

虽然“日期格式化”最常见是年月日时分秒，但业务统计里往往还会进一步切到周、季度、年。

按年统计

SELECT
    DATE_FORMAT(created_at, '%Y') AS stat_year,
    COUNT(*) AS total_count
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y')
ORDER BY stat_year;

按季度统计

DATE_FORMAT() 本身没有直接的季度占位符，通常配合 QUARTER() 使用：

SELECT
    CONCAT(YEAR(created_at), '-Q', QUARTER(created_at)) AS stat_quarter,
    COUNT(*) AS total_count
FROM orders
GROUP BY YEAR(created_at), QUARTER(created_at)
ORDER BY YEAR(created_at), QUARTER(created_at);

按周统计

SELECT
    YEAR(created_at) AS stat_year,
    WEEK(created_at, 1) AS stat_week,
    COUNT(*) AS total_count
FROM orders
GROUP BY YEAR(created_at), WEEK(created_at, 1)
ORDER BY stat_year, stat_week;

这里的 WEEK(created_at, 1) 里的模式值会影响周的计算规则。做周报时必须和业务定义保持一致，否则统计口径会对不上。

---

实际项目中常见的错误写法

错误一：把月份 %m 当成分钟

错误写法：

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

正确写法：

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

错误二：在 WHERE 中对索引列做格式化

错误写法：

SELECT *
FROM orders
WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2026-03-10';

更优写法：

SELECT *
FROM orders
WHERE created_at >= '2026-03-10 00:00:00'
  AND created_at < '2026-03-11 00:00:00';

错误三：把格式化后的字符串当时间继续比较

例如：

SELECT *
FROM orders
WHERE DATE_FORMAT(created_at, '%Y-%m-%d') > '2026-03-01';

虽然有时结果看起来对，但本质上你在做字符串比较，不是严格意义上的时间值比较。尤其当格式不统一时，非常容易出错。

更稳妥的方式依然是直接比较原始日期字段。

错误四：忽略时区导致 TIMESTAMP 展示异常

如果字段是 TIMESTAMP，而应用层、数据库层、连接层时区配置不一致，就可能出现“数据库里明明是 10 点，查出来变成 18 点”的问题。

这种问题不是 DATE_FORMAT() 本身造成的，而是时间类型和时区共同作用的结果。排查时必须先确认：

• 字段类型是 DATETIME 还是 TIMESTAMP
• MySQL 会话时区是什么
• 应用框架 JDBC 连接参数如何配置
• 服务器时区和数据库时区是否一致

---

MySQL 5.7 和 MySQL 8.0 在日期格式化上的区别

如果只讨论 DATE_FORMAT()、STR_TO_DATE()、UNIX_TIMESTAMP()、FROM_UNIXTIME() 这些基础函数，那么在 MySQL 5.7 和 MySQL 8.0 中，核心用法没有本质变化。

MySQL 5.7

• 基础日期格式化函数已很完整
• 常见项目中的日期显示、统计、转换需求都能满足

MySQL 8.0

• 基础格式化函数用法仍然一致
• 在复杂查询、窗口函数、CTE 等能力增强后，围绕时间统计的 SQL 组织方式更灵活
• 对分析型场景支持更好，但这不是 DATE_FORMAT() 本身的变化

也就是说：

对“日期格式化”本身而言，5.7 和 8.0 差异不大；真正的差别更多体现在复杂统计 SQL 的写法能力上，而不是格式化占位符规则变了。

---

在 Java 项目里该把格式化放在数据库还是应用层

这是实际开发中经常争论的问题。

答案不是绝对的，要看场景。

适合放在数据库层的场景

1. 报表 SQL 直接输出展示字段
2. 后台管理列表需要快速拼出日期字符串
3. 按日、按月聚合时，SQL 中顺便生成维度字段
4. 导出任务主要依赖数据库结果集

例如：

SELECT
    id,
    order_no,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS created_time
FROM orders;

这种写法很直接。

更适合放在应用层的场景

1. 一个时间字段要面向多个终端，用不同格式展示
2. 国际化、多语言、多时区展示
3. 前端可能需要原始时间和格式化时间共存
4. 你想避免 SQL 中混入太多展示逻辑

例如在 Java 中保留 LocalDateTime，交给接口层或前端去格式化，通常会更灵活。

实战建议

• 统计维度、报表维度：数据库层格式化很常见
• 面向用户最终展示：应用层更灵活
• 高性能过滤条件：不要依赖数据库格式化后再筛选

---

几条非常实用的经验结论

第一条：显示可以格式化，过滤尽量用原值范围

这是最重要的一条。

第二条：分钟是 %i，不是 %m

这个错误非常高频。

第三条：按维度统计时，DATE_FORMAT() 很方便

尤其是日、月、小时聚合。

第四条：字符串转时间要先用 STR_TO_DATE()

不要直接拿乱格式字符串去比时间。

第五条：时间戳转换要熟悉 FROM_UNIXTIME() 和 UNIX_TIMESTAMP()

这在接口对接和日志处理里几乎绕不过去。

第六条：TIMESTAMP 涉及时区，DATETIME 更像字面时间

不要把两者混为一谈。

---

一组覆盖日常工作的常用 SQL 备忘

当前时间格式化输出

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

提取日期部分

SELECT DATE(created_at) AS created_date
FROM orders;

提取时间部分

SELECT TIME(created_at) AS created_time
FROM orders;

按天统计数据量

SELECT
    DATE_FORMAT(created_at, '%Y-%m-%d') AS stat_day,
    COUNT(*) AS total_count
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d')
ORDER BY stat_day;

按月统计数据量

SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS stat_month,
    COUNT(*) AS total_count
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY stat_month;

查询某一天的数据

SELECT *
FROM orders
WHERE created_at >= '2026-03-10 00:00:00'
  AND created_at < '2026-03-11 00:00:00';

查询某个月的数据

SELECT *
FROM orders
WHERE created_at >= '2026-03-01 00:00:00'
  AND created_at < '2026-04-01 00:00:00';

字符串转日期

SELECT STR_TO_DATE('20260310', '%Y%m%d') AS parsed_date;

时间戳转日期时间

SELECT FROM_UNIXTIME(1768097445, '%Y-%m-%d %H:%i:%s') AS formatted_dt;

---

总结

MySQL 的日期格式化，表面看只是一个 DATE_FORMAT()，但真正落到项目里，它其实连接着四类问题：

1. 时间字段怎么展示
2. 时间维度怎么统计
3. 字符串和时间怎么互转
4. SQL 写法怎么兼顾性能

如果只是会写：

DATE_FORMAT(created_at, '%Y-%m-%d')

那只能算“知道这个函数存在”。真正实用的掌握，应该是：

• 知道 %Y、%m、%d、%H、%i、%s 分别代表什么
• 知道什么时候该用 DATE_FORMAT()，什么时候该用 DATE()、TIME()、YEAR()
• 知道 STR_TO_DATE()、FROM_UNIXTIME() 如何配套使用
• 知道不要在索引列筛选条件上直接套格式化函数
• 知道 DATETIME 和 TIMESTAMP 的语义区别

把这些点真正串起来，MySQL 日期格式化才算从“会用”变成“用得稳”。