玖叶教程网

前端编程开发入门

2024年04月16日

《MySQL 入门教程》第16篇MySQL常用函数之日期函数

原文地址：https://blog.csdn.net/horses/article/details/107563145

原文作者：不剪发的Tony老师

来源平台：CSDN

上一篇介绍了 MySQL 中常用的字符函数，本篇我们继续讨论常用的 MySQL 日期函数。

日期函数和运算符用于日期和时间类型的数据进行操作并返回一个结果。以下是 MySQL 中常见的日期函数和运算符：


下面我们通过一些示例来说明这些函数的作用。

16.1 获取当前日期和时间

CURDATE()、CURRENT_DATE() 和 CURRENT_DATE 函数用于返回当前时间，返回格式为日期类型的 ‘YYYY-MM-DD’ 或者整数类型的 YYYYMMDD 。例如：

select curdate(), current_date + 0;
curdate() |current_date + 0|
----------|----------------|
2020-07-26|        20200726|

CURRENT_TIME、CURRENT_TIME([fsp]) 和 CURTIME([fsp]) 函数用于返回当前时间，返回格式为时间类型的 ‘hh:mm:ss’ 或者整数类型的 hhmmss。例如：

select curtime(), current_time + 0;
curtime()|current_time + 0|
---------|----------------|
 20:59:57|          205957|

参数 fsp 用于指定小数秒的精度，取值为 0 到 6。例如：

select current_time(6);
current_time(6)|
---------------|
21:00:15.432279|

NOW([fsp]) 函数用于返回当前日期时间，返回格式为日期时间类型的 ‘YYYY-MM-DD hh:mm:ss’ 或者整数类型的 YYYYMMDDhhmmss；CURRENT_TIMESTAMP([fsp])、CURRENT_TIMESTAMP、LOCALTIME([fsp])、LOCALTIME、LOCALTIMESTAMP([fsp])、LOCALTIMESTAMP 都是 Now() 函数的同义词。例如：

select now(), current_timestamp(6), localtime, localtimestamp(6);
now()              |current_timestamp(6)      |localtime          |localtimestamp(6)         |
-------------------|--------------------------|-------------------|--------------------------|
2020-07-26 21:01:03|2020-07-26 21:01:03.369695|2020-07-26 21:01:03|2020-07-26 21:01:03.369695|

SYSDATE([fsp]) 函数用于返回函数执行时的日期时间，返回格式为日期时间类型的 ‘YYYY-MM-DD hh:mm:ss’ 或者整数类型的 YYYYMMDDhhmmss。例如：

select sysdate(), sysdate(6);
sysdate()          |sysdate(6)                |
-------------------|--------------------------|
2020-07-26 21:08:53|2020-07-26 21:08:53.322645|

需要注意的是，NOW() 函数返回的是语句开始执行的时间，对于存储函数或者触发器返回的是函数执行或者触发语句执行的时间；SYSDATE() 函数返回的是该函数被执行的时间，同一个语句中多次调用可能返回不同的值。例如：

select now(), sysdate(), sleep(6), now(), sysdate();
now()              |sysdate()          |sleep(6)|now()              |sysdate()          |
-------------------|-------------------|--------|-------------------|-------------------|
2020-07-26 21:11:50|2020-07-26 21:11:50|       0|2020-07-26 21:11:50|2020-07-26 21:11:56|

从结果可以看出，两个 NOW() 函数返回了相同的时间；但是两个 SYSDATE() 函数的结果相差 6 秒；SLEEP() 函数用于暂停指定的秒数。

通过系统选项 --sysdate-is-now 可以将 SYSDATE() 设置为 NOW() 的同义词。

UTC_DATE() 和 UTC_DATE 函数用于返回当前 UTC 日期，UTC_TIME([fsp]) 和 UTC_TIME 函数用于返回当前 UTC 时间，UTC_TIMESTAMP([fsp]) 和 UTC_TIMESTAMP 函数用于返回当前 UTC 日期和时间。例如：

select utc_date(), utc_time(6), utc_timestamp(6);
utc_date()|utc_time(6)    |utc_timestamp(6)          |
----------|---------------|--------------------------|
2020-07-26|13:23:41.993849|2020-07-26 13:23:41.993849|

