数据库存储时间你用对了吗？

❝本文来源于快手小粉丝咖啡的投稿，全文一起聊了聊，在数据库里如何做存储时间，从 DateTime、Timestamp、数值型时间戳3 个类型，从理论+代码来分析如何做选择。

数据库存储时间的类型及对比

1、前言

我们平时在开发中不可避免的要存储时间，比如我们要记录某条数据的创建时间、更新时间等等。数据库中有多种数据类型可以存储时间，那不同数据类型我们要怎么选择？

下面我会先介绍不同类型的特点和区别，也再去了解了不同的大佬、不同公司如何对待这个问题。最后，谈到时间，也额外补充Java中时间的一些问题。

2、DateTime

2.1、介绍：

DateTime 类型是没有时区信息的（时区无关） ，DateTime 类型保存的时间都是当前会话所设置的时区对应的时间。

2.2、日期范围和存储空间：

这个类型能保存大范围的值，从1000年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。

默认情况下，MySQL以一种可排序的、无歧义的格式显示Datetime值，例如“2008-01-16 22:37:08”

3、Timestamp

3.1、介绍：

Timestamp 类型字段的值会随着服务器时区的变化而变化，自动换算成相应的时间。因此，在不同时区，查询到同一个条记录此字段的值会不一样。

MySQL提供了FROM_UNIXTIME() 函数把 Unix 时间戳转换为日期，并提供了UNIX_TIMESTAMP() 函数把日期转换为 Unix 时间戳。

Timestamp 显示的值依赖于时区。MySQL服务器、操作系统，以及客户端连接都有时区设置。

3.2、存储空间和日期范围：

Timestamp 类型保存了从 1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同。Timestamp只使用4个字的存储空间，因此它的范围比Datetime小的多，只能表示从1970年到2038年。

3.3、测试

测试在不同时区下，DateTime 和 Timestamp 的区别：

建表SQL语句

CREATE TABLE`time_zone_test` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `date_time` datetime DEFAULT NULL,  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());

查看数据：

select date_time,time_stamp from time_zone_test;

结果：

修改时区：

set time_zone='+0:00';

结果会发现 date_time 的时间不会自动转换，time_stamp 则显示成具体时区的时间

那这个时候，再插入当前时区（UTC）的当前时间的SQL语句，再切换为原来的时区（UTC+8）

INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW()); set time_zone='+8:00';

再查看结果：

会发现不同时区下 date_time 的数据会有一定问题。

因此，当我们使用 DateTime 进行存储时间，要警惕客户端时区的变化，时区的变化可能会导致数据出现问题。

4、数值型时间戳

4.1、介绍

时间戳的定义是从一个基准时间开始算起，这个基准时间是「1970-1-1 00:00:00 +0:00」，从这个时间开始，用整数表示，以 秒/微秒 计时，随着时间的流逝这个时间整数不断增加。这样一来，我只需要一个数值，就可以完美地表示时间了。我们项目中，用 System.currentTimeMillis() 描述时间戳，计时单位是 微秒。

这个数值是一个绝对数值，即无论的身处地球的任何角落，这个表示时间的时间戳，都是一样的，生成的数值都是一样的，并且没有时区的概念，所以在系统的中时间的传输中，都不需要进行额外的转换了，只有在显示给用户的时候，才转换为字符串格式的本地时间。

使用数型值时间戳跨系统也很方便，毕竟只是存放的数值。缺点也很明显，就是数据的可读性太差了，你无法直观的看到具体时间。

5、对比

PS：MySQL 5.6.4 之后，允许 Datetime、Timestamp 使用 0～3个字节的额外空间去存储 微秒。语法为：type_name(fsp) ，例如：CREATE TABLE t1 (dt DATETIME(6)); （微秒的精度为6）

其他大佬如何选择存储时间的类型：

在阿里巴巴Java开发手册有如下规定：

《高性能MySQL第三版》则是如下建议：

王天舟大佬认为使用 bigint 的考量在于：

1. 避免因为大家需要理解时区转换等问题，造成困扰。
2. 避免夏令时等因素影响。
3. 尽量输出到端上进行时间渲染（一样是时区问题）。
4. 虽然我们也可以继续用 datetime ，然后把规则解释清楚，但是相比之下，用bigint/long的话，要解释的会少的多（也就意味着需要记忆的内容也少的多）。

PS：夏令时：每年从四月中旬第一个星期日的凌晨2时整（北京时间），将时钟拨快一小时，即将表针由2时拨至3时，夏令时开始；到九月中旬第一个星期日的凌晨2时整（北京夏令时），再将时钟拨回一小时，即将表针由2时拨至1时，夏令时结束。

其他公司：

滴滴公司的同学，他们说DateTime 和 Timestamp都有用

小米公司的同学，他们说使用 bigint

因此，对于数据库选择什么类型去存储时间，好像并没有一个银弹，了解每种方式的优点和缺点，根据实际场景才是王道。

6、拓展：关于Java中时间的问题

Java 8 之前的时间会有一些 BUG。在开发的过程中需要注意。

6.1 格式化的问题：

Calendar calendar = Calendar.getInstance();
// 2020-12-31
calendar.set(2020, Calendar.DECEMBER, 31);
Date strDate1 = calendar.getTime();

// 小写YYYY
DateFormat formatLowerCase = new SimpleDateFormat("yyyy/MM/dd");
System.out.println("2020-12-31 to yyyy/MM/dd: " + formatLowerCase.format(strDate1));
// 大写YYYY
DateFormat formatUpperCase = new SimpleDateFormat("YYYY/MM/dd");
System.out.println("2020-12-31 to YYYY/MM/dd: " + formatUpperCase.format(strDate1));

结果：

这里存在的问题是：

YYYY是week-based-year。

Week year 意思是当天所在的周属于的年份，一周从周日开始，周六结束，只要本周跨年，那么这周就算入下一年。所以 2020-12-31 识别出来会变成 2021-12-31。

因此，在 阿里巴巴Java开发手册中，也做了强制规定：

PS：在Java 8 中，对这个问题进行了改进，如果使用Java 8 时间 API 的话，则会报错。

6.2 Date 是可变的。

因为 Date 是可变的，因此该类不是线程安全的。

final class Photo {
    // 这里虽然用 final 修饰了，但是 createTime 的时间仍然是可变的。
    // 初始值为 1970年1月1日午夜
    private final Date creteTime = new Date(0);

    public Date getCreteTime() {
        return creteTime;
    }
}

public class Main {

    public static void main(String[] args) throws ParseException {
        Photo photo = new Photo();
        System.out.println(photo.getCreteTime());

        // 在外界进行更改
        Date copyDate = photo.getCreteTime();
        copyDate.setTime(new Date().getTime());

        // 再次输出发现时间变了
        System.out.println(photo.getCreteTime());
    }

}

结果：

可变的底层原因是，源码中的 setTime 直接修改时间

PS：Java 8 的 时间API 则对这个问题进行了改进。时间API 将不再是可变的。（不可变的原理是和 String 不可变是一样的。）以 LocalDate 为例，源码为：

7、参考资料

1. 《高性能MySQL》
2. 阿里巴巴Java开发手册
3. Java后端开发的那些坑
4. Data Type Storage Requirements
5. 老生常谈！数据库如何存储时间？你真的知道吗？

END