日期类型datetime和timestamp区别

一、基础知识

（1）时间戳

时间不分东西南北、在地球的每⼀个⾓落，每⼀个国家都是⼀样的。在中国时间过去5秒钟，美国同样也是过去了5秒钟，在地球的任何⼀个⾓度都是过去了5秒钟，时间戳就是相对于格林威治时间1970年01⽉01⽇00时00分00秒起⾄现在过去的总秒数，这个总秒数在世界上的任何地⽅都是相同，也就是说时间戳在世界上的任何地⽅都是相同的，时间戳没有时区的概念。时间戳强调的是过去时，相对1970年01⽉01⽇00时00分00秒起过去了多长时间。

（2）时区

我们知道了在任何地⽅时间戳都是相同的。当我们问现在是什么时候，我们总不能说相对于1970年01⽉01⽇00时00分00秒过去了n秒，太模糊了，于是就有了时间的定义，我们可以说现在是2020年03⽉15号23点53分，⽅便我们记忆和理解。
时间和实践戳相⽐较，时间强调的是现在时，现在是何年何⽉何⽇，何时何分何妙，⽽时间戳强调的是过去时，过去了多少秒。通常我们将每天太阳在最⾼点的时候定义为12点，⽽由于地球的⾃转，导致每个地⽅太阳出现在最⾼点的时间⼜不尽相同，为了统⼀时间（太阳最⾼点为12点）于是就出现了时区的概念。我们中国处于东+8区，美国处于西-5区
最后总结⼀个表达式：实践戳+时区=当前时间

二、datatime和timestamp区别

（1）占用空间

https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html
下表显示了日期和时间数据类型的存储要求。

• 非小数部分的 TIMESTAMP 编码：与 5.6.4 之前相同，除了大端而不是小端

• 非小数部分的 DATETIME 编码：

  1 bit sign (1= non-negative, 0= negative)
  17 bits year*13+month (year 0-9999, month 0-12)
  5 bits day (0-31)
  5 bits hour (0-23)
  6 bits minute (0-59)
  6 bits second (0-59)
  —————————————-
  40 bits = 5 bytes
  符号位始终为 1。保留值 0（负）。

• 小数部分编码取决于小数秒精度 (FSP)。 | 全汉 | 贮存 | | —- | —- | | 0 | 0 字节 | | 1,2 | 1 个字节 | | 3,4 | 2 个字节 | | 4,5 | 3 个字节 |

（2）表示范围

由于datetime和timestamp占据的字节不⼀样，以⾄于表⽰的存储范围也肯定不⼀样。

（3）存储形式

datetime类型的字段值写⼊的是什么，存储的就是什么，与时区没有关系。⽐如写⼊的是2020-03-10 08:09:30,存储的也是2020-03-1008:09:30。
timestamp类型的字段值本质存储的是时间戳。⽐如写⼊的是2020-03-10 08:09:30，且mysql时区为+8,所以存储的是UNIX_TIMESTAMP(2020-03-10 00:09:30)的值，与时区有关。

（4）表现形式

datetime和timestamp类型的字段，表现的都是yyyy-mm-dd hh:mm:ss，即⽤户从数据库获取到的数据都是yyyy-mm-dd hh:mm:ss类型的，不会因为timestamp本质存储的是时间戳，⽽⽤户获取的就是时间戳，他俩的表现形式是⼀样的。
timestamp类型的表现形式过程：FROM_UNIXTIME(value)+时区=时间，最终把时间展⽰出来。

（5）时区

通过上⾯的分析，我们知道了mysql的时区⼤概作⽤于以下俩点
1. timestamp类型的数据，mysql实际存储的是时间戳,⽽表现形式却是时间，时间戳转化为时间肯定需要时区的转时间戳时间化。时间戳+时区=时间。
2. mysql中的函数FROM_UNIXTIME()(把时间戳转化为时间)和UNIX_TIMESTANMP()(把时间转化为时间戳)也会根据mysql时区进⾏的转换

三、mysql存储时间时到底选择datetime，timestamp还是int类

（1）datetime和timestamp⽐⼤⼩

datetime类型字段

datetime类型字段存储的值的格式为时间格式，所以按照时间格式来⽐较⼤⼩即可。
⽐如获取2020-03-01以后新注册的⽤户
select from users where created_at > 2020-03-01
⽐如获取2020年以后新注册的⽤户
select from users where created_at >= 2020
### 或者
select * from users where year(created_at) > ‘2020’

timestamp类型字段

timestamp类型的字段对外表现形式和datetime类型是完全⼀致，所以⽐较时间⼤⼩和datetime完全⼀致。
可能有的⼈认为timestamp存储形式是时间戳，那么能不能直接拿时间戳进⾏⽐较呢？⽐如如下sql,发现筛选的数据不对，所以timestamp类型的字段不能直接拿时间戳进⾏⽐较的
select * from users where created_at > 21313131

⼀次线上迁库发⽣的事故

我们的数据库是使⽤timestamp类型来存储时间的，当前的数据库的时区为+8:00。
有⼀次我们需要迁库，迁移的那个库没太注意时区，迁移过去之后发现所有⽤户的每天获取的积分都不对了，获取积分的时间都少了8个⼩时。最后反应过来可能是新的数据库的时区与⽼数据库不⼀致，查看后发现新数据库的时区果然不是+8:00，时区修改完之后就和之前的时间对上了。

总结

我比较喜欢用datetime来存储日期，因为timestamp范围太小，有时候牵涉到计算挺麻烦的。特别需要注意的是，如果用timestamp存储数据，最早时间是“1970-01-01 00:00:01”，可是在存入数据库的时候，因为数据库会自动减去8小时，这样一来，时间就变成1969年了，你会发现报错误了，可是这个异常又不是经常发生，会很纳闷
所以存储时间的类型是很重要的，选择时需要考虑清楚。