北京时间是 +8 时区，所以 UTC 时间比当前时间慢 8 小时。

16.2 构造一个日期时间值

FROM_DAYS(N) 函数用于将一个第 0 年以来的天数转换为日期。例如：

select from_days(737997);
from_days(737997)|
-----------------|
       2020-07-26|

FROM_DAYS() 函数不支持公历出现之前的日期（1582 年）。另外，与该函数相反的是 TO_DAYS() 函数，参见下文第 16.4 节。

FROM_UNIXTIME(unix_timestamp[,format]) 函数用于将 Unix 时间戳转换为日期时间，返回格式为日期时间类型的 ‘YYYY-MM-DD hh:mm:ss’ 或者整数类型的 YYYYMMDDhhmmss。例如：

select from_unixtime(1595772000), from_unixtime(1595772000, '%Y %M %d %h:%i:%s %p');
from_unixtime(1595772000)|from_unixtime(1595772000, '%Y %M %d %h:%i:%s %p')|
-------------------------|-------------------------------------------------|

其中，字符串 format 用于设置日期时间的显示格式，具体参考下文第 16.4 节。

MAKEDATE(year,dayofyear) 函数返回一个由年和日组成的日期。例如：

select makedate(2020, 210), makedate(2020, 0);
makedate(2020, 210)|makedate(2020, 0)|
-------------------|-----------------|
         2020-07-28|                 |

2020 年第 210 天对于的日期是 2020-07-28。

MAKETIME(hour,minute,second) 函数返回一个由时、分、秒组成的时间。例如：

maketime(10, 20, 30)|maketime(10, 20, 30.456)|
--------------------|------------------------|
            10:20:30|         10:20:30.456000|

SEC_TO_TIME(seconds) 函数用于将数字形式的秒数转换为时间类型。例如：

select sec_to_time(3600), sec_to_time(3600)+0;
sec_to_time(3600)|sec_to_time(3600)+0|
-----------------|-------------------|
         01:00:00|              10000|

STR_TO_DATE(str,format) 函数用于将字符串转换为日期时间类型。例如：

select str_to_date('5 1, 2020','%m %d, %Y'), str_to_date('1:00:00','%H:%i:%s');
str_to_date('5 1, 2020','%m %d, %Y')|str_to_date('1:00:00','%H:%i:%s')|
------------------------------------|---------------------------------|
                          2020-05-01|                         01:00:00|

其中，字符串 format 用于设置日期时间的格式；另外，该函数是 DATE_FORMAT() 函数的相反操作，具体参考下文第 16.4 节。

TIMESTAMP(expr) 函数用于返回参数对应的日期时间，TIMESTAMP(expr1,expr2) 函数用于将一个日期时间增加指定的时间并返回结果。例如：

select timestamp('2020-07-26'), timestamp('2020-07-26 02:00:00', '10:00:00');
timestamp('2020-07-26')|timestamp('2020-07-26 02:00:00', '10:00:00')|
-----------------------|--------------------------------------------|
    2020-07-26 00:00:00|                         2020-07-26 12:00:00|

16.3 获取日期时间中的信息

DATE(expr) 函数用于获取日期时间中的日期部分，TIME(expr) 函数用于获取日期时间中的时间部分。例如：

select date('2020-07-26 10:00:00'), date('2020-07-26'), time('2020-07-26 10:00:00.450');
date('2020-07-26 10:00:00')|date('2020-07-26')|time('2020-07-26 10:00:00.450')|
---------------------------|------------------|-------------------------------|
                 2020-07-26|        2020-07-26|                10:00:00.450000|

EXTRACT(unit FROM date) 函数用于获取日期时间中的某个部分。例如：

select extract(year from '2020-07-26') extract,
       extract(year_month from '2020-07-26') extract,
       extract(hour_second from '2020-07-26 12:30:45') extract;
extract|extract|extract|
-------|-------|-------|
   2020| 202007| 123045|

其中，unit 用于指定返回那一部分信息，包括年、月、日、时、分、秒等；完整的取值可以参考官方文档。

YEAR(date) 函数用于返回日期中的年份。例如：

select year('2020-07-26'),year('0000-01-01');
year('2020-07-26')|year('0000-01-01')|
------------------|------------------|
              2020|                 0|

