mysql 存储的时候带了毫秒值 日期不对

在使用 MySQL 数据库时，有时候我们会遇到日期不正确的问题，尤其是在存储日期时带有毫秒值的情况下。本文将介绍这个问题的原因，并提供一些解决方案。

问题描述

在 MySQL 中，日期时间类型分为多种，包括 DATE、TIME、DATETIME、TIMESTAMP 等。其中，DATETIME 和 TIMESTAMP 类型都可以存储日期和时间的值。

在存储日期时间值时，如果我们希望保留毫秒值，可以使用 DATETIME(3) 或 TIMESTAMP(3) 类型。例如，下面的 SQL 语句创建了一个包含毫秒值的 DATETIME 类型的表：

CREATE TABLE `my_table` (
  `id` INT(11) PRIMARY KEY,
  `date_time` DATETIME(3)
);

然而，当我们向这个表插入数据时，可能会遇到日期不正确的问题。

问题原因

MySQL 存储日期时间值时，会根据时区进行转换。默认情况下，MySQL 使用服务器的时区来处理日期时间值。因此，如果服务器的时区设置不正确，就会导致存储的日期值不正确。

另外，DATETIME 和 TIMESTAMP 类型的存储分辨率是不同的。DATETIME 类型存储的最大有效精度是秒级，而 TIMESTAMP 类型存储的最大有效精度是微秒级。因此，当我们使用 DATETIME 类型存储毫秒值时，MySQL 会自动将毫秒值四舍五入到秒级。

这就是导致日期不正确的原因，因为毫秒值被四舍五入到了秒级。

解决方案

解决这个问题的方法有以下几种：

1. 修改时区设置

如果我们发现日期不正确，首先要检查服务器的时区设置。可以使用以下 SQL 语句查看当前时区的设置：

SELECT @@global.time_zone, @@session.time_zone;

如果时区设置不正确，可以通过修改配置文件或者使用 SET GLOBAL time_zone = 'timezone'; 命令来修改时区设置。在修改时区设置后，重新插入数据应该能够解决日期不正确的问题。

2. 使用 TIMESTAMP 类型

如果我们需要存储毫秒值，并且确保日期正确，可以考虑使用 TIMESTAMP(3) 类型来代替 DATETIME(3) 类型。TIMESTAMP 类型存储的最大有效精度是微秒级，可以满足我们的需求。

下面是一个示例：

CREATE TABLE `my_table` (
  `id` INT(11) PRIMARY KEY,
  `timestamp` TIMESTAMP(3)
);

3. 存储毫秒值为整数

如果我们只是需要存储毫秒值，并不需要使用日期时间相关的功能，可以考虑将毫秒值存储为整数。这样可以避免日期不正确的问题，并且节省存储空间。

例如，可以将毫秒值存储为一个 BIGINT 类型的整数，单位为毫秒：

CREATE TABLE `my_table` (
  `id` INT(11) PRIMARY KEY,
  `millis` BIGINT(20)
);

结论

在使用 MySQL 存储日期时间值时，如果带有毫秒值并且发现日期不正确，需要检查时区设置和数据类型是否正确。在修改时区设置或者选择适当的数据类型后，应该能够解决日期不正确的问题。

希望本文能够帮助你理解并解决存储带有毫秒值的日期不正确的问题。

pie
    title 数据类型分布
    "DATETIME" : 45
    "TIMESTAMP" : 35
    "BIGINT" : 20

参考文献：

  • [MySQL 8.0 Reference Manual - 11.3 Date and Time Types](