【Mysql】学习记录·日期

日期说明

日期和时间函数主要用来处理日期和时间值，一般的日期函数除了使用DATE类型的参数外，也可以使用DATESTAMP类型或者TIMESTAMP类型的参数，但是会忽略这些值的时间部分。相同的，以TIME类型值为参数的函数，可以接受TIMESTAMP类型的参数，但是会忽略日期部分。许多日期函数可以同时接收数和字符串这两种参数。

---

获取当前日期的函数和获取当前时间的函数

CURDATE()函数和CURRENT_DATE()函数的作用相同，将当前日期按照按照YYYY-MM-DD或YYYYMMDD格式返回。
CURTIME()函数和CURRENT_TIME()函数的作用相同，将当前时间以HH:MM:SS或HHMMSS格式返回。
CURDATE()+0 将当前日期值转换为数值型。
案例： 使用日期函数获取系统当前日期；使用时间函数获取当前时间。
命令语句：

select CURDATE(), CURRENT_DATE(), CURDATE()+0;

执行结果：

命令语句：

select CURTIME(),CURRENT_TIME(),CURTIME()+0;

执行结果：

---

获取当前日期和时间的函数

CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE()四个函数的作用相同。均返回当前日期和时间值，格式为YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS。
案例： 使用日期函数获取当前系统日期和时间。
命令语句：

select CURRENT_TIMESTAMP(), LOCALTIME(), NOW(), SYSDATE();

执行结果：

---

UNIX时间戳函数

UNIX_TIMESTAMP()函数以UNIX时间戳函数的形式返回当前时间；UNIX_TIMESTAMP(d)函数将时间d以UNIX时间戳的形式返回。
FROM_UNIXTIME(date)函数把UNIX时间戳函数转换为普通格式的时间，与UNIX_TIMESTAMP(date)函数互为反函数。
案例： 使用UNIX_TIMESTAMP()函数返回UNIX格式的时间戳；使用FROM_UNIXTIME()函数将UNIX时间戳函数转换为普通格式时间。
命令语句：

select UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), NOW(), FROM_UNIXTIME(1534058518);

执行结果：

---

返回UTC日期的函数和返回UTC时间的函数

UTC_DATE()函数返回当前UTC（世界标准时间）日期值，其格式为YYYY-MM-DD或YYYYMMDD，具体格式取决于函数是用在字符串还是数字语境中。
UTC_TIME()返回当前的UTC时间值，其格式为HH:MM:SS或HHMMSS，具体格式取决函数是在字符串还是数字语境中。
案例： 使用UTC_DATE()函数返回当前UTC日期值；使用UTC_TIME返回当前的UTC时间值。
命令语句：

select UTC_DATE(), UTC_DATE()+0;

执行结果：

命令语句：

select UTC_TIME(), UTC_TIME()+0;

执行结果：

---

获取月份函数MONTH(date)和MONTHNAME(date)

MONTH(date)函数返回date对应月份，范围1—12。
MONTHNAME(date)函数返回date对应月份的英文全名。
案例： 使用MONTH()函数指定日期中月份；使用MONTHNAME()函数返回指定日期中的月份名称。
命令语句：

select MONTH('2020-02-01'), MONTH('2023-02-01');

执行结果：

命令语句：

select MONTHNAME('2023-02-01'), MONTHNAME('2023-02-01');

执行结果：

---

获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)

DAYNAME(d)函数返回d对应法人工作日的英文名称，如Sunday、Monday等。
DAYOFWEEK(d)函数是返回d对应的一周中的索引（位置）。1表示周日，2表示周一…7表示周六。
案例： 使用DAYNAME()函数返回指定日期的星期数；使用DAYOFWEEK()函数返回当前日期对应的周索引
命令语句：

select DAYNAME('2023-02-01'), DAYOFWEEK('2023-02-01'), DAYOFWEEK('2023-02-01');

执行结果：

由于2023年02月01日为周三，因此WEEKDAY()返回的结果是Wednesday 而DAYOFWEEK()返回的索引值是1。可以看到，WEEKDAY()函数和DAYOFWEEK()函数都是返回指定日期在某一周内的位置，只是索引编号不同。

---

获取星期数的函数WEEK(d)和WEEKOFYEAR(d)

