【MySQL 使用秘籍】一网打尽 MySQL 时间和日期类型与相关操作函数（一）

最新推荐文章于 2024-10-01 12:47:02 发布

TakingCoding4Granted

最新推荐文章于 2024-10-01 12:47:02 发布

阅读量1k

点赞数 1

分类专栏： # MySQL Cookbook 文章标签： mysql 数据库 database 日期时间

本文链接：https://blog.csdn.net/weixin_37780776/article/details/125030762

版权

MySQL Cookbook 专栏收录该内容

16 篇文章 2 订阅

订阅专栏

文章目录

1. 引言
2. 选择合适数据类型
问题
讨论
解决方案

3. 使用精确到小数的时间
4. 修改 MySQL 的日期格式
问题
讨论
解决方案
日期数据的插入
日期数据的显示

5. 设置客户端所在时区
问题
解决方案
讨论

6. 转化不同时区的日期时间
问题
解决方案
讨论

1. 引言

MySQL 中有许多用来表示日期和时间的数据类型，同时还包含很多用于操作日期和时间的函数。MySQL 将日期和时间以各种格式保存在服务端，因此我们也有必要了解各种格式之间的转换，以选择符合预期的格式。本文主要从以下几个方面介绍了如何使用 MySQL 中和日期以及时间相关的数据类型和函数：

选择合适的数据类型

针对日期和时间，MySQL 中多种可选的数据类型可供创建数据表等时候进行选择。了解各种数据类型的性质和特点有助于你选择合适的类型。

显示日期和时间

MySQL 默认使用特定的格式显示日期和时间。如果你希望以其他格式进行显示，可以通过使用特定的函数并指定对应格式来实现。

修改客户端时区

MySQL 服务端会使用客户端所在时区来解析 TIMESTAMP 类型的值，而非使用服务端自身所在时区。因此，处于不同时区的客户端需要将其所在时区设置为期望的时区，以此来使得服务端可以正确地对其 TIMESTAMP 类型的值进行解析。

获取当前日期和时间

MySQL 中提供用于返回日期和时间的函数。对于那些必须知道这些信息或者需要据此计算相对于某日期或时间偏移特定时间长度的日期或时间的应用很有用。

记录行的修改时间

TIMESTAMP 和 DATETIME 两种数据类型都具有一个特别的性质，即自动更新记录行的创建和最后修改时间。

截取日期或时间的组成部分，或根据组成部分创建日期或时间

你可以将日期或时间分割成多个组成部分，然后只取你需要的部分。例如：日期中月份的部分，时间中的小时部分。相反，你也可以通过各种组成部分来合成具体的日期和时间。

实现日期或时间和基本计时单位之间的转换

有时候会遇到需要对进行日期或时间进行计算的场景，很多时候通过使用日期或时间对应的天数或秒数进行计算会更加简单。因此，MySQL 提供了支持，使得在日期或时间和天数或秒数之间的转换变得很简单。

日期或时间运算

你可以对日期或时间进行运算，以此来得到二者之间的差值或和等，这样的应用场景包括确定年龄，相对日期计算以及日期偏移等。

2. 选择合适数据类型

问题

你需要存日期或时间类型的数据，但是不知道使用哪一个具体类型最合适。

讨论

想要选择合适数据类型，你需要综合考虑待保存数据的特点以及你希望如何使用这些数据两方面。

解决方案

想要选择合适的日期和时间类型，你可以先问自己以下几个问题：

你是只需要日期部分还是时间部分，还是两部分都需要？
你希望对应的数据类型所能支持的数据范围是什么？
你是否希望对应字段可以被自动更新为当前日期和时间？

MySQL 提供了 DATE 和 TIME 两种数据类型，用来分别表示日期和时间。除此之外，还提供了 DATETIME 和 TIMESTAMP 两种数据类型来同时表示日期和时间。这些数据类型具有以下特点：

DATE 类型的日期格式为 CCYY-MM-DD ，其中 CC ，YY ，MM 以及 DD 分别日期中的世纪，年份，月份以及日的部分。DATE 类型支持的范围在 1000-01-01 到 9999-12-31 。
TIME 类型的时间格式为 hh:mm:ss ，其中 hh ，mm 以及 ss 分别表示时间中的小时，分钟以及秒数。TIME 虽然通常被认为是指一天中的时间，实际上， MySQL 将其视为经过的时间 。因此，该数据类型不仅有可能比 23:59:59 要大，更有可能是负数¹。

