MySQL 日期格式化函数使用实践

SET lc_time_names = 'zh_CN';
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %W');   -- 2026年09月20日 星期日

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');   -- 2026-09-20 13:45:06
SELECT DATE_FORMAT(NOW(), '%Y-%m');               -- 2026-09（按月分组常用）
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');      -- 到分钟
SELECT DATE_FORMAT(NOW(), '%c月%e日');            -- 9月20日（不补零）

SELECT STR_TO_DATE('2026/09/20', '%Y/%m/%d');                 -- 2026-09-20
SELECT STR_TO_DATE('2026-09-20 13:45:06', '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('20260920', '%Y%m%d');
SELECT STR_TO_DATE('20/09/2026', '%d/%m/%Y');                 -- 非「年月日」顺序也能解析

-- 转成日期后就能正常做范围比较和运算
SELECT * FROM orders
WHERE create_time >= STR_TO_DATE('2026/09/01', '%Y/%m/%d');

SELECT id, raw_date FROM tmp_import
WHERE STR_TO_DATE(raw_date, '%Y-%m-%d') IS NULL AND raw_date IS NOT NULL;

-- 以下示例以会话时区为 UTC（time_zone = '+00:00'）为准
SELECT UNIX_TIMESTAMP('2026-09-20 13:45:06');           -- 1789911906
SELECT FROM_UNIXTIME(1789911906);                       -- 2026-09-20 13:45:06
SELECT FROM_UNIXTIME(1789911906, '%Y-%m-%d %H:%i:%s');  -- 第二参数就是 DATE_FORMAT 的格式

-- 毫秒时间戳（很多接口返回 13 位）要先除以 1000
SELECT FROM_UNIXTIME(1789911906123 / 1000, '%Y-%m-%d %H:%i:%s');

-- 库里存 UTC，展示时转成东八区
SELECT CONVERT_TZ(create_time, '+00:00', '+08:00') FROM orders;

-- 查看当前时区
SELECT @@global.time_zone, @@session.time_zone, NOW(), UTC_TIMESTAMP();

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

-- 按天统计订单量
SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS day, COUNT(*) AS cnt
FROM orders
WHERE create_time >= '2026-09-01' AND create_time < '2026-10-01'
GROUP BY day ORDER BY day;

-- 按月统计（跨年也不会混在一起，因为带了年份）
SELECT DATE_FORMAT(create_time, '%Y-%m') AS month, SUM(amount) AS total
FROM orders
GROUP BY month ORDER BY month;

-- ✗ 不推荐：字段被函数包住，索引失效，等于全表扫描
SELECT * FROM orders
WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2026-09-20';

-- ✓ 推荐：写成范围条件，能走 create_time 上的索引
SELECT * FROM orders
WHERE create_time >= '2026-09-20 00:00:00'
  AND create_time <  '2026-09-21 00:00:00';

ALTER TABLE orders
  ADD INDEX idx_create_date ((DATE(create_time)));

-- 表达式与索引定义完全一致时才会命中
SELECT * FROM orders WHERE DATE(create_time) = '2026-09-20';

SELECT TIMESTAMPDIFF(YEAR,  birth_date, CURDATE()) AS age,
       TIMESTAMPDIFF(HOUR,  create_time, NOW())    AS hours_passed,
       DATEDIFF(NOW(), create_time)                AS days_passed
FROM users;

-- 最近 7 天（用日期运算，不要用函数包字段）
SELECT * FROM orders WHERE create_time >= CURDATE() - INTERVAL 7 DAY;

SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);
SELECT LAST_DAY(NOW());                                  -- 本月最后一天
SELECT DATE_FORMAT(NOW(), '%Y-%m-01');                   -- 本月第一天
SELECT DATE_SUB(DATE(NOW()), INTERVAL DAYOFMONTH(NOW()) - 1 DAY);   -- 同样效果

-- 最常用的格式化
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')   -- 2026-09-20 13:45:06
DATE_FORMAT(NOW(), '%Y-%m-%d')            -- 2026-09-20
DATE_FORMAT(NOW(), '%Y-%m')               -- 2026-09  按月分组
DATE_FORMAT(NOW(), '%x-%v')               -- ISO 周（配合年份用）

-- 字符串 → 日期 / 时间戳 → 日期
STR_TO_DATE('2026/09/20', '%Y/%m/%d')
FROM_UNIXTIME(1789911906, '%Y-%m-%d %H:%i:%s')
UNIX_TIMESTAMP('2026-09-20 13:45:06')

-- 取部件
DATE(d), YEAR(d), MONTH(d), DAY(d), HOUR(d), MINUTE(d), SECOND(d)

-- 运算
DATE_ADD(NOW(), INTERVAL 7 DAY), DATE_SUB(NOW(), INTERVAL 1 MONTH)
DATEDIFF(NOW(), create_time), TIMESTAMPDIFF(YEAR, birth_date, CURDATE())
LAST_DAY(NOW())

-- 查某一天（走索引的写法）
WHERE create_time >= '2026-09-20' AND create_time < '2026-09-21'

-- 时区
CONVERT_TZ(create_time, '+00:00', '+08:00')
SET lc_time_names = 'zh_CN';              -- 输出中文星期 / 月名