MySQL日期和时间相关函数

2020-04-23 本文已影响0人月饮沙

由于日期时间的格式化及变更涉及知识点较多，不包含在本文中，会单独描写。

本文问题

如何获取当前的日期、时间、日期和时间？
now()和sysdate()函数的区别和联系？
如何获取一个日期时间的日期部分（年月日等）?
如何获取一个日期时间的时间部分（时分秒等）？
如何查看一个日期是星期几？
如何查看一个日期是本年/月/周中的第几天？
如何进行时间与秒数的转换？

日期和时间相关函数

基本规则

下方函数说明中：date表示日期，time表示时间，fsp表示精度
对于要求输入为time的函数，如果提供了DATETIME，自动截取时间部分，对于要求输入为date的函数，自动截取日期部分。

获取日期时间

获取当前日期及时间
- NOW([fsp]) CURRENT_TIMESTAMP() CURRENT_TIMESTAMP LOCALTIME() LOCALTIME LOCALTIMESTAMP() LOCALTIMESTAMP
- SYSDATE([fsp])

在同一个语句/存储过程中，多次调用now()的结果是相同的，但是多次调用SYSDATE()的结果可能不同

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

获取当前日期 CURDATE() CURRENT_DATE() CURRENT_DATE
获取当前时间 CUTTIME() CURRENT_TIME() CURRENT_TIME
获取日期所在月份的最后一天 LAST_DAY(date)
示例

mysql> select now(),curdate(),curtime(),last_day(now());
+---------------------+------------+-----------+-----------------+
| now()               | curdate()  | curtime() | last_day(now()) |
+---------------------+------------+-----------+-----------------+
| 2020-04-23 17:25:24 | 2020-04-23 | 17:25:24  | 2020-04-30      |
+---------------------+------------+-----------+-----------------+
1 row in set (0.00 sec)

获取UTC日期和时间

获取UTC日期 UTC_DATE, UTC_DATE()
获取UTC时间 UTC_TIME, UTC_TIME([fsp])
获取UTC日期和时间 UTC_TIMESTAMP, UTC_TIMESTAMP([fsp])

mysql> select utc_date,utc_time,utc_timestamp(6);
+------------+----------+----------------------------+
| utc_date   | utc_time | utc_timestamp(6)           |
+------------+----------+----------------------------+
| 2020-04-23 | 09:28:18 | 2020-04-23 09:28:18.277545 |
+------------+----------+----------------------------+
1 row in set (0.00 sec)

获取日期时间中的部分值

日期相关

日期 DATE(date)
年 YEAR(date)
月 MONTH(date)
日 DAY(date) DAYOFMONTH(date)

mysql> select now(6),date(now()),year(now()),month(now()),monthname(now()),day(now()),dayofmonth(now());
+----------------------------+-------------+-------------+--------------+------------------+------------+-------------------+
| now(6)                     | date(now()) | year(now()) | month(now()) | monthname(now()) | day(now()) | dayofmonth(now()) |
+----------------------------+-------------+-------------+--------------+------------------+------------+-------------------+
| 2020-04-23 15:46:00.041446 | 2020-04-23  |        2020 |            4 | April            |         23 |                23 |
+----------------------------+-------------+-------------+--------------+------------------+------------+-------------------+
1 row in set (0.00 sec)

时间相关

时间 TIME(time)
小时 HOUR(time)
分 MINUTE(time)
秒 SECOND(time)
微秒MICROSECOND(expr)

mysql> select now(6),time(now()),hour(now()),minute(now()),second(now()),microsecond(now(6));
+----------------------------+-------------+-------------+---------------+---------------+---------------------+
| now(6)                     | time(now()) | hour(now()) | minute(now()) | second(now()) | microsecond(now(6)) |
+----------------------------+-------------+-------------+---------------+---------------+---------------------+
| 2020-04-23 15:55:38.012470 | 15:55:38    |          15 |            55 |            38 |               12470 |
+----------------------------+-------------+-------------+---------------+---------------+---------------------+
1 row in set (0.00 sec)

季度

今年的第几季度 QUARTER()

