type
Post
status
Published
date
Dec 20, 2024
slug
timestamp&datetime&bigint
summary
tags
时间戳
timestamp
datetime
数据库
category
技术分享
icon
fa-solid fa-file
password
时间戳、timestamp和datetime、bigint
0 时间戳
首先我们来了解一下什么是时间戳。
或许大家都曾听说过时间戳这个东西，或许还有人听说过时间戳是 " 绝对的 "，那么为什么时间戳是绝对的，是怎么实现的呢？
首先我们要知道时间戳的起始时间：
1970年1月1日 00:00:00 UTC。年月日，时分秒大家都能看懂，但是这个 UTC 是什么东西？其实它的名字叫做协调世界时（Coordinated Universal Time，也叫世界统一时间）。众所周知，世界上有 24 个时区，25 个时间线，起始线和结束线相互重合，因此在重合的这条线，也就是 本初子午线 边缘跳跃的话，时间要加一天或减一天（从西往东减一，从东往西加一）。而这条线（即 0 度经线）所在的时间就是协调世界时（UTC），UTC 是全球统一的标准时间，各国、各区所在的时区的时间都是在此基础上计算而来的。看到这大家应该知道时间戳是怎么做到绝对了，因为时间戳实际上是 UTC 的时间戳，与时区是无关的，无论你在喜马拉雅山还是马里亚纳海沟，生成的时间戳都是相同的。但也正因如此，时间戳无法表达各个时区的时间，所以在将时间戳转为
YYYY-MM-DD dd-mm-ss 格式后还要再加上时区的偏差值。如 1734652800 对应的时间为 2024-12-20 00:00:00 UTC+0:00，中国时间为 2024-12-20 08:00:00 UTC+8:00，纽约时间为 2024-12-19 19:00:00 UTC-5:00。那么接下来有一个问题：时间戳有没有最大值？或者说有生之年时间戳会不会用完？
答案是有最大值，并且可以被用完。大家应该知道 windows 有 32 位和 64 位两种区别吧，不只是 Windows，MacOS、Linux 都有 32 位和 64 位之分，准确的来说是老机器和新机器之分。过去的老机器使用的是 32 位的 CPU，一次性只能处理 32 位（也就是 4 个字节）的数据，相应的，许多数字也被设计成了最大 32 位的方便计算，时间戳就是其中之一。而 32 位能表示的最大值是 2^32=2147483647，另外 1 年 365 天的总秒数是 31536000，相除等于68 年，准确的来说是 1970-01-01 00:00:01 到 2038-01-19 03:14:07。
2038 年距离我们并不远，甚至近在眼前，加上预估互联网井喷式的发展，数据量日益膨胀的未来，更快的 64 位 CPU 便应运而生，操作系统也随之变为 64 位的，时间戳也用 64 位来存储了，比如 Java 中时间戳是用 Long 类型存储的，而 Long 最大支持 64 位的数据。
虽然还有些软件和程序仍然还在使用 32 位的架构，但好在 64 位 CPU 能够向下兼容 32 位操作系统，相信这些软件会在 2038 年之前完成转变。
接下来说说 MySQL 中存储时间的两种数据类型：
TIMESTAMP 和 DATETIME 。它们的主要区别在于存储范围、精度以及是否与时区相关。以下是详细对比：1 TIMESTAMP
1.1 特点
- 直接存储 32 位的时间戳。
- 存储日期和时间在 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 范围。
- 与时区相关：
- 存储时会根据当前会话的时区设置转换为 UTC。
- 读取时会将 UTC 转换回当前会话的时区。
- 占用 4 字节 的存储空间。
- 支持的格式：
YYYY-MM-DD HH:MM:SS。
1.2 适用场景
- 需要存储与时区相关的时间戳。
- 需要记录事件发生的时间，且需要确保在不同时区显示一致。
- 分布式系统或跨区域应用，确保时间一致性。
- 打个比方，比如美国和中国都用同一套试卷，为避免考卷泄露，商量好了要同时开始考试，如果中国考试时间为 1 月 23 日下午 8 点，那么美国同一刻就应该在上午 7 点，这便是确保时间一致性。否则在中国的考生就等着美国的考生传来答案了。
1.3 示例
CURRENT_TIMESTAMP可以作为默认值。
- 自动更新时间戳是
TIMESTAMP的一个便捷功能。
2 DATETIME
2.1 特点
- 不存储时间戳，存储的是时间转为的紧凑的二进制格式（而非早期版本中的字符串存储）。
- 存储日期和时间的范围是 1000-01-01 00:00:00 到 9999-12-31 23:59:59。
- 与时区无关：
- 数据存储和读取时不会进行时区转换。
- 占用 8 字节 的存储空间。
- 支持的格式：
YYYY-MM-DD HH:MM:SS。
其存储大小如下：
精度 | 存储大小 |
无微秒（默认） | 5 字节（40 位） |
精确到 1-2 位微秒 | 6 字节（48 位） |
精确到 3-4 位微秒 | 7 字节（56 位） |
精确到 5-6 位微秒 | 8 字节（64 位） |
- 无微秒的
DATETIME：2024-12-27 15:30:45占用 5 字节。
- 带 6 位微秒的
DATETIME：2024-12-27 15:30:45.123456占用 8 字节。
2.2 适用场景
- 不需要与时区相关的时间转换。
- 需要记录更广泛时间范围的事件，比如生日、业务时间表、日志记录及历史数据或未来事件。
- 打个比方，你的生日是 1 月 23 日，但是你移居美国华盛顿后当然也是按照当地的 1 月 23 日庆祝生日，不可能按照国内的时间吧。如果用
TIMESTAMP来存储生日，到了华盛顿你会发现 " 咦，我的生日怎么变成 1 月 22 日的上午 11 点了 "，所以为了避免这种现象用定值是最好的，字符串是一种方式，但对于 MySQLDATETIME更舒适。
2.3 示例
DATETIME字段的默认值可以是任意有效日期时间。
3 精度（MySQL 5.6.4+ 支持）
- 如果需要更高的时间精度，可以为
TIMESTAMP或DATETIME字段指定微秒精度（最大 6 位小数）。
- 示例：
4 BITINT
总结上述两种数据类型，
TIMESTAMP 直接存储时间戳，但是却是 32 位的；DATETIME 存储的时间无法加减时区（非常麻烦）。于是我们可以使用另一种方式：用 BIGINT 来存储毫秒级的时间戳。缺点：
- 无法阅读。
TIMESTAMP和DATETIME在显示时都为YYYY-MM-DD HH:MM:SS格式，能在数据库直观的看到年月日时分秒，而时间戳只是一堆无规则的数字（一般也没人在数据库里看时间，也算不得上什么问题）。
优点：
BIGINT是 64 位的，所以不用担心 68 年的问题。
- 方便时区转换。将时间戳传给后端后可以很方便地转为客户端时区的时间。
- 作者:林明菁
- 链接:https://blog.lxuan.fun/article/timestamp%26datetime%26bigint
- 声明:本文采用 CC BY-NC-SA 4.0 许可协议，转载请注明出处。
