Postgresql具有timestamp
数据类型,分辨率为1微秒,范围为4713 BC到294276 AD,占用8个字节(请参阅https://www.postgresql.org/docs/current/datatype-datetime.html)。
我计算出该范围内的微秒总数为(294276+4713(×365.25×24×60×60×1000000=9.435375266×10⁸.这小于2⁶⁴ =1.844674407×10⁹,但也超过2⁶³=9.223372037×10⁸.
由于日历的怪异和闰年,我可能会休息几天,但我认为把数字降到2以下是不够的⁶³。
那么,为什么要这样选择限制呢?为什么不使用64位的完整范围?
自2000-01-01 00:00:00
以来,时间戳的内部表示形式以微秒为单位,存储为8字节整数。所以可能的最长年份是
SELECT (2::numeric^63 -1) / 365.24219 / 24 / 60 / 60 / 1000000 + 2000;
?column?
═════════════════════════
294277.2726976055146158
(1 row)
这解释了上限。
src/include/datatype/timestamp.h
:中的注释解释了最小值
/*
* Range limits for dates and timestamps.
*
* We have traditionally allowed Julian day zero as a valid datetime value,
* so that is the lower bound for both dates and timestamps.
*
* The upper limit for dates is 5874897-12-31, which is a bit less than what
* the Julian-date code can allow. For timestamps, the upper limit is
* 294276-12-31. The int64 overflow limit would be a few days later; again,
* leaving some slop avoids worries about corner-case overflow, and provides
* a simpler user-visible definition.
*/
因此,最小值取自儒略历日期的下限。