文章目录
- 1. 获取当前日期或时间
- 问题
- 解决方案
- 讨论
- 2. 使用 `TIMESTAMP` 或 `DATETIME` 记录行修改时间
- 问题
- 解决方案
- 讨论
- 3. 提取日期或时间的组成部分
- 问题
- 解决方案
- 使用提取函数
- 使用格式化函数
- 4. 使用组成部分合成日期或时间
- 问题
- 解决方案
- 讨论
阅读本文前建议先阅读 【MySQL 使用秘籍】一网打尽 MySQL 时间和日期类型与相关操作函数（一）
1. 获取当前日期或时间
问题
你希望获取当前日期或时间。
解决方案
在 MySQL 中，函数 CURDATE() ， CURTIME() 或 NOW() 分别可以获取客户端所在时区的当前日期，时间以及日期和时间。函数 UTC_DATE() ，UTC_TIME() 以及 UTC_TIMESTAMP() 分别可以获取 UTC 日期，时间以及日期和时间。
讨论
mysql> SELECT CURDATE(), CURTIME(), NOW();
+------------+-----------+---------------------+
| CURDATE() | CURTIME() | NOW() |
+------------+-----------+---------------------+
| 2022-05-30 | 23:39:25 | 2022-05-30 23:39:25 |
+------------+-----------+---------------------+
1 row in set (0.01 sec)实际上，CURRENT_DATE ， CURRENT_TIME 以及 CURRENT_TIMESTAMP 分别和函数 CURDATE() ， CURTIME() 或 NOW() 的有相同功能：
mysql> SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
+--------------+--------------+---------------------+
| CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP |
+--------------+--------------+---------------------+
| 2022-05-30 | 23:43:00 | 2022-05-30 23:43:00 |
+--------------+--------------+---------------------+
1 row in set (0.00 sec)2. 使用 TIMESTAMP 或 DATETIME 记录行修改时间
问题
你希望实现自动记录数据行的创建和修改时间。
解决方案
你可以使用 TIMESTAMP 和 DATETIME 两种数据类型自动初始化以及自动更新的性质。
MySQL 中提供 TIMESTAMP 和 DATETIME 两种数据类型，可用来记录日期和时间值。在【MySQL 使用秘籍】一网打尽 MySQL 时间和日期类型与相关操作函数（一）中，已经介绍了这两种数据类型可以表示的数据范围。这里将继续介绍使用这两种数据类型的字段所具有的特殊性质，即对于使用 TIMESTAMP 和 DATETIME 两种数据类型的字段：
- 如果同时为该字段声明了
DEFAULT CURRENT_TIMESTAMP性质，那么对于新的数据行，在INSERT语句中如果直接省略该字段插入的值，那么记录插入成功后该字段将默认被设置为数据行的创建日期和时间； - 如果同时为该字段声明了
ON UPDATE CURRENT_TIMESTAMP性质，那么对于该字段所在的数据行，如果更新其中任何字段后，那么该字段最终都将被更新为当前日期和时间。
TIMESTAMP 和 DATETIME 两种数据类型的上述性质，使之非常适用于需要记录数据行的创建和修改日期和时间的场景。下面将以 TIMESTAMP 类型的字段为例，进行详细介绍，而对于 DATETIME 也非常类似，而且在涉及二者的区别时，将额外指出。
首先，使用下列 SQL 语句创建示例数据表：
CREATE TABLE tsdemo
(
val INT,
ts_both TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ts_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_update TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);使用上述建表语句创建的数据表中虽然有三个字段都是 TIMESTAMP 类型，但是每个字段所具有的性质又有不同：
-
ts_both字段既可以实现自动初始化又可以实现自动更新数据行。因此该字段可以记录对数据行进行任意修改的操作，即包括插入和更新； -
ts_create只会实现自动初始化数据行。因此该字段可用于记录数据行的创建时间，并自始至终都保持不变； -
ts_update只会实现自动更新数据行。这个特点一般很少单独使用。
下面通过先向表中插入数据，接着查询后观察各个字段的取值情况：
mysql> INSERT INTO tsdemo (val) VALUES(5);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO tsdemo (val,ts_both,ts_create,ts_update)
-> VALUES(10,NULL,NULL,NULL);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM tsdemo;
+------+---------------------+---------------------+-----------+
| val | ts_both | ts_create | ts_update |
+------+---------------------+---------------------+-----------+
| 5 | 2022-06-04 16:52:26 | 2022-06-04 16:52:26 | NULL |
| 10 | NULL | NULL | NULL |
+------+---------------------+---------------------+-----------+
2 rows in set (0.01 sec)分析上述结果，可知：
- 第一条 SQL 语句表明，对于支持自动初始化的字段，你可以在
INSERT语句中省略其对应插入的值，这些字段也会被自动设置为当前日期和时间，而对于仅支持自动更新的字段，其值将会被设置为NULL； - 第二条 SQL 语句表明，不论对于支持自动初始化还是自动更新的字段，当你将其显式地设置为 NULL 时，最终所有
TIMESTAMP类型的字段都会被设置为NULL。
为了验证字段 ts_both 和 ts_update 的确可以在对应数据行发生改变时自动更新该字段的日期和时间，可通过下列 SQL 语句来实现：
mysql> SELECT * FROM tsdemo;
+------+---------------------+---------------------+---------------------+
| val | ts_both | ts_create | ts_update |
+------+---------------------+---------------------+---------------------+
| 5 | 2022-06-04 16:52:26 | 2022-06-04 16:52:26 | NULL |
| 11 | 2022-06-04 17:02:50 | NULL | 2022-06-04 17:02:50 |
+------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)如果你同时修改多条记录的字段，那么上述现象将会在修改成功的数据行中都得以体现：
mysql> UPDATE tsdemo SET val = val + 1;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT * FROM tsdemo;
+------+---------------------+---------------------+---------------------+
| val | ts_both | ts_create | ts_update |
+------+---------------------+---------------------+---------------------+
| 6 | 2022-06-04 17:05:48 | 2022-06-04 16:52:26 | 2022-06-04 17:05:48 |
| 12 | 2022-06-04 17:05:48 | NULL | 2022-06-04 17:05:48 |
+------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)需要注意的是，如果一条 UPDATE 语句实际最终没有修改数据行的任何字段，那么具有自动更新日期和日期形式的字段也不会有任何修改，例如：
mysql> UPDATE tsdemo SET val = val;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql> SELECT * FROM tsdemo;
+------+---------------------+---------------------+---------------------+
| val | ts_both | ts_create | ts_update |
+------+---------------------+---------------------+---------------------+
| 6 | 2022-06-04 17:05:48 | 2022-06-04 16:52:26 | 2022-06-04 17:05:48 |
| 12 | 2022-06-04 17:05:48 | NULL | 2022-06-04 17:05:48 |
+------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)讨论
如前所述，上述 TIMESTAMP 类型所具有的自动初始化和自动更新的性质也适用于 DATETIME 类型的字段，只不过二者有如下几点差异：
- 对于
TIMESTAMP类型字段，即使建表时没有显式指定DEFAULT和ON UPDATE性质，服务器也会默认指定。对于DATETIME数据类型，除非显式指定，否则对应字段永远都不会自动初始化或更新。如果你希望关闭对于TIMESTAMP类型字段默认指定DEFAULT和ON UPDATE的性质，可以通过将系统变量explicit_defaults_for_timestamp设置为ON来实现；
mysql> DROP TABLE tsdemo;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE tsdemo
-> (
-> val INT,
-> ts TIMESTAMP,
-> dt DATETIME
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO tsdemo (val) VALUES(5);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM tsdemo;
+------+---------------------+------+
| val | ts | dt |
+------+---------------------+------+
| 5 | 2022-06-04 17:48:28 | NULL |
+------+---------------------+------+
1 row in set (0.00 sec)
mysql> UPDATE tsdemo SET val = val + 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM tsdemo;
+------+---------------------+------+
| val | ts | dt |
+------+---------------------+------+
| 6 | 2022-06-04 17:49:23 | NULL |
+------+---------------------+------+
1 row in set (0.00 sec)3. 提取日期或时间的组成部分
问题
你希望仅获取日期或时间的一部分。
解决方案
针对此需求有两种类型的方式来实现：
- 最快的方式是使用专门的提取函数，如：
MONTH()、MINUTE()； - 使用格式化函数
DATE_FORMAT()或TIME_FORMAT()时指定对应日期或时间部分的标识符。
使用提取函数
MySQL 中提供了许多函数，这些函数可用于提取日期和（或）时间的一部分。例如：函数 DATE() 和 TIME() 可分别用于提取日期和时间部分。
mysql> SELECT dt, DATE(dt), TIME(dt) FROM datetime_val;
+---------------------+------------+----------+
| dt | DATE(dt) | TIME(dt) |
+---------------------+------------+----------+
| 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 |
| 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 |
| 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 |
| 2017-03-16 12:30:15 | 2017-03-16 | 12:30:15 |
+---------------------+------------+----------+
4 rows in set (0.01 sec)下表是 MySQL 提供的部分提取函数，其中：和日期相关的函数适用于 DATE ，DATETIME 或 TIMESTAMP 数据类型；和时间相关的函数适用于 TIME ，DATETIME 或 TIMESTAMP 数据类型。
函数 | 返回值 |
| 日期中的年份部分 |
| 数字形式的月份（ |
| 月份名称（ |
| 数字形式的日（ |
| 星期全称（ |
| 数据形式的星期（ |
| 数据形式的星期（ |
| 一年中的天数（ |
| 数字形式的小时部分（ |
| 数字形式的分钟部分（ |
| 数字形式的秒数部分（ |
| 不定 |
mysql> SELECT dt, YEAR(dt), DAYOFMONTH(dt), HOUR(dt), SECOND(dt)
-> FROM datetime_val;
+---------------------+----------+----------------+----------+------------+
| dt | YEAR(dt) | DAYOFMONTH(dt) | HOUR(dt) | SECOND(dt) |
+---------------------+----------+----------------+----------+------------+
| 1970-01-01 00:00:00 | 1970 | 1 | 0 | 0 |
| 1999-12-31 09:00:00 | 1999 | 31 | 9 | 0 |
| 2000-06-04 15:45:30 | 2000 | 4 | 15 | 30 |
| 2017-03-16 12:30:15 | 2017 | 16 | 12 | 15 |
+---------------------+----------+----------------+----------+------------+
4 rows in set (0.01 sec)此外，对于上表的各个函数，还需要注意以下两点：
- 函数
DAYNAME()将返回完整的星期名称。MySQL 中并不直接提供用于返回三个字符缩写形式的星期名称，但你可以结合使用LEFT()函数来实现：
mysql> SELECT d, DAYNAME(d), LEFT(DAYNAME(d),3) FROM date_val;
+------------+------------+--------------------+
| d | DAYNAME(d) | LEFT(DAYNAME(d),3) |
+------------+------------+--------------------+
| 1864-02-28 | Sunday | Sun |
| 1900-01-15 | Monday | Mon |
| 1999-12-31 | Friday | Fri |
| 2000-06-04 | Sunday | Sun |
| 2017-03-16 | Thursday | Thu |
+------------+------------+--------------------+
5 rows in set (0.02 sec)- 如果希望获取星期名称的数字形式，可以使用
DAYOFWEEK()或WEEKDAY()函数，但需要特别注意二者的返回值范围。函数DAYOFWEEK()的返回值是到
，代表从周日到周六；函数
WEEKDAY()的返回值是到
mysql> SELECT d, DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d) FROM date_val;
+------------+------------+--------------+------------+
| d | DAYNAME(d) | DAYOFWEEK(d) | WEEKDAY(d) |
+------------+------------+--------------+------------+
| 1864-02-28 | Sunday | 1 | 6 |
| 1900-01-15 | Monday | 2 | 0 |
| 1999-12-31 | Friday | 6 | 4 |
| 2000-06-04 | Sunday | 1 | 6 |
| 2017-03-16 | Thursday | 5 | 3 |
+------------+------------+--------------+------------+
5 rows in set (0.00 sec)上述表格中，函数 EXTRACT() 的功能更加通用和灵活：
mysql> SELECT dt, EXTRACT(DAY FROM dt), EXTRACT(HOUR FROM dt)
-> FROM datetime_val;
+---------------------+----------------------+-----------------------+
| dt | EXTRACT(DAY FROM dt) | EXTRACT(HOUR FROM dt) |
+---------------------+----------------------+-----------------------+
| 1970-01-01 00:00:00 | 1 | 0 |
| 1999-12-31 09:00:00 | 31 | 9 |
| 2000-06-04 15:45:30 | 4 | 15 |
| 2017-03-16 12:30:15 | 16 | 12 |
+---------------------+----------------------+-----------------------+
4 rows in set (0.00 sec)该函数中用于指定期望获取的日期或时间部分的关键字可以是 YEAR ，MONTH ，DAY ，HOUR ，MINUTE 或 SECOND 。
如果需要获取当前日期的年份，月份或星期名等信息，可使用上述各提取函数并结合函数 CURDATE() 或 NOW() 来实现：
mysql> SELECT CURDATE(), YEAR(CURDATE()) AS year,
-> MONTH(CURDATE()) AS month, MONTHNAME(CURDATE()) AS monthname,
-> DAYOFMONTH(CURDATE()) AS day, DAYNAME(CURDATE()) AS dayname;
+------------+------+-------+-----------+------+----------+
| CURDATE() | year | month | monthname | day | dayname |
+------------+------+-------+-----------+------+----------+
| 2022-06-04 | 2022 | 6 | June | 4 | Saturday |
+------------+------+-------+-----------+------+----------+
1 row in set (0.00 sec)同理，如果要获取当前时间的小时，分钟或秒数等信息，可使用上述各提取函数并结合函数 CURTIME() 或 NOW() 来实现：
mysql> SELECT NOW(), HOUR(NOW()) AS hour,
-> MINUTE(NOW()) AS minute, SECOND(NOW()) AS second;
+---------------------+------+--------+--------+
| NOW() | hour | minute | second |
+---------------------+------+--------+--------+
| 2022-06-04 19:58:14 | 19 | 58 | 14 |
+---------------------+------+--------+--------+
1 row in set (0.00 sec)使用格式化函数
函数 DATE_FORMAT() 和 TIME_FORMAT() 可用于重新格式化日期或时间值。通过指定恰当的格式限定符，可以仅提取日期或时间中的某组成部分：
mysql> SELECT dt,
-> DATE_FORMAT(dt,'%Y') AS year,
-> DATE_FORMAT(dt,'%d') AS day,
-> TIME_FORMAT(dt,'%H') AS hour,
-> TIME_FORMAT(dt,'%s') AS second
-> FROM datetime_val;
+---------------------+------+------+------+--------+
| dt | year | day | hour | second |
+---------------------+------+------+------+--------+
| 1970-01-01 00:00:00 | 1970 | 01 | 00 | 00 |
| 1999-12-31 09:00:00 | 1999 | 31 | 09 | 00 |
| 2000-06-04 15:45:30 | 2000 | 04 | 15 | 30 |
| 2017-03-16 12:30:15 | 2017 | 16 | 12 | 15 |
+---------------------+------+------+------+--------+
4 rows in set (0.00 sec)当你希望一次性提取日期或时间的多个组成部分，或者希望以不同于默认格式的方式来展示提取出的值的时候，格式化函数会显得更加便捷。例如，如果希望从 DATETIME 类型的值中提取整个日期或时间部分，可以使用类似下列的方式：
mysql> SELECT dt,
-> DATE_FORMAT(dt,'%Y-%m-%d') AS 'date part'
-> TIME_FORMAT(dt,'%T') AS 'time part'
-> FROM datetime_val;
+---------------------+------------+-----------+
| dt | date part | time part |
+---------------------+------------+-----------+
| 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 |
| 1999-12-31 09:00:00 | 1999-12-31 | 09:00:00 |
| 2000-06-04 15:45:30 | 2000-06-04 | 15:45:30 |
| 2017-03-16 12:30:15 | 2017-03-16 | 12:30:15 |
+---------------------+------------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT dt,
-> DATE_FORMAT(dt,'%M %e, %Y') AS 'descriptive date',
-> TIME_FORMAT(dt,'%H:%i') AS 'hours/minutes'
-> FROM datetime_val;
+---------------------+-------------------+---------------+
| dt | descriptive date | hours/minutes |
+---------------------+-------------------+---------------+
| 1970-01-01 00:00:00 | January 1, 1970 | 00:00 |
| 1999-12-31 09:00:00 | December 31, 1999 | 09:00 |
| 2000-06-04 15:45:30 | June 4, 2000 | 15:45 |
| 2017-03-16 12:30:15 | March 16, 2017 | 12:30 |
+---------------------+-------------------+---------------+
4 rows in set (0.00 sec)4. 使用组成部分合成日期或时间
问题
你希望通过将日期或时间的组成部分进行合并，进而得到完整的日期或时间值。或者你希望替换日期的组成部分来得到一个新的日期值。
解决方案
你可以使用一下几种方式来实现你的需求：
- 使用函数
MAKETIME()将小时，分钟以及秒数合并为TIME类型的值； - 使用格式化函数
DATE_FORMAT()或TIME_FORMAT()实现对日期或时间指定部分的替换； - 使用上述介绍的提取函数先得到日期或时间的各组成部分，然后使用
CONCAT()函数实现组合。
讨论
首先是 MAKETIME() 函数的使用方式：
mysql> SELECT MAKETIME(10,30,58), MAKETIME(-5,0,11);
+--------------------+-------------------+
| MAKETIME(10,30,58) | MAKETIME(-5,0,11) |
+--------------------+-------------------+
| 10:30:58 | -05:00:11 |
+--------------------+-------------------+
1 row in set (0.00 sec)接着是使用格式化函数的方式，日期合成通常都是基于给定的日期，一方面保留希望使用，另一方面替换其他部分。例如，假设你需要得到给定日期所在月份的第一天对应的日期，你可以按照类似下列的方式使用 DATE_FORMAT() 函数：
mysql> SELECT d, DATE_FORMAT(d,'%Y-%m-01') FROM date_val;
+------------+---------------------------+
| d | DATE_FORMAT(d,'%Y-%m-01') |
+------------+---------------------------+
| 1864-02-28 | 1864-02-01 |
| 1900-01-15 | 1900-01-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 |
| 2017-03-16 | 2017-03-01 |
+------------+---------------------------+
5 rows in set (0.00 sec)同理，函数 TIME_FORMAT() 也支持类似操作：
mysql> SELECT t1, TIME_FORMAT(t1,'%H:%i:00') FROM time_val;
+----------+----------------------------+
| t1 | TIME_FORMAT(t1,'%H:%i:00') |
+----------+----------------------------+
| 15:00:00 | 15:00:00 |
| 05:01:30 | 05:01:00 |
| 12:30:20 | 12:30:00 |
+----------+----------------------------+
3 rows in set (0.01 sec)最后是结合提取函数和字符串拼接函数 CONCAT() 的方式：
mysql> SELECT d, CONCAT(YEAR(d),'-',MONTH(d),'-01') FROM date_val;
+------------+------------------------------------+
| d | CONCAT(YEAR(d),'-',MONTH(d),'-01') |
+------------+------------------------------------+
| 1864-02-28 | 1864-2-01 |
| 1900-01-15 | 1900-1-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-6-01 |
| 2017-03-16 | 2017-3-01 |
+------------+------------------------------------+
5 rows in set (0.00 sec)可以注意到，上述有一些月份只有一位数字，为了确保所有月份都有两位数字（这也是 ISO 规范中定义的标准格式），可以使用 LPAD() 函数实现按需填充 ：
mysql> SELECT d, CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01')
-> FROM date_val;
+------------+------------------------------------------------+
| d | CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01') |
+------------+------------------------------------------------+
| 1864-02-28 | 1864-02-01 |
| 1900-01-15 | 1900-01-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 |
| 2017-03-16 | 2017-03-01 |
+------------+------------------------------------------------+
5 rows in set (0.00 sec)同理，对于时间的合并也是类似的：
mysql> SELECT t1,
-> CONCAT(LPAD(HOUR(t1),2,'0'),':',LPAD(MINUTE(t1),2,'0'),':00')
-> FROM time_val;
+----------+---------------------------------------------------------------+
| t1 | CONCAT(LPAD(HOUR(t1),2,'0'),':',LPAD(MINUTE(t1),2,'0'),':00') |
+----------+---------------------------------------------------------------+
| 15:00:00 | 15:00:00 |
| 05:01:30 | 05:01:00 |
| 12:30:20 | 12:30:00 |
+----------+---------------------------------------------------------------+
3 rows in set (0.00 sec)如果希望通过日期和时间合并后得到日期时间值，可以使用一个空格将二者连接起来：
mysql> SET @d = '2022-06-04', @t = '20:45:00';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @d, @t, CONCAT(@d,' ',@t);
+------------+----------+---------------------+
| @d | @t | CONCAT(@d,' ',@t) |
+------------+----------+---------------------+
| 2022-06-04 | 20:45:00 | 2022-06-04 20:45:00 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
