MySQL教程62-MySQL日期和时间函数
CURDATE和CURRENT_DATE函数:获取系统当前日期
MySQL 中 CURDATE() 和 CURRENT_DATE() 函数的作用相同,将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回,具体格式根据函数用在字符串或数字语境中而定。
【实例】使用日期函数 CURDATE 和 CURRENT_DATE 获取系统当前日期,输入的 SQL 语句和执行结果如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0; +------------+----------------+------------------+ | CURDATE() | CURRENT_DATE() | CURRENT_DATE()+0 | +------------+----------------+------------------+ | 2020-08-19 | 2020-08-19 | 20200819 | +------------+----------------+------------------+ 1 row in set (0.05 sec)
由运行结果可以看到,两个函数的作用相同,返回了相同的系统当前日期,“CURDATE()+0”将当前日期值转换为数值型的。
CURTIME和CURRENT_TIME函数:获取系统当前时间
MySQL 中 CURTIME() 和 CURRENT_TIME() 函数的作用相同,将当前时间以“HH:MM:SS”或“HHMMSS”格式返回,具体格式根据函数用在字符串或数字语境中而定。
【实例】使用时间函数 CURTIME 和 CURRENT_TIME 获取系统当前时间,输入的 SQL 语句和执行结果如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME()+0; +-----------+----------------+------------------+ | CURTIME() | CURRENT_TIME() | CURRENT_TIME()+0 | +-----------+----------------+------------------+ | 16:16:43 | 16:16:43 | 161643 | +-----------+----------------+------------------+ 1 row in set (0.01 sec)
由运行结果可以看出,两个函数返回的结果相同,都返回了当前的系统时间。CURRENT_TIME()+0 是将当前日期值转换为数值型的。
NOW和SYSDATE函数:获取当前时间日期
MySQL中 NOW() 和 SYSDATE() 函数的作用相同,都是返回当前日期和时间值,格式为“YYYY-MM-DD HH:MM:SS”或“YYYYMMDDHHMMSS”,具体格式根据函数用在字符串或数字语境中而定。
【实例 1】使用日期时间函数 NOW 和 SYSDATE 获取当前系统的日期和时间,输入的 SQL 语句和执行结果如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT NOW(),SYSDATE(); +---------------------+---------------------+ | NOW() | SYSDATE() | +---------------------+---------------------+ | 2020-08-19 16:17:25 | 2020-08-19 16:17:25 | +---------------------+---------------------+ 1 row in set (0.05 sec)
由运行结果可以看到,NOW 函数和 SYSDATE 函数返回的结果是相同的。
虽然在 MySQL 中 NOW() 和 SYSDATE() 都表示当前时间,但是 NOW() 取的是语句开始执行的时间,而 SYSDATE() 取的是语句执行过程中动态的实时时间。
【实例 2】先查询了 NOW() 和 SYSDATE(),然后 sleep 了 3 秒,再查询 NOW() 和 SYSDATE(),结果如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select now(),sysdate(),sleep(3),now(),sysdate(); +---------------------+---------------------+----------+---------------------+---------------------+ | now() | sysdate() | sleep(3) | now() | sysdate() | +---------------------+---------------------+----------+---------------------+---------------------+ | 2020-08-19 16:18:11 | 2020-08-19 16:18:11 | 0 | 2020-08-19 16:18:11 | 2020-08-19 16:18:14 | +---------------------+---------------------+----------+---------------------+---------------------+ 1 row in set (3.06 sec)
由运行结果可以看出,NOW() 函数始终获取的是 SQL 语句开始执行的时间,而 SYSDATE() 函数则是动态获取的实时时间。
UNIX_TIMESTAMP函数:获取UNIX时间戳
MySQL UNIX_TIMESTAMP(date) 若无参数调用,返回一个无符号整数类型的 UNIX 时间戳('1970-01-01 00:00:00'GMT之后的秒数)。
若用 date 来调用 UNIX_TIMESTAMP(),它会将参数值以'1970-01-01 00:00:00'GMT后的秒数的形式返回。
【实例】使用 UNIX_TIMESTAMP() 函数返回 UNIX 格式的时间戳,输入 SQL 语句和执行结果如下。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW(); +------------------+-----------------------+---------------------+ | UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW() | +------------------+-----------------------+---------------------+ | 1597886483 | 1597886483 | 2020-08-20 09:21:23 | +------------------+-----------------------+---------------------+ 1 row in set (0.00 sec)
FROM_UNIXTIME函数:时间戳转日期
MySQL FROM_UNIXTIME(date) 函数把 UNIX 时间戳转换为普通格式的日期时间值,与 UNIX_TIMESTAMP () 函数互为反函数。
【实例】使用 FROM_UNIXTIME(date) 函数将 UNIX 时间戳转换为普通格式时间。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT FROM_UNIXTIME(1597886480); +---------------------------+ | FROM_UNIXTIME(1597886480) | +---------------------------+ | 2020-08-20 09:21:20 | +---------------------------+ 1 row in set (0.00 sec)
MONTH函数:获取指定日期的月份
MySQL 中 MONTH(date) 函数返回指定 date 对应的月份,范围为 1~12。
【实例】使用 MONTH() 函数返回指定日期中的月份,输入的 SQL 语句和执行结果如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT MONTH('2020-08-20'), MONTH('2020-8-20'); +---------------------+--------------------+ | MONTH('2020-08-20') | MONTH('2020-8-20') | +---------------------+--------------------+ | 8 | 8 | +---------------------+--------------------+ 1 row in set (0.00 sec)
由运行结果可知,2020-08-20 对应的月份是 8。
MONTHNAME函数:获取指定日期月份的英文名称
MySQL 中 MONTHNAME(date) 函数返回日期 date 对应月份的英文全名。
【实例】使用 MONTHNAME() 函数返回指定日期中月份的名称,输入的 SQL 语句和执行结果如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT MONTHNAME('2020-08-20'); +-------------------------+ | MONTHNAME('2020-08-20') | +-------------------------+ | August | +-------------------------+ 1 row in set (0.00 sec)
DAYNAME函数:获取指定日期的星期名称
MySQL DAYNAME(date) 函数返回 date 对应的工作日英文名称,例如 Sunday、Monday 等。
【实例】使用 DAYNAME(date) 函数返回指定日期的工作日名称。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT DAYNAME('2020-08-20'), DAYNAME('2020-08-21'); +-----------------------+-----------------------+ | DAYNAME('2020-08-20') | DAYNAME('2020-08-21') | +-----------------------+-----------------------+ | Thursday | Friday | +-----------------------+-----------------------+ 1 row in set (0.00 sec)
由运行结果可知,2020-08-20 这一天是星期四,英文名称是 Thursday。
DAYOFWEEK函数:获取日期对应的周索引
MySQL 中 DAYOFWEEK(date) 函数返回 date 对应的一周中的索引(位置)。1 表示周日,2 表示周一,……,7 表示周六。这些索引值对应于ODBC标准。
【实例】使用 DAYOFWEEK() 函数返回日期对应的周索引,输入的 SQL 语句和执行结果如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT DAYOFWEEK('2020-08-20'), DAYOFWEEK('2020-08-21'); +-------------------------+-------------------------+ | DAYOFWEEK('2020-08-20') | DAYOFWEEK('2020-08-21') | +-------------------------+-------------------------+ | 5 | 6 | +-------------------------+-------------------------+ 1 row in set (0.00 sec)
WEEK函数:获取指定日期是一年中的第几周
通常,一年的正常年份为365
天,闰年为366
天。一年又可以分为许多周,每周有7
天。 所以一年,我们经常有365/7 = 52
周,周范围是从1
到52
。
要查看给定日期属于哪个周数,您可以使用WEEK
函数,如下所示:
WEEK(date, mode);
WEEK函数接受两个参数:
date
是要获取周数的日期。mode
是一个可选参数,用于确定周数计算的逻辑。它允许您指定本周是从星期一还是星期日开始,返回的周数应在0
到52
之间或0
到53
之间。
如果忽略mode
参数,默认情况下WEEK
函数将使用default_week_format
系统变量的值。
要获取default_week_format
变量的当前值,请使用SHOW VARIABLES
语句如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> show global variables like 'default_week_format'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | default_week_format | 0 | +---------------------+-------+ 1 row in set, 1 warning (0.04 sec)
在我们的服务器中,default_week_format
的默认值为0
,下表格说明了mode
参数如何影响WEEK
函数:
模式 | 一周的第一天 | 范围 |
---|---|---|
0 | 星期日 | 0-53 |
1 | 星期一 | 0-53 |
2 | 星期日 | 1-53 |
3 | 星期一 | 1-53 |
4 | 星期日 | 0-53 |
5 | 星期一 | 0-53 |
6 | 星期日 | 1-53 |
7 | 星期一 | 1-53 |
DAYOFYEAR函数:获取指定日期在一年中的位置
MySQL 中 DAYOFYEAR(d) 函数返回 d 是一年中的第几天,范围为 1~366。
【实例】使用 DAYOFYEAR() 函数返回指定日期在一年中的位置,输入的 SQL 语句和执行过程如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT DAYOFYEAR('2020-08-20'); +-------------------------+ | DAYOFYEAR('2020-08-20') | +-------------------------+ | 233 | +-------------------------+ 1 row in set (0.00 sec)
DAYOFMONTH函数:获取指定日期在一个月的位置
MySQL 中 DAYOFMONTH(d) 函数返回 d 是一个月中的第几天,范围为 1~31。
【实例】使用 DAYOFMONTH() 函数返回指定日期在一个月中的位置,输入的 SQL 语句和执行结果如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT DAYOFMONTH('2020-08-20'); +--------------------------+ | DAYOFMONTH('2020-08-20') | +--------------------------+ | 20 | +--------------------------+ 1 row in set (0.00 sec)
YEAR函数:获取年份
MySQL YEAR() 函数可以从指定日期值中来获取年份值。
YEAR() 函数需要接受 date 参数,并返回日期的年份。语法格式如下:
YEAR(date);
YEAR() 函数返回的年份值范围为 1000 到 9999,如果日期为零,YEAR() 函数返回 0。
【实例】使用 YEAR() 函数获取当前时间的年份,输入 SQL 语句和执行结果如下。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2020 | +-------------+ 1 row in set (0.00 sec)
TIME_TO_SEC函数:将时间转换为秒值
MySQL TIME_TO_SEC(time) 函数返回将参数 time 转换为秒数的时间值,转换公式为“小时 ×3600+ 分钟 ×60+ 秒”。
【实例】使用 TIME_TO_SEC(time) 函数将时间值转换为秒值。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select time_to_sec('2020-08-20 09:37:30'), time_to_sec('09:37:30'); +------------------------------------+-------------------------+ | time_to_sec('2020-08-20 09:37:30') | time_to_sec('09:37:30') | +------------------------------------+-------------------------+ | 34650 | 34650 | +------------------------------------+-------------------------+ 1 row in set (0.00 sec)
SEC_TO_TIME函数:将秒值转换为时间格式
MySQL SEC_TO_TIME(seconds) 函数返回将参数 seconds 转换为小时、分钟和秒数的时间值。
【实例】使用 SEC_TO_TIME(seconds) 函数将秒值转换为时间格式,输入 SQL 语句和执行结果如下。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT SEC_TO_TIME('34650'); +----------------------+ | SEC_TO_TIME('34650') | +----------------------+ | 09:37:30.000000 | +----------------------+ 1 row in set (0.00 sec)
结果为字符串型
DATE_ADD和ADDDATE函数:向日期添加指定时间间隔
MySQL DATE_ADD(date,INTERVAL expr type) 和 ADDDATE(date,INTERVAL expr type) 两个函数的作用相同,都是用于执行日期的加运算。
DATE_ADD() 和 ADDDATE() 函数有两个参数:
- date 是 DATE 或 DATETIME 的起始值。
- INTERVAL expr type是要添加到起始日期值的间隔值。
【实例 1】使用 DATE_ADD(date,INTERVAL expr type) 函数执行日期的加运算。输入 SQL 语句和执行结果如下。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT DATE_ADD('2020-08-20 09:37:30',INTERVAL 1 SECOND) AS C1, -> DATE_ADD('2020-8-20 09:37:30',INTERVAL '1:1' MINUTE_SECOND) AS C2, -> ADDDATE('2020-08-20 09:37:30',INTERVAL 1 SECOND) AS C3, -> ADDDATE('2020-08-20 09:37:30',INTERVAL '1:1' MINUTE_SECOND) AS C4; +---------------------+---------------------+---------------------+---------------------+ | C1 | C2 | C3 | C4 | +---------------------+---------------------+---------------------+---------------------+ | 2020-08-20 09:37:31 | 2020-08-20 09:38:31 | 2020-08-20 09:37:31 | 2020-08-20 09:38:31 | +---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)
由执行结果可以看出,DATE_ADD(date,INTERVAL expr type) 和 ADDDATE(date,INTERVAL expr type) 函数的功能完全相同,在原始时间 '2020-08-20 09:37:30' 上加 1 秒之后结果都是 '2020-08-20 09:37:31' ;在原始时间上加 1 分钟 1 秒的写法是表达式 '1:1',最终可得结果 '2020-08-20 09:38:31'。
DATE_SUB和SUBDATE函数:日期减法运算
MySQL DATE_SUB(date,INTERVAL expr type) 和 SUBDATE(date,INTERVAL expr type) 两个函数作用相同,都是执行日期的减法运算。
DATE_SUB() 和 SUBDATE() 函数接受两个参数:
- date 是 DATE 或 DATETIME 的起始值。
- expr 是一个字符串,用于确定从起始日期减去的间隔值。type 是 expr 可解析的间隔单位,例如 DAY,HOUR 等
【实例】使用 DATE_SUB(date,INTERVAL expr type) 和 SUBDATE(date,INTERVAL expr type) 函数执行日期的减运算,输入 SQL 语句与执行结果如下。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT DATE_SUB('2020-08-20',INTERVAL 31 DAY) AS C1, -> SUBDATE('2020-08-20',INTERVAL 31 DAY) AS C2, -> DATE_SUB('2020-08-20 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS C3; +------------+------------+---------------------+ | C1 | C2 | C3 | +------------+------------+---------------------+ | 2020-07-20 | 2020-07-20 | 2020-08-19 23:59:59 | +------------+------------+---------------------+ 1 row in set (0.00 sec)
由执行结果可以看出,DATE_SUB(date,INTERVAL expr type) 和SUBDATE (date,INTERVAL expr type) 函数的功能完全相同。
提示:DATE_ADD(date,INTERVAL expr type) 和 DATE_SUB(date,INTERVAL expr type) 函数在指定加减的时间段时也可以指定负值,加法的负值即返回原始时间之前的日期和时间,减法的负值即返回原始时间之后的日期和时间。
ADDTIME函数:时间加法运算
MySQL ADDTIME(time,expr) 函数用于执行时间的加法运算。添加 expr 到 time 并返回结果。
其中:time 是一个时间或日期时间表达式,expr 是一个时间表达式。
【实例】使用 ADDTIME() 函数进行时间的加法运算,输入 SQL 语句和返回结果如下。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT ADDTIME('2020-08-20 23:59:59','0:1:1'), -> ADDTIME('10:30:59','5:10:37'); +----------------------------------------+-------------------------------+ | ADDTIME('2020-08-20 23:59:59','0:1:1') | ADDTIME('10:30:59','5:10:37') | +----------------------------------------+-------------------------------+ | 2020-08-21 00:01:00 | 15:41:36 | +----------------------------------------+-------------------------------+ 1 row in set (0.00 sec)
由执行结果可以看出,在原始日期时间 '2020-08-20 23:59:59' 上加 0 小时 1 分 1 秒之后返回的日期时间是 '2020-08-21 00:01:00';在原始时间 '10:30:59' 上加 5 小时 10 分 37 秒之后返回的日期时间是 '15:41:36'。
SUBTIME函数:时间减法运算
MySQL SUBTIME(time,expr) 函数用于执行时间的减法运算。
其中:函数返回 time。expr 表示的值和格式 time 相同。time 是一个时间或日期时间表达式, expr 是一个时间。
操作和ADDTIME类似, 不赘述
DATEDIFF函数:获取两个日期的时间间隔
MySQL 中 DATEDIFF(date1,date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。计算时只用到这些值的日期部分。
【实例】使用 DATEDIFF() 函数计算两个日期之间的间隔天数,输入的 SQL 语句和执行结果如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select DATEDIFF('2020-08-20','2020-08-21 11:00:00'); +----------------------------------------------+ | DATEDIFF('2020-08-20','2020-08-21 11:00:00') | +----------------------------------------------+ | -1 | +----------------------------------------------+ 1 row in set (0.00 sec)
DATE_FORMAT函数:格式化指定的日期
MySQL 中 DATE_FORMAT(date,format) 函数是根据 format 指定的格式显示 date 值。
DATE_FORMAT() 函数接受两个参数:
- date:是要格式化的有效日期值
- format:是由预定义的说明符组成的格式字符串,每个说明符前面都有一个百分比字符(%)。
主要的 format 格式如下表所示。
说明符 | 说明 |
---|---|
%a | 工作日的缩写名称(Sun~Sat) |
%b | 月份的缩写名称(Jan…Dec) |
%c | 月份,数字形式(0~12) |
%D | 带有英语后缀的该月日期(0th, 2st, 3nd,…) |
%d | 该月日期,数字形式(00~31) |
%e | 该月日期,数字形式((0~31) |
%f | 微秒(000000 …999999) |
%H | 以 2 位数表示 24 小时(00~23) |
%h, %I | 以 2 位数表示 12 小时(01~12) |
%i | 分钟,数字形式(00~59) |
%j | —年中的天数(001~366) |
%k | 以 24 小时(0~23)表示 |
%l | 以12小时(1~12)表示 |
%M | 月份名称(January~December) |
%m | 月份,数字形式(00~12) |
%p | 上午(AM) 或下午(PM) |
%r | 时间,12小时制(小时 (hh): 分钟 (mm) : 秒数 (ss) 后加 AM 或 PM) |
%S, %s | 以 2 位数形式表示秒(00~59) |
%T | 时间,24 小时制(小时 (hh): 分钟 (mm): 秒数 (ss)) |
%U | 周(00~53),其中周日为每周的第一天 |
%u | 周(00~53),其中周一为每周的第一天 |
%V | 周(01~53),其中周日为每周的第一天,和%X同时使用 |
%v | 周(01~53),其中周一为每周的第一天,和%x同时使用 |
%W | 星期标识(周日、周一、周二…周六) |
%w | —周中的每日(0= 周日…6= 周六) |
%X | 该周的年份,其中周日为每周的第一天,数字形式,4 位数,和%V同时使用 |
%x | 该周的年份,其中周一为每周的第一天,数字形式,4位数,和%v同时使用 |
%Y | 4 位数形式表示年份 |
%y | 2 位数形式表示年份 |
%% | %一个文字字符 |
【实例】使用 DATE_FORMAT() 函数格式化输出日期和时间值,输入的 SQL 语句和执行结果如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT DATE_FORMAT('2020-08-20 10:45:00','%W %M %D %Y') AS col1, -> DATE_FORMAT('2020-08-20 10:45:00','%h:i% %p %M %D %Y') AS col2; +---------------------------+--------------------------+ | col1 | col2 | +---------------------------+--------------------------+ | Thursday August 20th 2020 | 10:i AM August 20th 2020 | +---------------------------+--------------------------+ 1 row in set (0.00 sec)
由运行结果可以看到,时间分别按照不同的参数已转换为不同格式的日期值和时间值。
WEEKDAY函数:获取指定日期在一周内的索引位置
MySQL 中 WEEKDAY(d) 返回 d 对应的工作日索引。0 表示周一,1 表示周二,……,6 表示周日。
【实例】使用 WEEKDAY() 函数返回日期对应的工作日索引,输入的 SQL 语句和执行结果如下所示。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> SELECT WEEKDAY('2020-08-20'), WEEKDAY('2020-08-21'); +-----------------------+-----------------------+ | WEEKDAY('2020-08-20') | WEEKDAY('2020-08-21') | +-----------------------+-----------------------+ | 3 | 4 | +-----------------------+-----------------------+ 1 row in set (0.00 sec)
由运行结果可以看出,WEEKDAY() 和 DAYOFWEEK() 函数都是返回指定日期在某一周内的位置,只是索引编号不同。