menu
  • 首页 首页 icon
  • 工具库 工具库 icon
    • IP查询 IP查询 icon
  • 内容库 内容库 icon
    • 快讯库 快讯库 icon
    • 精品库 精品库 icon
    • 问答库 问答库 icon
  • 更多 更多 icon
    • 服务条款 服务条款 icon

MySQL(七)-日期和时间函数的使用

武飞扬头像
Fɪʀᴇᴡᴏʀᴋs
帮助1人


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

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

CURDATE0和 CURRENT DATEO函数的作用相同，将当前日期按照“YYYY-MM-DD’或YYYYMMDD格式的值返回，具体格式根据函数在字符串或是数字语境中而定。

mysql>  SELECT CURDATE(),CURRENT_DATE(), CURDATE()   0;
 ------------ ---------------- --------------- 
| CURDATE()  | CURRENT_DATE() | CURDATE()   0 |
 ------------ ---------------- --------------- 
| 2023-04-28 | 2023-04-28     |      20230428 |
 ------------ ---------------- --------------- 
1 row in set (0.00 sec)

CURTIME0和CURRENT TIME0函数的作用相同，将当前时间以“HH:MMSS’或HHMMSS的格式返回，具体格式根据函数在字符串或是数字语境中而定。

mysql> SELECT CURTIME(),CURRENT_TIME(),CURTIME()   0;
 ----------- ---------------- --------------- 
| CURTIME() | CURRENT_TIME() | CURTIME()   0 |
 ----------- ---------------- --------------- 
| 09:50:51  | 09:50:51       |         95051 |
 ----------- ---------------- --------------- 
1 row in set (0.00 sec)

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

CURRENT_TIMESTAMPO、LOCALTIME0、NOWO和SYSDATE04个函数的作用相同，均返回当前日期和时间值，格式为“YYYY-MM-DDHH:MM:SS’或YYYYMMDDHHMMSS，具体格式根据函数在字符串或数字语境中而定。

mysql> SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
 --------------------- --------------------- --------------------- --------------------- 
| CURRENT_TIMESTAMP() | LOCALTIME()         | NOW()               | SYSDATE()           |
 --------------------- --------------------- --------------------- --------------------- 
| 2023-04-28 09:52:41 | 2023-04-28 09:52:41 | 2023-04-28 09:52:41 | 2023-04-28 09:52:41 |
 --------------------- --------------------- --------------------- --------------------- 
1 row in set (0.00 sec)

3 UNIX时间戳函数

UNIX TIMESTAMP(date)若无参数调用，表示的是从 1970 年 1 月 1 日 00:00:00 UTC 到当前时间的秒数，它是一个整数，可以用于方便地进行时间计算。date 可以是一个DATE 字符串、DATETIME 字符串、TIMESTAMP 或一个当地时间的YYMMDD或YYYYMMDD 格式的数字。

mysql>  SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), NOW();
 ------------------ ----------------------- --------------------- 
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW()               |
 ------------------ ----------------------- --------------------- 
|       1682647228 |            1682647228 | 2023-04-28 10:00:28 |
 ------------------ ----------------------- --------------------- 
1 row in set (0.00 sec)

FROM_UNIXTIME 函数：将一个表示 UNIX 时间戳的秒数转换为一个日期时间值。两个函数互为反函数。

mysql>  SELECT FROM_UNIXTIME('1234567891');
 ----------------------------- 
| FROM_UNIXTIME('1234567891') |
 ----------------------------- 
| 2009-02-14 07:31:31.000000  |
 ----------------------------- 
1 row in set (0.00 sec)

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

UTC DATE0函数返回当前 UTC(世界标准时间)日期值，其格式为“YYYY-MM-DD’或YYYYMMDD，具体格式取决于函数是否用在字符串或数字语境中。

mysql>  SELECT UTC_DATE(), UTC_DATE()   0;
 ------------ ---------------- 
| UTC_DATE() | UTC_DATE()   0 |
 ------------ ---------------- 
| 2023-04-28 |       20230428 |
 ------------ ---------------- 
1 row in set (0.00 sec)

UTC TIME0返回当前 UTC 时间值，其格式为“HH:MM:SS’或HHMMSS，具体格式取决于函数是否用在字符串或数字语境中。

mysql>  SELECT UTC_TIME(), UTC_TIME()   0;
 ------------ ---------------- 
| UTC_TIME() | UTC_TIME()   0 |
 ------------ ---------------- 
| 02:02:42   |          20242 |
 ------------ ---------------- 
1 row in set (0.00 sec)

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

MONTH(date)函数返回 date 对应的月份，范围值为 1~12。

mysql> SELECT MONTH('2023-02-13');
 --------------------- 
| MONTH('2023-02-13') |
 --------------------- 