WEEK(d)函数用于计算日期d是一年中的第几周。WEEK()的双参数形式允许指定该星期是否起始于周日或周一，以及返回值是否为0-53或1-53.若Mode参数被省略，怎使用default_week_format系统自变量的值，可参考下表：

Mode	一周的第一天	范围	第一周计算方式
0	周日	0~53	遇到本年的第一个星期天开始，是第一周
1	周一	0~53	若第一周能超过三天，则计算为本年的第一周
2	周日	1~53	遇到本年的第一个星期天开始，是第一周
3	周一	1~53	若第一周能超过三天，则计算为本年的第一周
4	周日	0~53	若第一周能超过三天，则计算为本年的第一周
5	周一	0~53	遇到本年的第一个星期天开始，是第一周
6	周日	1~53	若第一周能超过三天，则计算为本年的第一周
7	周一	1~53	遇到本年的第一个星期天开始，是第一周

WEEKOFYEAR(d)计算某天位与一年中的第几周，范围是1-53，相当于WEEK(d,3)
案例： 使用WEEK()和WEEKOFYEAR()函数查询指定日期是一年中的第几周
命令语句：

select WEEK('2023-02-01'), WEEK('2023-02-01',0), WEEK('2023-02-01',1), WEEK('2023-02-01',3), WEEKOFYEAR('2023-02-01');

执行结果：

可以看到，WEEK('2023-02-01')使用了一个参数，第二个参数为默认值default_week_format，MySQL中该值默认为0，指定一周的第一天为周日，因此和WEEK('2023-02-01',0)的返回结果相同；另外WEEK('2023-02-01',1)和WEEK('2023-02-01',3)结果相同，说明2023年1月1日是个周日或周一，一周没有过3天，所以这几个参数结果都说明这周是第5周。

---

获取天数的函数DAYOFYEAR(d)和DAYOFMONTH(d)

DAYOFYEAR(d)函数返回d是一年中的第几天，范围是1-366。
DAYOFMONTH(d)函数返回d是一个月中的第几天，范围是1-31。
案例： 使用DAYOFYEAR()函数返回指定日期在一年中的位置；使用DAYOFMONTH()函数返回指定日期在一个月中的位置
命令语句：

select DAYOFYEAR('2023-02-01'), DAYOFMONTH('2023-02-01');

执行结果：

---

获取年份、季度、小时、分钟和秒钟的函数

YEAR(date)函数返回date对应的年份，范围是1970-2069。
QUARTER(date)函数返回date对应的一年中的季度值，范围1-4。
HOUR(time)函数返回time对应的时钟数，范围是0-23。
MINUTE(time)函数返回time对应的分钟数，范围是0-59。
SECOND(time)函数返回time对应的秒钟数，范围是0-59。
案例： 使用YEAR()函数返回指定日期对应的年份；使用QUARTER()函数返回指定日期对应的季度，使用HOUR()函数返回指定时间对应的时钟值，使用MINUTE()函数返回指定时间对应的分钟值，使用SECOND()函数返回指定时间对应的秒钟值
命令语句：

select YEAR('2023-02-01'), QUARTER('2023-02-01');

执行结果：

命令语句：

select HOUR('2023-02-01 16:50:30'), MINUTE('2023-02-01 16:50:30'), SECOND('2023-02-01 16:50:30');

执行结果：

---

获取日期指定值的函数EXTRACT(type FROM date)

EXTRACT(type FROM date)函数所使用的时间间隔类型说明符同DATE_ADD()或DATE_SUB()的相同，但它是从日期中提取一部分，而不是执行日期运算。
案例： 使用EXTRACT()函数提取日期值或时间值
命令语句：

select EXTRACT(YEAR FROM '2023-02-01') as col1, EXTRACT(YEAR_MONTH FROM '2023-02-01') as col2, EXTRACT(DAY_MINUTE FROM '2023-02-01 17:01:10') as col3;

执行结果：

Type值为YEAR时只返回年值，结果为2023；type值为YEAR_MONTH时返回年月值，结果为202302；type值为DAY_MINUTE时返回日、小时和分钟值，结果为11701。

---

时间和秒钟转换函数

