SQL日期时间函数

作者：哪吒
时间：2024年12月

概述

SQL日期时间函数用于处理日期和时间数据。这些函数可以帮助我们获取当前时间、格式化日期、计算时间差、提取日期部分等操作，是处理时间相关业务逻辑的重要工具。

获取当前时间函数

NOW() - 获取当前日期时间

返回当前的日期和时间。

SELECT NOW() AS current_datetime;                    -- 结果: '2024-12-25 14:30:45'
SELECT NOW() + INTERVAL 1 DAY AS tomorrow;          -- 结果: '2024-12-26 14:30:45'
SELECT NOW() - INTERVAL 1 HOUR AS one_hour_ago;     -- 结果: '2024-12-25 13:30:45'

CURDATE() / CURRENT_DATE() - 获取当前日期

返回当前日期（不包含时间）。

SELECT CURDATE() AS current_date;                    -- 结果: '2024-12-25'
SELECT CURRENT_DATE() AS current_date;               -- 结果: '2024-12-25'

CURTIME() / CURRENT_TIME() - 获取当前时间

返回当前时间（不包含日期）。

SELECT CURTIME() AS current_time;                    -- 结果: '14:30:45'
SELECT CURRENT_TIME() AS current_time;               -- 结果: '14:30:45'

SYSDATE() - 系统日期时间

返回系统当前日期时间（与NOW()略有不同）。

SELECT SYSDATE() AS system_datetime;                 -- 结果: '2024-12-25 14:30:45'
-- SYSDATE()在函数执行时获取时间，NOW()在语句开始时获取

日期时间格式化函数

DATE_FORMAT() - 日期格式化

将日期按指定格式转换为字符串。

-- 常用格式
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS date_only;           -- 结果: '2024-12-25'
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日') AS chinese_date;     -- 结果: '2024年12月25日'
SELECT DATE_FORMAT(NOW(), '%H:%i:%s') AS time_only;          -- 结果: '14:30:45'
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS full_format; -- 结果: '2024-12-25 14:30:45'

-- 更多格式选项
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y') AS long_format;    -- 结果: 'Wednesday, December 25, 2024'
SELECT DATE_FORMAT(NOW(), '%y/%m/%d %h:%i %p') AS short_format; -- 结果: '24/12/25 02:30 PM'

TIME_FORMAT() - 时间格式化

格式化时间值。

SELECT TIME_FORMAT('14:30:45', '%H:%i') AS formatted_time;    -- 结果: '14:30'
SELECT TIME_FORMAT('14:30:45', '%h:%i %p') AS am_pm_time;     -- 结果: '02:30 PM'

STR_TO_DATE() - 字符串转日期

将字符串按指定格式转换为日期。

SELECT STR_TO_DATE('2024-12-25', '%Y-%m-%d') AS parsed_date;
SELECT STR_TO_DATE('25/12/2024 14:30', '%d/%m/%Y %H:%i') AS parsed_datetime;
SELECT STR_TO_DATE('2024年12月25日', '%Y年%m月%d日') AS chinese_parsed;

日期时间提取函数

YEAR(), MONTH(), DAY() - 提取年月日

从日期中提取年、月、日部分。

SELECT YEAR('2024-12-25') AS year_part;              -- 结果: 2024
SELECT MONTH('2024-12-25') AS month_part;            -- 结果: 12
SELECT DAY('2024-12-25') AS day_part;                -- 结果: 25
SELECT DAYOFMONTH('2024-12-25') AS day_of_month;     -- 结果: 25

HOUR(), MINUTE(), SECOND() - 提取时分秒

从时间中提取时、分、秒部分。

SELECT HOUR('14:30:45') AS hour_part;                -- 结果: 14
SELECT MINUTE('14:30:45') AS minute_part;            -- 结果: 30
SELECT SECOND('14:30:45') AS second_part;            -- 结果: 45

DAYNAME(), MONTHNAME() - 获取名称

获取星期几和月份的名称。

SELECT DAYNAME('2024-12-25') AS day_name;            -- 结果: 'Wednesday'
SELECT MONTHNAME('2024-12-25') AS month_name;        -- 结果: 'December'

