mysql中常见函数(日期函数)

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;