DATETIME 和 TIMESTAMP 两种类型都是 CCYY-MM-DD hh:mm:ss 格式的日期和时间格式。这两种类型虽然在很多方面都很类似，但还是需要注意二者以下的几点区别：
- DATETIME 类型支持的范围在 1000-01-01 00:00:00 到 9999-12-31 00:00:00 之间，而 TIMESTAMP 类型仅支持从 1970 年到 2038 年之间的部分；
- DATETIME 和 TIMESTAMP 类型在 MySQL 5.6.5 之后都支持自动初始化和自动更新，但是在这之前 DATETIME 类型是不支持的；
- 当客户端插入一个 TIMESTAMP 类型的值，服务端会将其从和客户端会话相同时区转化为 UTC 时间并保存。当客户端查询该类型值时，服务端在返回前会对其做相反的操作。
通过指定时间的精度，还可以实现最高精确到微秒的时间。

本文后续主要使用以下三张数据表，下面分别是对应的建表语句和数据插入语句：

time_val ：

# time_val.sql

DROP TABLE IF EXISTS time_val;
CREATE TABLE time_val
(
  t1  TIME,
  t2  TIME
);

INSERT INTO time_val (t1,t2) VALUES('15:00:00','15:00:00');
INSERT INTO time_val (t1,t2) VALUES('05:01:30','02:30:20');
INSERT INTO time_val (t1,t2) VALUES('12:30:20','17:30:45');

date_val ：

# date_val.sql

# table of test date_val

DROP TABLE IF EXISTS date_val;
CREATE TABLE date_val
(
  d DATE
);

# Do not change the 1900 or 2000 dates. They're needed to illustrate
# points about leap-year testing.

INSERT INTO date_val (d) VALUES('1864-02-28');
INSERT INTO date_val (d) VALUES('1900-01-15');
INSERT INTO date_val (d) VALUES('1999-12-31');
INSERT INTO date_val (d) VALUES('2000-06-04');
INSERT INTO date_val (d) VALUES('2017-03-16');

datetime_val ：

# datetime_val.sql

# table of test date-and-time values (same values as timestamps table,
# but as DATETIME values)

DROP TABLE IF EXISTS datetime_val;
CREATE TABLE datetime_val
(
  dt  DATETIME
);

# These values are the same for datetime_val and timestamp_val.
# Don't change one without changing the other.

INSERT INTO datetime_val (dt) VALUES('1970-01-01 00:00:00');
INSERT INTO datetime_val (dt) VALUES('1999-12-31 09:00:00');
INSERT INTO datetime_val (dt) VALUES('2000-06-04 15:45:30');
INSERT INTO datetime_val (dt) VALUES('2017-03-16 12:30:15');

3. 使用精确到小数的时间

自从 MySQL 5.6.4 及往后更新的版本，对于 DATETIME ，TIME 以及 TIMESTAMP 这些包含时间部分的数据类型，都支持最多精确到微秒的时间。

默认情况下，各种类型的时间部分仅精确到整秒，而不会包含小数部分，如果包含，则需要在字段定义时显式声明，具体是在类型名称后包含 ( fsp ) ² ，其中 fsp 的取值在 $0$ 到 $6$ 之间，而 $0$ （等价于不指定）表示精确到整秒， $6$ 表示精确到微秒，下面使用 CURTIME() 函数进行了演示：

mysql> SELECT CURTIME(), CURTIME(2), CURTIME(6);
+-----------+-------------+-----------------+
| CURTIME() | CURTIME(2)  | CURTIME(6)      |
+-----------+-------------+-----------------+
| 16:12:09  | 16:12:09.57 | 16:12:09.575096 |
+-----------+-------------+-----------------+
1 row in set (0.00 sec)

4. 修改 MySQL 的日期格式

问题

MySQL 使用 ISO 8601 标准中定义的格式来存储日期和时间类型的值，你希望对其进行修改。

讨论

实际上，你无法这么做，也就是说 MySQL 的服务端总是使用符合 ISO 8601 标准的格式来存储日期和时间类型的值。然而，在保存日期或时间类型时，如果其格式不符合 ISO 8601 标准，你可以使用如 STR_TO_DATE() 等内置函数将其进行转换；在查询日期或时间类型时，如果你不希望结果以 ISO 8601 标准的格式显示，你可以使用如 DATE_FORMAT() 等内置函数对其进行转换。

解决方案

如前所述，MySQL 的 DATE 日期使用符合 ISO 8601 格式 CCYY-MM-DD 进行存储。由于年份、月份以及日的部分都有固定长度而且这三个组成部分按照固定的从左到右排列，因此该字段很自然地会按照日期顺序进行排序。

然而，问题在于：