DAYOFWEEK(), DAYOFYEAR() - 获取序号

获取一周中的第几天、一年中的第几天等。

SELECT DAYOFWEEK('2024-12-25') AS day_of_week;       -- 结果: 4 (周三，周日为1)
SELECT DAYOFYEAR('2024-12-25') AS day_of_year;       -- 结果: 360
SELECT WEEKOFYEAR('2024-12-25') AS week_of_year;     -- 结果: 52

QUARTER() - 获取季度

获取日期所在的季度。

SELECT QUARTER('2024-12-25') AS quarter_num;         -- 结果: 4
SELECT QUARTER('2024-03-15') AS quarter_num;         -- 结果: 1

日期时间计算函数

DATE_ADD() / DATE_SUB() - 日期加减

对日期进行加减运算。

-- 日期加法
SELECT DATE_ADD('2024-12-25', INTERVAL 1 DAY) AS next_day;        -- 结果: '2024-12-26'
SELECT DATE_ADD('2024-12-25', INTERVAL 1 MONTH) AS next_month;    -- 结果: '2025-01-25'
SELECT DATE_ADD('2024-12-25', INTERVAL 1 YEAR) AS next_year;      -- 结果: '2025-12-25'
SELECT DATE_ADD('2024-12-25 14:30:45', INTERVAL 2 HOUR) AS later; -- 结果: '2024-12-25 16:30:45'

-- 日期减法
SELECT DATE_SUB('2024-12-25', INTERVAL 1 DAY) AS yesterday;       -- 结果: '2024-12-24'
SELECT DATE_SUB('2024-12-25', INTERVAL 1 WEEK) AS last_week;      -- 结果: '2024-12-18'

ADDDATE() / SUBDATE() - 简化的日期加减

SELECT ADDDATE('2024-12-25', 7) AS week_later;        -- 结果: '2025-01-01'
SELECT SUBDATE('2024-12-25', 7) AS week_earlier;      -- 结果: '2024-12-18'
SELECT ADDDATE('2024-12-25', INTERVAL 1 MONTH) AS next_month; -- 结果: '2025-01-25'

DATEDIFF() - 计算日期差

计算两个日期之间的天数差。

SELECT DATEDIFF('2024-12-25', '2024-12-20') AS days_diff;     -- 结果: 5
SELECT DATEDIFF('2024-12-20', '2024-12-25') AS days_diff;     -- 结果: -5
SELECT DATEDIFF(NOW(), '2024-01-01') AS days_since_new_year;

TIMEDIFF() - 计算时间差

计算两个时间之间的差值。

SELECT TIMEDIFF('14:30:45', '12:15:30') AS time_diff;         -- 结果: '02:15:15'
SELECT TIMEDIFF('2024-12-25 14:30:45', '2024-12-25 12:15:30') AS datetime_diff;

TIMESTAMPDIFF() - 计算时间戳差

计算两个日期时间之间指定单位的差值。

SELECT TIMESTAMPDIFF(DAY, '2024-12-20', '2024-12-25') AS days;    -- 结果: 5
SELECT TIMESTAMPDIFF(HOUR, '2024-12-25 12:00:00', '2024-12-25 14:30:00') AS hours; -- 结果: 2
SELECT TIMESTAMPDIFF(MINUTE, '14:00:00', '14:30:00') AS minutes;  -- 结果: 30
SELECT TIMESTAMPDIFF(YEAR, '2020-01-01', '2024-01-01') AS years;  -- 结果: 4

日期时间转换函数

DATE() - 提取日期部分

从日期时间中提取日期部分。

SELECT DATE('2024-12-25 14:30:45') AS date_only;      -- 结果: '2024-12-25'
SELECT DATE(NOW()) AS today;

TIME() - 提取时间部分

从日期时间中提取时间部分。

SELECT TIME('2024-12-25 14:30:45') AS time_only;      -- 结果: '14:30:45'
SELECT TIME(NOW()) AS current_time;

TIMESTAMP() - 创建时间戳

创建时间戳值。

