流行的 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），但对于 DATETIME 值，直到 5.6.5 版本才可用。如果需要，你可以使用 MySQL 中 CURRENT_TIMESTAMP 的同义词，例如 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 列会被隐式地创建为同时具有这两个子句。
要检查此变量的状态，请运行
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 会根据你是否使用冒号来不同地解释它。例如，值 10:34 被 MySQL 视为 10:34:00。也就是说，十点过 34 分。但是如果你省略冒号，写成 1034，MySQL 会将其视为 00:10:34。也就是说，十分 34 秒。
最后，你应该知道，从 5.6.4 版本开始，TIME 值（以及 DATETIME 和 TIMESTAMP 列的时间部分）可以采用小数单位。要使用它，请在数据类型定义的末尾添加一个括在括号中的整数（最大值为六）。
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星期几的名称（示例：Saturday）%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 条评论