【MySQL基础-19】MySQL日期函数完全指南：高效处理时间数据的艺术

AllenBright

于 2025-04-01 10:43:58 发布

阅读量451

点赞数 4

分类专栏： # MySQL 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/weixin_39033358/article/details/146810832

版权

MySQL 专栏收录该内容

34 篇文章

订阅专栏

在数据库管理和数据分析领域，时间数据处理占据了极其重要的位置。无论是电商平台的订单分析、金融系统的交易记录，还是社交媒体的用户行为追踪，时间维度都是不可或缺的分析要素。MySQL提供了一套强大而全面的日期和时间函数，能够帮助开发者高效地进行时间计算、格式转换和周期分析。

本文将系统性地介绍MySQL中最实用、最高频使用的日期函数，并通过真实业务场景展示它们的应用技巧，最后提供性能优化建议，帮助您掌握MySQL时间数据处理的精髓。

1. MySQL日期时间类型概览

在深入函数之前，我们先了解MySQL支持的日期时间类型：

DATE：格式’YYYY-MM-DD’，仅存储日期
TIME：格式’HH:MM:SS’，仅存储时间
DATETIME：格式’YYYY-MM-DD HH:MM:SS’，存储日期和时间
TIMESTAMP：与DATETIME类似，但受时区影响，范围更小(1970-2038)
YEAR：存储年份，2位或4位格式

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. 基础日期时间函数

2.1 获取当前日期时间

SELECT NOW();      -- 当前日期和时间 '2023-08-20 15:30:45'
SELECT CURDATE();  -- 当前日期 '2023-08-20'
SELECT CURTIME();  -- 当前时间 '15:30:45'
SELECT UTC_DATE(); -- UTC当前日期
SELECT UTC_TIME(); -- UTC当前时间

2.2 日期时间提取函数

SELECT DATE('2023-08-20 15:30:45'); -- 提取日期部分 '2023-08-20'
SELECT TIME('2023-08-20 15:30:45'); -- 提取时间部分 '15:30:45'

SELECT YEAR('2023-08-20');     -- 2023
SELECT MONTH('2023-08-20');    -- 8
SELECT DAY('2023-08-20');      -- 20
SELECT HOUR('15:30:45');       -- 15
SELECT MINUTE('15:30:45');     -- 30
SELECT SECOND('15:30:45');     -- 45
SELECT MICROSECOND('15:30:45.123456'); -- 123456

SELECT DAYNAME('2023-08-20');  -- 'Sunday' (星期名称)
SELECT MONTHNAME('2023-08-20'); -- 'August' (月份名称)

2.3 日期时间格式化

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- '2023-08-20 15:30:45'
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y');    -- 'Sunday, August 20, 2023'

-- 常用格式说明符：
-- %Y 四位年份 / %y 两位年份
-- %m 月份(01-12) / %c 月份(1-12)
-- %d 日(01-31) / %e 日(1-31)
-- %H 24小时制 / %h 12小时制
-- %i 分钟 / %s 秒
-- %W 星期名称 / %a 星期缩写
-- %M 月份名称 / %b 月份缩写

3. 日期时间计算函数

3.1 日期加减运算

-- 基本加减
SELECT DATE_ADD('2023-08-20', INTERVAL 1 DAY);    -- '2023-08-21'
SELECT DATE_SUB('2023-08-20', INTERVAL 1 MONTH);  -- '2023-07-20'

-- 简写形式
SELECT '2023-08-20' + INTERVAL 1 WEEK;  -- '2023-08-27'
SELECT '2023-08-20' - INTERVAL 3 MONTH; -- '2023-05-20'

-- 支持的时间单位：
-- MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
-- SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND等组合单位

3.2 日期差值计算

SELECT DATEDIFF('2023-08-25', '2023-08-20'); -- 5 (天数差)
SELECT TIMEDIFF('18:30:00', '15:30:00');     -- '03:00:00' (时间差)
SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2023-08-01'); -- 7 (月数差)

-- 支持的单位：
-- MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

3.3 时间戳转换

SELECT UNIX_TIMESTAMP('2023-08-20 15:30:45'); -- 1692516645
SELECT FROM_UNIXTIME(1692005445);             -- '2023-08-14 17:30:45'

4. 高级日期时间处理技巧

4.1 工作日计算（排除周末）

-- 计算5个工作日后的日期
SELECT 
    DATE_ADD('2023-08-18', INTERVAL 5 + 
    CASE 
        WHEN DAYOFWEEK('2023-08-18') + 5 >= 7 THEN 2
        WHEN DAYOFWEEK('2023-08-18') + 5 >= 6 THEN 1
        ELSE 0
    END DAY) AS workday_after_5;

4.2 月末日期处理

-- 获取某月的最后一天
SELECT LAST_DAY('2023-02-15'); -- '2023-02-28'
SELECT LAST_DAY('2024-02-15'); -- '2024-02-29' (闰年)

-- 计算当月剩余天数
SELECT DATEDIFF(LAST_DAY(NOW()), NOW()) AS days_remaining;

4.3 生成日期序列

-- 生成最近7天的日期序列
SELECT CURDATE() - INTERVAL n DAY AS date_sequence
FROM (
    SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
) AS numbers;

5. 实际业务场景应用

5.1 案例1：用户留存率分析

-- 计算次日留存率
SELECT 
    COUNT(DISTINCT day1.user_id) AS day1_users,
    COUNT(DISTINCT day2.user_id) AS day2_users,
    ROUND(COUNT(DISTINCT day2.user_id) / COUNT(DISTINCT day1.user_id) * 100, 2) AS retention_rate
FROM 
    (SELECT DISTINCT user_id FROM logins WHERE DATE(login_time) = '2023-08-01') day1
LEFT JOIN 
    (SELECT DISTINCT user_id FROM logins WHERE DATE(login_time) = '2023-08-02') day2
ON day1.user_id = day2.user_id;

5.2 案例2：月度销售报告

-- 生成月度销售汇总报告
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_order_value
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

5.3 案例3：用户生日提醒

-- 查询未来7天内过生日的用户
SELECT 
    user_id,
    name,
    DATE_FORMAT(birthday, '%M %d') AS birthday
FROM users
WHERE 
    DATE_FORMAT(birthday, '%m-%d') BETWEEN 
        DATE_FORMAT(NOW(), '%m-%d') AND 
        DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY), '%m-%d');

6. 性能优化建议

索引使用策略：
- 对经常用于查询条件的日期列创建索引
- 避免在索引列上使用函数，如WHERE DATE(created_at) = '2023-08-20'
- 改为范围查询：WHERE created_at >= '2023-08-20' AND created_at < '2023-08-21'
存储优化：
- 根据需求选择合适的数据类型（TIMESTAMP比DATETIME节省空间）
- 考虑使用INT存储UNIX时间戳以获得更好性能
查询优化：
- 对于大量历史数据，考虑按日期分区
- 避免在WHERE子句中对日期列进行计算
时区处理：
- 保持应用层和数据库时区一致
- 使用CONVERT_TZ()函数处理多时区需求

7. MySQL 8.0新增日期函数

MySQL 8.0引入了更多强大的日期函数：

-- 获取指定日期所在季度的第一天
SELECT DATE_ADD(DATE_FORMAT('2023-08-20', '%Y-01-01'), 
       INTERVAL QUARTER('2023-08-20') QUARTER - INTERVAL 1 QUARTER);

-- 窗口函数中的日期计算
SELECT 
    user_id,
    login_date,
    LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login
FROM user_logins;