时间类型和时间戳_Mysql

Unix 时间戳以及日期表示方法

Unix 时间戳表示的是从世界标准时间（UTC，Coordinated Universal Time）的 1970 年 1 月 1 日 0 时 0 分 0 秒开始的偏移量。

全球共有 24 个时区，分为东西各 12 时区。所有地区在使用同一个时间戳的基础上，根据当地时区调整时间的表示。

现在比较常见的日期和时间的表示标准是 ISO8601，或者在其基础上更加标准化的 RFC3339。

举个例子，北京时间 2021 年 1 月 28 日 0 时 0 分 0 秒用 RFC3339 表示为：2021-01-28T00:00:00+08:00。

+08:00 表示东 8 区，2021-01-28T00:00:00 表示这个时区的人所看到的时间。加号如果改为减号，则表示西时区。

比较特殊的是 UTC 时区，可以表示为 2006-01-02T15:04:05+00:00，但通常简化为 2006-01-02T15:04:05Z。

在使用的时候，应当根据时区调整时间的展示。例如 1611792000 可以表示为 2021-01-28T00:00:00Z 或者 2021-01-28T08:00:00+08:00。

日期和时间的解析

不同的数据来源很可能使用不同的时间表示方法。根据是否可读分成两类：

用数字表示的时间戳
用字符串表示的年月日时分秒

数字类型就不详细说明。

字符串又根据是否有时区分为两类：

2021-01-28 00:00:00 没有包含时区信息
2021-01-28T08:00:00+08:00 包含了时区信息

在解析没有包含时区信息的字符串时，通常要由程序员指定时区，否则默认为 UTC 时区。如果附带时区，那就可以不用另外指定。

例如 Golang 的时间库，就有两个方法：

Parse(layout, value string)
ParseInLocation(layout, value string, loc *Location)

在解析的时候，会先根据年月日时分秒计算出一个整数。接着看 value 是否包含时区信息。

如果 value 包含时区，那么就会给解析后的整数加一个偏移量，这个偏移量由时区与 UTC 时区之间的位置关系决定。

如果 value 不包含时区信息，Parse 会将其设置为 UTC 时区，ParseInLocation 会根据传入的时区调整解析出来的整数，并将时区设置为传入的时区。

日期和时间的存储

和解析时一样，保存日期和时间的方式有多种。

例如 Golang 的 Time ：

type Time struct {
	wall uint64
	ext  int64
	loc *Location  // 位置。用于调整时间的表示。
}

Golang 存储的不是 Unix 时间戳，但是会根据情况将其转换为时间戳。对于 loc 的修改不会对 Unix 时间戳产生影响，只会影响时间的展示形式。

MongoDB 使用的 bson.Date 使用 int64 存储从 1970 年 1 月 1 日以来的毫秒数。

MySQL 使用 DATETIME 类型存储不包含时区的年月日时分秒，查询时以 YYYY-MM-DD HH:MM:SS 的形式展示。也可以用四个字节的 TIMESTAMP 类型存储 Unix 时间戳。

时间戳的问题

以前在保存时间戳的时候，通常都使用四个字节，也就是 32 位的有符号整数存储。

把二进制的 01111111 11111111 11111111 11111111 转化为十进制后得到 2147483647，再转化为北京时间得到 2038-01-19 11:14:07。

这就表示 32 位整数最多只能存储到 2038 年的时间，因此被称为 “2038 年问题”。

比较新的一些项目会通过各种方式解决这个问题，通常是使用 64 位整数来存储时间戳。但使用方式各有不同。

例如 Golang 使用了两个 64 位整数来存储。其中无法符号整数 wall，第一位表示是否有单调时间。

如果为 1，则表示有单调时间。
wall 的 2~34 位存储自 1885 年 1 月 1 日 0 时 0 分 0 秒以来的秒数，35~64 位存储纳秒数。
有符号的 64 位整数 ext 存储从进程启动以来的纳秒数（单调时间）。
如果为 0，则表示没有单调时间。
wall 的 2~64 不存储时间。
有符号的 64 位整数 ext 存储从 0001 年 1 月 1 日 0 时 0 分 0 秒以来的秒数。

MongoDB 则是使用 int64 存储从 1970 年 1 月 1 日以来的 UTC 毫秒数。

MySQL 没有解决 TIMESTAMP 类型的问题，它始终是四个字节。因此如果要解决这个问题，最好使用 DATETIME。但是 DATETIME 也有问题，它没法存储时区。不过大多数应用都无需考虑时区问题，无需担心。

时间的展示

