为什么要用毫秒值的形式存储日期时间？

毫秒值

存储

解答

日期

64 条回复 • 2019-07-05 12:15:29 +08:00

1

saulshao

2019-04-01 15:42:38 +08:00

我印象中，这个是从 Unix 学来的。Unix 里最早所有的时间都是这么存储的。后来关系数据库就也这么干。
计算机最早只认识整数，其他的数据类型都是从整数扩展来的。日期时间也不例外。我怀疑即使是现代关系数据库，这个日期时间格式在后台也是用毫秒存储的。
搞数据库软件开发的能不能出来详细解释一下。

2

codebear01

2019-04-01 15:42:55 +08:00

节省空间吧我是存成 datetime 类型

3

opengps

2019-04-01 15:43:09 +08:00

时间戳，某些系统下获取很方便，存储省空间，大小比较筛选范围也比较方便。只是展示时候需要转化下

4

banker

2019-04-01 15:46:13 +08:00

为什么不呢?毫秒可以转换为秒 /分钟 /小时 /天,反之则转不回来
毫秒有一定的区分度,1 秒钟可以有几万笔交易

5

Septembers

2019-04-01 15:46:15 +08:00

1

unix timestamp 会带来很多额外的问题
1. 闰秒
2. 会与 UT1 存在偏差
3. 多时区表达问题

6

hihipp

2019-04-01 15:46:23 +08:00

https://www.v2ex.com/t/331989

7

rrfeng

2019-04-01 15:48:28 +08:00 via Android

9

@Septembers

使用 timestamp 恰恰是避免这些问题的最佳实践！！

8

murmur

2019-04-01 15:49:42 +08:00

省事直接 new 一下就是对应的时间封装

9

Septembers

2019-04-01 15:52:00 +08:00

2

@rrfeng timestamp 是造成这些问题的原因
ISO 8601 方式描述时间才能解决这个问题

10

love

2019-04-01 16:39:43 +08:00

@Septembers timestamp 是怎么产生闰秒问题能说说吗

11

geelaw

2019-04-01 16:45:50 +08:00 via iPhone

@love #10 需要许多额外数据才能把时间戳转换为挂钟时间

在 .NET 里面默认比毫秒还要精确，是 tick （ 100 纳秒），数据库内部存储时间可能是存储从某刻经难过的 tick 数，也可能是挂钟时间。

12

rrfeng

2019-04-01 16:45:57 +08:00 via Android

@Septembers
ISO 8601 只是一个把 timestamp 转换成易读格式的标准而已。

13

love

2019-04-01 16:51:29 +08:00

@geelaw 需要什么额外数据？

14

danielmiao

2019-04-01 16:52:46 +08:00

mysql 的时间格式精度只能到秒级(至少是 5.5 以前是这样的)，如果需要记录毫秒级，只能使用 Unix timestamp

15

huangdayu

2019-04-01 16:54:19 +08:00

不是因为时间格式转换方便吗？

16

ksc010

2019-04-01 17:15:02 +08:00

搜了下貌似时间戳转换为时间字符串没有考虑闰秒的情况

17

DOLLOR

2019-04-01 17:19:48 +08:00 via Android

你想计算两个时间的差，直接减就可以了

18

leo108

2019-04-01 17:21:57 +08:00

看来楼主是没有受过时区问题的毒打

19

webdisk

2019-04-01 17:26:59 +08:00

@Septembers 你完全说反了。
@ksc010 有个软件包会按照你说的这些情况进行更新一些数据，最新版本是 tzdata2019a.tar.gz

20

zjsxwc

2019-04-01 17:30:47 +08:00

时区问题，我怎么知道你给的字符串日期是哪个时区

方便计算时间区间，直接数字加减比较就行

21

hirasawayui

2019-04-01 17:30:50 +08:00

@leo108 我曾经遭受了 safari 时区问题的毒打，哈哈哈

22

reus

2019-04-01 17:33:19 +08:00

用时间类型，可以储存时区等信息

23

jamesliu96

2019-04-01 17:37:53 +08:00 via Android

@Septembers 千古奇才啊

24

geelaw

2019-04-01 17:43:25 +08:00

3

@love #13 你需要知道哪些时间点发生了闰秒，以及你需要的时区，以及那个时区在那个时候的夏令时调整。

@webdisk #19 我觉得 @Septembers 和 @rrfeng 以及其他人观点不同是因为大家认为的“日期时间”不是同一个。

