侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰，成功永远属于自信且有毅力的人！

  • 累计撰写 297 篇文章
  • 累计创建 134 个标签
  • 累计收到 4 条评论
标签搜索

目 录CONTENT

文章目录
数据库 MySql

MySql日期时间函数的使用方式

孔子说JAVA
孔子说JAVA
2022-09-12 / 0 评论 / 0 点赞 / 392 阅读 / 13,702 字 / 正在检测是否收录...

MySQL数据库中提供了很丰富的函数，这些函数可以帮助用户更加方便的处理表中的数据，他们会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。SELECT语句及其条件表达式都可以使用这些函数。同时，INSERT 、UPDATE、DELECT语句及其条件表达式也可以使用这些函数。本文主要讲解日期和时间函数，函数列表如下：

函数名称 作 用
CURDATE 和 CURRENT_DATE 两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME 两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE 两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH 获取指定日期中的月份
MONTHNAME 获取指定日期中的月份英文名称
DAYNAME 获取指定曰期对应的星期几的英文名称
DAYOFWEEK 获取指定日期对应的一周的索引位置值
WEEK 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR 获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH 获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR 获取年份，返回值范围是 1970〜2069
TIME_TO_SEC 将时间参数转换为秒数
SEC_TO_TIME 将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE 两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE 两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME 时间加法运算，在原始时间上添加指定的时间
SUBTIME 时间减法运算，在原始时间上减去指定的时间
DATEDIFF 获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT 格式化指定的日期，根据参数返回指定格式的值
WEEKDAY 获取指定日期在一周内的对应的工作日索引
HOUR(time) 返回时间的小时，返回值的范围是0到23。
MINUTE(time) 返回时间分钟，范围为0到59。
SECOND(time) 返回时间秒数，范围为0到59。

1、CURDATE和CURRENT_DATE 日期函数

日期函数 CURDATE 和 CURRENT_DATE 获取系统当前日期。

mysql> SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0;
+------------+----------------+------------------+
| CURDATE()  | CURRENT_DATE() | CURRENT_DATE()+0 |
+------------+----------------+------------------+
| 2017-04-01 | 2017-04-01     |         20170401 |
+------------+----------------+------------------+
1 row in set (0.03 sec)

由运行结果可以看到，两个函数的作用相同，返回了相同的系统当前日期，“CURDATE()+0”将当前日期值转换为数值型。

2、CURTIME和CURRENT_TIME 时间函数

时间函数 CURTIME 和 CURRENT_TIME 获取系统当前时间。

mysql> SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME()+0;
+-----------+----------------+------------------+
| CURTIME() | CURRENT_TIME() | CURRENT_TIME()+0 |
+-----------+----------------+------------------+
| 19:39:51  | 19:39:51       |           193951 |
+-----------+----------------+------------------+
1 row in set (0.04 sec)

由运行结果可以看出，两个函数返回的结果相同，都返回了当前的系统时间。CURRENT_TIME()+0 是将当前日期值转换为数值型。

3、NOW、SYSDATE、LOCALTIME、LOCALTIMESTAMP 日期时间函数

NOW() 和 SYSDATE() 函数的作用相同，都是返回当前日期和时间值，格式为“YYYY-MM-DD HH：MM：SS”或“YYYYMMDDHHMMSS”，具体格式根据函数用在字符串或数字语境中而定。

mysql> SELECT NOW(),SYSDATE();
+---------------------+---------------------+
| NOW()               | SYSDATE()           |
+---------------------+---------------------+
| 2017-04-01 19:36:52 | 2017-04-01 19:36:52 |
+---------------------+---------------------+
1 row in set (0.04 sec)

由运行结果可以看到，NOW 函数和 SYSDATE 函数返回的结果是相同的。

虽然在 MySQL 中 NOW() 和 SYSDATE() 都表示当前时间，但是 NOW() 取的是语句开始执行的时间，而 SYSDATE() 取的是语句执行过程中动态的实时时间。

