Mysql的timestamp时间戳详解及2038问题-猿码集

1. Mysql的timestamp时间戳概述

MySQL 的 timestamp 数据类型在存储时占用4个字节，能够保存从 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC 之间的时间。实际上，timestamp 是在服务器的时区中保存的，因此在转换到客户端的时候，它可能会有所改变。

1.1 TIMESTAMP 值的插入

在插入时间戳类型时，可以使用NOW（）或者CURRENT_TIMESTAMP 来插入当前的系统时间，格式如下(header 表示表头，calories 表示热量值):


INSERT INTO header (date_created, date_updated) VALUES(NOW(),NOW());
INSERT INTO calories (date_created, `value`) VALUES(NOW(),26);

1.2 TIMESTAMP 值的更新

TIMESTAMP 类型的值可以使用 NOW() 函数进行更新，也可以使用特殊的 date/time函数和其他算术运算进行更新。下面的例子显示如何使用当前日期和时间进行更新:


UPDATE header SET date_updated = NOW();
UPDATE calories SET `value` = `value` + 3 WHERE id = 14;

2. Mysql的timestamp时间戳的2038问题

2021年，MySQL所依赖的全球公共标准时间（UTC）是从1970年开始计算时间戳。数据库存储时间戳时，将一个32位整形数据转换成所需时间，精确到秒级别。但转换后这个32位的整数，所能支持的最大值仅有2的32次幂-1，即2,147,483,647。因此，在2038年1月19日03时14分07秒(UTC)，时间戳用完将重新从0开始计算。

2.1 解决方法

为了解决时间戳的2038问题，MySQL由4.1.0开始提供了一个特殊的存储类型-bigint，它用8个字节存储，最大可以存储9,223,372,036,854,775,807的数据，可以远远超出2038年01月19日的时间戳存储范围。此外，从5.6.4版本开始，MySQL的timestamp和datetime数据类型支持fractional seconds，即支持到微秒级，使得统计学上高精度的时间统计变得容易。

2.2 具体示例

下面是一个在MySQL中使用BIGINT存储时间戳的示例:


CREATE TABLE my_table (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  timestamp BIGINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO my_table (timestamp) VALUES (UNIX_TIMESTAMP());

3. timestamp与datetime数据类型的区别

虽然timestamp和datetime都可以用来存储日期和时间，但是它们之间有一些重要的区别。

3.1 存储空间

timestamp 数据类型存储时占用4个字节，datetime数据类型需要使用8个字节。这意味着，在存储相同的日期和时间时，使用timestamp 可以占用更少的存储空间。

3.2 存储范围

在 MySQL 中，timestamp 类型只能存储从 1970 年到2038 年之间的时间戳，而 datetime 类型则可以存储的日期范围更加广泛，从 1000 年到 9999 年。

3.3 时间精度

timestamp 可以使用到秒级精度，但只有在从 MySQL 5.6.4 版本开始，才可能出现更高精度的浮点数小数位。而 datetime 数据类型可以使用到微秒级精度。

Mysql的timestamp时间戳详解及2038问题