我认为“日期时间”是指按照历法决定的一个时刻的年、月、日、时、分、秒（不考虑相对论，假设只有一个时刻），而“时刻”是一个和历法无关的概念。

例如“太平洋时间 2019 年 3 月 10 日 2 时 31 分 12 秒”是一个不存在的“日期时间”，因为夏令时的调整。

再比如“北京时间 2017 年 1 月 1 日 7 时 59 分 59.5 秒”对应两个“时刻”，因为闰秒。

25

kera0a

2019-04-01 17:46:52 +08:00 via iPhone

因为时间戳不包含时区信息，所以不存在时区问题😄

26

aleko

2019-04-01 17:48:36 +08:00

我司用纳秒..js 的精度还不够 !!!

27

yushiro

2019-04-01 17:52:23 +08:00 via iPhone

@kera0a timestamp 用的就是 UTC+0 时区，请注意 unix timestamp 的定义

28

rrfeng

2019-04-01 17:54:41 +08:00

@geelaw 你这样一说我才醒悟过来

@Septembers 同学之前提到了 UT 的概念，其实就是『天文历法时间』，根据天文天象计算出来的时间。也就是现在说的 GMT 时间。而 UTC 就是 timestamp 表示的时间，根据一个时间点，以原子钟的标准秒计时计算出来的时间。

而 ISO 8601 跟计时方式或者时间获得方式无关（ UT 还是 UTC ），只是一种『表示规范』。
所以正确的用法是：
1. 当前的 UTC 时间是 2019-04-01T09:48Z （ ISO 8601 表示法。）
2. 当前的 GMT 时间是 2019-04-01T09:48Z （ ISO 8601 表示法。）

所以『绝对时间』里，utc=ut，但是要让两者在表示上相同（同时转换为 ISO 8601 ），就会需要做闰秒闰年的调整了。

29

CruelMoon

2019-04-01 17:55:30 +08:00

@geelaw #24 偶觉得你的说法很有启发性，但偶有个问题：unix 时间戳是从 1970 年 1 月 1 日 0 时 0 分 0 秒起至现在的总秒数。1970 年 1 月 1 日是历法中的概念，那么由此推算出的 unix 时间戳也是和历法有关的概念。
所以要怎么得到一个纯粹的“时刻”呢？

30

kera0a

2019-04-01 17:59:18 +08:00 via iPhone

@yushiro 😄我知道，我只是调侃一下楼上说的时间戳造成的时区问题。谢谢提醒

31

geelaw

2019-04-01 18:13:02 +08:00 via iPhone

@CruelMoon #29 UTC 的 1970 年 1 月份 1 日凌晨对应惟一的“时刻”，而“现在”也是一个时刻，所以 Unix 时间戳是用一个时刻（时间戳表示的时刻）和一个固定时刻（ 1970-1-1 ）的差表示第一个时刻。

32

rrfeng

2019-04-01 18:19:30 +08:00

@CruelMoon
某个纯粹的时刻是与任何无关的（不考虑高深的物理学上的概念）。
比如『我出生的那一刻』，而 UT （历法时间）和 UTC （协调时间）都是用来表述我出生的绝对时刻的一种方式。
而历法时间中的『 1970 年 1 月份 1 日 0 时』是一个绝对时刻，只是我们用 UT 来描述它。

33

libook

2019-04-01 19:06:56 +08:00

没有万金油，根据需求设计，希望做比较和计算方便就用时间戳，希望做分组方便就记录日期，日期也有带时区不带时区的，要是都需要就冗余一下，全都记录。

对于这种问题，不站队，生产力是最重要的。

34

passerbytiny

2019-04-01 19:19:15 +08:00 via Android

1

@saulshao 据我所知，oracle 和 mysql 的日期和时间类型都是年月日时分秒类型，而不是时间戳。通常这些类型还会添加额外的辅助字节。
时间戳表示法的原因不是电脑只认整数，电脑只认二进制，整数也是要转换的。用时间戳的原因是这两个：转换逻辑换空间，“懒人技巧”换复杂的对象。

35

richard1122

2019-04-01 19:56:18 +08:00

@passerbytiny #34 然而 mysql 对于 DATETIME 或者 TIMESTAMP 类型的存储就是时间戳，只是默认展示时进行了格式化（且与链接字符串的时区或 mysql server 配置的时区相关）

参考：

https://dev.mysql.com/doc/refman/8.0/en/datetime.html

"The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC."

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html -> Date and Time Type Storage Requirements

36

zqx

2019-04-01 20:13:00 +08:00 via Android

只有整型，是所有数据库都支持的

