一. TIMESTAMPDIFF函数
语法:
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
参数:
unit 单位( second 秒
minute 分钟
hour 小时
day 天
week 周
month 月
year 年)
datetime_expr1,datetime_expr2 两个时间(后者减去前者)
示例:
SELECT TIMESTAMPDIFF(SECOND,'2022-06-17 00:00:00','2022-06-17 23:59:59');
# 结果 = 86399秒
SELECT TIMESTAMPDIFF(MINUTE,'2022-06-17 00:00:00','2022-06-17 23:59:59');
# 结果 = 1439分钟
SELECT TIMESTAMPDIFF(HOUR,'2022-06-17 00:00:00','2022-06-17 23:59:59');
# 结果 = 23小时
SELECT TIMESTAMPDIFF(DAY,'2022-06-17 00:00:00','2022-06-17 23:59:59');
# 结果 = 0天
SELECT TIMESTAMPDIFF(WEEK,'2022-06-17 00:00:00','2022-06-17 23:59:59');
# 结果 = 0周
SELECT TIMESTAMPDIFF(MONTH,'2022-06-17 00:00:00','2022-06-17 23:59:59');
# 结果 = 0月
SELECT TIMESTAMPDIFF(YEAR,'2022-06-17 00:00:00','2022-06-17 23:59:59');
# 结果 = 0年
# 可以结合日期函数进行搭配使用
SELECT TIMESTAMPDIFF(SECOND,'2022-06-17', CURRENT_DATE());
SELECT TIMESTAMPDIFF(SECOND,'2022-06-17 00:00:00',NOW());
SELECT TIMESTAMPDIFF(SECOND,'2022-06-17 00:00:00',CURRENT_TIMESTAMP());
# 补充说明
SELECT CURRENT_DATE();
# 结果 = 2022-06-17
SELECT NOW();
# 结果 = 2022-06-17 23:55:55
SELECT CURRENT_TIMESTAMP();
# 结果 = 2022-06-17 23:55:55