YEARWEEK(date) 和 YEARWEEK(date,mode) 函数返回日期 date 所属的年分和周数。例如：

select yearweek('2020-01-01'),yearweek('2020-01-01', 3);
yearweek('2020-01-01')|yearweek('2020-01-01', 3)|
----------------------|-------------------------|
                201952|                   202001|

参数 mode 决定了每周第一天是星期天还是星期一，以及每一天属于哪一年的第几周。默认情况（mode 为 0）下，2020-01-01 属于 2019 年第 52 周；mode 取值为 3 时，2020-01-01 属于 2020 年第 1 周。关于 mode 参数的具体介绍，参考下文中的 WEEK() 函数。

QUARTER(date) 函数用于返回日期 date 所属的季节，取值返回从 1 到 4。例如：

select quarter('2020-07-26');
quarter('2020-07-26')|
---------------------|
                    3|

MONTH(date) 函数返回日期 date 所在的月份，MONTHNAME(date) 函数用于返回日期 date 所属的月份名称。例如：

select month('2020-07-26'), monthname('2020-07-26');
month('2020-07-26')|monthname('2020-07-26')|
-------------------|-----------------------|
                  7|七月                    |

显示月份名称的语言由系统变量 lc_time_names 控制，中文为“zh_CN”。

WEEK(date[,mode]) 函数用于返回日期 date 属于一年中的第几周，参数 mode 用于控制一周的第一天是星期天还是星期一，以及每一天属于哪一年的第几周。mode 的可能取值和行为如下：

下图是 2020 年 1 月份的日历（星期天为每周的第一天）：

以下示例演示了不同 mode 参数的作用：

select week('2020-01-01'), week('2020-01-01', 2), week('2020-01-01', 4), week('2020-01-01', 6);
week('2020-01-01')|week('2020-01-01', 2)|week('2020-01-01', 4)|week('2020-01-01', 6)|
------------------|---------------------|---------------------|---------------------|
                 0|                   52|                    1|                    1|

默认的 mode（0）由系统变量 default_week_format 的值决定，此时 2020-01-01 属于 2020 年的第 0 周；mode 为 2 时 2020-01-01 属于 2019 年的第 52 周；mode 为 4 或者 6 时 2020-01-01 属于 2020 年的第 1 周。

WEEKOFYEAR(date) 函数返回 date 所属的日历周（1-53），等价于 WEEK(date,3) 。

DAYOFYEAR(date) 函数返回 date 是一年中的第几天（1-366），DAY(date) 和 DAYOFMONTH(date) 函数返回 date 属于一月中的第几天（0-31），DAYOFWEEK(date) 函数返回 date 是一星期中的第几天（1 = 星期天）。例如：

select dayofyear('2020-07-26'), dayofmonth('2020-07-26'), dayofweek('2020-07-26');
dayofyear('2020-07-26')|dayofmonth('2020-07-26')|dayofweek('2020-07-26')|
-----------------------|------------------------|-----------------------|
                    208|                      26|                      1|

DAYNAME(date) 函数返回 date 是星期几。例如：

select dayname('2020-07-26');
dayname('2020-07-26')|
---------------------|
星期日                |

显示星期名称的语言由系统变量 lc_time_names 控制，中文为“zh_CN”。

WEEKDAY(date) 函数返回 date 属于一周中的第几天，星期一是 0，星期天是 6。例如：

select weekday('2020-07-26');
weekday('2020-07-26')|
---------------------|
                    6|

LAST_DAY(date) 函数返回 date 所在月份的最后一天。例如：

select last_day('2020-07-26');
last_day('2020-07-26')|
----------------------|
            2020-07-31|

HOUR(time) 函数返回 time 中的小时部分。例如：

select hour('2020-07-26 10:00:00'), hour('30:20:10');
hour('2020-07-26 10:00:00')|hour('30:20:10')|
---------------------------|----------------|
                         10|              30|

MINUTE(time) 函数返回 time 中的分钟。例如：

select minute('2020-07-26 10:20:30'), minute('30:20:10');
minute('2020-07-26 10:20:30')|minute('30:20:10')|
-----------------------------|------------------|
                           20|                20|

