3.日期和时间函数
#3.1 获取日期、时间
SELECT CURDATE(),CURTIME(),NOW(),SYSDATE(),UTC_DATE(),
UTC_TIME()
FROM DUAL;
#3.2 日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2021-10-01 12:12:32'),
FROM_UNIXTIME(1637828587),FROM_UNIXTIME(1633061552)
FROM DUAL;
#3.3 获取月份、星期、星期数、天数等函数
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;
日期的操作函数
#3.4 日期的操作函数
SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),
EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2021-05-12')
FROM DUAL;
时间和秒钟转换的函数
#3.5 时间和秒钟转换的函数
SELECT TIME_TO_SEC(CURTIME()),
SEC_TO_TIME(59902)
FROM DUAL;
计算日期和时间的函数
#3.6 计算日期和时间的函数
SELECT NOW(),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 col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL
1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
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;
日期的格式化与解析
#3.7 日期的格式化与解析
# 格式化:日期 --->字符串
# 解析: 字符串--->日期
#此时我们谈的是日期的显示格式化和解析
#之前,我们接触过隐式的格式化或解析
SELECT *
FROM employees
WHERE hire_date='1993-01-13';
#格式化
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),
DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(NOW(),'%H:%i:%S'),
DATE_FORMAT(NOW(),'%Y-%M-%D %H:%i:%S %W %w %T %r')
FROM DUAL;
#解析:格式化的逆过程
SELECT STR_TO_DATE('2021-November-25th 17:08:30 Thursday 4 17:08:30 05:08:30 PM','%Y-%M-%D %H:%i:%S %W %w %T %r'),
FROM DUAL;
SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;
SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;