type
status
date
slug
summary
tags
category
icon
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 许可协议，转载请注明出处。