TIME_TO_SEC(time)函数返回已转换为秒的time参数。转换公式为小时*3600+分钟*60+秒。
SEC_TO_TIME(seconds)函数返回时间，格式为HH:MM:SS或HHMMSS。
案例： 使用TIME_TO_SEC()函数将时间转换为秒值；使用SEC_TO_TIME()函数将秒数转换为时间格式
命令语句：

select TIME_TO_SEC('2023-02-01 17:21:50');

执行结果：

命令语句：

select SEC_TO_TIME('62510');

执行结果：

---

计算日期和时间的函数

计算日期时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()、SUBTIME()和DATE_DIFF()。

DATE_ADD(date,IINTERVAL expr type)和DATE_SUB(date,IINTERVAL expr type)

在DATE_ADD(date,IINTERVAL expr type)和DATE_SUB(date,IINTERVAL expr type)中，date是一个DATETIME或DATE值，用来指定起始时间；expr是一个表达式，用来指定从起始日期，开始添加或减去的时间间隔值，expr也是一个字符串，对于赋值的时间间隔，可以以一个负号开头；type为关键词，它表明了表达式被解释的方式。下表列出了type和expr参数的关系。

Type 值	预期的 expr格式
Microseconde	microseconds
second	seconds
minute	minutes
houre	hours
day	days
week	weeks
month	months
quarter	quarters
year	years
second_microsecond	seconds.microseconds
minute_microsecond	minutes.microseconds
minute_second	minutes.seconds
hour_microsecond	hours.microseconds
hour_second	hours:minutes:seconds
hour_minute	hours:minutes
day_microsecond	days.microseconds
day_second	days hours:minutes:seconds
day_minute	davs hours:minutes
day_hour	days hours
year_month	years-months

若date参数是一个date值，计算时只会包括YEAR、MONTH和DAY部分(即没有时间部分)，其结果是一个DATE值。否则，结果将是一个DATETIME值。

日期加：DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type)

DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type)两个函数的作用相同，都是执行日期的加运算。
案例： 使用DATE_ADD()和ADDDATE()函数执行日期加操作
命令语句：

select DATE_ADD('2023-02-01 23:59:59', INTERVAL 2 SECOND) AS col1, ADDDATE('2023-02-01 23:59:59', INTERVAL 2 SECOND) AS col2, DATE_ADD('2023-02-01 23:59:59', INTERVAL '1:1' MINUTE_SECOND) AS col3;

执行结果：

日期减：DATE_SUB(date,INTERVAL expr type)和SUBDATE(date,INTERVAL expr type)

案例： 使用DATE_SUB()和SUBDATE()函数执行日期减操作
命令语句：

select DATE_SUB('2023-01-01 00:00:01', INTERVAL 2 SECOND) AS col1, SUBDATE('2023-01-01 00:00:01', INTERVAL 2 SECOND) AS col2, DATE_SUB('2023-01-01 00:00:01', INTERVAL '0 0:1:1' DAY_SECOND) AS col3;

执行结果：

日期加：ADDTIME(date expr)函数

ADDTIME(date expr)函数将expr值添加到date，并返回修改后的值，date是一个日期或者日期表达式，而expr是一个时间表达式。
案例： 使用ADDTIME()函数进行时间加操作
命令语句：

select ADDTIME('2023-12-31 23:59:59', '1:1:1'), ADDTIME('01:01:01', '01:01:01');

执行结果：

日期减：SUBTIME(date expr)函数

SUBTIME(date expr)函数将expr值减去到date，并返回修改后的值，date是一个日期或者日期表达式，而expr是一个时间表达式。
案例： 使用SUBTIME()函数进行时间减操作
命令语句：

select SUBTIME('2023-12-31 23:59:59', '1:1:1'), SUBTIME('01:01:01', '01:01:01');

执行结果：

日期差：DATEDIFF(date1,date2)函数

DATEDIFF(date1,date2)函数返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或者date-and-time表达式，计算中只用到这些值的日期部分。
案例： 使用DATEDIFF()函数计算两个日期之间间隔的天数
命令语句：

select DATEDIFF('2023-12-31 23:59:59', '2023-01-01'), DATEDIFF('2023-12-31 23:59:59', '2024-01-01');

执行结果：

---

格式化日期和时间的函数

