MySQL 为什么我在将“1965-05-15”等日期转换为 TIMESTAMP 时得到输出 0（零）

在本文中，我们将介绍为什么在将“1965-05-15”等日期转换为 TIMESTAMP 类型时，会得到输出 0（零）的原因，并探讨一些解决办法。

阅读更多：MySQL 教程

TIMESTAMP 数据类型

在 MySQL 中，TIMESTAMP 是一种用于存储日期和时间的数据类型。它用于表示特定日期和时间的值，范围从 1970 年 1 月 1 日 00:00:01 到 2038 年 1 月 19 日 03:14:07，精确到秒级。

UTC 和本地时间

在 MySQL 中，TIMESTAMP 类型默认以 UTC（协调世界时）格式存储，并在检索时将其转换为本地时间。

时间戳计算规则

TIMESTAMP 类型的存储采用了自 1970 年 1 月 1 日 00:00:01 以来经过的秒数来表示。而输入的日期“1965-05-15”转换为时间戳时，MySQL 使用的计算规则是将输入的日期和时间当作本地时间，并将其转换为 UTC 时间。

零值输出问题

当输入的日期是“1965-05-15”等存在于 DST（夏时制）切换的日期时，可能会导致输出为零值（0）。这是由于在转换为 UTC 时发生了错误。

以下是一个示例：

CREATE TABLE `test_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `date_col` DATE NOT NULL,
  `timestamp_col` TIMESTAMP NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test_table` (`date_col`, `timestamp_col`) VALUES ('1965-05-15', '1965-05-15');

执行上述代码后，我们可以查询表中的数据：

SELECT * FROM `test_table`;

输出结果可能如下：

| id | date_col    | timestamp_col        |
|----|-------------|----------------------|
| 1  | 1965-05-15  | 0000-00-00 00:00:00  |

从上述结果可以看出，输入的日期“1965-05-15”被转换成了零值。

解决办法

为了解决上述问题，我们可以采用以下几种方法之一：

1. 使用 DATETIME 类型

将 TIMESTAMP 类型改为 DATETIME 类型。DATETIME 类型不会自动转换为 UTC 时间，因此不会出现零值的问题。但是，需要注意 DATETIME 类型不会自动更新和记录时间戳。

2. 使用字符串存储日期

将日期作为字符串存储，而不是使用 TIMESTAMP 数据类型。这种方法可以避免转换问题，并保留原始输入的日期格式。

3. 手动将日期转换为 UTC 时间

在 MySQL 中，可以通过执行转换函数 CONVERT_TZ 或使用 DATE_ADD 函数手动将日期从本地时间转换为 UTC 时间。这样，即使输入的日期是存在夏时制切换的日期，也可以正确转换为 UTC 时间。

以下是一个示例：

SELECT `date_col`, CONVERT_TZ(`date_col`, 'LOCAL_TIME_ZONE', 'UTC') AS `utc_time` FROM `test_table`;

上述代码中，LOCAL_TIME_ZONE 需要替换为实际使用的本地时间区域。

总结

在将“1965-05-15”等日期转换为 TIMESTAMP 类型时得到输出 0（零）的问题是由于在转换为 UTC 时间时发生了计算错误。我们可以通过使用 DATETIME 类型、字符串存储日期或手动将日期转换为 UTC 时间来解决这个问题。选择最适合自己需求的解决办法，避免出现输出零值的情况。