先查询了 NOW() 和 SYSDATE()，然后 sleep 了 3 秒，再查询 NOW() 和 SYSDATE()，结果如下：

mysql> select now(),sysdate(),sleep(3),now(),sysdate();
+-----------------------+------------------------+-------------+-----------------------+---------------------+
| now()                 | sysdate()              | sleep(3)    | now()                 | sysdate()           |
+-----------------------+------------------------+-------------+------------------- ---+---------------------+
| 2019-02-27 10:59:39   | 2019-02-27 10:59:39    |        0    | 2019-02-27 10:59:39   | 2019-02-27 10:59:42 |
+-----------------------+------------------------+-------------+-----------------------+---------------------+
1 row in set (3.00 sec)

由运行结果可以看出，NOW() 函数始终获取的是 SQL 语句开始执行的时间，而 SYSDATE() 函数则是动态获取的实时时间。

MySQL中的LOCALTIME()函数或LOCALTIMESTAMP()函数用于检查当前日期和时间值。它以“ YYYY-MM-DD HH：MM：SS”格式或YYYYMMDDHHMMSS.uuuuuu格式返回当前日期和时间的值，具体取决于该函数是在字符串上下文中还是在数字上下文中使用。

SELECT LOCALTIME as CurrDateTime ;
# 或
SELECT LOCALTIME() as CurrDateTime ;
# 或
SELECT LOCALTIMESTAMP as CurrDateTime ;
# 或
SELECT LOCALTIMESTAMP() as CurrDateTime ;

# 输出：
CURRDATETIME
2020-10-15 13:33:54

使用LOCALTIME函数或LOCALTIMESTAMP()函数以数字格式获取当前日期和时间。

SELECT LOCALTIME + 0 as CurrDateTime ;
# 或
SELECT LOCALTIMESTAMP + 0 as CurrDateTime ;

# 输出：
CURRDATETIME
20201015133627

4、UNIX_TIMESTAMP 获取UNIX时间戳函数

UNIX_TIMESTAMP(date) 若无参数调用，返回一个无符号整数类型的 UNIX 时间戳（'1970-01-01 00:00:00’GMT之后的秒数）。若用 date 来调用 UNIX_TIMESTAMP()，它会将参数值以’1970-01-01 00:00:00’GMT后的秒数的形式返回。

mysql> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();
+------------------+-----------------------+---------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW()               |
+------------------+-----------------------+---------------------+
|       1551251270 |            1551251270 | 2019-02-27 15:07:50 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

5、FROM_UNIXTIME 时间戳转日期函数

FROM_UNIXTIME(date) 函数把 UNIX 时间戳转换为普通格式的日期时间值，与 UNIX_TIMESTAMP () 函数互为反函数。

mysql> SELECT FROM_UNIXTIME(1150051270);
+---------------------------+
| FROM_UNIXTIME(1150051270) |
+---------------------------+
| 2006-06-12 02:41:10       |
+---------------------------+
1 row in set (0.00 sec)

6、MONTH 获取指定日期的月份函数

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

mysql> SELECT MONTH('2017-12-15');
+---------------------+
| MONTH('2017-12-15') |
+---------------------+
|                  12 |
+---------------------+
1 row in set (0.00 sec)

由运行结果可知，2017-12-15 对应的月份是 12。

7、MONTHNAME 日期月份的英文名称函数

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

mysql> SELECT MONTHNAME('2017-12-15');
+-------------------------+
| MONTHNAME('2017-12-15') |
+-------------------------+
| December                |
+-------------------------+
1 row in set (0.00 sec)

由运行结果可知，2017-12-15 对应月份的英文全名是 December。

8、DAYNAME 获取星期名称函数

DAYNAME(date) 函数获取指定日期的星期名称，返回 date 对应的工作日英文名称，例如 Sunday、Monday 等。

mysql> SELECT DAYNAME('2006-06-12');
+-----------------------+
| DAYNAME('2006-06-12') |
+-----------------------+
| Monday                |
+-----------------------+
1 row in set (0.00 sec)