尽管 ISO 格式很常见，但是并非所有数据库都使用这种格式，因此，如果在不同数据库中转移数据，可能引起问题；
对于人来说，更直观的日期显示格式可能是 MM/DD/YY 或者 DD-MM-CCYY 等形式。

因此，对于使用 MySQL 的新手来说，他们经常问及的一个问题是“如何才能让 MySQL 以特定格式如 MM/DD/CCYY 的形式来保存日期？”实际上，如前所述，由于 MySQL 服务端总是以符合 ISO 8601 标准格式的形式进行日期的存储，这个问题本身从一开始就是不成立的。正确的问题应该是“如果我有一个特定格式日期，我如何才能以 MySQL 支持的格式进行存储，或者当从 MySQL 服务端查询到某日期值时，如何使结果按照期望的格式进行展示？”

因此，修改 MySQL 日期格式的问题可以分为以下两种情况：

对于日期数据的插入。为了能将非 ISO 8601 格式的日期保存至 MySQL 服务端，你通常需要对该日期进行重写³。当然，有人可能会说，也可以直接将该日期以字符串的形式进行保存（例如：保存在 CHAR 类型的字段中），但是如果这样的话，你就不能使用 MySQL 提供的对于日期类型的各种操作了。

对于日期数据的显示。你可以将 ISO 格式的日期转化为其他格式进行显示，具体地， DATE_FORMAT() 函数提供了灵活的参数，使得可以将日期转化为各种期望的格式。

日期数据的插入

当计划插入 MySQL 的日期数据不是 ISO 格式时，一种常见的解决方案是使用 STR_TO_DATE() 函数，该函数接受两个参数，第一个参数是字符串形式的日期信息，第二个参数是字符串形式的格式信息。在格式信息中，使用一系列 %c 来指定格式指定。例如：%Y ，%M 以及 %d 分别表示四位数字形式的年份，两位数字形式的月份以及两位数字形式的日。例如，如果想要将 May 29, 2022 格式的日期插入 DATE 类型的字段，可以这么做：

