【MySQL】日期和时间函数（下）
1.时间和秒钟转换的函数
| 函数 | 小写 | 用法 |
|---|---|---|
| TIME_TO_SEC(time) | time_to_sec | 将time转化为秒并返回结果，公式：小时*3600+分钟*60+秒 |
| SEC_TO_TIME | sec_to_time | 将seconds转化为包含小时、分钟、秒的时间 |
SELECT TIME_TO_SEC(NOW());
SELECT SEC_TO_TIME(78774);
2.计算日期和时间的函数
第一组
| 函数 | 小写 | 用法 |
|---|---|---|
| DATE_ADD(datetime,INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) | date_add，addDate | 返回与给定日期相差interval时间段的日期时间 |
| DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type) | date_sub，subDate | 返回与date相差interval时间间隔的日期 |
上述函数中type的取值
| 间隔类型（type取值） | 小写 | 含义 |
|---|---|---|
| HOUR | hour | 小时 |
| MINUTE | minute | 分钟 |
| SECOND | second | 秒 |
| YEAR | year | 年 |
| MONTH | month | 月 |
| DAY | day | 日 |
| YEAR_MONTH | year_month | 年和月 |
| DAY_HOUR | day_hour | 日和小时 |
| DAY_MINUTE | day_minute | 日和分钟 |
| DAY_SECOND | day_second | 日和秒 |
| HOUR_MINUTE | hour_minute | 小时和分钟 |
| HOUR_SECOND | hour_second | 小时和秒 |
| MINUTE_SECOND | minute_second | 分钟和秒 |
示例1：
SELECT
NOW(), -- 2022-08-12 08:49:44
DATE_ADD(NOW(),INTERVAL 1 DAY) AS col1, -- 2022-08-13 08:49:44
DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2, -- 2021-10-21 23:32:13
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3, -- 2021-10-21 23:32:13
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4, -- 2021-10-21 23:33:13 相差一分一秒
DATE_ADD(NOW(),INTERVAL -1 YEAR) AS col5, -- 2021-08-12 08:49:44
DATE_ADD(NOW(),INTERVAL '1_1' YEAR_MONTH) AS col6 -- 2023-09-12 08:49:44 相差一年一个月
FROM DUAL;
示例2：
SELECT
DATE_SUB('2021-04-21',INTERVAL 31 DAY) AS col1, -- 2020-12-21 间隔31天
SUBDATE('2021-04-21',INTERVAL 31 DAY) AS col2, -- 2021-03-21
DATE_SUB('2021-04-21 20:12:30',INTERVAL '1 1' DAY_HOUR) AS col3 -- 2021-04-20 19:12:30 间隔一天一小时
FROM DUAL;
第二组
| 函数 | 小写 | 用法 |
|---|---|---|
| ADDTIME(time1,time2) | addTime | 返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数 |
| SUBTIME(time1,time2) | subTime | 返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数 |
| DATEDIFF(date1,date2) | dateDiff | 返回date1-date2的日期间隔天数 |
| TIMEDIFF(time1,time2) | timeDiff | 返回time1-time2的时间间隔 |
| FROM_DAYS(N) | from_days | 返回从0000年1月1日期，N天以后的日期 |
| TO_DAYS(date) | to_days | 返回日期date距离0000年1月1日的天数 |
| LAST_DAY(date) | last_day | 返回date所在月份的最后一天的日期 |
| MAKEDATE(year,n) | makeDate | 针对给定年份与所在年份中的天数返回一个日期 |
| MAKETIME(hour,minute,second) | makeTime | 将给定的小时、分钟和秒组合成时间并返回 |
| PERIOD_ADD(time,n) | period_add | 返回time加上n后的时间 |
示例1：
SELECT
NOW(), -- 2022-08-12 09:25:25
ADDTIME(NOW(),20), -- 2022-08-12 09:25:45 加了20秒
SUBTIME(NOW(),30), -- 2022-08-12 09:24:55 减了30秒
SUBTIME(NOW(),'1:1:3'), -- 2022-08-12 08:24:22
DATEDIFF(NOW(),'2021-10-01'), -- 315 日期间隔天数
TIMEDIFF(NOW(),'2021-10-25 22:10:10'), -- 838:59:59 时间间隔
FROM_DAYS(366), -- 0001-01-01 从0000-01-01经过366天后的日期
TO_DAYS('0000-12-25'), -- 359 0000-12-25距离0000-01-01的天数
LAST_DAY(NOW()), -- 2022-08-31 该月最后一天
MAKEDATE(YEAR(NOW()),12), -- 2022-01-12 2022年经过12天
MAKETIME(10,21,23), -- 10:21:23 组合成时间
PERIOD_ADD(20200101010101,10) -- 20200101010111 加上10
FROM DUAL;
示例2：
查询7天内的新增用户有多少？
regist_time为用户注册时间。
SELECT COUNT(*) AS num
FROM new_user
WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7;
3.日期的格式化与解析
- 格式化：日期 ---> 字符串
- 解析： 字符串 ----> 日期
| 函数 | 小写 | 用法 |
|---|---|---|
| DATE_FORMAT(date,fmt) | date_format | 按照字符串fmt格式化日期date值 |
| TIME_FORMAT(time,fmt) | time_format | 按照字符串fmt格式化时间time值 |
| GET_FORMAT(date_type,format_type) | get_format | 返回日期字符串的显示格式 |
| STR_TO_DATE(str,fmt) | str_to_date | 按照字符串fmt对str进行解析，解析为一个日期 |
示例：
SELECT
DATE_FORMAT(NOW(),'%H:%i:%s'), -- 11:05:12
STR_TO_DATE('09/01/2009','%m/%d/%Y'), -- 2009-09-01
STR_TO_DATE('20140422154706','%Y%m%d%H%i%s'), -- 2014-04-22 15:47:06
GET_FORMAT(DATE, 'USA'), -- %m.%d.%Y
DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')), -- 08.12.2022
STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') -- 2020-01-01
FROM DUAL;
fmt参数常用的格式符
| fmt格式符 | 说明 | fmt格式符 | 说明 | fmt格式符 | 说明 |
|---|---|---|---|---|---|
| %Y | 4位数字表示年份 | %y | 两位数字表示年份 | ||
| %M | 月名表示月份（January,...） | %m | 两位数字表示月份（01,02，03...） | ||
| %b | 缩写的月名（Jan,Feb,...） | %c | 数字表示月份（1,2,3,...） | ||
| %D | 英文后缀表示月中的天数（1st,2nd,3rd,...） | %d | 两位数字表示月中的天数（01,02,...） | %e | 数字形式表示月中的天数（1,2,3,4,5...） |
| %H | 两位数字表示小时，24小时制（01,02,...24） | %h和%l | 两位数字表示小时，12小时制（01,02,...,12） | ||
| %k | 数字形式的小时，24小时制（1,2,3,...,24） | %l | 数字形式表示小时，12小时制（1,2,3,4...） | ||
| %i | 两位数字表示分钟（00,01,...,59） | %S和%s | 两位数字表示秒（00,01,...,59） | ||
| %W | 一周中的星期名称（Sunday...） | %a | 一周中的星期缩写（Sun,Mon,Tues,...） | %w | 以数字表示周中的天数（0=Sunday，1=Monday...） |
| %j | 以3位数字表示年中的天数（001,002,...） | %U | 以数字表示年中的第几周（1,2,3,...）其中Sunday为周中的第一天 | %u | 以数字表示年中的第几周，（1,2,3...）其中Monday为周中第一天 |
| %T | 24小时制 | %r | 12小时制 | ||
| %p | AM或PM | %% | 表示% |
GET_FORMAT函数中date_type和format_type参数取值：
| 日期类型 | 格式化类型 | 返回的格式化字符串 |
|---|---|---|
| DATE | USA | %m.%d.%Y |
| DATE | JIS | %Y-%m-%d |
| DATE | ISO | %Y-%m-%d |
| DATE | EUR | %d.%m.%Y |
| DATE | INTERNAL | %Y%M%d |
| TIME | USA | %h:%i:%s%p |
| TIME | JIS | %H:%i:%s |
| TIME | ISO | %H:%i:%s |
| TIME | EUR | %H.%i.%s |
| TIME | INTERNAL | %H%i%s |
| DATETIME | USA | %Y-%m-%d %H.%i.%s |
| DATETIME | JIS | %Y-%m-%d %H:%i:%s |
| DATETIME | ISO | %Y-%m-%d %H:%i:%s |
| DATETIME | EUR | %Y-%m-%d %H.%i.%s |
| DATETIME | INTERNAL | %Y%m%d%H%i%s |