由运行结果可知，2006-06-12 这一天是星期一，英文名称是 Monday。

9、DAYOFWEEK 周索引函数

获取日期对应的周索引函数 DAYOFWEEK(d) 返回 d 对应的一周中的索引（位置）。1 表示周日，2 表示周一，……，7 表示周六。这些索引值对应于ODBC标准。

mysql> SELECT DAYOFWEEK('2017-12-15');
+-------------------------+
| DAYOFWEEK('2017-12-15') |
+-------------------------+
|                       6 |
+-------------------------+
1 row in set (0.04 sec)

由运行结果可知，2017 年 12 月 15 日为周五，因此返回其对应的索引值为 6。

10、WEEK 第几周函数

WEEK() 函数计算日期 date 是一年中的第几周。WEEK(date,mode) 函数允许指定星期是否起始于周日或周一，以及返回值的范围是否为 0～52 或 1～53。WEEK函数接受两个参数：

  • date是要获取周数的日期。
  • mode是一个可选参数，用于确定周数计算的逻辑。

如果忽略 mode 参数，默认情况下 WEEK 函数将使用 default_week_format 系统变量的值。要获取 default_week_format 变量的当前值，请使用 SHOW VARIABLES 语句如下：

mysql> SHOW VARIABLES LIKE 'default_week_format';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+
1 row in set

使用 WEEK(date) 函数查询指定日期是一年中的第几周。

mysql> SELECT WEEK('2018-10-25',1);
+----------------------+
| WEEK('2018-10-25',1) |
+----------------------+
|                   43 |
+----------------------+
1 row in set (0.00 sec)

由运行结果可知，2018-10-25 是一年中的第 43 周。

11、DAYOFYEAR 一年中的位置函数

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

mysql> SELECT DAYOFYEAR('2017-12-15');
+-------------------------+
| DAYOFYEAR('2017-12-15') |
+-------------------------+
|                     349 |
+-------------------------+
1 row in set (0.00 sec)

由运行结果可以看出，1、3、5、7、8、10月份各31天，2月份各28天，4、6、9、11月份各30天，再加上12月份15天，因此返回结果为349。

12、DAYOFMONTH 一个月的位置函数

DAYOFMONTH函数获取指定日期在一个月的位置，DAYOFMONTH(d) 函数返回 d 是一个月中的第几天，范围为 1～31。

mysql> SELECT DAYOFMONTH('2017-12-15');
+--------------------------+
| DAYOFMONTH('2017-12-15') |
+--------------------------+
|                       15 |
+--------------------------+
1 row in set (0.02 sec)

由运行结果可以看出，2017-12-15 是这个月的第 15 天。

13、YEAR 获取年份函数

YEAR() 函数可以从指定日期值中来获取年份值。YEAR() 函数需要接受 date 参数，并返回日期的年份。YEAR(date)函数返回的年份值范围为 1000 到 9999，如果日期为零，YEAR() 函数返回 0。

mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2019 |
+-------------+
1 row in set (0.00 sec)

14、TIME_TO_SEC 将时间转换为秒值函数

TIME_TO_SEC(time) 函数返回将参数 time 转换为秒数的时间值，转换公式为“小时 ×3600+ 分钟 ×60+ 秒”。

mysql> SELECT TIME_TO_SEC('15:15:15');
+-------------------------+
| TIME_TO_SEC('15:15:15') |
+-------------------------+
|                   54915 |
+-------------------------+
1 row in set (0.00 sec)

由执行结果可以看出，根据计算公式“15×3600+15×60+25”得出结果秒数 54915。

15、SEC_TO_TIME 将秒值转换为时间格式函数

SEC_TO_TIME(seconds) 函数返回将参数 seconds 转换为小时、分钟和秒数的时间值。

mysql> SELECT SEC_TO_TIME('54925');
+----------------------+
| SEC_TO_TIME('54925') |
+----------------------+
| 15:15:25             |
+----------------------+
1 row in set (0.00 sec)

