MySQL时间函数

最新推荐文章于 2025-05-28 14:24:52 发布

wsh_wshkk

最新推荐文章于 2025-05-28 14:24:52 发布

阅读量1.8k

点赞数 23

CC 4.0 BY-SA版权

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/wsh_xx/article/details/145987643

一、核心时间差函数

1. TIMEDIFF()

返回两个时间/日期时间值的时间差（expr1 - expr2）

结果格式为HH:MM:SS或HHH:MM:SS

语法：

TIMEDIFF(expr1, expr2)

示例：

-- 计算两个时间点差
SELECT TIMEDIFF('2024-02-15 14:30:00', '2024-02-14 09:15:00');
-- 结果：29:15:00（29小时15分钟）

-- 跨日期时间差
SELECT TIMEDIFF('18:45:30', '08:15:45');
-- 结果：10:29:45

-- 错误示例（类型不一致）
SELECT TIMEDIFF('2024-02-15', '14:30:00'); -- 返回NULL

2. DATEDIFF()

日期差计算返回两个日期之间的天数差（expr1 - expr2）

忽略时间部分，仅计算日期差

语法：

DATEDIFF(expr1, expr2)

示例：

SELECT DATEDIFF('2024-02-20 23:59:59', '2024-02-18 00:00:00');
-- 结果：2（天数差）


--判断天数差是否在某个范围
WHERE DATEDIFF("2019-07-27", activity_date) BETWEEN 0 AND 29
--相差30天内

3. TIMESTAMPDIFF()

灵活的时间单位，支持的单位：

MICROSECOND：微
SECOND：秒
MINUTE：分钟
HOUR：小时
DAY：天
WEEK：周
MONTH：月
QUARTER：季度
YEAR：年

返回整数差值（向下取整）

语法：

TIMESTAMPDIFF(unit, start_expr, end_expr)

示例：

-- 计算年龄（精确到年）
SELECT TIMESTAMPDIFF(YEAR, '1990-05-15', CURDATE()) AS age;

-- 计算服务时长（精确到分钟）
SELECT 
    TIMESTAMPDIFF(HOUR, login_time, logout_time) AS hours,
    TIMESTAMPDIFF(MINUTE, login_time, logout_time) % 60 AS minutes
FROM user_sessions;

二、时间分解函数

1. 时间分量提取

函数	返回值范围	示例
YEAR()	1000-9999	`YEAR('2024-02-15') → 2024`
MONTH()	1-12	`MONTH('2024-07-01') → 7`
DAY()	1-31	`DAY('2024-02-29') → 29`
HOUR()	0-23	`HOUR('15:30:45') → 15`
MINUTE()	0-59	`MINUTE('15:30:45') → 30`
SECOND()	0-59	`SECOND('15:30:45') → 45`
MICROSECOND()	0-999999	`MICROSECOND('15:30:45.123456') → 123456`

2. 特殊日期函数

（1）季度获取

-- 获取季度
SELECT QUARTER('2024-11-15');  -- 结果：4

（2）星期几

DAYOFWEEK():1为周日，7为周六

WEEKDAY():0为周一，6为周日

-- 获取星期索引（1=周日）
SELECT DAYOFWEEK('2024-02-18'); -- 结果：1
---1是周日

（3）天数（该年的第几天）

-- 获取年度第几天
SELECT DAYOFYEAR('2024-02-29'); -- 结果：60

(4)LAST_DAY()

返回日期所在月份的最后一天

SELECT LAST_DAY('2024-10-05');
--- 2024-10-31

(5)UNIX_TIMESTAMP()

返回日期或时间的Unix时间戳

SELECT UNIX_TIMESTAMP('2023-10-05 14:30:45');
-- 1696516245

三、日期和时间的格式化

1.DATE_FORMAT()

将日期或时间格式化为指定的字符串

常用格式符：

%Y：四位年份（如 2024）
%m：两位月份（01-12）
%d：两位天数（01-31）
%H：两位小时（00-23）
%i：两位分钟（00-59）
%s：两位秒数（00-59）

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
---结果：2024-02-15 14:30:45

2.STR_TO_DATE()

将字符串转换为日期或时间

SELECT STR_TO_DATE('05-10-2023', '%d-%m-%Y');
-- 结果：2023-10-05

四、其他时间用法

1.获取当前日期和时间

（1）NOW()

返回当前的日期和时间

SELECT NOW();
--2025-03-14 18:51:01

（2）CURDATE()

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

SELECT CURDATE();
--2025-03-14

（3）CURTIME()

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

SELECT CURTIME();
--18:51:01

2.日期和时间的计算

（1）DATE_ADD()

在日期上添加指定的时间间隔

SELECT DATE_ADD('2023-10-05', INTERVAL 1 DAY);
---2024-10-06

（2）DATE_SUB()

在日期上减去指定的时间间隔

SELECT DATE_SUB('2024-10-05', INTERVAL 1 MONTH);
-- 2024-09-05

3. 四、实际应用

1.计算用户的年龄

假设有一个用户表 users，其中包含用户的出生日期 birthdate，可以使用以下 SQL 计算用户的年龄：

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

2. 查询最近 7 天的订单

假设有一个订单表 orders，其中包含订单创建时间 created_at，可以使用以下 SQL 查询最近 7 天的订单：

SELECT * 
FROM orders 
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);