[MySQL]日期和时间函数
文章目录
- 1 获取日期、时间
- CURDATE() ，CURRENT_DATE()
- CURTIME() ， CURRENT_TIME()
- NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()
- UTC_DATE()
- UTC_TIME()
- 代码示例
- 2 日期与时间戳的转换
- UNIX_TIMESTAMP()
- UNIX_TIMESTAMP(date)
- FROM_UNIXTIME(timestamp)
- 代码示例
- 3 获取月份、星期、星期数、天数等函数
- YEAR(date) / MONTH(date) / DAY(date)
- HOUR(time) / MINUTE(time) / SECOND(time)
- MONTHNAME(date)
- DAYNAME(date)
- WEEKDAY(date)
- QUARTER(date)
- WEEK(date) ， WEEKOFYEAR(date)
- DAYOFYEAR(date)
- DAYOFMONTH(date)
- DAYOFWEEK(date)
- 代码示例
- 4 日期的操作函数
- EXTRACT(type FROM date)
- 5 时间和秒钟转换的函数
- TIME_TO_SEC(time)
- SEC_TO_TIME(seconds)
- 代码示例
- 6 计算日期和时间的函数
- DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)
- DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)
- ADDTIME(time1,time2)
- SUBTIME(time1,time2)
- DATEDIFF(date1,date2)
- TIMEDIFF(time1, time2)
- FROM_DAYS(N)
- TO_DAYS(date)
- LAST_DAY(date)
- MAKEDATE(year,n)
- MAKETIME(hour,minute,second)
- PERIOD_ADD(time,n)
- 代码示例
- 7 日期的格式化与解析
- DATE_FORMAT(date,fmt)
- TIME_FORMAT(time,fmt)
- GET_FORMAT(date_type,format_type)
- STR_TO_DATE(str, fmt)
- 非GET_FORMAT 函数中fmt参数常用的格式符：
- GET_FORMAT函数中date_type和format_type参数取值如下：
- 代码示例
1 获取日期、时间
CURDATE() ，CURRENT_DATE()
返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME()
返回当前时间，只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()
返回当前系统日期和时间
UTC_DATE()
返回UTC（世界标准时间）日期
UTC_TIME()
返回UTC（世界标准时间）时间
代码示例
SELECTCURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),UTC_DATE(),UTC_TIME()
FROM DUAL;
2 日期与时间戳的转换
UNIX_TIMESTAMP()
以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884
UNIX_TIMESTAMP(date)
将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp)
将UNIX时间戳的时间转换为普通格式的时间
代码示例
SELECTUNIX_TIMESTAMP(),UNIX_TIMESTAMP('2021-10-01 12:12:32'),FROM_UNIXTIME(1635173853),FROM_UNIXTIME(1633061552)
FROM DUAL;
3 获取月份、星期、星期数、天数等函数
YEAR(date) / MONTH(date) / DAY(date)
返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time)
返回具体的时间值
MONTHNAME(date)
返回月份：January，…
DAYNAME(date)
返回星期几：MONDAY，TUESDAY…SUNDAY
WEEKDAY(date)
返回周几，注意，周1是0，周2是1，…，周日是6
QUARTER(date)
返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date)
返回一年中的第几周
DAYOFYEAR(date)
返回日期是一年中的第几天
DAYOFMONTH(date)
返回日期位于所在月份的第几天
DAYOFWEEK(date)
返回周几，注意：周日是1，周一是2，…，周六是7
代码示例
SELECTYEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()),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 日期的操作函数
EXTRACT(type FROM date)
返回指定日期中特定的部分，type指定返回的值
SELECTEXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2021-05-12')
FROM DUAL;
5 时间和秒钟转换的函数
TIME_TO_SEC(time)
将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟 *60+秒
SEC_TO_TIME(seconds)
将 seconds 描述转化为包含小时、分钟和秒的时间
代码示例
SELECTTIME_TO_SEC(CURTIME()),SEC_TO_TIME(53515)
FROM DUAL;
6 计算日期和时间的函数
DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)
返回与给定日期时间相差INTERVAL时间段的日期时间
type的取值：
SELECTNOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),DATE_ADD(NOW(),INTERVAL -1 YEAR)
FROM DUAL;
SELECTDATE_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;
DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)
返回与date相差INTERVAL时间间隔的日期
type的取值：
SELECTNOW(),DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL;
ADDTIME(time1,time2)
返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数
SUBTIME(time1,time2)
返回time1减去time2后的时间。当time2为一个数字时，代表的是 秒 ，可以为负数
DATEDIFF(date1,date2)
返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2)
返回time1 - time2的时间间隔
FROM_DAYS(N)
返回从0000年1月1日起，N天以后的日期
TO_DAYS(date)
返回日期date距离0000年1月1日的天数
LAST_DAY(date)
返回date所在月份的最后一天的日期
MAKEDATE(year,n)
针对给定年份与所在年份中的天数返回一个日期，即指定年份的第几天
MAKETIME(hour,minute,second)
将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n)
返回time加上n后的时间
代码示例
SELECTADDTIME(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()),32),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;`在这里插入代码片`
SELECT# 日期转化为数值CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 0
FROM DUAL;
7 日期的格式化与解析
格式化：日期 —> 字符串
解析： 字符串 —> 日期
这里指的是日期的显式格式化和解析
DATE_FORMAT(date,fmt)
按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)
按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)
返回日期字符串的显示格式
既不是格式化也不是解析，用于得到日期的格式，如：
'%Y-%M-%D'
STR_TO_DATE(str, fmt)
按照字符串fmt对str进行解析，解析为一个日期
非GET_FORMAT 函数中fmt参数常用的格式符：
GET_FORMAT函数中date_type和format_type参数取值如下：
代码示例
#格式化：
SELECTDATE_FORMAT(CURDATE(),'%Y-%M-%D'),DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%h:%i:%S'),DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r')
FROM DUAL;
#解析：格式化的逆过程
SELECTSTR_TO_DATE('2021-October-25th 11:37:30 Monday 1','%Y-%M-%D %h:%i:%S %W %w'),GET_FORMAT(DATE,'USA'),DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;
