TIMESTAMPDIFF()

TIMESTAMPDIFF()函数返回两个时间的差集


语法

TIMESTAMPDIFF(interval, date1, date2)

SELECT TIMESTAMPDIFF(	SECOND,		‘2021-05-03 07:29:02‘, ‘2021-05-04 07:29:05‘); # 86403
SELECT TIMESTAMPDIFF(	MINUTE,		‘2021-05-03 07:29:02‘, ‘2021-05-04 07:29:05‘); # 1440
SELECT TIMESTAMPDIFF(	HOUR,		‘2021-05-03 07:29:02‘, ‘2021-05-04 07:29:05‘); # 24
SELECT TIMESTAMPDIFF(	DAY,		‘2021-05-03 07:29:02‘, ‘2021-05-04 07:29:05‘); # 1
SELECT TIMESTAMPDIFF(	WEEK,		‘2021-05-03 07:29:02‘, ‘2021-05-04 07:29:05‘); # 0
SELECT TIMESTAMPDIFF(	MONTH,		‘2021-05-03 07:29:02‘, ‘2021-05-04 07:29:05‘); # 0
SELECT TIMESTAMPDIFF(	QUARTER,	‘2021-05-03 07:29:02‘, ‘2021-05-04 07:29:05‘); # 0
SELECT TIMESTAMPDIFF(	YEAR,		‘2021-05-03 07:29:02‘, ‘2021-05-04 07:29:05‘); # 0

# 注意：TIMESTAMPDIFF()是`date2-date1`，是第二个日期参数减去第一个日期参数

DATEDIFF()

DATEDIFF()函数返回两个日期之间的天数间隔

语法

DATEDIFF(date1, date2)

SELECT DATEDIFF(‘2021-05-05 07:29:02‘, ‘2021-05-01 07:29:05‘); # 4

# 注意：DATEDIFF()是`date1-date2`，第一个参数减去第二个参数
# DATEDIFF()和TIMESTAMPDIFF()的减法逻辑正好相反

TIMEDIFF

TIMEDIFF()返回两个时间的差集

语法

TIMEDIFF(date1, date2)

SELECT TIMEDIFF(‘2021-05-05 07:29:02‘, ‘2021-05-01 07:29:05‘); # 95:59:57
															   # HH:mm:SS

NOW()

NOW()返回系统当前的日期和时间

SELECT NOW()		# 2021-05-03 15:26:24

CURDATE()

CURDATE()返回系统当前日期

SELECT CURDATE()	# 2021-05-03

CURTIME()

CURTIME()返回系统当前时间

SELECT CURTIME()	# 15:26:24

其他待补充

MYSQL日期相关的函数