MySQL中datetime和timestamp的区别

时间：2023-10-12     作者：smarteng     分类： 数据库相关

相同点

  1. 两个数据类型存储时间的格式一致。均为 YYYY-MM-DD HH:MM:SS

  2. 两个数据类型都包含「日期」和「时间」部分。

  3. 两个数据类型都可以存储微秒的小数秒（秒后6位小数秒）

区别

1. 占用空间

  • TIMESTAMP ：占 4 个字节（小数秒+3 个字节）

  • DATETIME ：在 MySQL 5.6.4 之前，占 8 个字节 ，之后版本，占 5 个字节。（小数秒+3 个字节）

类型 占据字节 表示形式
timestamp 4 字节 yyyy-mm-dd hh:mm:ss
datetime 8(5) 字节 yyyy-mm-dd hh:mm:ss

2. 表示范围

类型 表示范围
datetime '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'
timestamp '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'

timestamp 翻译为汉语即"时间戳"，它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数。对于某些时间的计算，如果是以 datetime 的形式会比较困难，假如我是 1994-1-20 06:06:06 出生，现在的时间是 2016-10-1 20:04:50 ，那么要计算我活了多少秒钟用 datetime 还需要函数进行转换，但是 timestamp 直接相减就行。

3. 时区(存入时间是否会自动转换？)

timestamp 只占 4 个字节，而且是以 utc 的格式储存， 它会自动检索当前时区并进行转换。

datetime 以 8 个字节储存，不会进行时区的检索.

也就是说，对于 timestamp 来说，如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。对于 datetime 来说，存什么拿到的就是什么。

4.使用 now() 存储当前时间时，保存的实际值，是否与当前计算机时间一致？

  • TIMESTAMP ：可能不一致。存储值会被转换成 UTC 时间值再存入数据库。

  • DATETIME ：与当前时间是一致的。

5.如果存入的是 NULL 时，两个类型如何存储？

  • TIMESTAMP ：会自动存储当前时间（ now() ）。

  • DATETIME ：不会自动存储当前时间，会直接存入 NULL 值。

使用场景辨析

TIMESTAMP 使用场景：计算飞机飞行时间

一架飞机，从中国北京起飞，降落在美国纽约，计算它从北京飞往纽约的飞行时间。飞机在北京时间 2021-10-10 11:05:00 从北京起飞，在纽约时间 2021-10-10 09:50:00 降落（JL8006）。

这个场景中，如果使用 TIMESTAMP 来存时间，起飞和降落时间的值，都会被转换成 UTC 时间，所以它们直接相减即可获得结果。但如果使用 DATATIME 格式存时间，还需要进行转换，才可以完成，容易出错。

DATATIME 使用场景：记录信息修改时间

如果只是记录文件修改时间，最后更新时间这种不涉及加减转换的情况，用 DATATIME 来存更直接，更方便，可读性高，不绕弯子，不容易出错。

案例

首先我们进入 MySQL sell，新建一个库：

ini复制代码CREATE DATABASE kalacloud_demo_database;

然后我们 USE 这个库，接下来的演示，在这个新建库中进行。

ini复制代码USE kalacloud_demo_database;

接着，我们新建一个包含 TIMESTAMP & DATATIME 两个数据类型的表：

sql复制代码CREATE TABLE time_demo_kalacloud (`timestamp` timestamp,`datetime` datetime);

好，现在我们往 time_demo_kalacloud 插入几组数据，看一下两个数据类型的存储样式：

sql复制代码insert into time_demo_kalacloud values
(NULL,NULL),
(now(),now()),
('19970701171207','19970701171207');

使用 SELECT 看一下数据库中的存储结果：

csharp复制代码select * from time_demo_kalacloud;

MySQL 5.7版本的数据库查询到的结果：

MySQL8版本数据库查询到的版本：

接着我们来改一下本地计算机时区，把 +8 区改为 +10 区，看看刚刚已经存储到数据库中的时间有什么变化：

ini复制代码set time_zone = '+10:00';
select * from time_demo_kalacloud;

实战案例小结：

1.向数据插入 NULL 时： timestamp 存入了当前时间（MySQL8版本为NULL），而 datetime 直接存了 NULL 本身。

2.向数据插入 now() 时：更改时区后， timestamp 随时区变化（小时+2）， datetime 没有变化。

3.向数据库直接插入时间值：更改时区后， timestamp 随时区变化（小时+2）， datetime 没有变化。

选择

如果在时间上要超过 Linux 时间的，或者服务器时区不一样的就建议选择 datetime 。

如果是想要使用自动插入时间或者自动更新时间功能的，可以使用 timestamp 。

如果只是想表示年、日期、时间的还可以使用 year 、 date 、 time ，它们分别占据 1、3、3 字节，而 datetime 就是它们的集合。

如果数据库中有timestamp类型的字段，mysql数据库不管是迁库，还是集群，都一定要保证时区的相同。如果mysql集群中的数据库时区不一致，timestamp的字段将会造成数据不一致的情况发生。 在迁移库或者搭建集群时一定检查时区，保证时区的相同 。中国时区默认是+8,所以不管是单节点mysql，还是mysql集群，我们第一件事就是应该将当前时区 time_zone 设置为 +8:00 。

标签： datetime timestamp
上一篇:MacOs 电脑关闭/打开IPV6 下一篇:C++声明结构
smarteng
smarteng

人生就流星，虽然转瞬即逝，但也有永恒。

恰饭
分类
热门文章
最新日志
存档
标签
timestamp datetime time awk 技术文章 SEM 营销 赚钱 go-zero 抓包 测试覆盖率 系统 hostname goquery mockgen gomock validator urldecode urlencode redirect context port pulsar Consul epoll yaml 错误处理 code-review xlsx excel c 读书 阅读 写作 生活 shen 统计 test Module mod 效率 无密码 天网 Future video computer tmux ce Logrotate supervisor command screen rsa java markdown 测试 水平居中 error brew homebrew http nodejs composer 工具 框架 gopath gomod golang go 播放器 服务器 腾讯云 单元测试 爬虫 命令 emlog replace 模型 PHP升级 阿里云 习惯 group_concat SQL Server PHPExcel 面试 单点登录 开源项目 tag guid yaf md5 博客 微博 团队 Geolocation Mac 客户端 网赚 豆瓣 编程语言
京东