流行的 MySQL 数据库系统的新手和有经验的用户通常会对数据库如何处理时间值感到困惑。有时，用户不愿学习有关时间值数据类型的更多知识。这可能是因为他们认为没有太多需要了解的。日期就是日期，对吧？好吧，并不总是这样。花几分钟时间学习 MySQL 如何存储和显示日期和时间是有益的。学习如何最好地利用数据库表中的时间值可以帮助你成为一个更好的程序员。
MySQL 时间数据类型
在 MySQL 中构建表时，你需要选择最有效地保存你打算插入表中的数据的数据类型 (INT, FLOAT, CHAR 等)。 MySQL 为你提供了五种时间值的数据类型。它们是：DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。
MySQL 使用 ISO 8601 格式以以下格式存储值
- DATE YYYY-MM-DD
- TIME HH:MM:SS
- TIMESTAMP YYYY-MM-DD HH:MM:SS
- YEAR YYYY
Datetime 与 Timestamp 的比较
你可能已经注意到 DATETIME 和 TIMESTAMP 数据类型保存相同的数据。你可能想知道两者之间是否有任何区别。的确有区别。
首先，可以使用的日期范围不同。 DATETIME 可以保存 1000-01-01 00:00:00 和 9999-12-31 23:59:59 之间的日期，而 TIMESTAMP 的范围要有限得多，从 1970-01-01 00:00:01 到 2038-01-19 03:14:07 UTC。
其次，虽然两种数据类型都允许你分别 auto_initialize 或 auto_update 它们各自的值（分别使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP），但直到 5.6.5 版本才可用于 DATETIME 值。 你可以选择使用 CURRENT_TIMESTAMP 的 MySQL 同义词之一，例如 NOW() 或 LOCALTIME()。
[ 立即下载：MariaDB 和 MySQL 速查表 ]
如果对 DATETIME 值使用 ON UPDATE CURENT_TIMESTAMP（或其同义词之一），但不使用 DEFAULT CURRENT_TIMESTAMP 子句，则该列将默认为 NULL。 除非你在表定义中包含 NOT NULL，否则会发生这种情况，在这种情况下，它默认为零。
另一个需要记住的重要事项是，虽然通常 DATETIME 和 TIMESTAMP 列都没有默认值，除非你声明一个，但此规则有一个例外。 如果未指定 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句，并且变量 explicit_defaults_for_timestamp 已禁用，则表中的第一列 TIMESTAMP 列将隐式创建 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句。
要检查此变量的状态，请运行
mysql> show variables like 'explicit_default%';如果要打开或关闭它，请运行此代码，使用 0 表示关闭，1 表示打开
mysql> set explicit_defaults_for_timestamp = 0;
时间
MySQL 的 TIME 数据类型看起来可能很简单，但一个优秀的程序员应该记住一些事项。
首先，请注意，尽管时间通常被认为是每天的时间，但实际上它是经过的时间。换句话说，它可以是负值，也可以大于 23:59:59。 MySQL 中的 TIME 值可以在 -838:59:59 到 838:59:59 的范围内。
此外，如果缩写时间值，MySQL 会根据是否使用冒号以不同的方式解释它。例如，MySQL 将值 10:34 视为 10:34:00。 也就是说，十点过 34 分。 但是如果你省略冒号，1034'，MySQL 会将其视为 00:10:34。 也就是说，十分 34 秒。
最后，你应该知道，从 5.6.4 版开始，TIME 值（以及 DATETIME 和 TIMESTAMP 列的时间部分）可以采用小数单位。 要使用它，请在数据类型定义的末尾添加一个整数（最大值为 6），并用括号括起来。
time_column TIME(2)时区
时区变化不仅会在现实世界中引起混乱和疲劳，而且还已知会在数据库系统中引起问题。 地球分为 24 个不同的时区，通常每经度 15 度变化一次。 我说通常是因为一些国家选择以不同的方式做事。 例如，中国在一个时区内运行，而不是预期的五个时区。
问题是，如何处理位于不同时区的数据库系统的用户。 幸运的是，MySQL 并没有使这太困难。
要检查你的会话时区，请运行
mysql> select @@session.time_zone;如果显示 System，则表示它使用的是在 my.cnf 配置文件中设置的时区。 如果你在本地计算机上运行 MsSQL 服务器，这很可能是你得到的，你无需进行任何更改。
如果你想更改会话的时区，请运行以下命令，例如
mysql> set time_zone = '-05:00';这将你的时区设置为比 UTC 晚五个小时。（美国/东部）。
获取星期几
为了配合本教程其余部分的代码，你应该在你的系统上创建一个包含日期值的表。 例如
mysql> create table test
( row_id smallint not null auto_increment primary key,
the_date date not null);然后使用 ISO 8601 格式将一些随机日期插入到表中，例如
mysql> insert into test (the_date) VALUES ('2022-01-05');
我在我的测试表中放置了四行日期值，但是你可以根据需要放置尽可能少的行。
有时你可能想知道特定日期是星期几。 MySQL 为你提供了一些选项。
第一个，也许是最明显的方法是使用 DAYNAME() 函数。 使用示例表，DAYNAME() 会告诉你每个日期的星期几
mysql> SELECT the_date, DAYNAME(the_date) FROM test ;
+------------+-------------------------------+
| the_date | DAYNAME(the_date) |
+------------+-------------------------------+
| 2021-11-02 | Tuesday |
| 2022-01-05 | Wednesday |
| 2022-05-03 | Tuesday |
| 2023-01-13 | Friday |
+------------+-------------------------------+
4 rows in set (0.00 sec)
获取星期几的另外两种方法返回整数值，而不是星期几的名称。 它们是 WEEKDAY() 和 DAYOFWEEK()。 它们都返回数字，但它们不返回相同的数字。 WEEKDAY() 函数返回一个从 0 到 6 的数字，其中 0 是星期一，6 是星期日。 另一方面，DAYOFWEEK() 返回一个从 1 到 7 的数字，其中 1 是星期日，7 是星期六。
mysql> SELECT the_date, DAYNAME(the_date),
WEEKDAY(the_date), DAYOFWEEK(the_date) FROM test;
+------------+------------------+------------------+--------------------+
| the_date | DAYNAME(the_date)| WEEKDAY(the_date)| DAYOFWEEK(the_date)|
| 2021-11-02 | Tuesday | 1 | 3 |
| 2022-01-05 | Wednesday | 2 | 4 |
| 2022-05-03 | Tuesday | 1 | 3 |
| 2023-01-13 | Friday | 4 | 6 |
+------------+------------------+------------------+--------------------+
4 rows in set (0.00 sec)当你只需要日期的一部分时
有时你可能在 MySQL 表中存储了一个日期，但你只想访问日期的一部分。 这没问题。
MySQL 中有几个方便命名的函数，可以轻松访问日期对象的特定部分。 为了展示几个示例
mysql> SELECT the_date, YEAR(the_date), MONTHNAME(the_date),
DAYOFMONTH(the_date) FROM test ;
+-----------+---------------+-------------------+---------------------+
| the_date | YEAR(the_date)|MONTHNAME(the_date)| DAYOFMONTH(the_date)|
+-----------+---------------+-------------------+---------------------+
| 2021-11-02| 2021 | November | 2 |
| 2022-01-05| 2022 | January | 5 |
| 2022-05-03| 2022 | May | 3 |
| 2023-01-13| 2023 | January | 13 |
+-----------+---------------+-------------------+---------------------+
4 rows in set (0.00 sec)MySQL 还允许你使用 EXTRACT() 函数来访问日期的一部分。 你提供给该函数的参数是一个单位说明符（请确保它是单数）、FROM 和列名。 因此，要仅从我们的测试表中获取年份，你可以编写
mysql> SELECT EXTRACT(YEAR FROM the_date) FROM test;
+----------------------------------------------+
| EXTRACT(YEAR FROM the_date) |
+----------------------------------------------+
| 2021 |
| 2022 |
| 2022 |
| 2023 |
+----------------------------------------------+
4 rows in set (0.01 sec)使用不同的格式插入和读取日期
如前所述，MySQL 使用 ISO 8601 格式存储日期和时间值。 但是，如果你想以另一种方式存储日期和时间值，例如日期的 MM-DD-YYYY 格式，该怎么办？ 好吧，首先，不要尝试。 MySQL 以 8601 格式存储日期和时间，就这样。 不要试图改变这一点。 但是，这并不意味着你必须在将数据输入数据库之前将数据转换为该特定格式，或者你不能以所需的任何格式显示数据。
如果你想以非 ISO 方式格式化的日期输入到表中，则可以使用 STR_TO_DATE()。 第一个参数是要存储在数据库中的日期的字符串值。 第二个参数是格式化字符串，它使 MySQL 知道日期的组织方式。 让我们看一个简单的例子，然后我将更深入地研究这个奇怪的格式化字符串是怎么回事。
mysql> insert into test (the_date) values (str_to_date('January 13, 2023','%M %d, %Y'));
Query OK, 1 row affected (0.00 sec)你将格式化字符串放在引号中，并在每个特殊字符前面加上百分号。 上面代码中的格式序列告诉 MySQL 我的日期由完整的月份名称 (%M)、后跟两位数的日期 (%d)、然后是逗号、最后是四位数的年份 (%Y) 组成。 请注意，大小写很重要。
其他一些常用的格式化字符串字符是
%b缩写的月份名称（示例：Jan）%c数字月份（示例：1）%W日期名称（示例：星期六）%a缩写的日期名称（示例：Sat）%T24 小时制时间（示例：22:01:22）%r12 小时制时间，带 AM/PM（示例：10:01:22 PM）%y2 位数年份（示例：23）
请注意，对于 2 位数年份 (%y)，年份范围是 1970 年到 2069 年。 因此，70 到 99 之间的数字被假定为 20 世纪，而 00 到 69 之间的数字被假定为 21 世纪。
如果你的数据库中存储了一个日期，并且你想使用不同的格式显示它，则可以使用 DATE_FORMAT() 函数
mysql> SELECT DATE_FORMAT(the_date, '%W, %b. %d, %y') FROM test;
+-----------------------------------------+
| DATE_FORMAT(the_date, '%W, %b. %d, %y') |
+-----------------------------------------+
| Tuesday, Nov. 02, 21 |
| Wednesday, Jan. 05, 22 |
| Tuesday, May. 03, 22 |
| Friday, Jan. 13, 23 |
+-----------------------------------------+
4 rows in set (0.00 sec)结论
本教程应该为你提供一个有用的 MySQL 中日期和时间值概述。 我希望本文已经教会了你一些新知识，使你能够更好地控制和更深入地了解 MySQL 数据库如何处理时间值。
6 条评论