Why is the range of the timestamp type 4713 BC to 294276 AD?

user_1456761 0
Viewed 451

Postgresql has a timestamp datatype with resolution 1 microsecond and range 4713 BC to 294276 AD that takes up 8 bytes (see https://www.postgresql.org/docs/current/datatype-datetime.html).

I have calculated the total number of microseconds in that range as (294276 + 4713) × 365.25 × 24 × 60 × 60 × 1000000 = 9.435375266×10¹⁸. This is less than 2⁶⁴ = 1.844674407×10¹⁹, but also more than 2⁶³ = 9.223372037×10¹⁸.

I might be off by a few days due to calendar weirdness and leap years, but I don't think it's enough to push the number below 2⁶³.

So, why were the limits chosen like that? Why not use the full range available with 64 bits?

postgresql
1 Answers

The internal representation of timestamps is in microseconds since 2000-01-01 00:00:00, stored as an 8-byte integer. So the maximum possible year would be something like

SELECT (2::numeric^63 -1) / 365.24219 / 24 / 60 / 60 / 1000000 + 2000;

        ?column?         
═════════════════════════
 294277.2726976055146158
(1 row)

which explains the upper limit.

The minimum is explained by a comment in 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.
 */

So the minimum is taken from the lower limit on Julian dates.

user_64643080
Related