mysql中常見函數(日期函數)

直接po代碼吧，代碼中有注釋

#三、日期函數
#NOW() 傳回目前系統日期+時間
SELECT NOW();
#curdate() 傳回目前系統日期，不包含時間
SELECT CURDATE();
#curtime() 傳回目前時間，不包含日期
SELECT CURTIME();

#可以擷取指定的部分，年、月、日、小時、分鐘、秒
SELECT YEAR('2019-01-08') AS 'year', MONTH('2019-03-06') AS 月, DAY('2019-02-09') "日";
SELECT HOUR('2019-01-08 18:36:25') AS '時', MINUTE('2019-03-06 18:36:25') AS 分, SECOND('2019-02-09 18:36:25') "秒";
#
SELECT SYSDATE();
#目前時間
SELECT CURTIME();
#目前時間：等同于 CURTIME()
SELECT CURRENT_TIME();
#
SELECT CURRENT_TIMESTAMP();
SELECT CURRENT_TIMESTAMP;
SELECT LOCALTIME();
SELECT LOCALTIME;
SELECT LOCALTIMESTAMP();
SELECT LOCALTIMESTAMP;

#擷取月份的英文名，結果為September
SELECT MONTHNAME('2019-09-06');
#
SELECT 
hiredate AS "入職時間", 
YEAR(hiredate) 入職年份, 
MONTH(hiredate) AS 入職月份, 
DAY(hiredate) '入職日' 
FROM employees;

#str_to_date 将字元通過指定的格式轉換成日期
SELECT 
STR_TO_DATE('2018-3-4', '%Y-%m-%d') AS output, 
STR_TO_DATE('2018/09/02', '%Y/%c/%d') AS output, 
STR_TO_DATE('19-5-7', '%y-%c-%d') AS output;

#
SELECT 
STR_TO_DATE('2018-3-4 17:13:55', '%Y-%m-%d %H:%i:%s') AS output,
STR_TO_DATE('2018-3-4 11:18:37', '%Y-%m-%d %h:%i:%s') AS output,
STR_TO_DATE('2018-3-4 12:16:28', '%Y-%m-%d %h:%i:%s') AS output,
STR_TO_DATE('2018-3-4 12:16:28', '%y-%m-%d %h:%i:%s') AS output,
STR_TO_DATE('18-3-4 12:16:28', '%y-%m-%d %h:%i:%s') AS output,
STR_TO_DATE('2018-3-4 15:22:39', '%y-%m-%d %h:%i:%s') AS output;
#
SELECT * FROM employees WHERE hiredate = '1992-04-03';
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('04-03-1992', '%m-%d-%Y');
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-03-1992', '%c-%d-%Y');
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-03 92', '%c-%d %y');

#date_format() 将日期轉換成字元
SELECT 
DATE_FORMAT(NOW(), '%Y年%m月%d日') AS output,
DATE_FORMAT(NOW(), '%y年%m月%d日') AS output,
DATE_FORMAT(NOW(), '%Y年%c月%d日') AS output,
DATE_FORMAT(NOW(), '%y年%c月%d日') AS output,
DATE_FORMAT('2018-06-08', '%Y年%m月%d日') AS output,
DATE_FORMAT('2018-02-05', '%y年%c月%d日') AS output
;
#
SELECT DATE_FORMAT('2018-09-05', '%Y%m%d');
SELECT DATE_FORMAT('2018-09-05', '%Y-%m-%d');
SELECT DATE_FORMAT('2018-09-05', '%Y/%m/%d');
SELECT DATE_FORMAT('2018-09-05', '%Y#%m#%d');
SELECT DATE_FORMAT('2018/09/05', '%Y-%m-%d');
SELECT DATE_FORMAT('2018/09/05', '%Y-%c-%d');
SELECT DATE_FORMAT('2018/9/05', '%Y-%c-%d');
SELECT DATE_FORMAT('2018/9/05', '%Y-%m-%d');
SELECT DATE_FORMAT('20180905', '%Y-%m-%d');
SELECT DATE_FORMAT('20180905', '%y-%m-%d');
SELECT DATE_FORMAT('180905', '%y-%m-%d');
SELECT DATE_FORMAT('180905', '%Y-%m-%d');
SELECT DATE_FORMAT('2018/09/05', '%Y-%c-%d');
SELECT DATE_FORMAT('2018/09/05', '%y-%c-%d');
#結果為05-09-2018
SELECT DATE_FORMAT('18-09-05', '%d-%m-%Y');
#結果為05-09-18
SELECT DATE_FORMAT('18-09-05', '%d-%m-%y');
#結果為2018年05月06日
SELECT DATE_FORMAT('2018-05-06', '%Y年%m月%d日');

#查詢有獎金的員工名和入職日期(xx月/xx日 xx年)
SELECT 
last_name,
hiredate,
DATE_FORMAT(hiredate, '%m月/%d日 %y年') AS 入職日期,
DATE_FORMAT(hiredate, '%c月/%e日 %Y年') AS 入職日期
FROM employees WHERE commission_pct IS NOT NULL;