背景:
最近做数仓数据处理,想记录一批数据的有效时间,根据习惯,指定了有效日期为"2100-01-01 00:00:00",
又有一些需求,需要计算有效期到某个时间点的时间,故使用
select UNIX_TIMESTAMP("2100-01-01 00:00:00") -
UNIX_TIMESTAMP(now())
,数据写入后,发现竟然为负数,跟自己用日期计算的结果完全不同。故查阅了一些资料,了解了UNIX时间戳的故事。
基础资料:
UNIX时间戳是从1970年1月1日(UTC/GMT的午夜)开始所经过的秒数,不考虑润秒。
UNIX时间戳(英文为 Unix opoch, Unix time,POSIX time 或 Unix timestamp)
UNIX时间戳的 0 按照ISO 8601 规范为 :1970-01-01T00:00:00Z.
一个小时表示为UNIX时间戳格式为:3600秒;一天表示为UNIX时间戳为86400秒,润秒不计算。
趣事:
因为大多数的 Unix 系统中 UNIX时间戳存储为 32 位,首位为符号位,用来储存正负。0为正,1为负。所以UNIX时间戳存储在计算机中的最大值是0111111111111111111111111111111(第一位为0,其他31为1),当时间达到最大时,按二进制计算规则末尾加1,足2进1,所有的位数都会置位0。计算结果会是100000000000000000000000000000(第一为1,其他为0)。但是由于1位为符号为,所以会得到"错误"的时间戳结果。按这个规则进行推算,当UNIX时间戳达到2038年时会变成0,这样会引发 2038 年问题,也称为 Y2038。上一次时间戳为0是1970-01-01 00:00:00。这也算是一个公知问题了。
测试sql:
select UNIX_TIMESTAMP("2100-01-01 00:00:00")-UNIX_TIMESTAMP("2023-10-26 00:03:23")
,UNIX_TIMESTAMP("2038-01-19 11:14:08") , UNIX_TIMESTAMP("2023-10-26 00:03:23"),UNIX_TIMESTAMP("1970-01-01 00:00:00")
测试结果:下次时间戳为0的时间是,北京时间:2038-01-19 11:14:08