在 MySQL 中处理时间

2023-05-02

7分钟阅读时长

这篇关于 MySQL 中日期和时间的概述将帮助你在数据库表中处理时间值。

流行数据库系统 MySQL 的新老用户常常会对数据库处理时间值的方式感到困惑。有时用户不会费心去了解时间值的数据类型。这可能是因为他们觉得本身也没有什么好了解的。日期就是日期，对吧？好吧，并非总是如此。花几分钟时间了解 MySQL 如何存储和显示日期和时间是有益的。学习如何最好地利用数据库表中的时间值可以帮助你成为更好的编码者。

MySQL 时间值类型

当你在 MySQL 中新建表时，选择合适的数据类型（INT、FLOAT、CHAR 等）高效地保存插入到表中的数据。MySQL 为时间值提供了五种数据类型。它们是 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。

MySQL 使用 ISO 8601 格式来存储以下格式的值（LCTT 译注：国际标准 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 值不能这样操作。如果你要用 DATETIME，你可以使用 CURRENT_TIMESTAMP 的 MySQL 同义词之一，例如 NOW() 或 LOCALTIME()。

如果你对一个 DATETIME 值使用 ON UPDATE CURENT_TIMESTAMP（或其同义词之一），但没有使用 DEFAULT CURRENT_TIMESTAMP 子句，那么这个列的默认值为 NULL。除非你在表的定义中包含 NOT NULL，在这种情况下，它默认为 0。

另一件需要记住的重要事情是，尽管通常情况下，除非你声明一个默认值，否则 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;

TIME

MySQL 的时间 TIME 数据类型可能看起来很简单，但有几件事是一个优秀的程序员应该牢记的。

首先要注意的是，虽然 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，意思是 10 分钟 34 秒。

最后，你应该知道 TIME 值（以及 DATETIME 和 TIMESTAMP 字段的时间部分）从 5.6.4 版本开始，可以取一个小数部分。要使用它，请在数据类型定义的结尾处添加一个整数（最大值为 6）的圆括号。

time_column TIME(2)

时区

时区变化不仅在现实世界中产生混乱和疲劳，而且也会在数据库系统中制造麻烦。地球被划分为 24 个独立的时区，通常每隔 15 度经度就会发生变化。我说通常是因为一些国家行事方式不同。例如中国只在一个时区运作，而不是预期的五个时区。

你如何处理处于不同时区的数据库系统的用户就成了一个问题。幸运的是，MySQL 并没有使这个问题变得太困难。

要检查你的会话时区，请运行：

mysql> select @@session.time_zone;

如果结果显示 System，这意味着它正在使用你的 my.cnf 配置文件中设置的时区。如果你在本地计算机上运行你的 MySQL 服务器，这可能就是你会得到的，你不需要做任何改变。

如果你想改变你的会话的时区，请运行如下命令：

mysql> set time_zone = '-05:00';

这将你的时区设置为美国/东部 US/Eastern ，比协调世界时 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');

我在我的 test 表中插入了四行日期值，你插入多少行都可以。

有时你可能想知道某一天是星期几。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 和列名。因此，为了从我们的 test 表中仅获取年份，你可以写：

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）
%T 24 小时制的时间（例如: 22:01:22）
%r 带 AM/PM 的 12 小时制的时间（例如: 10:01:22 PM）
%y 两位数的年份（例如: 23）

请注意，对于两位数年份 %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 数据库如何处理时间值。

（题图：MJ/76b6481a-a271-4e81-bc17-dd7fbe08a240）

via: https://opensource.com/article/23/2/temporal-values-mysql

作者：Hunter Coleman 选题：lkxed 译者：hanszhao80 校对：wxy

本文由 LCTT 原创编译，Linux中国荣誉推出