DATE_FORMAT(date,format)函数

DATE_FORMAT(date,format)函数根据format指定的格式显示date值。
主要的format格式如下表所示：

说明符	说明
%Y	4位数形式的年份
%y	2位数形式的年份
%M	月份名称(January-December)
%b	月份的缩写名称(Jan-Dec)
%c	月份，数字形式(0-12)
%m	月份，数字形式(00-12)
%a	星期几的缩写名称(Sum-Sat)
%W	工作日名称(周日 - 周六)
%w	一周中的每日(0=周日 - 6=周六)
%D	带有英语后缀的该月日期 (0th，1st，2nd，3rd…)
%d	该月日期，数字形式(00-31)
%e	该月日期，数字形式(0-31)
%p	上午(AM)或下午(PM)
%H	以2位数表示24小时(00-23)
%h,%I	以2位数表示12小时(01-12)
%k	以24小时制表示(0-23)
%l	以12小时制表示(1-12)
%i	分钟，数字形式(00-59)
%S,%s	以2位数形式表示秒(00-59)
%f	微秒(000000-999999)
%j	一年中的天数(001-366)
%r	时间，12小时制(hh:mm:ss AM/PM)
%T	时间，24小时制 (HH:mm:ss)
%U	周(00-53)，其中周日为每周第一天
%u	周(00-53)，其中周一为每周第一天
%V	周(01-53)，周日为每周第一天；配合 %X 使用
%v	周(01-53)，周一为每周第一天；配合 %x 使用
%X	该周的年份，其中周日为每周第一天；配合 %V 使用
%x	该周的年份，其中周一为每周第一天；配合 %v 使用

案例： 使用DATE_FORMAT()函数格式化输出日期和时间值
命令语句：

select DATE_FORMAT('2023-01-01 10:10:10', '%W %M %Y') AS col1, DATE_FORMAT('2023-01-01 10:10:10', '%D %y %a %b %m %b %j') AS col2, DATE_FORMAT('2023-01-01 10:10:10', '%H %i %s') AS col3, DATE_FORMAT('2023-01-01 10:10:10', '%X %V') AS col4;

执行结果：

TIME_FORMAT(time,format)函数

TIME_FORMAT(time,format)函数根据format字符串安排time值的格式。若time值包含一个大于23的小时部分，%H和%K小时格式说明会产生一个超出0—23的通常范围的值。
案例： 使用TIME_FORMAT()函数格式化输出时间值
命令语句：

select TIME_FORMAT('20:00:00', '%H %k %h %I %l');

执行结果：

GET_FORMAT(val_type,format_type)函数

GET_FORMAT(val_type,format_type)函数返回日期时间字符串的显示格式，val_type表示日期数据类型，包括DATE、DATETIME和TIME；format_type表示格式化显示类型，包括EUR、INTERVAL、ISO、JIS、USA。
GET_FORMAT根据两个值类型组合返回的字符串显示格式，如下表：

值类型	格式化类型	显示格式字符串
DATE	EUR	%d.%m.%Y
DATE	INTERVAL	%Y%m%d
DATE	ISO	%Y-%m-%d
DATE	JIS	%Y-%m-%d
DATE	USA	%m.%d.%Y
TIME	EUR	%H.%i.%s
TIME	INTERVAL	%H%i%s
TIME	ISO	%H:%i:%s
TIME	JIS	%H:%i:%s
TIME	USA	%H:%i:%s %p
DATETIME	EUR	%Y-%m-%d %H.%i.%s
DATETIME	INTERVAL	%Y%m%d%H%i%s
DATETIME	ISO	%Y-%m-%d %H:%i:%s
DATETIME	JIS	%Y-%m-%d %H:%i:%s
DATETIME	USA	%Y-%m-%d %H.%i.%s

案例： 使用GET_FORMAT()函数显示不同格式化下的格式字符串，SQL语句如下：
命令语句：

select GET_FORMAT(DATE, 'EUR'), GET_FORMAT(DATE, 'USA'), DATE_FORMAT('2023-02-01 20:50:10', GET_FORMAT(DATE,'USA'));

执行结果：

---

相关引用：

1. 知乎 - MySQL应用总结(十三)—函数的操作(3):日期时间函数