mysql> CREATE TABLE t
    -> (
    ->   d DATE
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t (d) VALUES(STR_TO_DATE('May 29, 2022','%M %d, %Y'));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT d FROM t;
+------------+
| d          |
+------------+
| 2022-05-29 |
+------------+
1 row in set (0.00 sec)

日期数据的显示

对于日期的显示，如前所述，在没有显式指定的情况下，MySQL 使用 ISO 格式（即 CCYY-MM-DD）。如果希望以其他形式进行显示，可以使用函数 DATE_FORMAT() 或 TIME_FORMAT() 函数对服务端返回结果进行转换。

DATE_FORMAT() 函数接受两个参数：第一个参数是 DATE ， DATETIME 或者 TIMESTAMP 类型的值，第二个参数是用于指定日期和（或）时间是以什么样的格式显示的，其中第二个参数中使用的格式描述符和 STR_TO_DATE() 使用的为同一个集合。

下面是使用 DATE_FORMAT() 函数的的一个示例：

mysql> SELECT d, DATE_FORMAT(d, '%M %d, %Y') FROM date_val;
+------------+-----------------------------+
| d          | DATE_FORMAT(d, '%M %d, %Y') |
+------------+-----------------------------+
| 1864-02-28 | February 28, 1864           |
| 1900-01-15 | January 15, 1900            |
| 1999-12-31 | December 31, 1999           |
| 2000-06-04 | June 04, 2000               |
| 2017-03-16 | March 16, 2017              |
+------------+-----------------------------+
5 rows in set (0.00 sec)

如下表所示，在 MySQL 的参考手册中，提供一系列的格式限定符，这些格式限定符可用于 DATE_FORMAT() ，TIME_FORMAT() 以及 STR_TO_DATE() 等函数中。

格式限定符	含义
`%Y`	四位数字形式的年份
`%y`	两位数字形式的月份
`%M`	完整的月份名称
`%b`	月份名称，仅前三个字母
`%m`	两位数字形式的月份
`%c`	一年中的第几个月
`%d`	两位数字形式的日
`%e`	一个月中的第几天
`%W`	一周中某一天的全称
`%r`	$12$ 小时制的时间，时间前有 `AM` 和 `PM` 后缀
`%T`	$24$ 小时制的时间
`%H`	两位数字形式的小时
`%i`	两位数字形式的分钟
`%s`	两位数字形式的秒数
`%%`	字面量 `%`

上述的各种限定符中，和时间相关的只有在 DATE_FORMAT() 函数的第一个参数既有日期部分又有时间部分（即应该是 DATETIME 或 TIMESTAMP 类型）才生效。下面是一个 DATE_FORMAT() 进行日期时间显示格式转换的案例：

mysql> SELECT dt,
    -> DATE_FORMAT(dt, '%c/%e/%y %r') AS format1,
    -> DATE_FORMAT(dt, '%M %e, %Y %T') AS format2
    -> FROM datetime_val;
+---------------------+----------------------+----------------------------+
| dt                  | format1              | format2                    |
+---------------------+----------------------+----------------------------+
| 1970-01-01 00:00:00 | 1/1/70 12:00:00 AM   | January 1, 1970 00:00:00   |
| 1999-12-31 09:00:00 | 12/31/99 09:00:00 AM | December 31, 1999 09:00:00 |
| 2000-06-04 15:45:30 | 6/4/00 03:45:30 PM   | June 4, 2000 15:45:30      |
| 2017-03-16 12:30:15 | 3/16/17 12:30:15 PM  | March 16, 2017 12:30:15    |
+---------------------+----------------------+----------------------------+
4 rows in set (0.00 sec)

TIME_FORMAT() 和 DATE_FORMAT() 函数用法类似。该函数可用于 TIME ，DATETIME 或者 TIMESTAMP 类型的值，但是需要注意的是，该函数只接受上述表格中和时间相关的格式限定符：

mysql> SELECT dt,
    -> TIME_FORMAT(dt, '%r') AS '12-hour time',
    -> TIME_FORMAT(dt, '%T') AS '24-hour time'
    -> FROM datetime_val;
+---------------------+--------------+--------------+
| dt                  | 12-hour time | 24-hour time |
+---------------------+--------------+--------------+
| 1970-01-01 00:00:00 | 12:00:00 AM  | 00:00:00     |
| 1999-12-31 09:00:00 | 09:00:00 AM  | 09:00:00     |
| 2000-06-04 15:45:30 | 03:45:30 PM  | 15:45:30     |
| 2017-03-16 12:30:15 | 12:30:15 PM  | 12:30:15     |
+---------------------+--------------+--------------+
4 rows in set (0.00 sec)

如果函数 DATE_FORMAT() 或 TIME_FORMAT() 都无法转换出你希望的结果，那么你可以根据需要写一个自定义的存储函数。假设你希望将 24 小时制的 TIME 类型值转换为 12 小时制格式，但是你希望后缀是 a.m. 或 p.m. 而不是 AM 或 PM 。

下面的函数就可以实现这样的功能，下列函数主要使用了 TIME_FORMAT() 函数，然后先将 %r 给出的后缀去除，再拼接上期望的后缀：

CREATE FUNCTION time_ampm (t TIME)
RETURNS VARCHAR(13) # mm:dd:ss {a.m.|p.m.} format
DETERMINISTIC
RETURN CONCAT(LEFT(TIME_FORMAT(t, '%r'), 9),
              IF(TIME_TO_SEC(t) < 12*60*60, 'a.m.', 'p.m.'));
              
mysql> SELECT t1, time_ampm(t1) FROM time_val;
+----------+---------------+
| t1       | time_ampm(t1) |
+----------+---------------+
| 15:00:00 | 03:00:00 p.m. |
| 05:01:30 | 05:01:30 a.m. |
| 12:30:20 | 12:30:20 p.m. |
+----------+---------------+
3 rows in set (0.00 sec)

5. 设置客户端所在时区

问题

你有一个和 MySQL 服务端建立连接的客户端应用程序，该客户端和服务端在不同的时区。因为这样的原因，当保存 TIMESTAMP 类型的值时，这些值将不会以正确的 UTC 时间进行存储。

解决方案

在客户端和服务端建立连接之后，客户端应该设置名为 time_zone 的系统变量。

讨论

时区的设置对于 TIMESTAMP 类型的值有重要的影响：

在客户端和服务端建立连接之后，客户端会话默认的时区和服务端一致。如下列 SQL 语句所述，系统变量 @@global.time_zone 和 @@session.time_zone 分别表示服务端和客户端会话所在的时区。如果所有的客户端都和服务端在同一个时区，那么没有什么需要特别处理的。然而，如果客户端所在的时区和服务端所在时区不一致，且此时客户端在向服务端插入 TIMESTAMP 类型的值之前没有进行必要的时区修正，那么保存在服务端的 UTC 时间值将有误；

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

在 MySQL 服务端启动时，它会先检查自身的运行环境，以确定所在的时区⁴；

对于每一个和服务端建立联系的客户端，服务端根据和客户端会话相同的时区（由第一点可知，该时区默认和服务端一致）来解析 TIMESTAMP 值。当客户端插入一个 TIMESTAMP 类型的值，服务端会将转化为 UTC 时间⁵并保存；当客户端查询该时间值时，服务端会做相反操作。

下面以一个具体的例子来进行说明。假设服务端和客户端 $C_1$ 在同一个时区，且客户端执行了下列语句：

mysql> CREATE TABLE t (ts TIMESTAMP);
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO t (ts) VALUES('2022-05-29 22:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT ts FROM t;
+---------------------+
| ts                  |
+---------------------+
| 2022-05-29 22:00:00 |
+---------------------+
1 row in set (0.00 sec)

由上述可以看出，客户端 $C_1$ 看到的结果和保存的结果一致。如果还有另外一个客户端 $C_2$ ，如果该客户端查询同一个值，其将看到相同的日期和时间，但如果该客户端和服务端在不同的时区，那么该日期和时间在该客户端所在时区下就是错误的。同样地，如果通过客户端 $C_2$ 存储一个 TIMESTAMP 类型的值，那么由客户端 $C_1$ 查询得到结果在其所在时区下也是错误的。

上述问题的症结在于，在客户端和服务端建立连接后，客户端会话的默认时区和服务端一致。因此，为了应对上述问题，需要在客户端和服务端建立连接后，显式地设置客户端会话所在的时区。例如：东京的时间相较于北京时间早一个小时，即东京时间和 UTC 时间相差 $9$ 个小时，而北京时间和 UTC 时间相差 $8$ 个小时。也就是说，在正确设置的情况下，查询上述 2022-05-29 22:00:00 的结果，在东京时间下应该是 2022-05-29 23:00:00 。要实现这样的效果，需要进行如下设置：

mysql> SET SESSION time_zone = '+09:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | +09:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ts FROM t;
+---------------------+
| ts                  |
+---------------------+
| 2022-05-29 23:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SET SESSION time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ts FROM t;
+---------------------+
| ts                  |
+---------------------+
| 2022-05-29 22:00:00 |
+---------------------+
1 row in set (0.00 sec)

6. 转化不同时区的日期时间

问题

你有一个日期时间值，但你希望其在其他时区是什么日期和时间。例如：你计划和全球不同区域的人员开一场电话会议，而你必须要告诉他们会议在各自的当地是什么日期和时间。

解决方案

使用 CONVERT_TZ() 函数。

讨论

函数 CONVERT_TZ() 接受三个参数：一个日期时间值，以及两个表示时区的值。该函数会以第一个时区值来解析日期时间值，然后返回在第二个时区下对应的日期时间。

假设我现在居住在上海，计划和世界各地的几个人开一次线上会议。下表是每个与会者所在地区和对应时区情况：

地址	时区
美国，伊利诺伊州，芝加哥	`-05:00`
德国，柏林	`+01:00`
英国，伦敦	`+00:00`
加拿大，亚伯达，埃德蒙顿	`-07:00`
澳大利亚，布里斯班	`+10:00`

下列 SQL 语句就使用了 CONVERT_TZ() 函数进行了对应地区的时区转换：

mysql> SET @dt = '2022-05-30 06:00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @dt AS Shanghai,
    -> CONVERT_TZ(@dt, '+08:00', '-05:00') AS Chicago,
    -> CONVERT_TZ(@dt, '+08:00', '+01:00') AS Berlin,
    -> CONVERT_TZ(@dt, '+08:00', '+00:00') AS London,
    -> CONVERT_TZ(@dt, '+08:00', '-07:00') AS Edmonton,
    -> CONVERT_TZ(@dt, '+08:00', '+10:00') AS Brisbane\G
*************************** 1. row ***************************
Shanghai: 2022-05-30 06:00:00
 Chicago: 2022-05-29 17:00:00.000000
  Berlin: 2022-05-29 23:00:00.000000
  London: 2022-05-29 22:00:00.000000
Edmonton: 2022-05-29 15:00:00.000000
Brisbane: 2022-05-30 08:00:00.000000
1 row in set (0.00 sec)

在 MySQL 中，类型为 TIME 的字段，其取值范围是 -838:59:59 到 838:59:59 。 ↩︎
fsp: fractional seconds support ↩︎
在一些情况下，如果你希望插入日期数据和 ISO 格式很相近，你也可以不用先对其进行重写。例如：当把某种格式的日期保存至 MySQL 的 DATE 类型字段中时，MySQL 服务端会将 87–1-7 和 1987-1-7 以及数字 870107 和 19870107 都视为日期 1987-01-07 。 ↩︎
如果希望手动指定时区，可以在启动服务端时使用 --default-time-zone 选项。 ↩︎
在服务端内部，该时间以距离 1970-01-01 00:00:00 UTC 的秒数的形式保存 ↩︎