MySQL第六章(单行函数之日期和时间函数) 详细

一.获取时间和日期

函数	功能
CURDATE() / CURRENT_DATE()	返回当前日期, 只包含年月日
CURTIME() / CURRENT_TIME()	返回当前时间, 只包含时分秒
NOW()	返回当前系统日期和时间
UTC_DATE()	返回世界标准日期
UTC_TIME()	返回世界标准时间

SELECT CURDATE(),CURTIME(),NOW()
FROM DUAL 
/*
CURDATE() |CURTIME()|NOW()              |
----------+---------+-------------------+
2022-09-24| 21:52:14|2022-09-24 21:52:14|
 */

SELECT UTC_DATE(), UTC_TIME()
FROM DUAL
/*
UTC_DATE()|UTC_TIME()|
----------+----------+
2022-09-24|  13:54:24|
 */

二.日期与时间戳

函数	功能
UNIX_TIMESTAMP()	以时间戳的形式返回当前时间
UNIX_TIMESTAMP(date)	将时间date以时间戳的形式返回
FROM_UNIXTIME(timestamp)	将时间戳的时间转换为普通格式的时间

SELECT UNIX_TIMESTAMP(), FROM_UNIXTIME(1664028458),
UNIX_TIMESTAMP(NOW()),FROM_UNIXTIME(1664029021)
FROM DUAL
/*
UNIX_TIMESTAMP()|FROM_UNIXTIME(1664028458)|UNIX_TIMESTAMP(NOW())|FROM_UNIXTIME(1664029021)|
----------------+-------------------------+---------------------+-------------------------+
      1664028458|      2022-09-24 22:07:38|           1664029033|      2022-09-24 22:17:01|
*/

三.获取月份, 星期, 星期数, 天数等函数

函数	功能
YEAR(date) / MONTH(date) / DAY(date)	返回具体的日期值
HOUR(TIME) / MINUTE(time) / SECOND(time)	返回具体的时间值
MONTHNAME(date)	返回月份
DAYNAME(date)	返回星期几
WEEKDAY(date)	返回周几, 注意周一是0, 周二是1…周日是6
QUARTER(date)	返回日期对应的季度, 1,2,3,4
WEEK(date) / WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几, 注意周日是1,周一是2…周六是7

四.日期的操作函数

函数	功能
EXTRACT(type FROM date)	返回指定日期中特定的部分, type指定返回的值

type取值	功能
MICROSECOND	返回毫秒数
SECOND	返回秒数
MINUTE	返回分钟数
HOUR	返回小时数
DAY	返回天数
WEEK	返回日期在一年中的第几个星期
MONTH	返回日期在一年中的第几个月
QUARTER	返回日期在一年中的第几个季度
YEAR	返回日期的年份
SECOND_MICROSECOND	返回秒和毫秒值
MINUTE_MICROSECOND	返回分钟和毫秒值
MINUTE_SECOND	返回分钟和秒值
HOUR_MICROSECOND	返回小时和毫秒值
HOUR_SECOND	返回小时和秒值
HOUR_MINUTE	返回小时和分钟值
DAY_MICROSECOND	返回天和毫秒值

五.时间和秒钟的转换函数

函数	功能
TIME_TO_SEC(time)	将time转换为秒并返回结果值
SEC_TO_TIME(seconds)	讲seconds描述转换为包含小时,分钟和秒的时间

转换公式为: 小时x 3600+分钟 x 60+秒

SELECT TIME_TO_SEC(curtime()),SEC_TO_TIME(41644)
FROM DUAL 
/*
 TIME_TO_SEC(curtime())|SEC_TO_TIME(41644)|
----------------------+------------------+
                 41686|          11:34:04|
 */

六.计算日期和时间的函数

第一组

函数	功能
DATE_ADD(datetime, INTERVAL expr type) / ADDDATE(datetime, INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date, INTERVAL expr type), SUBDATE(date, INTERVAL expr type)	返回与date相差INTERVAL时间间隔的日期