37

Buffer2Disk

2019-04-01 20:54:24 +08:00

@richard1122 有一个新问题，既然 TIMESTAMP 是有时间范围限制的，2039 年以后的怎么表示。。。。。

38

GeekCourse

2019-04-01 21:07:06 +08:00 via Android

@Buffer2Disk 换成 long 就可以了

39

loveCoding

2019-04-01 21:58:02 +08:00

花里胡哨不靠谱,还是 long 简单

40

sampeng

2019-04-02 00:47:11 +08:00 via iPhone

@rrfeng 多年前被时区支配的恐惧…

41

sampeng

2019-04-02 00:56:06 +08:00 via iPhone

楼上讨论飞了…应该和 linux 什么的无关。不然 mysql 之类里面为何有个专门的日期字段…个人觉得有些是习惯，有些是跟风。我觉得都可以…上面说处理日期问题，复杂度综合来说都各有长短不相上下。因为现在的时间库都抽象的很好用了。mysql 也自带两类个互转函数。

42

gjquoiai

2019-04-02 01:38:06 +08:00

我从来没搞明白过时间。。_(:з)∠)_ 所以我每次都查这个。。http://taaviburns.ca/presentations/what_you_need_to_know_about_datetimes/what_you_need_to_know_about_datetimes.pdf

43

binux

2019-04-02 01:53:53 +08:00 via Android

不需要而额外信息就能方便地计算比较

44

t6attack

2019-04-02 03:29:51 +08:00

日期时间的书写格式有 N 多种，如果把语言因素也加进去，则有上百种。这么多写法，总要有一个标准。
阿拉伯数字人类共通，作为标准最合适。方便转为任意可读格式。而且方便排序、加减、筛选等操作。

45

nine

2019-04-02 04:44:37 +08:00 via iPhone

从开发的角度来讲不应该使用 timestamp，浪费开发成本。
当然如果你有积累的时间戳处理 lib 并且能熟练处理除外。
如果你有遗留系统使用 timestamp，除外，因为那也是没办法的事。

新系统应尽量使用数据库时间字段

46

passerbytiny

2019-04-02 08:50:28 +08:00

@richard1122 #33 你是从哪里看出来 DATETIME 是时间戳存储的，自己阅读能力有限，请不要误导人。

DATE DATETIME 是年月日时分秒+纳秒值格式，TIMESTAMP 是时间戳的格式。你就算没看懂 “ MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.” 这句话，看到'1000-01-01 00:00:00' to '9999-12-31 23:59:59'这种范围，也不应该认为它是时间戳。

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

47

Jonz

2019-04-02 08:59:05 +08:00

我理解的就是方便处理时区问题

48

shakoon

2019-04-02 09:36:16 +08:00

当系统并发很高的时候，用秒是远远不够的。一秒钟的数据条数就有成百上千时，用毫秒来记录更准确的发生时间就有意义了。对于金融机构来说，交易流水记到毫秒已经是最宽松的要求了。

49

jlkm2010

2019-04-02 10:17:38 +08:00

在 mysql 中用 bigint 存储毫秒时间戳

50

richard1122

2019-04-02 10:31:09 +08:00

@passerbytiny #46 不好意思日常不太用 DATETIME 类型，想当然了。

纠正一下前面的回复，DATETIME 的保存方式参考： https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html

51

EasyProgramming

2019-04-02 11:43:07 +08:00

看下这个：
https://my.oschina.net/u/2312080/blog/3016702

52

tonyaiken

2019-04-02 11:48:58 +08:00

我之前分享过一篇深度探讨计算机应该如何处理时间的文章 https://tonyxu.io/zh/reads/2019-03-03/

53

aleung

2019-04-02 12:27:42 +08:00 via Android

我们的 design guideline：系统内部所有涉及到时间的变量和持久化都统一使用 Unix epoch millisecond，在系统边界（如输入和显示）才转换。这样应用内部的处理才简洁一致。
类似语言编码，在系统内部统一一种编码格式才简单。

54

yaxin

2019-04-02 15:11:44 +08:00

unixtimestamp 与时区无关

55

t2doo

2019-04-02 15:20:43 +08:00

汗。。。我一直都是存成 1456794054 这样的，然后 2 个时间相减再求一下时间差。。。还是存成 timestamp 吧

56

t2doo

2019-04-02 15:21:34 +08:00

错了，还是存成 datetime 吧

57

gaius

2019-04-02 15:27:53 +08:00

oracle 和 mysql 的 timestamp 类型都没有时区信息。