mysql> select now(),quarter(now());
+---------------------+----------------+
| now()               | quarter(now()) |
+---------------------+----------------+
| 2020-04-23 17:15:05 |              2 |
+---------------------+----------------+
1 row in set (0.00 sec)

星期相关

星期名称 DAYNAME(date)
星期序号 WEEKDAY(date)
(0 = Monday, 1 = Tuesday, … 6 = Sunday).

mysql> select now(),dayname(now()),weekday(now());
+---------------------+----------------+----------------+
| now()               | dayname(now()) | weekday(now()) |
+---------------------+----------------+----------------+
| 2020-04-23 16:01:45 | Thursday       |              3 |
+---------------------+----------------+----------------+
1 row in set (0.00 sec)

日期序列相关

天数

今年的第几天 DAYOFYEAR(date)
本月的第几天 DAYOFMONTH(date)
本周的第几天 DAYOFWEEK(date)
(1 = Sunday, 2 = Monday, …, 7 = Saturday)

mysql> select now(),dayofweek(now()),dayofmonth(now()),dayofyear(now());
+---------------------+------------------+-------------------+------------------+
| now()               | dayofweek(now()) | dayofmonth(now()) | dayofyear(now()) |
+---------------------+------------------+-------------------+------------------+
| 2020-04-23 16:02:52 |                5 |                23 |              114 |
+---------------------+------------------+-------------------+------------------+
1 row in set (0.00 sec)

周数

根据每周的起始日期不同，对于同一个日期，相应的周数结果也可能不同，每周的起始日期可以使用default_week_format设置，也可以在使用函数时，通过mode独立指定。
mode和周起始日期的关系
mode为单数表示一周从周一开始，双数表示一周从周日开始。

`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	包含第一个周一

mysql> select week('20200101',0),week('20200101',2),week('20200101',4),week('20200101',6);
+--------------------+--------------------+--------------------+--------------------+
| week('20200101',0) | week('20200101',2) | week('20200101',4) | week('20200101',6) |
+--------------------+--------------------+--------------------+--------------------+
|                  0 |                 52 |                  1 |                  1 |
+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql>select week('20200101',1),week('20200101',3),week('20200101',5),week('20200101',7);
+--------------------+--------------------+--------------------+--------------------+
| week('20200101',1) | week('20200101',3) | week('20200101',5) | week('20200101',7) |
+--------------------+--------------------+--------------------+--------------------+
|                  1 |                  1 |                  0 |                 52 |
+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

今年的第几周 WEEK(date[,mode])
今年的第几周 WEEKOFYEAR(date)
等同于WEEK(date,3)
年+第几周 YEARWEEK(date[,mode])

日期时间转换

秒数转时间 SEC_TO_TIME(second)
时间转秒数 TIME_TO_SEC(time)

mysql> select sec_to_time(3612),time_to_sec(010012);
+-------------------+---------------------+
| sec_to_time(3612) | time_to_sec(010012) |
+-------------------+---------------------+
| 01:00:12          |                3612 |
+-------------------+---------------------+
1 row in set (0.00 sec)

问题答案

如何获取当前的日期、时间、日期和时间？
日期：current_date,current_date(),curdate()
时间：current_time,current_time(),curtime()
日期及时间：sysdate(),now()
UTC日期和时间：utc_timestamp,utc_date,utc_time
now()和sysdate()函数的区别和联系？
联系：都可以用来获取当前时间
区别： now()可以用来做DATETIME和TIMESTAMP列的自动更新值，sysdate()不可以。在同一个语句中，多次调用now()的结果相同，但是sysdate()结果不一定相同。
如何获取一个日期时间的日期部分（年月日等）?
日期：date()
年：year()
月：month(),monthname()
日：day(),dayofmonth()
季度：quarter()
如何获取一个日期时间的时间部分（时分秒等）？
时间：time()
时：hour()
分：minute()
秒：second()
微秒：microsecond()
如何查看一个日期是星期几？
dayname() 结果是英文全拼
dayofweek() 结果是数字，0=Mon...6=Sat
如何查看一个日期是本年/月/周中的第几天？
dayofyear(),dayofmonth(),dayofweek()
如何进行时间与秒数的转换？
时间转秒数 time_to_sec()
秒数转时间 sec_to_time()