mysql中日期与时间函数的应用与格式化

应使用 FROM_unixTIME() 将时间戳转为可读日期，如 FROM_UNIXTIME(1717023600) 返回 ‘2024-05-30 15:00:00’，支持自定义格式；STR_TO_date() 仅适用于字符串转日期，不接受整数时间戳。

mysql 里怎么把时间戳转成可读日期？用 FROM_UNIXTIME() 而不是 STR_TO_DATE()

很多新手看到时间戳（比如 1717023600）第一反应是用 STR_TO_DATE()，但这个函数只处理字符串，不认整数时间戳。真正该用的是 FROM_UNIXTIME()。

• FROM_UNIXTIME(1717023600) → '2024-05-30 15:00:00'（默认格式）
• 加第二个参数可自定义格式：FROM_UNIXTIME(1717023600, '%Y年%m月%d日 %H点%i') → '2024年05月30日 15点00'
• 注意：如果字段是 BIGINT 类型存的 Unix 时间戳，直接传入即可；如果是字符串（如 '1717023600'），MySQL 会自动隐式转换，但不建议依赖这点
• 时区影响大：该函数按 MySQL 当前会话时区输出，select @@time_zone 查看当前设置，必要时先执行 SET time_zone = '+8:00'

想从日期字段里单独取年份或小时？别用字符串截取，用 YEAR()、HOUR() 等专用函数

有人用 SUBSTR(created_at, 1, 4) 提年份，看似能用，但一遇到 DATE 或 DATETIME 字段类型就出错——SUBSTR 是字符串函数，对日期类型会触发隐式转换，结果不可靠且慢。

• 正确做法：YEAR(created_at)、MONTH(created_at)、DAY(created_at)、HOUR(updated_at)、MINUTE(updated_at)
• 这些函数返回整数，可用于 GROUP BY 或条件筛选，比如：

  SELECT YEAR(order_time) AS y, COUNT(*) FROM orders GROUP BY y;

• WEEKDAY() 和 DAYOFWEEK() 返回值不同：前者周一是 0，后者周日是 1，写报表时容易搞反，建议注释说明
• 对 NULL 值安全：所有这类函数遇到 NULL 都返回 NULL，不会报错，但要注意后续逻辑是否容错

DATE_FORMAT() 格式化输出时，为什么有些格式符不生效？检查字段类型和 SQL 模式

DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') 没问题，但换成 DATE_FORMAT('2024-05-30', '%W %M %e, %Y') 却返回 NULL，常见原因是输入不是合法日期类型。

• 输入必须是 DATE、DATETIME 或能被 MySQL 自动识别为日期的字符串（如 '2024-05-30'），像 '30/05/2024' 这种格式会失败
• 如果字段是 VARCHAR 存的日期，先用 STR_TO_DATE(col, '%d/%m/%Y') 转成日期，再套 DATE_FORMAT()
• 严格模式下（sql_mode 含 STRICT_TRANS_TABLES），非法日期如 '2024-02-30' 会导致整个表达式为 NULL，而不是静默转成 '2024-03-01'
• 中文星期/月份需要系统字符集支持：确保客户端连接字符集是 utf8mb4，且 MySQL 的 lc_time_names 设为 'zh_CN'，否则 %W 显示英文

计算两个时间差用 TIMESTAMPDIFF()，别手算秒数除法

用 (UNIX_TIMESTAMP(end) - UNIX_TIMESTAMP(start)) / 3600 算小时数，看着简单，但跨夏令时、闰秒、时区切换时结果错得离谱。

• TIMESTAMPDIFF(HOUR, start_time, end_time) 自动处理时区和日历逻辑，推荐用于业务统计
• 单位支持：SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR
• 注意顺序：TIMESTAMPDIFF 是「后减前」，即 TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-05') 返回 4，不是 5
• 性能友好：比嵌套 UNIX_TIMESTAMP() 快，尤其在大表 WHERE 条件中使用时，能走索引（如果字段有索引）

MySQL 的日期函数看着简单，实际踩坑多在类型隐式转换、时区配置、SQL 模式和输入格式这四点上。写完记得用真实数据测一遍 NULL、边界日期（如 2024-02-29）、不同时区客户端，不然上线后才发现显示乱码或统计偏差，就得翻日志查半天。