技术文档 · 2021年 9月 9日 0

MySQL 日期时间存储类型 datetime、timestamp 以及 int 的联系和区别

大概印象

这三个类型都可以用来存储日期时间,datetime 和 timestamp 展现形式如 '2020-07-27 14:25:30',int 则是整型存储秒数,形如 1595852730

个人通常会用 int 类型存储日期时间,一开始是觉得比较方便,因为 time() 获取当前的(与 1970-01-01 08:00:00 间隔的)秒数可以直接存入数据库。显示的时候秒数方便通过 date() 转化其他任意想要的日期时间格式。且日期时间以整型存储在排序的时候整型更直观。

但这其实都是我自己的想法,datetime 和 timestamp 在 PHP 存入数据库的时候,可以设置明确的(字符串类型)值,也可以借助 MySQL 自带的字段属性便捷设置。如果是显示默认格式的日期时间,datetime 和 timestamp 倒是比较方便的;但如果需要显示特定格式的日期,比如说几分钟,几小时这种形式(借助自定义的公共函数实现),需要先转化为 int 类型秒数,再转化为想要的日期时间格式。此外,datetime 和 timestamp 是可以正常比较大小进行排序的。如果是在查询条件中需要指定日期或者时间,可以通过 MySQL 的 SUBSTR(addtime FROM 1 FOR 10) 方法截取比较部分(与 PHP 中的库函数相比,MySQL 中的 SUBSTR() 默认的开始值是 1,而非 0,这点需要注意的)。这一点上,比 int 类型通过比较范围(例子在下面)或者通过 sql 时间戳和日期格式转换 之后再用 SUBSTR() 方法截取进行比较方便得多。

-- int 类型存储日期时间,筛选 2020-07-27 的记录
`time` >= UNIX_­TIME­STAMP('2020-07-27 00:00:00') AND `time` <= UNIX_­TIME­STAMP('2020-07-27 23:59:59')

datetime、timestamp 以及 int 的联系和区别

联系就是都能存储时间日期了,主要看区别。

自动更新日期

原文 mysql 的 datetime 和 timestamp 的区别还挺多,需要注意细节 在这一点上我是不完全认同的。

原文说 datetime 默认值为 null 不会自动更新,而 timestamp 默认值为 not null 的时候,默认时间和之后的更新时间都为当前时间 CURRENT_TIMESTAMP。这其实是不对的,默认值都不一样,哪有什么可比性。而且默认时间和更新时间只与当前设置的默认值和更新值有关的。

我的测试环境:MySQL 5.6Navicat 11.1.13

两个类型的默认值都可以自定义,当设置为 CURRENT_TIMESTAMPDEFAULT CURRENT_TIMESTAMP),插入记录时字段值自动保存为当前时间。当设置为空时,date­time 为空,time­stamp 为 0000-00-00 00:00:00。但插入记录时,都显示 0000-00-00 00:00:00。而当设置为 null 时,两者默认值也都会 null,插入记录也为 null。

自动更新日期主要使用到是另外一个字段属性 ON UPDATEON UPDATE CURRENT_TIMESTAMP 约定更新记录时更新当前字段值为当前时间(更新时间)。这个属性在当前测试环境的 Nav­i­cat 设计表界面,time­stamp 有一个 根据当前时间戳更新 的 check­box 选项,会自动默认勾选,而 date­time 没有。这就让原文作者有一个错觉,time­stamp 类型会自动更新,而 date­time 不会。其实归根结底,还是 ON UPDATE 在起作用。测试发现,date­time 虽然没有这个自动更新的选择项,但可以通过更改数据库表结构语句进行修改设置。具体操作如下:

ALTER TABLE `xxx`
MODIFY COLUMN `ddatetime`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

当 date­time 和 time­stamp 两个类型的字段设置默认值为 null 后,属性值勾选 not null 进行保存更新(Nav­i­cat),time­stamp 会自动设置默认值为 CUR­REN­T_­TIME­STAMP,并自动勾选 根据当前时间戳更新;而 date­time 默认值为空。

int 默认值为 0,就是 1970-01-01 00:00:00(服务器上 php -r "echo date('Y-m-d H:i:s',0);" 的测试结果,本地 Win­dows 测试为 1970-01-01 01:00:00,不清楚什么原因导致的)。

日期存储方式

time­stamp 存储的时间会自动转换为 utc (格林威治标准时间),北京时间是东八区和它差了 8 个小时,在存储的时候会减去 8 小时,所以如果你是用 my­batis 操作的读取,需要配置下再加上 8 小时(Java 还没自学到哪一块,尴尬)

date­time 不需要做出任何转变,与时区无关。这点在跨时区迁移数据时可能会造成影响。

int 不清楚… 猜测跟 time­stamp 一致,即与时区有关。

支持范围不一样

time­stamp 支持的范围是 1970-01-01 00:00:01 到 2038-01-19 03:14:07,在数据库用整型的 int 型存储,int 型的范围是 2 的 32 次方减 1。

date­time 支持的日期范围是 从 0000-00-00 00:00:00 到 9999-12-31 23:59:59

int 与 time­stamp 支持范围一致。

使用 timestamp 可能出现的错误

如果用 time­stamp 存储数据,最早时间是 “1970-01-01 00:00:01”,可是在存入数据库的时候,因为数据库会自动减去 8 小时,这样一来,时间就变成 1969 年了,你会发现报错误了,可是这个异常又不是经常发生。

在设置自定义的 time­stamp 的时候,使用 1970-01-01 08:00:00 或者小于这个的日期时间插入时,会自动保存成 '0000-00-00 00:00:00',而 1970-01-01 08:00:01 和大于这个的日期时间插入,显示正常。与其默认值设置无关,与当前时区有关。暂未发现错误或错误提示,猜测原文作者遇到的错误,可能是因为 Java 语言的报错,或者不同类型的数据库产生的错误。

参考:mysql 的 datetime 和 timestamp 的区别还挺多,需要注意细节

转自 《MySQL 日期时间存储类型 datetime、timestamp 以及 int 的联系和区别》