|                   2 |
 --------------------- 
1 row in set (0.00 sec)

MONTHNAME(date)函数返回日期date 对应月份的英文全名

mysql>  SELECT MONTHNAME('2023-08-23');
 ------------------------- 
| MONTHNAME('2023-08-23') |
 ------------------------- 
| August                  |
 ------------------------- 
1 row in set (0.00 sec)

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

DAYNAME(d)函数返回d 对应的工作日的英文名称。

mysql>  SELECT DAYNAME('208-05-24');
 ---------------------- 
| DAYNAME('208-05-24') |
 ---------------------- 
| Tuesday              |
 ---------------------- 
1 row in set (0.00 sec)

DAYOFWEEK(d)函数返回d对应的一周中的索引(位置，1 表示周日，2表示周一，…，7表示周六)。

mysql> SELECT DAYOFWEEK('2211-09-30');
 ------------------------- 
| DAYOFWEEK('2211-09-30') |
 ------------------------- 
|                       2 |
 ------------------------- 
1 row in set (0.00 sec)

WEEKDAY(d)返回d对应的工作日索引:0表示周一，1 表示周二，…，6 表示周日

mysql> SELECT WEEKDAY('1911-08-03 22:23:00'), WEEKDAY('2024-07-01');
 -------------------------------- ----------------------- 
| WEEKDAY('1911-08-03 22:23:00') | WEEKDAY('2024-07-01') |
 -------------------------------- ----------------------- 
|                              3 |                     0 |
 -------------------------------- ----------------------- 
1 row in set (0.00 sec)

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

WEEK(d)计算日期d 是一年中的第几周。WEEK0的双参数形式允许指定该星期是否起始于周日或周一,以及返回值的范围是否为0~53 或1~53。若Mode参数被省略,则使用default week format系统自变量的值。
学新通

mysql> SELECT WEEK('2011-02-20'),WEEK('2011-02-20',0), WEEK('2011-02-20',1);
 -------------------- ---------------------- ---------------------- 
| WEEK('2011-02-20') | WEEK('2011-02-20',0) | WEEK('2011-02-20',1) |
 -------------------- ---------------------- ---------------------- 
|                  8 |                    8 |                    7 |
 -------------------- ---------------------- ---------------------- 
1 row in set (0.00 sec)

使用不同的参数的原因是不同地区和国家的习惯不同，每周的第
天并不相同。

mysql>  SELECT WEEK('2011-02-20',3), WEEKOFYEAR('2011-02-20');
 ---------------------- -------------------------- 
| WEEK('2011-02-20',3) | WEEKOFYEAR('2011-02-20') |
 ---------------------- -------------------------- 
|                    7 |                        7 |
 ---------------------- -------------------------- 
1 row in set (0.00 sec)

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

DAYOFYEAR(d)函数返回d 是一年中的第几天，范围是 1~366。

mysql> SELECT DAYOFYEAR('1911-08-03');
 ------------------------- 
| DAYOFYEAR('1911-08-03') |
 ------------------------- 
|                     215 |
 ------------------------- 
1 row in set (0.00 sec)

DAYOFMONTH(d)函数返回d 是一个月中的第几天，范围是 1~31。

mysql>  SELECT DAYOFMONTH('1911-08-03');
 -------------------------- 
| DAYOFMONTH('1911-08-03') |
 -------------------------- 
|                        3 |
 -------------------------- 
1 row in set (0.00 sec)

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

YEAR(date)返回date 对应的年份，范围是1970~2069。

mysql>   SELECT YEAR('22-02-03'),YEAR('96-02-03');
 ------------------ ------------------ 
| YEAR('22-02-03') | YEAR('96-02-03') |
 ------------------ ------------------ 
|             2022 |             1996 |
 ------------------ ------------------ 
1 row in set (0.00 sec)

QUARTER(date)返回 date 对应的一年中的季度值，范围是1~4。

mysql>  SELECT QUARTER('22-05-01');
 --------------------- 
| QUARTER('22-05-01') |
 --------------------- 
|                   2 |
 --------------------- 
1 row in set (0.00 sec)

MINUTE(time)返回time 对应的分钟数，范围是0~59。

mysql>  SELECT MINUTE('02-03-04 05:06:07');
 ----------------------------- 
| MINUTE('02-03-04 05:06:07') |
 ----------------------------- 
|                           6 |
 ----------------------------- 
1 row in set (0.00 sec)

SECOND(time)返回time 对应的秒数，范围是0~59。


mysql>  SELECT SECOND('05:06:07');
 -------------------- 
| SECOND('05:06:07') |
 -------------------- 
|                  7 |
 -------------------- 
1 row in set (0.00 sec)

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

