MYSQL-日期、时间函数

获取当前日期、时间

SELECT NOW() FROM DUAL;
SELECT CURDATE() FROM DUAL;
SELECT CURRENT_DATE() FROM DUAL;
SELECT CURRENT_TIME() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;
SELECT CURTIME() FROM DUAL;
SELECT SYSDATE() FROM DUAL;
SELECT UTC_DATE() FROM DUAL;
SELECT UTC_TIME() FROM DUAL;

日期与时间戳的转换

UNIX_TIMESTAMP()

• 日期转换为时间戳

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP("2023-02-28 10:58:10") FROM DUAL;

FROM_UNIXTIME()

• 时间戳转换为日期

SELECT FROM_UNIXTIME(1677679090) FROM DUAL;

获取月份、星期、星期数、天数等函数

SELECT
	YEAR (NOW()),
	MONTH (CURRENT_TIMESTAMP()),
	DAY (NOW()),
	HOUR (NOW()),
	MINUTE (NOW()),
	SECOND (SYSDATE()),
	QUARTER (SYSDATE()),
	WEEK (NOW()),
	WEEKDAY(NOW()),
	MONTHNAME('2023-03-03'),
	DAYNAME('2023-03-03'),
	DAYOFYEAR(NOW()),
	DAYOFMONTH(NOW()),
	DAYOFWEEK(NOW()),
	YEARWEEK(NOW())
FROM
	DUAL;

EXTRACT(unit FROM date)

• 函数提取日期的一部分

/*
unit选项值:
DAY
DAY_HOUR
DAY_MICROSECOND
DAY_MINUTE
DAY_SECOND
HOUR
HOUR_MICROSECOND
HOUR_MINUTE
HOUR_SECOND
MICROSECOND
MINUTE
MINUTE_MICROSECOND
MINUTE_SECOND
MONTH
QUARTER
SECOND
SECOND_MICROSECOND
WEEK
YEAR
YEAR_MONTH
*/

SELECT
	EXTRACT(YEAR FROM NOW()),
	EXTRACT(MONTH FROM SYSDATE()),
	EXTRACT(DAY FROM CURRENT_TIMESTAMP()),
	EXTRACT(HOUR FROM NOW()),
	EXTRACT(MINUTE FROM SYSDATE()),
	EXTRACT(SECOND FROM SYSDATE()),
	EXTRACT(QUARTER FROM NOW()),
	EXTRACT(
		WEEK
		FROM
			CURRENT_TIMESTAMP ()
	)

时间和秒钟的转换

TIME_TO_SEC()

SELECT TIME_TO_SEC(CURTIME());
SELECT TIME_TO_SEC(CURRENT_TIME());

SEC_TO_TIME()

SELECT SEC_TO_TIME(81405);# 22:36:45

计算日期、时间的函数

DATE_ADD(date,INTERVAL expr unit)

/* unit选项值
MICROSECOND	间隔单位：毫秒
SECOND	间隔单位：秒
MINUTE	间隔单位：分钟
HOUR	间隔单位：小时
DAY	间隔单位：天
WEEK	间隔单位：星期
MONTH	间隔单位：月
QUARTER	间隔单位：季度
YEAR	间隔单位：年
SECOND_MICROSECOND	复合型，间隔单位：秒、毫秒，expr可以用两个值来分别指定秒和毫秒
MINUTE_MICROSECOND	复合型，间隔单位：分、毫秒
MINUTE_SECOND	复合型，间隔单位：分、秒
HOUR_MICROSECOND	复合型，间隔单位：小时、毫秒
HOUR_SECOND	复合型，间隔单位：小时、秒
HOUR_MINUTE	复合型，间隔单位：小时分
DAY_MICROSECOND	复合型，间隔单位：天、毫秒
DAY_SECOND	复合型，间隔单位：天、秒
DAY_MINUTE	复合型，间隔单位：天、分
DAY_HOUR	复合型，间隔单位：天、小时
YEAR_MONTH	复合型，间隔单位：年、月
*/