SECOND(time) 函数返回 time 中的秒数（0-59），MICROSECOND(expr) 函数返回 time 中的毫秒。例如：

select second('10:20:30'), microsecond('30:20:10.123456');
second('10:20:30')|microsecond('30:20:10.123456')|
------------------|------------------------------|
                30|                        123456|

16.4 将日期时间转换为其他类型

DATE_FORMAT(date,format) 函数将 date 格式化成字符串，参数 format 用于指定格式化字符串；它是 STR_TO_DATE() 的相反函数。例如：

select date_format(now(), '%W %d %M %Y, %r');
date_format(now(), '%W %d %M %Y, %r')|
-------------------------------------|
星期一 27 七月 2020, 03:29:19 AM       |

其中，百分号开始的字符是格式说明符，具体特定的意义。


TIME_FORMAT(time,format) 函数将 time 格式化成字符串，参数 format 用于指定格式化字符串。例如：

select time_format(now(), '%r');
time_format(now(), '%r')|
------------------------|
04:24:49 AM             |

TIME_TO_SEC(time) 函数将 time 转换为秒数。例如：

select time_to_sec('1:00:00');

TO_DAYS(date) 函数返回从第 0 年到 date 以来的天数。例如：

select to_days('2020-07-26'), from_days(737997);
to_days('2020-07-26')|from_days(737997)|
---------------------|-----------------|
               737997|       2020-07-26|

TO_DAYS() 函数和 FROM_DAYS() 函数一样，不支持公历出现之前的日期（1582 年）。

TO_SECONDS(expr) 函数返回从第 0 年到 expr 以来的秒数。例如：

select to_seconds('2020-07-26 00:00:00');
to_seconds('2020-07-26 00:00:00')|
---------------------------------|
                      63762940800|

TO_SECONDS() 函数和 TO_DAYS() 函数一样，不支持公历出现之前的日期（1582 年）。

UNIX_TIMESTAMP([date]) 函数返回 date 对应的 Unix 时间戳（自 ‘1970-01-01 00:00:00’ UTC 以来的秒数），省略参数 date 表示当前时间对应的时间戳。例如：

select unix_timestamp(), unix_timestamp(now());
unix_timestamp()|unix_timestamp(now())|
----------------|---------------------|
      1595796384|           1595796384|

UNIX_TIMESTAMP() 是 FROM_UNIXTIME() 函数的相反操作。

GET_FORMAT({DATE|TIME|DATETIME}, {‘EUR’|‘USA’|‘JIS’|‘ISO’|‘INTERNAL’}) 函数返回指定的格式字符串，通常与 DATE_FORMAT() 和 STR_TO_DATE() 函数一起使用。例如：

select date_format('2020-07-26',get_format(date,'USA')) date_format,
       str_to_date('2020-07-26 20:30:00',GET_FORMAT(DATETIME,'ISO')) str_to_date;
date_format|str_to_date        |
-----------|-------------------|
07.26.2020 |2020-07-26 20:30:00|

第一个 GET_FORMAT() 函数返回了 USA 的日期格式字符串，第二个GET_FORMAT() 函数返回了 ISO 的日期时间格式字符串。

16.5 日期时间的加减运算

ADDDATE(date,INTERVAL expr unit)、ADDDATE(expr,days)、DATE_ADD(date,INTERVAL expr unit) 函数为日期时间增加一个时间间隔。例如：

select adddate('2020-01-01', interval 1 hour), date_add('2020-01-01', interval 1 hour), adddate('2020-01-01', 1);
adddate('2020-01-01', interval 1 hour)|date_add('2020-01-01', interval 1 hour)|adddate('2020-01-01', 1)|
--------------------------------------|---------------------------------------|------------------------|
2020-01-01 01:00:00                   |2020-01-01 01:00:00                    |2020-01-02              |

参数 INTERVAL expr unit 可以指定不同的时间间隔，具体可以参考官方文档。

SUBDATE(date,INTERVAL expr unit)、SUBDATE(expr,days)、DATE_SUB(date,INTERVAL expr unit) 函数从日期时间中减去一个时间间隔。例如：