EXTRACT(type FROM date)函数所使用的时间间隔类型说明符与DATE ADDO或DATE SUBO的相同，但它从日期中提取一部分，而不是执行日期运算。

mysql> SELECT EXTRACT(YEAR FROM '1976-07-02') AS col1,
    ->      EXTRACT(YEAR_MONTH FROM '2011-08-13 02:03:03') AS col2,
    ->      EXTRACT(DAY_MINUTE FROM '2311-09-14 03:04:03') AS col3;
 ------ -------- -------- 
| col1 | col2   | col3   |
 ------ -------- -------- 
| 1976 | 201108 | 140304 |
 ------ -------- -------- 
1 row in set (0.00 sec)

type值为YEAR 时，只返回年值，type 值为 YEAR MONTH 时返回年与月份，type 值为 DAY_MINUTE 时，返回日、小时和分钟值。

11 时间和秒钟转换的函数

TIME TOSEC(time)返回已转化为秒的 time 参数。转换公式为:小时3600 分钟60 秒。

mysql>  SELECT TIME_TO_SEC('13:23:00');
 ------------------------- 
| TIME_TO_SEC('13:23:00') |
 ------------------------- 
|                   48180 |
 ------------------------- 
1 row in set (0.00 sec)

SEC TO TIME(seconds)返回被转化为小时、分钟和秒数的 seconds 参数值，其格式为HH:MM:SS’或HHMMSS，具体格式根据该函数是否用在字符串或数字语境中而定。

mysql>  SELECT SEC_TO_TIME(12345),SEC_TO_TIME(12345) 0,
    ->      TIME_TO_SEC('13:23:00'), SEC_TO_TIME(72580);
 -------------------- ---------------------- ------------------------- -------------------- 
| SEC_TO_TIME(12345) | SEC_TO_TIME(12345) 0 | TIME_TO_SEC('13:23:00') | SEC_TO_TIME(72580) |
 -------------------- ---------------------- ------------------------- -------------------- 
| 03:25:45           |                32545 |                   48180 | 20:09:40           |
 -------------------- ---------------------- ------------------------- -------------------- 
1 row in set (0.00 sec)

12 计算日期和时间的函数

计算日期和时间的函数有 DATE ADDO、ADDDATE0、DATE SUBO、SUBDATEO、ADDTIME0、SUBTIMEO和 DATE DIFFO。
在DATE ADD(date,INTERVAL expr type)和 DATE SUB(date,INTERVAL expr type)中，date是一个DATETIME或DATE 值，用来指定起始时间。expr 是个表达式，用来指定从起始日期添加或减去的时间间隔值。对于负值的时间间隔，expr 可以以一个负号“-’开头。type 为关键词,指示了表达式被解释的方式。
指定修改的时间段时，也可以指定负值，负值代表相减，即返回以前的日期和时间。
学新通
学新通
若 date 参数是一个 DATE 值，计算只会包括 YEAR、MONTH和 DAY 部分(没有时间部分)，其结果是一个 DATE 值;否则，结果将是一个 DATETIME 值。

DATEADD(dateINTERVAL expr type)和ADDDATE(dateINTERVAL expr type)两个函数的作用相同，执行日期的加运算。