SELECT
	DATE_ADD(NOW(), INTERVAL 1 DAY),# 当前时间增加一天
	DATE_ADD(NOW(), INTERVAL - 1 YEAR),# 当前时间减去一年
	DATE_ADD(
		NOW(),
		INTERVAL '1_1' YEAR_MONTH
	),# 当前是时间增加一年一个月
	DATE_ADD(
		NOW(),
		INTERVAL '1' YEAR_MONTH
	)# 当前时间增加一年
	

ADDTIME(expr1,expr2)

• 向时间添加指定时间并返回日期时间

SELECT ADDTIME('2023-03-03 22:53:34',10);
# 2023-03-03 22:53:44
SELECT ADDTIME('2023-03-03 22:53:34','2:7:30');
# 2023-03-04 01:01:04
SELECT ADDTIME('2023-03-03 22:53:34','33 2:5:30');
# 2023-04-06 00:59:04

SUBTIME(expr1,expr2)

• 向时间减去指定时间并返回日期时间

SELECT SUBTIME('2023-03-03 22:53:34',30);
# 2023-03-03 22:53:04
SELECT SUBTIME('2023-03-03 00:53:34','1:1:3');
# 2023-03-02 23:52:31
SELECT SUBTIME('2023-03-03 00:53:34','2 1:1:3');
# 2023-02-28 23:52:31

DATEDIFF(expr1,expr2)

• 返回日期expr1减去expr2所得的天数

SELECT DATEDIFF('2023-03-03','2022-12-07');# 86

TIMEDIFF(expr1,expr2)

• 返回两个时间（expr1减去expr2）相减得到的差值，格式为hh:mm:ss

SELECT TIMEDIFF('2023-03-07 22:56:56','2023-03-06 08:32:09'); 
# 38:24:47

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

• 返回（datetime_expr2-datetime_expr1）的时间差，结果单位由unit参数给出。

/*
SECOND：秒
MINUTE：分钟
HOUR：小时
DAY：天
WEEK：星期
MONTH：月
QUARTER：季度
YEAR：年
*/
SELECT TIMESTAMPDIFF(HOUR,'2023-03-07 22:56:56','2023-03-06 08:32:09');
# -38
SELECT TIMESTAMPDIFF(DAY,'2023-03-07 22:56:56','2023-03-10 08:32:09');
# 2

FROM_DAYS(N)

• 计算从0年开始指定天数之后的日期，仅适用于公历

TO_DAYS(date)

• 返回从0000年（公元1年）至当前日期的总天数

SELECT FROM_DAYS(738946),TO_DAYS('2023-03-02') FROM DUAL;

LAST_DAY(date)

• 返回某个月最后一天的日期

SELECT LAST_DAY(NOW());

MAKEDATE(year,dayofyear)

• 返回基于年份和天数的日期

SELECT MAKEDATE(YEAR(NOW()),96),MAKEDATE(2023,96);

MAKETIME(hour,minute,second)

• 根据小时，分钟和秒值创建并返回时间

SELECT MAKETIME(12,34,36);# 12:34:36

PERIOD_ADD(P,N)

• 在格式 YYMM 或 YYYYMM 中添加给定期间内的给定月份数

SELECT PERIOD_ADD(2302,5),PERIOD_ADD(202302,5),PERIOD_ADD(2023-03,-10);
# 202307 202307 202010

日期的格式化与解析

日期格式化

SELECT employee_id,DATE_FORMAT(hire_date,'%Y-%M-%D')  FROM employees ;
SELECT employee_id,DATE_FORMAT(hire_date,'%Y-%m-%d')  FROM employees  ;
SELECT employee_id,DATE_FORMAT(hire_date,'%Y-%M-%D %h:%i:%S %W %w %T %r')  FROM employees  ;

日期解析

SELECT STR_TO_DATE('2023-March-1st 10:19:49 Wednesday 3','%Y-%M-%D %h:%i:%S %W %w') FROM DUAL;