58

c4f36e5766583218

2019-04-02 18:06:33 +08:00

1

以 mysql 来说，用 bigint 来存较好，优点 1. 存储值与时区无关 2. 不用考虑各台机器时区以及 mysql 所用时区不一致问题，缺点: 查询结果需做转换才能可读(mysql-cli/gui)
----
DATETIME: 存储的可以理解为就是你给它的值(yyyy-MM-dd HH:mm:ss)，但是不会带上时区信息，所以如果各台机器时区不一致，那库里存的就不知道它的时区了
TIMESTAMP: 存储的可以理解为把你给它的值(yyyy-MM-dd HH:mm:ss)以 mysql 所用时区来理解，转成 UTC 时间来存储，所以如果程序所用时区与 mysql 所用时区不一致，那 mysql 就存错值了
----
那如果程序所用时区与 mysql 所用时区都全部一致，DATETIME 和 TIMESTAMP 有什么区别呢？
1. 各台机器 0 时区，mysqlA 0 时区，数据都在 mysqlA 上，mysqlB 用的是 1 时区，把 mysqlA 导出导入到 mysqlB 中
__DATETIME: A,B 查询结果一致，时区信息自己记得是 0 时区
__TIMESTAMP: A,B 查询结果不同，分别是各自 mysql 所用时区
（ ps: 把 mysqlB 时区改一致不就完了......额，就以它不能改吧
2. 其它区别: 如取值范围。。（自己上网看
----
至于闰秒问题。不考虑 2333. [有看到建议取消闰秒]( https://zh.wikipedia.org/wiki/闰秒#建議取消閏秒)
TIMESTAMP 范围问题，程序能活到那个时候再说~（等 mysql 加大 TIMESTAMP 字节？
----
我有试过用 TIMESTAMP(3)的; java 程序里加 Filter 在取值设值时用程序所用时区和 mysql 所用时区差来调整时间 java.util.Date; json api 返回的话是用 Long 的。这样 sql 查库是可读的时间展示，但是其它语言程序好写这样的 Filter 吗？

59

c4f36e5766583218

2019-04-02 18:12:32 +08:00

#58 ```各台机器```改为```各个程序```比较好，反正大家理解意思就行

60

ggicci

2019-04-02 20:54:03 +08:00

可以降低理解成本、运维成本，多出来的时间可以泡杯 java 晒晒太阳

61

zjyl1994

2019-04-04 01:06:09 +08:00 via Android

之前 DateTime 的时候服务器时区数据库时区等等配置搞死人，用时间戳的话前端用的时候就转换一下，轻松又方便

62

c4f36e5766583218

2019-04-18 15:31:03 +08:00

#58 可能说的也不是那么准确(也可能胡说瞎说 /说错了)，反正大概明白意思就行。
总结就是(后面的数字代表时区，1 就是+1 时区，2 就是+2 时区):
1、mysql1 与 mysql2 时区不同
2、app1 从 mysql1 读写与 app3 从 mysql1 读写，app1 与 app3 时区不同
3、mysql1 与 app3 读写，mysql1 与 app3 时区不同
三种时区不同导致的问题。。

最近在使用:
* mysql-connector-java 8.0.15
发现 DATETIME，TIMESTAMP 的读写都会根据程序所用时区和 mysql 所用时区差值做时区上的修正
* mysql-connector-java 5.1.46
默认不会修正时间，需 useTimezone=true

ps1: 那我还加 Filter 来修正时间干嘛？我想到了，不是#58 说的原因，加这个 Filter 是用来做 sql 日志打印上的修正的。比如 app3 与 mysql1，app3 打印出来的可执行 sql 时间是按照 app3 显示的，如果直接 copy 出来拿到 mysql1-cli 上执行就会引起时区上的问题（程序上与数据库里是不会有问题的，这只是一个强迫症问题）

ps2: mariadb 可以用用感觉蛮好的

63

c4f36e5766583218

2019-04-18 16:28:26 +08:00

#62 一、mysql-connector-java 提供了修正时区的功能，可以解决上面说的 23 问题；但问题 1 在 mysql1 导出导入到 mysql2 下，DATETIME 类型后续的 app3 从 mysql2 读写数据会出现时区问题，TIMESTAMP 不会。二、那个 Filter 需 mysql-connector-java 提供可配否修正时区，不然不晓得怎么写。

64

shanechiu

2019-07-05 12:15:29 +08:00

https://dev.mysql.com/doc/refman/8.0/en/datetime.html