MySQL 时间日期函数

时间日期类型

MySQL中主要支持以下几种时间日期类型：

1. DATE - 日期类型

   • 格式：'YYYY-MM-DD'
   • 范围：'1000-01-01' 到 '9999-12-31'
   • 示例：'2023-05-20'

2. TIME - 时间类型

   • 格式：'HH:MM:SS'
   • 范围：'-838:59:59' 到 '838:59:59'
   • 示例：'15:30:00' 或 '02:45:30.123456'（支持微秒）

3. DATETIME - 日期时间类型

   • 格式：'YYYY-MM-DD HH:MM:SS'
   • 范围：'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
   • 存储方式​：按字面值存储，不进行时区转换
   • 示例：'2023-05-20 15:30:00'
   • 适合存储固定时间点（如生日、历史事件）
   • 不自动更新
   • 占用 ​8 字节​ 存储空间

4. TIMESTAMP - 时间戳类型

   • 格式：'YYYY-MM-DD HH:MM:SS'
   • 范围：1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC（受 32 位限制）
   • 存储方式​：转换为 UTC 存储，检索时转回当前时区
   • 适合记录数据变更时间（如 created_at、updated_at）
   • 可自动初始化/更新（配合 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP）
   • 占用 ​4 字节​ 存储空间
   • 受时区影响（存储和检索时会根据服务器时区转换）

5. YEAR - 年份类型

   • 格式：YYYY
   • 范围：1901 到 2155
   • 示例：'2023'

DATETIME VS TIMESTAMP​

​使用建议​

• 需要记录 ​事件发生的具体时间​（如订单时间）且不关心时区 → ​DATETIME​
• 需要 ​自动记录数据插入/更新时间​ 或处理时区敏感数据 → ​TIMESTAMP​
• 注意 TIMESTAMP 的 ​2038 年问题​（未来 MySQL 可能扩展为 64 位）

日期增减

日期加：DATE_ADD

基本语法

DATE_ADD(date, INTERVAL expr unit)

功能
在指定日期/时间上添加一个时间间隔

参数说明

• date：要处理的日期/时间值（可以是DATE, DATETIME或TIMESTAMP类型）
• expr：要添加的间隔数值（可以是正数或负数）
• unit：时间单位（见下方支持的单位）

支持的时间单位

MICROSECOND | SECOND | MINUTE | HOUR | DAY 
| WEEK | MONTH | QUARTER | YEAR 
| SECOND_MICROSECOND | MINUTE_MICROSECOND 
| MINUTE_SECOND | HOUR_MICROSECOND 
| HOUR_SECOND | HOUR_MINUTE 
| DAY_MICROSECOND | DAY_SECOND 
| DAY_MINUTE | DAY_HOUR 
| YEAR_MONTH

使用示例

-- 加1天
SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);  
-- 结果: 2023-01-02

-- 加1个月
SELECT DATE_ADD('2023-01-31', INTERVAL 1 MONTH); 
-- 结果: 2023-02-28 (自动处理月末)

-- 加1年
SELECT DATE_ADD('2020-02-29', INTERVAL 1 YEAR);  
-- 结果: 2021-02-28 (闰年自动调整)

-- 加3小时30分钟
SELECT DATE_ADD('2023-01-01 12:00:00', INTERVAL '3:30' HOUR_MINUTE); 
-- 结果: 2023-01-01 15:30:00

-- 使用负数表示减法
SELECT DATE_ADD('2023-01-01', INTERVAL -1 DAY);  
-- 结果: 2022-12-31

日期减：DATE_SUB

基本语法

DATE_SUB(date, INTERVAL expr unit)

功能
与DATE_ADD相反，从指定日期/时间减去一个时间间隔

使用示例

-- 减1天
SELECT DATE_SUB('2023-01-01', INTERVAL 1 DAY);  
-- 结果: 2022-12-31

-- 减1个月
SELECT DATE_SUB('2023-03-31', INTERVAL 1 MONTH); 
-- 结果: 2023-02-28

-- 等同于DATE_ADD使用负数
SELECT DATE_SUB('2023-01-01', INTERVAL 1 DAY) = DATE_ADD('2023-01-01', INTERVAL -1 DAY); 
-- 结果: 1(TRUE)

简写语法

MySQL还提供更简洁的日期加减写法：

-- 加1天
SELECT '2023-01-01' + INTERVAL 1 DAY;

-- 减1个月
SELECT '2023-01-01' - INTERVAL 1 MONTH;

实际应用场景

场景1：计算到期日

-- 贷款30天后到期
SELECT loan_date, DATE_ADD(loan_date, INTERVAL 30 DAY) AS due_date 
FROM loans;

场景2：查找最近7天的记录

SELECT * FROM orders 
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);

场景3：处理订阅续期

-- 订阅续期1年
UPDATE subscriptions 
SET end_date = DATE_ADD(end_date, INTERVAL 1 YEAR)
WHERE user_id = 1001;

注意事项

1. 自动调整无效日期：

   SELECT DATE_ADD('2023-01-31', INTERVAL 1 MONTH); -- 返回2023-02-28
   

2. 闰年处理：

   SELECT DATE_ADD('2020-02-29', INTERVAL 1 YEAR); -- 返回2021-02-28
   

3. 时间溢出处理：

   SELECT DATE_ADD('2023-01-01 23:59:59', INTERVAL 1 SECOND); -- 返回2023-01-02 00:00:00
   

4. 性能考虑：在WHERE条件中对列使用这些函数会导致索引失效

与其他日期函数的对比

时间/日期差