type的取值	含义
HOUR	小时
MINUTE	分钟
SECOND	秒
YEAR	年
MONTH	月
DAY	日
YEAR_MONTH	年和月
DAY_HOUR	日和小时
DAY_MINUTE	日和分钟
DAY_SECOND	日和秒
HOUR_MINUTE	小时和分钟
HOUR_SECOND	小时和秒
MINUTE_SECOND	分钟和秒

SELECT NOW(), DATE_ADD(NOW(), INTERVAL 1 YEAR), #增加一年
DATE_ADD(NOW(), INTERVAL -1 YEAR), #减一年
DATE_SUB(NOW(), INTERVAL 1 YEAR) #减一年
FROM DUAL

SELECT NOW(), ADDDATE('2022-09-25 14:39:23', INTERVAL 1 DAY) AS ADDDAY,
DATE_ADD(NOW(), INTERVAL '1_1' DAY_HOUR) AS ADDHOUR
FROM DUAL
/*
 NOW()              |ADDDAY             |ADDHOUR            |
-------------------+-------------------+-------------------+
2022-09-25 14:53:49|2022-09-26 14:39:23|2022-09-26 15:53:49|
 */

第二组

函数	功能
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)	将给定的小时,分钟,秒组合成时间返回
PERIO_ADD(time,n)	返回time加上n后的时间

七.日期的格式化与解析

格式化: 日期----->字符串
解 析: 字符串----->日期

这里我们谈的是日期的显示格式化和解析
显示: 不用和原数据的数据与格式一模一样 (如: ‘2022-09-25’与’2022_09_25’)
隐式: 必须和原数据的数据与格式一模一样 (如: ‘2022-09-25’与’2022-09-25’)

函数	功能
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(date,fmt)	按照字符串fmt格式化时间date值
STR_TO_DATE(str,fmt)	按照字符串fmt对str进行解析,解析为一个日期

格式符(fmt)	功能	格式符	功能
%Y	4位数字表示年份	%y	2位数字表示年份
%M	用英文月名表示月份(January…)	%m	2位数字表示月份(01, 02…)
%b	缩写的月名(Jan, Feb…)	%c	数字表示月份(1,2,3,…)
%D	英文后缀表示月中的天数(1st,2nd,3rd)	%d	2位数字表示月中的天数(01,02…)
%e	1位数字表示月中的天数(1,2,3…)
%H	2位数字表示小时,24小时制	%h	2位数字表示小时,12小时制
%k	1位数字表示小时,24小时制	%l	1位数字表示小时,12小时制
%i	2位数字表示分钟	%S / %s	2位数字表示秒
%W	一周中的英文名称	%w	数字表示星期,(0周日, 1周一)
%a	一周中的星期缩写(Sun, Mon…)	%j	以三位数表示年中的天数(001,002)
%U	以数字表示年中的第几周,(1,2,3),其中Sunday为周中第一天	%u	以数字表示年中的第几周,(1,2,3),其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),
DATE_FORMAT(CURDATE(),'%Y-%m-%d')
FROM DUAL 
/*
 DATE_FORMAT(CURDATE(),'%Y-%M-%D')|DATE_FORMAT(CURDATE(),'%Y-%m-%d')|
---------------------------------+---------------------------------+
2022-September-25th              |2022-09-25                       |
 */

SELECT STR_TO_DATE('2022-9-25 16:53:12','%Y-%m-%d %H:%i:%s') AS atd
FROM DUAL 
#注意前后一一对应

函数	功能
GET_FORMAT(date_type,format_type)	返回日期字符串的显示格式

日期类型(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	INTTERNAL	%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

SELECT GET_FORMAT(DATE, 'USA')
FROM DUAL 
/*
 GET_FORMAT(DATE, 'USA')|
-----------------------+
%m.%d.%Y               |
 */


SELECT DATE_FORMAT(CURDATE(), GET_FORMAT(DATE, 'USA'))
FROM DUAL
/*
 DATE_FORMAT(CURDATE(), GET_FORMAT(DATE, 'USA'))|
-----------------------------------------------+
09.25.2022                                     |
 */