异常教程

mysql 日期格式化（手把手讲解）

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

截止目前， 星球 内专栏累计输出 90w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 3100+ 小伙伴加入学习 ，欢迎点击围观

在编程与数据库开发中，日期时间的处理是一个高频且复杂的操作场景。无论是电商系统的订单创建时间记录，还是日志分析中的时间戳解析，MySQL 日期格式化都是开发者必须掌握的核心技能之一。本文将从基础概念到实战技巧，系统化拆解这一主题，帮助读者建立完整的知识框架。

一、日期格式化的基础概念与核心函数

1.1 日期类型与存储形式

MySQL 中日期类型主要包括 DATE（仅日期）、DATETIME（日期+时间）和 TIMESTAMP（带时区的时间戳）。这些类型在存储时遵循标准化格式（如 YYYY-MM-DD HH:MM:SS），但实际业务场景中，开发者常需将其转换为更易读或符合业务规则的格式。

比喻：
可以把 MySQL 的日期存储格式想象成快递单上的标准地址，虽然格式统一，但最终用户可能需要将其转换为“省-市-区”或“邮编+详细地址”等不同形式，这就是格式化的本质需求。

1.2 核心函数：DATE_FORMAT()

MySQL 提供了 DATE_FORMAT(date, format) 函数，是实现日期格式化的核心工具。其语法结构简单，但格式化代码（如 %Y 表示四位年份）需要系统性记忆。

常用格式化代码示例

（以下表格与前文间空一行）
| 格式化代码 | 说明 | 示例输出 |
|-----------|--------------------------|------------------|
| %Y | 四位年份（如 2023） | 2023 |
| %y | 两位年份（如 23） | 23 |
| %m | 月份（补零，01-12） | 09 |
| %c | 月份（无补零，1-12） | 9 |
| %d | 日期（补零，01-31） | 05 |
| %H | 小时（24小时制，00-23） | 15 |
| %i | 分钟（00-59） | 30 |
| %s | 秒（00-59） | 45 |
| %a | 缩写星期名（如 Mon） | Mon |
| %W | 完整星期名（如 Monday） | Monday |

二、基础场景：常见日期格式转换

2.1 基础格式化示例

假设表 orders 中有一列 order_time（类型为 DATETIME），原始值为 2023-09-05 15:30:45，通过以下语句可实现不同格式转换：

-- 格式化为 "YYYY-MM-DD"  
SELECT DATE_FORMAT(order_time, '%Y-%m-%d') AS formatted_date FROM orders;  

-- 格式化为 "YYYY年MM月DD日 HH时MM分"  
SELECT DATE_FORMAT(order_time, '%Y年%m月%d日 %H时%i分') AS formatted_date FROM orders;

2.2 动态时间计算

结合 DATE_ADD() 或 DATE_SUB() 函数，可以实现带偏移量的日期格式化：

-- 获取上周同一时间的日期  
SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 WEEK), '%Y-%m-%d %H:%i:%s') AS last_week_time;

三、进阶技巧与特殊场景

3.1 反向操作：字符串转日期（STR_TO_DATE）

当需要将用户输入的非标准日期字符串（如 2023/09/05）转换为 MySQL 可识别的日期类型时，可用 STR_TO_DATE() 函数：

-- 将 "2023/09/05" 转换为标准日期格式  
SELECT STR_TO_DATE('2023/09/05', '%Y/%m/%d') AS parsed_date;

3.2 处理时区问题

通过结合 CONVERT_TZ() 函数，可实现跨时区日期格式化：

-- 将 UTC 时间转换为北京时间（+8小时）  
SELECT DATE_FORMAT(CONVERT_TZ(NOW(), '+00:00', '+08:00'), '%Y-%m-%d %H:%i:%s') AS beijing_time;

3.3 时间戳与日期的互转

利用 FROM_UNIXTIME() 和 UNIX_TIMESTAMP() 函数，可实现时间戳与格式化日期的相互转换：

-- 将时间戳 1700000000 格式化为 "YYYY-MM-DD HH:MM:SS"  
SELECT FROM_UNIXTIME(1700000000, '%Y-%m-%d %H:%i:%s') AS formatted_timestamp;  

-- 将当前日期转换为时间戳  
SELECT UNIX_TIMESTAMP(NOW()) AS current_unix_time;

四、实战案例分析

4.1 案例 1：电商订单系统的时间显示

某电商平台需在订单详情页显示“下单时间：2023年9月5日 下午3点30分”，可通过以下 SQL 实现：

SELECT  
    order_id,  
    DATE_FORMAT(order_time, '%Y年%m月%d日 %p%h时%i分') AS formatted_order_time  
FROM orders  
WHERE order_id = 12345;

4.2 案例 2：日志分析中的时间聚合

在日志分析场景中，需统计每天的访问量并按“YYYY-MM-DD”格式输出：

SELECT  
    DATE_FORMAT(log_time, '%Y-%m-%d') AS log_date,  
    COUNT(*) AS visit_count  
FROM access_logs  
GROUP BY log_date  
ORDER BY log_date DESC;

五、常见问题与解决方案

5.1 时区偏差导致的日期错误

若发现格式化结果与预期时间不一致，需检查服务器时区设置：

-- 查看当前时区  
SELECT @@global.time_zone, @@session.time_zone;  

-- 设置会话时区为 UTC  
SET time_zone = '+00:00';

5.2 性能优化建议

对大规模数据集进行格式化操作时，优先考虑以下优化：

  1. 避免在 WHERE 子句中使用函数：如 WHERE DATE_FORMAT(create_time, '%Y%m') = '202309' 会阻止索引使用，应改用 WHERE create_time >= '2023-09-01' AND create_time < '2023-10-01'。
  2. 预计算字段：对高频格式化需求，可新增计算列存储标准化格式。

六、总结

MySQL 日期格式化是开发者日常工作中不可或缺的技能，其核心在于理解日期存储逻辑、熟练掌握 DATE_FORMAT() 等函数，并结合业务场景灵活应用。本文通过基础概念、函数详解、实战案例与常见问题，系统化展示了这一主题的全貌。通过持续实践，开发者将能更高效地处理复杂的时间数据需求，为系统稳定性与用户体验提供保障。

提示：在实际开发中，建议将复杂格式化逻辑封装为存储过程或通过应用程序层处理，以平衡数据库性能与代码可维护性。

最新发布

查看更多文章