时间差：TIMESTAMPDIFF

TIMESTAMPDIFF 是 MySQL/MariaDB 中用于计算两个时间戳之间差异的函数。

基本语法

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

• unit: 时间单位（YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND 等）
• datetime_expr1: 起始时间
• datetime_expr2: 结束时间

时间单位选项

使用示例

1. 计算小时差

   SELECT TIMESTAMPDIFF(HOUR, '2022-03-22 08:00:00', '2022-03-22 17:30:00');
   -- 返回 9（只计算完整的小时数，忽略分钟部分）
   

2. 计算天数差

   SELECT TIMESTAMPDIFF(DAY, '2022-03-01', '2022-03-15');
   -- 返回 14
   

3. 计算月数差

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-15', '2022-05-20');
   -- 返回 4（不考虑具体的天数）
   

重要特性

1. 返回值总是整数：函数返回的是两个时间之间的完整单位数，不包含小数部分

2. 参数顺序敏感：datetime_expr2 减去 datetime_expr1，如果 datetime_expr1 更大，结果为负数

3. 自动处理日期格式：函数会自动识别标准日期时间格式

4. 跨年/月计算：能正确处理跨年、跨月的时间差计算

高级用法

1. 计算年龄

   SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users;
   

2. 计算服务时长（精确到月）

   SELECT 
     employee_name,
     TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS months_of_service
   FROM employees;
   

3. 组合使用

   SELECT 
     CONCAT(
       TIMESTAMPDIFF(YEAR, '2000-01-01', '2023-05-15'), '年',
       TIMESTAMPDIFF(MONTH, '2000-01-01', '2023-05-15') % 12, '个月'
     ) AS time_diff;
   

注意事项

1. 对于 MONTH 和 YEAR 单位，函数只比较月份和年份部分，不考虑具体的天数

2. 如果需要更精确的时间差（包含小数），可以考虑使用 TIMESTAMPDIFF 结合其他函数，或者使用 UNIX_TIMESTAMP 计算秒数差再转换

3. 在旧版 MySQL 中可能不支持某些时间单位（如 MICROSECOND）

案例
统计各岗位员工平均工作时长

时间差：UNIX_TIMESTAMP 

UNIX_TIMESTAMP 是 SQL 中常用的时间函数，主要用于获取或转换 Unix 时间戳（从1970年1月1日00:00:00 UTC到当前时间的秒数）。

计算时间差

SELECT (UNIX_TIMESTAMP('2022-03-22 17:00:00') - 
UNIX_TIMESTAMP('2022-03-22 08:00:00')) / 3600 
AS hours_diff;

返回当前时间的Unix时间戳

SELECT UNIX_TIMESTAMP();  

日期时间→时间戳

SELECT UNIX_TIMESTAMP('2023-10-01 12:00:00');

​时间戳→日期时间​

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());  

日期差：DATEDIFF

DATEDIFF()是MySQL中用于计算两个日期之间天数差的函数。

DATEDIFF(date1, date2)

• date1 和 date2 是日期或日期时间表达式
• 函数返回 date1 减去 date2 的天数差

示例

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

SELECT DATEDIFF('2023-12-31', '2023-01-01');  -- 返回 364

计算当前日期与特定日期的天数差

SELECT DATEDIFF(CURDATE(), '2023-01-01');

在表查询中使用

SELECT order_id, 
DATEDIFF(shipped_date, order_date) AS processing_days
FROM orders;

注意事项

• 时间部分会被忽略，只计算日期部分
• 如果date1早于date2，结果为负数
• 参数可以是DATE、DATETIME或TIMESTAMP类型
• 如果任一参数为NULL，结果也为NULL
• TIMESTAMPDIFF()可以计算更精确的时间差（年、月、日、小时等），DATEDIFF()只返回天数差

日期格式化

DATE_FORMAT

在 MySQL 中，DATE_FORMAT() 函数用于将日期或时间值格式化为指定的字符串格式。

正确语法

DATE_FORMAT(date, format)

常用格式说明符

示例用法

1. 获取四位数的年份：

SELECT DATE_FORMAT(created_at, '%Y') FROM your_table;

1. 获取完整的日期时间格式：

SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') FROM your_table;

1. 获取月份和年份：

SELECT DATE_FORMAT(created_at, '%M %Y') FROM your_table;
-- 结果示例："May 2023"

1. 在你的例子中，如果要提取年份：

SELECT DATE_FORMAT(created_at, '%Y') AS year FROM your_table;

注意事项

• MySQL 的 DATE_FORMAT() 函数使用百分号 (%) 作为格式说明符的前缀
• 函数名是 DATE_FORMAT 而不是 dateformt（注意大小写不敏感但拼写要正确）
• 第一个参数是日期/时间列或表达式，第二个参数是格式字符串

day()、month()、year()

select
    date_format (date, "%d") as day,
    count(*) as question_cnt
from
    question_practice_detail
where
    date_format (date, "%m") = 8
    and date_format (date, "%y") = 21
group by day

用day、month、year简化

select
    day (date) as day,
    count(*) as question_cnt
from
    question_practice_detail
where
    month (date) = 8
    and year (date) = 2021
group by
    day

（1）like语法：date like “2021-08%”
（2）year、month函数：year(date)=‘2021’ and month(date)=‘08’；
（3）date_format函数：date_format(date, ‘%Y-%m’)=‘2021-08’;

其他函数

last_day()

last_day()返回参数日期的最后一天

SELECT
	*,
	LAST_DAY( end_date ) as `LAST_DAY`
FROM
	job_history;