目录
MySQL中内置的日期函数,包括获取日期、日期格式化、日期计算
1、获取日期函数
--MySQL内置的获取日期时间函数:
SELECT
NOW() AS '当前日期+时间',
CURDATE() AS '当前日期',
CURTIME() AS '当前时间';
获取具体年月日函数:
SELECT
YEAR("2022-04-11 15:44:28") AS '年',
MONTH("2022-04-11 15:44:28") AS '月',
DAY("2022-04-11 15:44:28") AS '日',
HOUR("2022-04-11 15:44:28") AS '小时',
MINUTE("2022-04-11 15:44:28") AS '分钟',
SECOND("2022-04-11 15:44:28") AS '秒',
DAYNAME("2022-04-11 15:44:28") AS '星期几',
MONTHNAME("2022-04-11 15:44:28") AS '几月';
2、格式化日期函数
SELECT
NOW() AS "现在时间",
DATE_FORMAT(NOW(), '%Y.%m.%d') AS '格式化日期',
TIME_FORMAT(NOW(), '%h:%i:%s') AS '格式化时间'
3、计算日期函数
在日期基础上增加减少天数:
SELECT
NOW() AS "现在时间",
DATE_ADD(NOW(), INTERVAL 1 DAY) AS "增加1天",
DATE_SUB(NOW(), INTERVAL 1 DAY) AS "减少1天"
还可以增加减少年、月、小时、分钟:
SELECT
NOW() AS "现在时间",
DATE_ADD(NOW(), INTERVAL 1 YEAR) AS "增加1年",
DATE_SUB(NOW(), INTERVAL 1 MONTH) AS "减少1天",
DATE_SUB(NOW(), INTERVAL 1 HOUR) AS "减少1小时"
计算两个日期间隔天数:
SELECT
DATEDIFF('2022-04-11','2021-04-11') AS "间隔天数",
DATEDIFF('2022-04-11 01:00','2022-04-10 23:00') AS "间隔天数"