SQL中的 DATEDIFF函数与TIMESTAMPDIFF函数

首先这两个函数都是计算时间差。

DATEDIFF

用法：DATEDIFF (end_date,begin_date)
DATEDIFF函数只能计算天数差，不能计算年份、小时、分钟、秒差。

示例：

# 6
select datediff('2018-05-07','2018-05-01');

# 0
select datediff('2018-05-01','2018-05-01');

# -1
select datediff('2018-04-30','2018-05-01');

# 6
select datediff('2018-05-07 12:30:30','2018-05-01');

# 1
select datediff('2018-05-02 23:59:59','2018-05-01 00:00:00');

---

TIMESTAMPDIFF

用法：TIMESTAMPDIFF(参数,end_date,begin_date)
它计算一天是按24小时来定义的
可以计算两个日期之间相差的年、季度、月、星期、日、小时、分钟、秒

TIMESTAMPDIFF 参数取值如下：
YEAR：年
QUARTER：季度
MONTH：月
WEEK：星期
DAY：天
HOUR：小时
MINUTE：分钟
SECOND：秒

# 年 23
select timestampdiff(year,'2000-01-02 12:30:30','2023-04-23 20:45:40');

# 季度 1
select timestampdiff(QUARTER,'2023-01-02 12:30:30','2023-04-23 20:45:40');

# 月 3
select timestampdiff(month,'2023-01-02 12:30:30','2023-04-23 20:45:40');

# 星期 15
select timestampdiff(week,'2023-01-02 12:30:30','2023-04-23 20:45:40');

# 天 1
select timestampdiff(day,'2023-04-22 00:00:00','2023-04-23 23:59:59'); 

# 小时 47
select timestampdiff(hour,'2023-04-22 00:00:00','2023-04-23 23:59:59');

# 分钟 1
select timestampdiff(minute,'2023-04-23 13:15:15','2023-04-23 13:16:15');
select timestampdiff(minute,'2023-04-23 13:15:15','2023-04-23 13:16:16');

# 秒 60 61
select timestampdiff(second,'2023-04-23 13:15:15','2023-04-23 13:16:15');
select timestampdiff(second,'2023-04-23 13:15:15','2023-04-23 13:16:16');

DATADIFF与TIMESTAMPDIFF区别

• 如果日期格式是YY-MM-DD HH-MM-SS的格式时 DATEDIFF只截取YY-MM-DD部分进行相减，这样减出来的结果就会存在误差。而 TIMESTAMPDIFF则是按照24小时为一天来计算的

# 1 天
select datediff('2018-05-02 13:30:30','2018-05-01 14:30:30');
# 0 天
select timestampdiff(day,'2018-05-01 14:30:30','2018-05-02 13:30:30');
# 23 小时
select timestampdiff(hour,'2018-05-01 14:30:30','2018-05-02 13:30:30');
# 1 天
select timestampdiff(day,'2018-05-01 14:30:30','2018-05-02 14:30:30');

练习数据

CREATE TABLE `a` (
  `id` int NOT NULL,
  `day1` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1', '2000-01-21');
INSERT INTO `a` VALUES ('2', '2019-02-03');
INSERT INTO `a` VALUES ('3', '2018-05-05');