datetime与timestamp的区别

一、基本对比

表1：时间戳范围及类型之间的对比

除此之外，timestamp 可以给默认值 ‘CURRENT_TIMESTAMP’ ,会自动更新当前时间；

二、MySQL修改时区

方法一：
    set global time_zone = '+8:00'; ##修改mysql全局时区为北京时间，即我们所在的东8区
    set time_zone = '+8:00'; ##修改当前会话时区
    flush privileges; #立即生效
方法二：
    vim /etc/my.cnf ##在[mysqld]区域中加上
    default-time_zone = '+8:00'
    /etc/init.d/mysqld restart ##重启mysql使新时区生效

三、实验操作

第一步：建立数据表

CREATE TABLE `abc_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `bigint1` bigint(20) NOT NULL,
  `datetime1` datetime NOT NULL,
  `timestamp1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

图一：abc_test数据表结构

如图一所示，建立一张abc_test表，然后将字段名与字段类型一致

第二步：修改时区

利用方法一，进行修改MySQL的时区，将当前时区，有 +8 时区修改为 +7 时区：

    set global time_zone = '+7:00';
  set time_zone = '+7:00';
    flush privileges;

第三步：实验及结果

当前本地电脑右下角时间 2019-01-24 17:52

执行以下SQL：

SQL一：
INSERT INTO abc_test (`bigint1`, `datetime1`, `timestamp1`)
VALUE
    (
        unix_timestamp(now()),
        now(),
        now()
    );

SQL二：
INSERT INTO abc_test (`bigint1`, `datetime1`, `timestamp1`)
VALUE
    (
        unix_timestamp(now()),
        '2019-01-24 17:52:03',
        '2019-01-24 17:52:03'
    );

图二：SQL语句执行结果

四、两种类型可能出现问题的场景

• 类型为datetime可能出现的问题情景：

  发生某件事情的时候，服务器（北京）时间为 17:00 客户端（南非）时间为 11:00，相差6个小时；如果直接存客户端（南非）的时间的时候，采用 datetime 类型，服务端开发的童鞋，会发现这个操作是 17:00 发生的，如果你的客户有多个国家时区，那么你想还原真正发生时间，根本就不可能；

上面的例子告诉我们，第一，存时间的时候，参照物千万不要去客户端的时间，要取服务端的时间；第二，如果非要取客户端时间，可以取时间戳，然后保存为bigint 也能避免此类错误

• 类型为timestamp可能出现的问题情景：

  这个出现问题就很明显了，你存的时候，明明是17：00，但是查看数据库确是18：00

五、总结

讲到这里，还需要引入Linux系统时间的两个概念

• 硬件时钟(hwclock)：

  硬件时钟是存储在CMOS里的时钟，关机后该时钟依然运行，主板的电池为它供电。那个时钟依照主板石英晶体振荡器频率工作，

• 系统时钟(systohc)

  在启动系统后，系统从硬件时钟读取时间信息，之后独立运行。当调整系统时钟或与internet同步后，不会改变硬件时钟，下次启动又会变成硬件时钟的时间。

所以，我大胆的猜测，MySQL内部也是有一个 硬件时钟 和 系统时钟 的，当我们修改MySQL的时区的时候，SQL语句中的 now() ，获取的就是 系统时钟 里面的时间，存datetimestamp 时，会直接保存系统时钟；

而如果是timestamp，则timestamp会将存的时间，转为 硬件时钟 的时间，所以，当你系统时钟是 +7 时区，硬件时区是 +8 时区，存timestamp会把你存的时间，转为硬件时钟的时间，进行存储，如果你硬件时钟和系统时钟相等时，自然没事，如果不相等的时候，他会以硬件时钟为标准进行存储；

• 作为开发人员，需要明确几个时区问题：

（1）PHP语言时区（php.ini里设置），影响 date() 函数；
（2）MySQL系统时区（my.conf里设置），影响SQL语句中的 now() 函数
（3）客户端时区（也就是用户的电脑自己设置的时区），影响的是 JavaScript new Date() 函数

这里也提供一个新思路，如果数据库存的是时间戳，但是不同的国家使用该系统，需要转换不同国家的时区进行显示，仅需要前端用JavaScript处理一下就能实现了，因为JavaScript处理的时候，使用的是客户端的时区

  date = new Date(timeStamp*1000),
  y = date.getFullYear(),
  m = date.getMonth() + 1,
  d = date.getDate(),
  h = date.getHours(),
  mi = date.getMinutes(),

图三：客户端设置时区的方式

• 总结：

  由 SQL二 可以得到，timestamp会有时区问题，你插入的明明是 17:52 然而真正插入数据库中的却是 18:52

由 SQL一 可得到，datetime在错误的时区，取当前时间【 now() 】，并插入数据库中时，插入的是错误的时间，而timestamp会自动纠错

由 SQL一 和 SQL二 可得到：存时间戳是最为准确的！