SELECT TIMESTAMP('2024-12-25', '14:30:45') AS timestamp_value;
SELECT TIMESTAMP('2024-12-25 12:00:00') AS timestamp_value;

UNIX_TIMESTAMP() - Unix时间戳

转换为Unix时间戳或从Unix时间戳转换。

SELECT UNIX_TIMESTAMP() AS current_timestamp;         -- 当前Unix时间戳
SELECT UNIX_TIMESTAMP('2024-12-25 14:30:45') AS timestamp;
SELECT FROM_UNIXTIME(1735128645) AS from_timestamp;   -- 从Unix时间戳转换

特殊日期函数

LAST_DAY() - 月末日期

获取指定日期所在月份的最后一天。

SELECT LAST_DAY('2024-12-25') AS month_end;           -- 结果: '2024-12-31'
SELECT LAST_DAY('2024-02-15') AS feb_end;             -- 结果: '2024-02-29' (闰年)

MAKEDATE() - 构造日期

根据年份和一年中的第几天构造日期。

SELECT MAKEDATE(2024, 360) AS constructed_date;       -- 结果: '2024-12-25'
SELECT MAKEDATE(2024, 1) AS new_year;                 -- 结果: '2024-01-01'

MAKETIME() - 构造时间

根据时、分、秒构造时间。

SELECT MAKETIME(14, 30, 45) AS constructed_time;      -- 结果: '14:30:45'
SELECT MAKETIME(25, 30, 45) AS invalid_time;          -- 结果: NULL (无效时间)

实战示例

示例1：用户活动分析

-- 创建用户活动表
CREATE TABLE user_activities (
    id INT PRIMARY KEY,
    user_id INT,
    activity_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_activities (id, user_id, activity_type, created_at) VALUES
(1, 1001, 'login', '2024-12-20 09:30:00'),
(2, 1001, 'view_product', '2024-12-20 09:35:00'),
(3, 1001, 'purchase', '2024-12-20 10:15:00'),
(4, 1002, 'login', '2024-12-21 14:20:00'),
(5, 1002, 'logout', '2024-12-21 15:45:00');

-- 活动时间分析
SELECT 
    user_id,
    activity_type,
    created_at,
    DATE(created_at) AS activity_date,
    TIME(created_at) AS activity_time,
    DAYNAME(created_at) AS day_of_week,
    HOUR(created_at) AS activity_hour,
    DATEDIFF(NOW(), created_at) AS days_ago,
    CASE 
        WHEN HOUR(created_at) BETWEEN 6 AND 12 THEN '上午'
        WHEN HOUR(created_at) BETWEEN 13 AND 18 THEN '下午'
        WHEN HOUR(created_at) BETWEEN 19 AND 23 THEN '晚上'
        ELSE '深夜'
    END AS time_period
FROM user_activities
ORDER BY created_at;

示例2：订单统计报表

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_amount DECIMAL(10,2),
    order_date DATE,
    created_at TIMESTAMP
);

INSERT INTO orders VALUES
(1, 1001, 299.99, '2024-12-20', '2024-12-20 10:30:00'),
(2, 1002, 159.50, '2024-12-21', '2024-12-21 14:15:00'),
(3, 1001, 89.99, '2024-12-22', '2024-12-22 09:45:00'),
(4, 1003, 449.00, '2024-12-23', '2024-12-23 16:20:00');

-- 按时间维度统计
SELECT 
    -- 按日统计
    DATE(order_date) AS order_day,
    COUNT(*) AS daily_orders,
    SUM(order_amount) AS daily_revenue,
    
    -- 按周统计
    YEARWEEK(order_date) AS year_week,
    WEEK(order_date) AS week_number,
    
    -- 按月统计
    DATE_FORMAT(order_date, '%Y-%m') AS order_month,
    
    -- 按季度统计
    CONCAT(YEAR(order_date), '-Q', QUARTER(order_date)) AS order_quarter
FROM orders
GROUP BY DATE(order_date)
ORDER BY order_date;

示例3：会员到期提醒

-- 创建会员表
CREATE TABLE memberships (
    id INT PRIMARY KEY,
    user_id INT,
    membership_type VARCHAR(20),
    start_date DATE,
    end_date DATE
);

