日期和时间函数

#日期和时间函数
#获取日期，时间
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),UTC_DATE(),UTC_TIME()
FROM DUAL;

#日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2021-10-01 12:12:32'),
FROM_UNIXTIME(163517853),FROM_UNIXTIME(1633061552)
FROM DUAL;

#获取月份，星期，星期数，天数等函数
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),QUARTER(CURDATE()),
WEEK(CURDATE()),DAYOFYEAR(NOW()),DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
#日期的操作函数
SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(HOUR_MINUTE FROM NOW()),
EXTRACT(QUARTER FROM NOW())
FROM DUAL;
#时间和秒钟的转换函数
SELECT TIME_TO_SEC(CURTIME()),
SEC_TO_TIME(83355)
FROM DUAL;
#计算日期和时间的函数
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR)
DATE_ADD(NOW(),INTERVAL -1 YEAR),
DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL; 

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY) AS cool,ADDDATE('2021-10-21 2332:12',INTERVAL 1 SECOND) AS col3,DATE_ADD(NOW(),INTERVAL -1 YEAR) AS col5,#可以是负数
DATE_ADD(NOW(),INTERVAL '1_1' YEAR_MONTH) AS col6#需要单引号
FROM DUAL;

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),LAST_DAY(NOW()),MAKEDATE(year(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;

#日期的格式化与解析
#格式化：日期--->字符串
#解析：字符串--->日期
SELECT * 
FROM employees
WHERE hire_date='1993-01-13';
#格式化
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),''),DATE_FORMAT(NOW(),'%Y-%M-%D %Y-%m-%d %W %w %T %r')
FROM DUAL;
#解析：格式化的逆过程
SELECT STR_TO_DATE(' 2022-04-04 ','%Y-%m-%d')
FROM DUAL;

SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;
SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;