mysql日期函数大全及用法实例

ADDDATE(date, number)返回日期date后number天的日期拉。（牢固返回yyyy-MM-dd样式）
SELECT ADDDATE('2020-07-05', 100) AS ADDDATE;
ADDTIME(date1, date2)返回时刻date1加之date2的日期拉。
SELECT ADDTIME('2020-07-05 12:30:01', '10:00') AS ADDTIME;
CURDATE()返回现在日期拉。
SELECT CURDATE() AS CURDATE;
CURRENT_DATE()返回现在日期拉。（与CURDATE相似）
SELECT CURRENT_DATE() AS `CURRENT_DATE`;
或者
SELECT CURRENT_DATE AS `CURRENT_DATE`;
CURRENT_TIME()返回现在时刻拉。
SELECT CURRENT_TIME() AS `CURRENT_TIME`;
或者
SELECT CURRENT_TIME AS `CURRENT_TIME`;
CURRENT_TIMESTAMP()返回现在时刻戳拉。
SELECT CURRENT_TIMESTAMP() AS `CURRENT_TIMESTAMP`;
或者
SELECT CURRENT_TIMESTAMP AS `CURRENT_TIMESTAMP`;
CURTIME()返回现在时刻拉。（与CURRENT_TIME相似）
SELECT CURTIME() AS CURTIME;
DATE(date)从日期讲明式中提取日期拉。
SELECT DATE(20200809) AS DATE;
DATEDIFF(date1, date2)返回date1, date2之间相隔天数拉。（date1小于date2时，结局为负）
SELECT DATEDIFF('20200809', '20200908') AS DATEDIFF;
DATE_ADD(date, INTERVAL expr type)返回date增添指定的时刻距离后的时刻
SELECT DATE_ADD('2020-10-21 15:05:00', INTERVAL 2 DAY) AS DATE_ADD;
DATE_FORMAT(date, format)根据format样式返回时刻拉。
SELECT DATE_FORMAT('2020-10-21 15:05:01', '%Y/%m/%d') AS DATE_FORMAT;
DATE_SUB(date, INTERVAL expr type)返回日期减去指定的时刻距离后的时刻拉。
SELECT DATE_SUB('2020-10-21 15:05:00', INTERVAL 2 DAY) AS DATE_SUB;
DAY(date)返回date中的日期拉。
SELECT DAY('2020-10-21 15:05:01') AS DAY;
DAYNAME(date)返回date所处星期的英文全写拉。
SELECT DAYNAME('2020-10-21 15:05:01') AS DAYNAME;
DAYOFMONTH(date)返回date是当月的第几天拉。
SELECT DAYOFMONTH('2020-10-21 15:05:01') AS DAYOFMONTH;
DAYOFWEEK(date)返回date是一周的第几天拉。（1是周日，2是周一）
SELECT DAYOFWEEK('2020-10-21 15:05:01') AS DAYOFWEEK;
DAYOFYEAR(date)返回date是一年的第几天拉。
SELECT DAYOFYEAR('2020-10-21 15:05:01') AS DAYOFYEAR;
EXTRACT(type FROM date)从date中获取指定值拉。
SELECT EXTRACT(DAY FROM '2020-10-21 15:05:01') AS EXTRACT;
FROMDAYS(number)给定天数，返回对应日期
SELECT FROM_DAYS(738084) AS FROM_DAYS;
HOUR(date)从date中获取小时数拉。
SELECT HOUR('2020-10-21 15:05:01') AS `HOUR`;
LAST_DAY(date)返回date地址月份的最终一天拉。
SELECT LAST_DAY('2020-10-21 15:05:01') AS LAST_DAY;
LOCALTIME()返回现在时刻拉。
SELECT LOCALTIME() AS `LOCALTIME`;
或者
SELECT LOCALTIME AS `LOCALTIME`;
LOCALTIMESTAMP()返回现在时刻戳拉。
SELECT LOCALTIMESTAMP() AS `LOCALTIMESTAMP`;
或者
SELECT LOCALTIMESTAMP AS `LOCALTIMESTAMP`;
MAKEDATE(year, dayOfYear)依照年份year和一年中的第dayOfYear先天成日期拉。
SELECT MAKEDATE(2020,200) AS MAKEDATE;
MAKETIME(hour, minute, second)依照hour、minute、second变成时刻拉。
SELECT MAKETIME(1, 2, 3) AS MAKETIME;
MICROSECOND(date)返回date中的微秒数拉。
SELECT MICROSECOND('2020-10-21 15:05:01.002100') AS MICROSECOND;
MINUTE(date)返回date中的分钟数拉。
SELECT MINUTE('2020-10-21 15:05:01') AS `MINUTE`;
MONTHNAME(date)返回date地址月份的英文全写拉。
SELECT MONTHNAME('2020-10-21 15:05:01') AS MONTHNAME;
MONTH(date)返回date地址月份拉。
SELECT MONTH('2020-10-21 15:05:01') AS `MONTH`;
NOW()返回现在时刻拉。
SELECT NOW() AS NOW;
PERIOD_ADD(period, number)增添number个月至周期period(样式为YYMM或者YYYYMM)拉。
SELECT PERIOD_ADD('202010', 1) AS PERIOD_ADD;
PERIOD_DIFF(period1, period2)返回两个周期period一、period2的差值拉。
SELECT PERIOD_DIFF('202010', '202009') AS PERIOD_DIFF;
QUARTER(date)返回date地址季度拉。
SELECT QUARTER('2020-10-21 15:05:01') AS `QUARTER`;
SECOND(date)返回date中的秒值拉。
SELECT SECOND('2020-10-21 15:05:01') AS `SECOND`;
SEC_TO_TIME(number)将number转为时光秒样式拉。
SELECT SEC_TO_TIME(600) AS SEC_TO_TIME;
SEC_TO_DATE(date, format)将date依照format剖析成日期拉。
SELECT STR_TO_DATE('2020 10 25th', '%Y %c %D') AS STR_TO_DATE;
SUBDATE(date, number)返回date往前移number天后的日期拉。
SELECT SUBDATE('2020-10-21 15:05:01', 2) AS SUBDATE;
SUBTIME(date, number)返回date减去number秒的日期拉。
SELECT SUBTIME('2020-10-21 15:05:01', 2) AS SUBTIME;
SYSDATE()返回现在日期拉。（与now()相似）
SELECT SYSDATE() AS SYSDATE;
TIME(date)返回date的时刻部-分拉。
SELECT TIME('2020/10/25 15:05:01') AS TIME;
TIME_FORMAT(date, format)返回format样式的date的时刻部-分拉。
SELECT TIME_FORMAT('2020-10-25 15:05:01', '%H-%i-%s') AS TIME_FORMAT;
TIME_TO_SEC(date)将date转为秒为单元时刻戳拉。
SELECT TIME_TO_SEC('2020-10-25 15:05:01') AS TIME_TO_SEC;
TIMEDIFF(date1, date2)返回date1与date2的差值拉。
SELECT TIMEDIFF('2020-10-25 15:05:01', '2020-10-24 15:05:01') AS TIMEDIFF;
TIMESTAMP(date)返回一切的date拉。
SELECT TIMESTAMP('2020-10-25') AS `TIMESTAMP`;
TIMESTAMP(date, time)返回拼接起身的时刻拉。
SELECT TIMESTAMP('2020-10-25', '15:05:01') AS `TIMESTAMP`;
TO_DAYS(date)返回date差异 0000 年 1 月 1 日的天数拉。
SELECT TO_DAYS('2020-10-25 15:05:01') AS TO_DAYS;
WEEK(date)返回date地址周数拉。（0-53，默许是周日为一周的第一天）
SELECT WEEK('2020-10-25 15:05:01') AS `WEEK`;
WEEKDAY(date)返回date是周几拉。（0 表现周一，1 表现周两）
SELECT WEEKDAY('2020-10-25 15:05:01') AS WEEKDAY;
WEEKOFYEAR(date)返回date地址周数拉。（0-53，默许是周日为一周的第一天）
SELECT WEEKOFYEAR('2020-10-25 15:05:01') AS WEEKOFYEAR;
YEAR(date)返回date的年份拉。
SELECT YEAR('2020-10-25 15:05:01') AS `YEAR`;
YEARWEEK(date, mode)返回date的年份与地址周数拉。（mode0为周日为一周的第一天，1为周一为一周的第一天）
SELECT YEARWEEK('2020-10-25 15:05:01', 0) AS YEARWEEK;