INSERT INTO memberships VALUES
(1, 1001, 'VIP', '2024-01-01', '2024-12-31'),
(2, 1002, 'Premium', '2024-06-01', '2025-01-01'),
(3, 1003, 'Basic', '2024-10-01', '2024-12-31');

-- 会员状态和到期提醒
SELECT 
    user_id,
    membership_type,
    start_date,
    end_date,
    DATEDIFF(end_date, CURDATE()) AS days_remaining,
    CASE 
        WHEN end_date < CURDATE() THEN '已过期'
        WHEN DATEDIFF(end_date, CURDATE()) <= 7 THEN '即将过期'
        WHEN DATEDIFF(end_date, CURDATE()) <= 30 THEN '一个月内到期'
        ELSE '正常'
    END AS status,
    DATE_ADD(end_date, INTERVAL 1 YEAR) AS suggested_renewal_date,
    TIMESTAMPDIFF(MONTH, start_date, end_date) AS membership_duration_months
FROM memberships
ORDER BY days_remaining;

示例4：工作日计算

-- 计算工作日（排除周末）
SELECT 
    '2024-12-20' AS start_date,
    '2024-12-27' AS end_date,
    DATEDIFF('2024-12-27', '2024-12-20') AS total_days,
    -- 简单工作日计算（不考虑节假日）
    DATEDIFF('2024-12-27', '2024-12-20') - 
    (WEEK('2024-12-27') - WEEK('2024-12-20')) * 2 AS approximate_workdays;

-- 生成日期序列并标记工作日
WITH RECURSIVE date_series AS (
    SELECT '2024-12-20' AS date_value
    UNION ALL
    SELECT DATE_ADD(date_value, INTERVAL 1 DAY)
    FROM date_series
    WHERE date_value < '2024-12-27'
)
SELECT 
    date_value,
    DAYNAME(date_value) AS day_name,
    DAYOFWEEK(date_value) AS day_number,
    CASE 
        WHEN DAYOFWEEK(date_value) IN (1, 7) THEN '周末'
        ELSE '工作日'
    END AS day_type
FROM date_series;

性能优化建议

1. 使用日期索引

-- 为日期字段创建索引
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_created_at ON user_activities(created_at);

2. 避免在WHERE子句中使用函数

-- 不推荐：在WHERE中使用函数
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- 推荐：使用范围查询
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

3. 使用分区表处理大量时间数据

-- 按月分区
CREATE TABLE orders_partitioned (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    -- 更多分区...
);

常见错误和解决方案

1. 时区问题

-- 设置时区
SET time_zone = '+08:00';

-- 转换时区
SELECT 
    NOW() AS local_time,
    CONVERT_TZ(NOW(), '+08:00', '+00:00') AS utc_time;

2. 日期格式不匹配

-- 使用STR_TO_DATE处理不同格式
SELECT STR_TO_DATE('25-12-2024', '%d-%m-%Y') AS parsed_date;
SELECT STR_TO_DATE('Dec 25, 2024', '%M %d, %Y') AS parsed_date;

3. NULL值处理

-- 处理NULL日期
SELECT 
    COALESCE(end_date, '9999-12-31') AS safe_end_date,
    IFNULL(DATEDIFF(end_date, start_date), 0) AS safe_duration
FROM memberships;

最佳实践

1. 统一时区：在应用中统一使用UTC时间存储
2. 合适的数据类型：根据需要选择DATE、TIME、DATETIME或TIMESTAMP
3. 索引优化：为经常查询的日期字段创建索引
4. 避免函数计算：在WHERE条件中避免使用日期函数
5. 数据验证：确保日期数据的有效性和一致性

总结

SQL日期时间函数提供了强大的时间处理能力，合理使用这些函数可以：

• 高效处理各种时间相关的业务逻辑
• 实现复杂的时间计算和统计
• 提供灵活的日期格式化选项
• 支持多种时间维度的数据分析

在使用时要注意时区、性能和数据类型的选择，确保时间数据的准确性和查询效率。