MySQL学习笔记_常用函数（三） - 日期函数

MySQL学习笔记_常用函数（三） - 日期函数

函数	功能
CURDATE()	获取当前日期
CURTIME()	获取当前时间
NOW()	获取当前日期和时间
UNIX_TIMESTAMP(date)	获取日期date的UNIX时间戳
FROM_UNIXTIME()	获取UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
MONTHNAME(date)	返回日期date的月份值
YEAR(date)	返回日期date的年份
HOUR(time)	返回时间time的小时值
MINUTE(time)	返回时间time的分钟值

1. 获取当前日期或时间的函数：

# 获取当前日期+时间：
mysql> SELECT
    -> NOW(),
    -> CURRENT_TIMESTAMP(),
    -> LOCALTIME(),
    -> SYSDATE();
+---------------------+---------------------+---------------------+---------------------+
| NOW()               | CURRENT_TIMESTAMP() | LOCALTIME()         | SYSDATE()           |
+---------------------+---------------------+---------------------+---------------------+
| 2020-09-11 10:23:23 | 2020-09-11 10:23:23 | 2020-09-11 10:23:23 | 2020-09-11 10:23:23 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.02 sec)

# 获取当前日期：
mysql> SELECT
    -> CURDATE(),
    -> CURRENT_DATE();
+------------+----------------+
| CURDATE()  | CURRENT_DATE() |
+------------+----------------+
| 2020-09-11 | 2020-09-11     |
+------------+----------------+
1 row in set (0.00 sec)

# 获取当前时间：
mysql> SELECT
    -> CURTIME(),
    -> CURRENT_TIME();
+-----------+----------------+
| CURTIME() | CURRENT_TIME() |
+-----------+----------------+
| 10:26:07  | 10:26:07       |
+-----------+----------------+
1 row in set (0.02 sec)

# UNIX_TIMESTAMP()函数默认获取当前时间的时间戳，
# FROM_UNIXTIME(timestamp)函数参数必须指定
mysql> select UNIX_TIMESTAMP(), UNIX_TIMESTAMP('1996-04-22 12:23:32'), FROM_UNIXTIME(1599794353);
+------------------+---------------------------------------+---------------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP('1996-04-22 12:23:32') | FROM_UNIXTIME(1599794353) |
+------------------+---------------------------------------+---------------------------+
|       1599794456 |                             830147012 | 2020-09-11 11:19:13       |
+------------------+---------------------------------------+---------------------------+
1 row in set (0.00 sec)

# 获取UTC时间和日期
mysql> SELECT 
    -> NOW(),
    -> UTC_DATE(),
    -> UTC_TIME();
+---------------------+------------+------------+
| NOW()               | UTC_DATE() | UTC_TIME() |
+---------------------+------------+------------+
| 2020-09-11 11:27:35 | 2020-09-11 | 03:27:35   |
+---------------------+------------+------------+
1 row in set (0.00 sec)

2. 获取日期和时间的各部分值

mysql> SELECT NOW(), YEAR(NOW()), QUARTER(NOW()), MONTH(NOW()), WEEK(NOW()), DAYOFMONTH(NOW()), DAYOFYEAR(NOW()), DAYOFWEEK(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
+---------------------+-------------+----------------+--------------+-------------+-------------------+------------------+------------------+-------------+---------------+---------------+
| NOW()               | YEAR(NOW()) | QUARTER(NOW()) | MONTH(NOW()) | WEEK(NOW()) | DAYOFMONTH(NOW()) | DAYOFYEAR(NOW()) | DAYOFWEEK(NOW()) | HOUR(NOW()) | MINUTE(NOW()) | SECOND(NOW()) |
+---------------------+-------------+----------------+--------------+-------------+-------------------+------------------+------------------+-------------+---------------+---------------+
| 2020-09-11 11:36:49 |        2020 |              3 |            9 |          36 |                11 |              255 |                6 |          11 |            36 |            49 |
+---------------------+-------------+----------------+--------------+-------------+-------------------+------------------+------------------+-------------+---------------+---------------+
1 row in set (0.00 sec)
# 注意DAYOFWEEK()函数：一周的周日为第一天周六为第七天。
mysql> SELECT MONTHNAME(NOW()),MONTH(NOW());
+------------------+--------------+
| MONTHNAME(NOW()) | MONTH(NOW()) |
+------------------+--------------+
| September        |            9 |
+------------------+--------------+
1 row in set (0.00 sec)
# 注意MONTHNAME()函数和MONTH()函数的返回值

1. 计算时间差

# DATEDIFF(date1,date2):计算date1-date2(天数)
# ADDDATE(date,n):计算date加上n天后的日期
# SUBDATE(date,n):计算date减去n天前的日期
mysql> SELECT
    -> DATEDIFF(NOW(),'1996-06-07'),
    -> ADDDATE('1997-06-06',26),
    -> SUBDATE('1996-06-7',8),
    -> NOW();
+------------------------------+--------------------------+------------------------+---------------------+
| DATEDIFF(NOW(),'1996-06-07') | ADDDATE('1997-06-06',26) | SUBDATE('1996-06-7',8) | NOW()               |
+------------------------------+--------------------------+------------------------+---------------------+
|                         8862 | 1997-07-02               | 1996-05-30             | 2020-09-11 11:55:19 |
+------------------------------+--------------------------+------------------------+---------------------+
1 row in set (0.00 sec)

4. 另外，时间计算函数 [ADDDATE|SUBDATE](d, INTERVAL expr type)可以灵活计算时间：

# d:初始时间
# expr:表达式,决定时间长度
# type:决定所操作的对象
mysql> SELECT
    -> CURDATE(),
    -> ADDDATE(CURDATE(), INTERVAL '2|2' YEAR_MONTH),
    -> SUBDATE(CURDATE(), INTERVAL '2]2' YEAR_MONTH);
+------------+-----------------------------------------------+-----------------------------------------------+
| CURDATE()  | ADDDATE(CURDATE(), INTERVAL '2|2' YEAR_MONTH) | SUBDATE(CURDATE(), INTERVAL '2]2' YEAR_MONTH) |
+------------+-----------------------------------------------+-----------------------------------------------+
| 2020-09-11 | 2022-11-11                                    | 2018-07-11                                    |
+------------+-----------------------------------------------+-----------------------------------------------+
1 row in set (0.00 sec)
# 参数type的值与expr表达式如下表：

type的值	含义	expr表达式
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
HOUR	小时	hh
MINUTE	分钟	mm
SECOND	秒	ss
YEAR_MONTH	年和月	YY与MM之间用任意符号隔开
DAY_HOUR	日和小时	DD和hh之间用任意符号隔开
DAY_MINUTE	日和分钟	DD和mm之间用任意符号隔开
DAY_SECOND	日和秒	DD和ss之间用任意符号隔开
HOUR_MINUTE	小时和分钟	hh和mm之间用任意符号隔开
HOUR_SECOND	小时和秒	hh和ss之间用任意符号隔开
MINUTE_SECOND	分钟和秒	mm和ss之间用任意符号隔开