数据库都默认使用 UTC。如果不加以处理，存储到数据库的时间就会展示为与本地实际展示的时间不一致的形式。

例如 MongoDB 存储的是从 1970 年 1 月 1 日以来的 UTC 毫秒数，像 Navicat 这种工具，会用 UTC 的形式展示时间。这样其他时区的人看起来就会不习惯。

而 MySQL 就更难处理了，DATETIME 不带时区。

解决这个问题有三种思路：

修改数据库配置，改成本地时区
MongoDB 这样设置不会有影响，仍然存储的是毫秒数。只是在展示的时候会使用配置的时区格式化字符串。
MySQL 这样设置后，会对 NOW() 这种函数的结果产生影响。不会对 SQL 语句中直接写 0000-00-00 00:00:00 的情况产生影响。
查询的时候将其重新转换为本地时区
有三种：
- 为数据库连接会话设置时区。同上，只是在会话级别产生影响。
  MySQL 会有影响，如果不同地方的会话设置不同时区，又使用了 NOW()，得到的结果不一致。
- 在代码上做一层包装，用于调整时区。
  MongoDB 没啥影响，毕竟存储的是毫秒数。只是展示的时候做个调整。
  MySQL 可以始终存储为 UTC 形式，然后要展示的时候，用代码把时间格式化为本地时区的形式。
- 为数据库表创建 view，在 view 里面处理时区
  例如 MongoDB：
```
db.createView("view_name","collection_name",[
    {
        $addFields: {
            date: {
                $dateToString: {
                    date: "$date",
                    format: "%Y-%m-%dT%H:%M:%S+08:00",
                    timezone: "+08:00"
                }
            }
        }
    }
]);
```
  addFields 会覆盖同名的字段。上面的语句会将原先的 date 字段的值以新的格式展示。
存储的时候创建一个年月日时分秒和本地展示时间一致的 UTC 时间
这会改变数据库存储的时间戳，使得时间戳与实际时间戳不一致。对 MongoDB 会产生影响。
不过 MySQL 的 DATETIME 不是用时间戳，所以只要格式化到 SQL 语句的时间形式是本地时区的就行了。只是如果出现跨时区的用户、数据、开发人员，处理起来就比较麻烦。

具体实例

Golang MongoDB 库

MongoDB 的官方库在存储的时候，会使用 UTC 的时间戳。但在查询的时候，会判断是否设置了使用本地时间展示。如果没有设置按本地时间展示，则会将 Time 设置为 UTC 时区。

if !tc.UseLocalTimeZone {
    timeVal = timeVal.UTC()
}

如何事先配置好？

builder := bsoncodec.NewRegistryBuilder()

// 注册默认的编码和解码器
bsoncodec.DefaultValueEncoders{}.RegisterDefaultEncoders(builder)
bsoncodec.DefaultValueDecoders{}.RegisterDefaultDecoders(builder)

// 注册时间解码器
tTime := reflect.TypeOf(time.Time{})
tCodec := bsoncodec.NewTimeCodec(bsonoptions.TimeCodec().SetUseLocalTimeZone(true))
registry := builder.RegisterTypeDecoder(tTime, tCodec).Build()

client, err := mongo.NewClient(options.Client().ApplyURI(uri), options.Client().SetRegistry(registry))

MongoDB 使用的 bson.Date 使用 int64 存储 1970 年 1 月 1 日以来的毫秒数。从 MongoDB 查出来的也是这个数据。

如果 decode 的时候指定了存储结果的结构体的时间字段的类型，如 time.Time。则会将 int64 转化为 time.Time。如果不指定，则返回 int64。

可见 MongoDB 官方库使用的是第二种思路。

Golang MySQL 驱动的实例

https://github.com/go-sql-driver/mysql#loc

需要在连接的时候设置。dsn 里面带上 loc 参数。

在解析查询结果中的 DateTime 类型的时候，会将字节转换为字符串形式。这个字符串形式最长的情况是 0000-00-00 00:00:00.0000000。驱动会根据实际长度解析。

MySQL 驱动的做法是，如果 dsn 有带 loc 参数，那么在解析年月日时分秒和毫秒后，以这些数据和时区创建 time.Time。即 time.Date(y, mo, d, h, mi, s, t.Nanosecond(), loc)。

而在 insert 操作时，会将 time.Time 设置为指定的时区。v.In(mc.cfg.Loc).AppendFormat(b, timeFormat)，这里的 v 就是我们 Insert 的类型为 time.Time 的值。

可见 MySQL 驱动使用的是第三种思路。