MySQL时间魔法全解析 ⏰

🕰️ 时空管理员手册 在这里你将学会：

• 召唤当前时间的咒语 🔮
• 时间的变形与切割 ✂️
• 穿越时间的计算 🚀
• 时间格式的化妆术 💄
• 实战中的时间陷阱 🕳️

1. 基础时间咒语

🌟 时间三连击

-- 召唤当前完整时间（精确到秒）
SELECT NOW();  -- 🕒 输出：2023-08-20 14:30:45

-- 获取当前日期（不含时间）
SELECT CURDATE();  -- 📅 输出：2023-08-20

-- 提取纯时间部分
SELECT CURTIME();  -- 🕓 输出：14:30:45

/* 🧙 经典错误示范 */
SELECT NOW() + 0;  -- ❌ 错误的时间计算方式
SELECT CURDATE() - 1;  -- ❌ 日期不能直接相减

2. 时间变形术

拆解时间

-- 提取年份（适合统计年度数据）
SELECT YEAR('2023-08-20');  -- 📅 输出：2023

-- 获取月份（营销活动常用）
SELECT MONTH('2023-08-20');  -- 🗓️ 输出：8

-- 抓取星期（周报统计神器）
SELECT DAYOFWEEK('2023-08-20');  -- 📆 输出：1（周日=1，周六=7）

时间拼接

-- 创建完整时间（适合组合日期和时间）
SELECT MAKEDATE(2023, 232) + INTERVAL '14:30:45' HOUR_SECOND;
-- 🧩 输出：2023-08-20 14:30:45（第232天是8月20日）

⏱️ 时间函数冷知识

函数	输出示例	使用场景	类比说明
NOW()	2023-08-20 14:30:45	记录订单下单时间	时间照妖镜
CURDATE()	2023-08-20	统计每日销售额	日期收割机
UNIX_TIMESTAMP()	1692520245	生成唯一时间戳	时间转换器
FROM_UNIXTIME(1692520245)	2023-08-20 14:30:45	转换时间戳为可读格式	时光翻译机

3. 时间计算器

⚡ 穿越时空法则

-- 三天后的这个时间（适合设置有效期）
SELECT NOW() + INTERVAL 3 DAY;  -- 🚀 时间前进

-- 两小时前的时间（查询历史记录）
SELECT NOW() - INTERVAL 2 HOUR;  -- ⏪ 时间倒流

-- 精确时间差计算（计算服务时长）
SELECT TIMEDIFF('14:30:45', '10:15:30');  -- ⏱️ 输出：04:15:15

-- 日期差（计算年龄神器）
SELECT DATEDIFF('2023-08-20', '2000-05-15');  -- 🎂 输出：8499天

4. 时间格式化沙龙

🎨 时间化妆师

-- 美式时间格式
SELECT DATE_FORMAT(NOW(), '%m/%d/%Y %H:%i:%s');  -- 🇺🇸 输出：08/20/2023 14:30:45

-- 中文友好格式
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分');  -- 🇨🇳 输出：2023年08月20日 14时30分

-- 提取季度信息（财务报表专用）
SELECT DATE_FORMAT('2023-08-20', '%Q季度');  -- 📊 输出：3季度

5. 实战：用户活跃度分析 🕹️

-- 创建用户活跃表
CREATE TABLE 用户活跃 (
    用户ID INT PRIMARY KEY,
    最后登录时间 DATETIME DEFAULT NOW(),
    注册日期 DATE NOT NULL
) COMMENT '用户活跃度追踪表';

-- 查询最近7天活跃用户
SELECT * FROM 用户活跃 
WHERE 最后登录时间 >= NOW() - INTERVAL 7 DAY;

-- 计算用户年龄（按天显示）
SELECT 用户ID, DATEDIFF(CURDATE(), 注册日期) AS 注册天数 
FROM 用户活跃;

-- 查询上周访问数据（自然周统计）
SELECT *
FROM visit_stats
WHERE YEARWEEK(visit_date, 1) = YEARWEEK(CURDATE() - INTERVAL 1 WEEK, 1);
/* 🧙♂️ 参数说明：
   1. YEARWEEK第二个参数1表示周从周一开始
   2. CURDATE() - INTERVAL 1 WEEK 获取上周同一时间
   3. 完美处理跨年周数据 */
   
-- 另一种写法（适合索引优化）
SELECT *
FROM visit_stats 
WHERE visit_date BETWEEN 
    DATE_ADD(CURDATE(), INTERVAL - WEEKDAY(CURDATE()) - 7 DAY) AND
    DATE_ADD(CURDATE(), INTERVAL - WEEKDAY(CURDATE()) - 1 DAY);
/* ⚡ 性能提示：
   1. 使用BETWEEN可以利用索引
   2. WEEKDAY返回0=周一至6=周日
   3. 精确计算上周一00:00:00到上周日23:59:59 */

🚨 时间函数避坑指南

1. 时区陷阱：数据库时区 vs 系统时区（推荐统一使用UTC）
2. 闰年炸弹：DATE_ADD('2020-02-29', INTERVAL 1 YEAR) → 2021-02-28
3. 性能黑洞：WHERE DATE(create_time) = '2023-08-20' ❌（无法使用索引）
4. 千年虫复活：YEAR类型只存2位时要小心（Y2K问题）
5. 时间溢出：TIME类型最大值为838:59:59（约34天）

💡 时间管理大师秘籍

-- 获取本月最后一天（财务结算神器）
SELECT LAST_DAY(CURDATE());

-- 计算精确年龄（考虑闰年）
SELECT TIMESTAMPDIFF(YEAR, '2000-02-29', CURDATE());

-- 生成时间序列（数据分析必备）
SELECT CURDATE() - INTERVAL (a.a + (10 * b.a)) DAY 
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS b;