select subdate('2020-01-01', interval 1 day), subdate('2020-01-01', 1), date_sub('2020-01-01', interval 1 day);
subdate('2020-01-01', interval 1 day)|subdate('2020-01-01', 1)|date_sub('2020-01-01', interval 1 day)|
-------------------------------------|------------------------|--------------------------------------|
2019-12-31                           |2019-12-31              |2019-12-31                            |

日期时间数据也可以使用 + 或者 - 直接进行加减运算。例如：

select '2020-01-01' + interval 1 day, '2020-01-01 10:00:00' - interval 1 hour;
'2020-01-01' + interval 1 day|'2020-01-01 10:00:00' - interval 1 hour|
-----------------------------|---------------------------------------|
2020-01-02                   |2020-01-01 09:00:00                    |

ADDTIME(expr1,expr2) 函数为 expr1 增加指定的时间 expr2，SUBTIME(expr1,expr2) 为 expr1 减去指定的时间 expr2。例如：

select addtime('2020-01-01 00:00:00', '10:00:00'), subtime('10:00:00', '10:00:00');
addtime('2020-01-01 00:00:00', '10:00:00')|subtime('10:00:00', '10:00:00')|
------------------------------------------|-------------------------------|
2020-01-01 10:00:00                       |00:00:00                       |

PERIOD_ADD(P,N) 函数为一个时间段 P（不是 date 类型）增加指定的月数，返回格式为 YYYYMM。例如：

select period_add(202001, 6);
period_add(202001, 6)|
---------------------|
               202007|

TIMESTAMPADD(unit,interval,datetime_expr) 函数为 datetime_expr 增加一个指定的时间间隔。例如：

select timestampadd(day, 1, '2020-07-26'), timestampadd(minute, 10, '2020-07-26');

unit 用于指定时间间隔的单位，可以为 MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 或者 YEAR。

DATEDIFF(expr1,expr2) 函数计算两个日期之间的天数（expr1 ? expr2）。例如：

select datediff('2020-01-02','2020-01-01'), datediff('2020-01-02 00:00:00','2020-01-01 23:59:59');
datediff('2020-01-02','2020-01-01')|datediff('2020-01-02 00:00:00','2020-01-01 23:59:59')|
-----------------------------------|-----------------------------------------------------|
                                  1|                                                    1|

datetime 类型中的时间部分不参与计算。

PERIOD_DIFF(P1,P2) 函数返回两个时间段（P1 和 P2 不是 date 类型）之间相差的月数（P1-P2）。例如：

select period_diff(000002, 000001);
period_diff(000002, 000001)|
---------------------------|
                          1|

TIMEDIFF(expr1,expr2) 函数计算两个时间点之间相差的时间（expr1-expr2）。例如：

select timediff('2020-07-26 23:59:59', '2020-07-25 00:00:00');
timediff('2020-07-26 23:59:59', '2020-07-25 00:00:00')|
------------------------------------------------------|
                                              47:59:59|

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 函数返回两个日期时间之间相差的时间间隔（datetime_expr2 ? datetime_expr1），unit 用于指定时间间隔的单位，可以为 MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 或者 YEAR。例如：

select timestampdiff(second,'1970-01-01 08:00:00', '2020-07-26'), unix_timestamp('2020-07-26');
timestampdiff(second,'1970-01-01 08:00:00', '2020-07-26')|unix_timestamp('2020-07-26')|
---------------------------------------------------------|----------------------------|
                                               1595692800|                  1595692800|

16.6 时区转换

CONVERT_TZ(dt,from_tz,to_tz) 函数用于将日期时间从一个时区转换为另一个时区。例如：

select convert_tz('2020-07-26 00:00:00','+00:00','+08:00');
convert_tz('2020-07-26 00:00:00','+00:00','+08:00')|
---------------------------------------------------|
                                2020-07-26 08:00:00|

CONVERT_TZ() 函数也支持使用名称指定时区，相关的设置可以参考官方文档。


对了，在这里说一下，我目前是在职Java开发，如果你现在正在学习Java，了解Java，渴望成为一名合格的Java开发工程师，在入门学习Java的过程当中缺乏基础入门的视频教程，可以关注并私信我：01。获取。我这里有最新的Java基础全套视频教程。

标签：dayofweek 

作者:gojiuye , 分类:技术精选 , 浏览:18 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言