四、 日期和时间函数
4.1 获取日期、时间
举例：
SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0 FROM DUAL;
4.2 日期与时间戳的转换
举例：
mysql> SELECT UNIX_TIMESTAMP(now()); +-----------------------+ | UNIX_TIMESTAMP(now()) | +-----------------------+ | 1576380910 | +-----------------------+ 1 row in set (0.01 sec) mysql> SELECT UNIX_TIMESTAMP(CURDATE()); +---------------------------+ | UNIX_TIMESTAMP(CURDATE()) | +---------------------------+ | 1576339200 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT UNIX_TIMESTAMP(CURTIME()); +---------------------------+ | UNIX_TIMESTAMP(CURTIME()) | +---------------------------+ | 1576380969 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11') +---------------------------------------+ | UNIX_TIMESTAMP('2011-11-11 11:11:11') | +---------------------------------------+ | 1320981071 | +---------------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1576380910); +---------------------------+ | FROM_UNIXTIME(1576380910) | +---------------------------+ | 2019-12-15 11:35:10 | +---------------------------+ 1 row in set (0.00 sec)
4.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;
4.4 日期的操作函数
EXTRACT(type FROM date)函数中type的取值与含义：
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()), EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW()) FROM DUAL;
4.5 时间和秒钟转换的函数
举例：
mysql> SELECT TIME_TO_SEC(NOW()); +--------------------+ | TIME_TO_SEC(NOW()) | +--------------------+ | 78774 | +--------------------+ 1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(78774); +--------------------+ | SEC_TO_TIME(78----------+ 1 row in set (0.12 sec)
4.6 计算日期和时间的函数
第1组：
上述函数中type的取值：
举例：
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 DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1, SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2, DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3 FROM DUAL;
第2组：
举例：
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;
mysql> SELECT ADDTIME(NOW(), 50); +---------------------+ | ADDTIME(NOW(), 50) | +---------------------+ | 2019-12-15 22:17:47 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDTIME(NOW(), '1:1:1'); +-------------------------+ | ADDTIME(NOW(), '1:1:1') | +-------------------------+ | 2019-12-15 23:18:46 | +-------------------------+ 1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '1:1:1'); +-------------------------+ | SUBTIME(NOW(), '1:1:1') | +-------------------------+ | 2019-12-15 21:23:50 | +-------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBTIME(NOW(), '-1:-1:-1'); +----------------------------+ | SUBTIME(NOW(), '-1:-1:-1') | +----------------------------+ | 2019-12-15 22:25:11 | +----------------------------+ 1 row in set, 1 warning (0.00 sec)
mysql> SELECT FROM_DAYS(366); +----------------+ | FROM_DAYS(366) | +----------------+ | 0001-01-01 | +----------------+ 1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2020,1); +------------------+ | MAKEDATE(2020,1) | +------------------+ | 2020-01-01 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT MAKEDATE(2020,32); +-------------------+ | MAKEDATE(2020,32) | +-------------------+ | 2020-02-01 | +-------------------+ 1 row in set (0.00 sec)
mysql> SELECT MAKETIME(1,1,1); +-----------------+ | MAKETIME(1,1,1) | +-----------------+ | 01:01:01 | +-----------------+ 1 row in set (0.00 sec)
mysql> SELECT PERIOD_ADD(20200101010101,1); +------------------------------+ | PERIOD_ADD(20200101010101,1) | +------------------------------+ | 20200101010102 | +------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT TO_DAYS(NOW()); +----------------+ | TO_DAYS(NOW()) | +----------------+ | 737773 | +----------------+ 1 row in set (0.00 sec)
举例：查询 7 天内的新增用户数有多少？
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