由执行结果可以看出，将上例中得到的秒数 54925 通过 SEC_TO_TIME(seconds) 函数计算，返回结果是时间值 15:15:25，为字符串型。

16、DATE_ADD和ADDDATE 日期加法运算函数

DATE_ADD(date,INTERVAL expr type) 和 ADDDATE(date,INTERVAL expr type) 两个函数的作用相同，都是用于执行日期的加运算。DATE_ADD() 和 ADDDATE() 函数有两个参数：

  • date 是 DATE 或 DATETIME 的起始值。
  • INTERVAL expr type是要添加到起始日期值的间隔值。
mysql> SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C1,
    -> DATE_ADD('2018-10-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS C2,
    -> ADDDATE('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C3;
+---------------------+---------------------+---------------------+
| C1                  | C2                  | C3                  |
+---------------------+---------------------+---------------------+
| 2018-11-01 00:00:00 | 2018-11-01 00:01:00 | 2018-11-01 00:00:00 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

由执行结果可以看出，DATE_ADD(date,INTERVAL expr type) 和 ADDDATE(date,INTERVAL expr type) 函数的功能完全相同，在原始时间 ‘2018-10-31 23:59:59’ 上加 1 秒之后结果都是 ‘2018-11-01 00:00:00’ ；在原始时间上加 1 分钟 1 秒的写法是表达式 ‘1:1’，最终可得结果 ‘2018-11-01 00:01:00’。

17、DATE_SUB和SUBDATE 日期减法运算函数

DATE_SUB(date,INTERVAL expr type) 和 SUBDATE(date,INTERVAL expr type) 两个函数作用相同，都是执行日期的减法运算。DATE_SUB() 和 SUBDATE() 函数接受两个参数：

  • date 是 DATE 或 DATETIME 的起始值。
  • expr 是一个字符串，用于确定从起始日期减去的间隔值。type 是 expr 可解析的间隔单位，例如 DAY，HOUR 等
mysql> SELECT DATE_SUB('2018-01-02',INTERVAL 31 DAY) AS C1,
    -> SUBDATE('2018-01-02',INTERVAL 31 DAY) AS C2,
    -> DATE_SUB('2018-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS C3;
+------------+------------+---------------------+
| C1         | C2         | C3                  |
+------------+------------+---------------------+
| 2017-12-02 | 2017-12-02 | 2017-12-31 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) 函数在指定加减的时间段时也可以指定负值，加法的负值即返回原始时间之前的日期和时间，减法的负值即返回原始时间之后的日期和时间。

18、ADDTIME 时间加法运算函数

ADDTIME(time,expr) 函数用于执行时间的加法运算。添加 expr 到 time 并返回结果。其中：time 是一个时间或日期时间表达式，expr 是一个时间表达式。

mysql> SELECT ADDTIME('2018-10-31 23:59:59','0:1:1'),
    -> ADDTIME('10:30:59','5:10:37');
+----------------------------------------+-------------------------------+
| ADDTIME('2018-10-31 23:59:59','0:1:1') | ADDTIME('10:30:59','5:10:37') |
+----------------------------------------+-------------------------------+
| 2018-11-01 00:01:00                    | 15:41:36                      |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)

由执行结果可以看出，在原始日期时间 ‘2018-10-31 23:59:59’ 上加 0 小时 1 分 1 秒之后返回的日期时间是 ‘2018-11-01 00:01:00’；在原始时间 ‘10:30:59’ 上加 5 小时 10 分 37 秒之后返回的日期时间是 ‘15:41:36’。

19、SUBTIME 时间减法运算函数

SUBTIME(time,expr) 函数用于执行时间的减法运算。其中：函数返回 time。expr 表示的值和格式 time 相同。time 是一个时间或日期时间表达式， expr 是一个时间。

mysql> SELECT SUBTIME('2018-10-31 23:59:59','0:1:1'),SUBTIME('10:30:59','5:12:37');
+----------------------------------------+-------------------------------+
| SUBTIME('2018-10-31 23:59:59','0:1:1') | SUBTIME('10:30:59','5:12:37') |
+----------------------------------------+-------------------------------+
| 2018-10-31 23:58:58                    | 05:18:22                      |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)