mysql> SELECT DATE_ADD('2020-12-31 23:59:59', INTERVAL 1 SECOND) AS col1,
    ->      ADDDATE('2030-12-31 23:59:59', INTERVAL 1 SECOND) AS col2,
    ->      DATE_ADD('2040-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) AS col3;
 --------------------- --------------------- --------------------- 
| col1                | col2                | col3                |
 --------------------- --------------------- --------------------- 
| 2021-01-01 00:00:00 | 2031-01-01 00:00:00 | 2041-01-01 00:01:00 |
 --------------------- --------------------- --------------------- 
1 row in set (0.00 sec)

前两个是将时间增加 1S 后进行返回，最后是增加1分1秒进行返回的结果。

DATESUB(dateINTERVALexpr type)或者SUBDATE(dateINTERVALexpr type)两个函数的作用相同，执行日期的减运算。

mysql>  SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY) AS col1,
    ->      SUBDATE('2011-01-02', INTERVAL 31 DAY) AS col2,
    ->      DATE_SUB('2011-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS col3;
 ------------ ------------ --------------------- 
| col1       | col2       | col3                |
 ------------ ------------ --------------------- 
| 2010-12-02 | 2010-12-02 | 2010-12-31 23:59:59 |
 ------------ ------------ --------------------- 
1 row in set (0.00 sec)

DATE ADD和 DATE SUB 在指定修改的时间段时，也可以指定负值，负值代表相减即返回以前的日期和时间。

ADDTIME(date,expr)函数将 expr 值添加到 date，并返回修改后的值，date 是一个日期或者日期时间表达式，而expr 是一个时间表达式。

mysql>  SELECT ADDTIME('2000-12-31 23:59:59','1:1:1'), ADDTIME('02:02:02', '02:00:00');
 ---------------------------------------- --------------------------------- 
| ADDTIME('2000-12-31 23:59:59','1:1:1') | ADDTIME('02:02:02', '02:00:00') |
 ---------------------------------------- --------------------------------- 
| 2001-01-01 01:01:00                    | 04:02:02                        |
 ---------------------------------------- --------------------------------- 
1 row in set (0.00 sec)

SUBTIME(date,expr)函数将 date 减去 expr 值，并返回修改后的值。其中，date 是一个日期或者日期时间表达式，而expr 是一个时间表达式。

mysql>  SELECT SUBTIME('2000-12-31 23:59:59','1:1:1'), SUBTIME('02:02:02','02:00:00');
 ---------------------------------------- -------------------------------- 
| SUBTIME('2000-12-31 23:59:59','1:1:1') | SUBTIME('02:02:02','02:00:00') |
 ---------------------------------------- -------------------------------- 
| 2000-12-31 22:58:58                    | 00:02:02                       |
 ---------------------------------------- -------------------------------- 
1 row in set (0.00 sec)

DATEDIFF(datel,date2)返回起始时间 datel 和结束时间 date2 之间的天数。datel和 date2 为日期或日期时间表达式。计算中只用到这些值的日期部分。

mysql>  SELECT DATEDIFF('2010-12-31 23:59:59','2010-12-30') AS col1,
    ->      DATEDIFF('2010-11-30 23:59:59','2010-12-31') AS col2;
 ------ ------ 
| col1 | col2 |
 ------ ------ 
|    1 |  -31 |
 ------ ------ 
1 row in set (0.00 sec)

13 将日期和时间格式化的函数

DATEFORMAT(date,format)根据 format 指定的格式显示date值所示。
学新通
使用DATE FORMATO函数格式化输出日期和时间值

mysql>  SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS col1,
    ->      DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j') AS col2;
 ----------------------- -------------------------- 
| col1                  | col2                     |
 ----------------------- -------------------------- 
| Saturday October 1997 | 4th 97 Sat 04 10 Oct 277 |
 ----------------------- -------------------------- 
1 row in set (0.00 sec)

TIME FORMAT(timeformat)根据表达式format 的要求显示时间 time。表达式format 指定了显示的格式。因为TIME FORMAT(timeformat)只处理时间，所以format 只使用时间格式。

mysql>  SELECT TIME_FORMAT('16:00:00', '%H %k %h %I %l');
 ------------------------------------------- 
| TIME_FORMAT('16:00:00', '%H %k %h %I %l') |
 ------------------------------------------- 
| 16 16 04 04 4                             |
 ------------------------------------------- 
1 row in set (0.00 sec)

GET_FORMAT(val type,format type)返回日期时间字符串的显示格式,val type 表示日期数据类型,包括 DATE、DATETIME和TIME;format type 表示格式化显示类型,包括EUR、INTERVAL、ISO、JIS、USA。GET FORMAT 根据两个值类型组合返回的字符串显示格式。
学新通
学新通

mysql>  SELECT GET_FORMAT(DATE,'EUR'), GET_FORMAT(DATE,'USA');
 ------------------------ ------------------------ 
| GET_FORMAT(DATE,'EUR') | GET_FORMAT(DATE,'USA') |
 ------------------------ ------------------------ 
| %d.%m.%Y               | %m.%d.%Y               |
 ------------------------ ------------------------ 
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA') );
 ------------------------------------------------------------- 
| DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA') ) |
 ------------------------------------------------------------- 
| 10.05.2000                                                  |
 ------------------------------------------------------------- 
1 row in set (0.00 sec)

GET FORMAT(DATEUSA返回的显示格式字符串为%m.%d%Y，%m 以数字形式显示月份，%d 以数字形式显示日，%Y 以4 位数字形式显示年。

这篇好文章是转载于：学新通技术网

  • 版权申明： 本站部分内容来自互联网，仅供学习及演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，请提供相关证据及您的身份证明，我们将在收到邮件后48小时内删除。
  • 本站站名： 学新通技术网
  • 本文地址： /boutique/detail/tanhfkbiie
系列文章
更多 icon
同类精品
更多 icon
继续加载
友情链接: 申请要求：权重≥3，IP≥1000，内容属同类网站; 申请邮件： luke.wu●vfv.cc 友情链接交换群： 友情链接交换群
外链价格: 【首页 30/月 - 300/年】【内页 50/月 - 500/年】【全站 70/月 - 700/年】 购买外链请加QQ群： 站长交流群