MySQL之DATETIME与TIMESTAMP的时间精度问题

简介

MySQL 中有两种用于存储日期时间的数据类型：DATETIME 和 TIMESTAMP。它们在精确度和存储方式上有所不同。

本文将详细讲解这两种数据类型的不同之处，并通过示例说明一些注意事项，帮助您更好地理解它们。

DATETIME

DATETIME 数据类型存储日期和时间数据，精度为秒，范围为 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。

在 MySQL 中，DATETIME 的格式为 YYYY-MM-DD HH:MM:SS，其中 YYYY 表示四位数的年份，MM 表示两位数的月份，DD 表示两位数的日期，HH 表示小时，MM 表示分钟，SS 表示秒。例如：“2022-09-09 15:08:22”。

TIMESTAMP

TIMESTAMP 数据类型也用于存储日期和时间数据，但是它的精度比 DATETIME 更高，可以精确到微秒级别。另外，TIMESTAMP 数据类型可以自动将记录的创建时间和更新时间记录在列中。

在 MySQL 中，TIMESTAMP 格式为 YYYY-MM-DD HH:MM:SS[.fraction]，其中的 [.fraction] 部分表示微秒。例如：“2022-09-09 15:08:22.123456”。

需要注意的是，TIMESTAMP 数据类型只能表示从1970年1月1日00:00:01到2038年1月9日03:14:07期间的时间。

区别与注意事项

以下是 DATETIME 和 TIMESTAMP 的区别与注意事项：

存储方式不同

DATETIME 类型将日期和时间的值存储为从 '1000-01-01 00:00:00' 到当前日期和时间的秒数。

而 TIMESTAMP 类型使用自1970年1月1日00:00:01（UTC）以来的秒数来表示日期和时间值。

存储空间和效率

DATETIME 数据类型占用8个字节，而 TIMESTAMP 仅占用4个字节。因为 DATETIME 能够存储更大的时间范围和更高的精度。

相应地，TIMESTAMP 数据类型通常更具有效性，因为它需要较少的磁盘空间，且在查询时比 DATETIME 快。

时间范围不同

DATETIME 的时间范围为 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。

而 TIMESTAMP 的时间范围为从1970年1月1日00:00:01到2038年1月9日03:14:07期间的时间。

因此，在需要存储遥远的历史数据或者需要存储将来的日期时，最好使用 DATETIME。

时区不同

在 MySQL 中，DATETIME 类型的时间值没有时区的概念，可以认为它的时间是本地时间。而 TIMESTAMP 类型存储的时间值始终基于 UTC。

自动更新

与 DATETIME 不同，TIMESTAMP 列通常具有自动更新属性。如果创建或更新行时未提供 TIMESTAMP 等效列的值，则会将当前日期和时间插入该列。

示例

示例1：存储精度

对于需要高精度时间的场景，应该使用 TIMESTAMP 数据类型。

在下面的示例中，我们分别创建一个 DATETIME类型和一个TIMESTAMP类型的表，然后将相同的日期时间插入其中，比较它们的存储精度。

CREATE TABLE datetime_table (
  id INT PRIMARY KEY,
  ts DATETIME
);

CREATE TABLE timestamp_table (
  id INT PRIMARY KEY,
  ts TIMESTAMP
);

INSERT INTO datetime_table (id, ts) VALUES (1, '2022-09-09 15:08:22.123456');
INSERT INTO timestamp_table (id, ts) VALUES (1, '2022-09-09 15:08:22.123456');

SELECT * FROM datetime_table;
SELECT * FROM timestamp_table;

运行上述SQL语句后，我们可以看到，DATETIME类型仅保留了秒级别的精度，而TIMESTAMP类型保留了微秒级别的精度。这意味着，在 TIMESTAMP 类型的列中存储数据时，能够更加准确地记录日期和时间。

示例2：自动更新

如果需要记录记录创建时间和最后更新时间，可以使用 TIMESTAMP，因为它支持自动更新。

在下面的示例中，我们创建了一个 users 表，并将更新时间设为 TIMESTAMP 类型。当行被更新时，我们将看到该列自动更新为当前日期和时间。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO users (name) VALUES ('Alice');

-- 更新名称后，updated_at字段自动更改
UPDATE users SET name = 'Bob' WHERE id = 1;

SELECT * FROM users;

运行上述SQL语句后，我们可以看到在更新后，updated_at 字段的值将自动更新为当前日期和时间，以便更准确地跟踪数据的修改历史。

结论

在存储日期和时间数据时，应根据实际需求选择数据类型。如果需要更高的存储效率和更广泛的时间范围，请使用 DATETIME。如果需要更高的时间精度和自动更新功能，请使用 TIMESTAMP。