由执行结果可以看出，在原始日期时间 ‘2018-10-31 23:59:59’ 上减去 0 小时 1 分 1 秒之后返回的日期时间是 ‘2018-10-31 23:58:58’；在原始时间 ‘10:30:59’ 上减去 5 小时 12 分 37 秒之后返回的日期时间是 ‘05:18:22’。

20、DATEDIFF 获取两个日期的时间间隔函数

DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。计算时只用到这些值的日期部分。

mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,
    -> DATEDIFF('2017-11-30','2017-12-15') AS col2;
+------+------+
| COL1 | col2 |
+------+------+
|    1 |  -15 |
+------+------+
1 row in set (0.00 sec)

由运行结果可知，DATEDIFF() 函数返回 date1-date2 后的值，因此 DATEDIFF(‘2017-11-30’，‘2017-11-29’) 的返回值为 1，DATEDIFF(‘2017-11-30 23：59：59’，‘2017-12-15’) 的返回值为 -15。

21、DATE_FORMAT 格式化指定的日期函数

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 位数形式表示年份
%% %一个文字字符 
mysql> SELECT DATE_FORMAT('2017-11-15 21:45:00','%W %M %D %Y') AS col1,
    -> DATE_FORMAT('2017-11-15 21:45:00','%h:i% %p %M %D %Y') AS col2;
+------------------------------+----------------------------+
| col1                         | col2                       |
+------------------------------+----------------------------+
| Wednesday November 15th 2017 | 09:i PM November 15th 2017 |
+------------------------------+----------------------------+
1 row in set (0.03 sec)

由运行结果可以看到，“2017-11-15 21：45：00”分别按照不同的参数已转换为不同格式的日期值和时间值。

21、WEEKDAY 一周内的索引位置函数

WEEKDAY函数获取指定日期在一周内的索引位置。WEEKDAY(d) 返回 d 对应的工作日索引。0 表示周一，1 表示周二，……，6 表示周日。

mysql> SELECT WEEKDAY('2017-12-15');
+-----------------------+
| WEEKDAY('2017-12-15') |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)

由运行结果可以看出，WEEKDAY() 和 DAYOFWEEK() 函数都是返回指定日期在某一周内的位置，只是索引编号不同。

22、HOUR(time) 返回时间的小时

返回时间的小时。 对于一天时间值，返回值的范围是0到23。 但是，TIME值的范围实际上要大得多，所以HOUR可以返回大于23的值。

SELECT hour('12:13:14');                  # 12
SELECT hour('122:13:14');                 # 122
SELECT hour('12-13-14');                  # 0
SELECT hour('2008-09-10 12:13:14');       # 12
SELECT hour('2008-09-10 122:13:14');      # null
SELECT hour('2008-09-10 12-13-14');       # 12

23、MINUTE(time) 返回时间的分钟

返回时间分钟，范围为0到59。

SELECT minute('12:13:14');                # 13
SELECT minute('12:60:14');                # null
SELECT minute('12-13-14');                # 0
SELECT minute('2008-09-10 12:13:14');     # 13
SELECT minute('2008-09-10 122:13:14');    # null
SELECT minute('2008-09-10 12-13-14');     # 13

24、SECOND(time) 返回时间秒数

返回时间秒数，范围为0到59。

SELECT second('12:13:14');                # 14
SELECT second('12:13:60');                # null
SELECT second('12-13-14');                # 12
SELECT second('2008-09-10 12:13:14');     # 14
SELECT second('2008-09-10 122:13:14');    # null
SELECT second('2008-09-10 12-13-14');     # 14
0
mysql函数
版权归属： 孔子说JAVA
本文链接： https://www.kongzid.com/archives/mysql15
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区