Re: Support for dates before 4713 BC - Mailing list pgsql-general

On 2022-08-20 14:05:42 +0000, Watzinger, Alexander wrote:
> I'm working on the open source project OpenAtlas (https://openatlas.eu) which
> is used to enter historical and archeological data.
> Although we really appreciate the wonderful PostgreSQL software, the limitation
> of not being able to use dates before 4713 BC is problematic in regard to
> archeological data.
> The only reason for this limitation I found was that the Julian Calendar wasn't
> created with this in mind.

Nitpick:

The Julian *calendar* is the calendar (with 365 days per year and leap
years every 4 years) devised by Julius Caesar (or somebody in his employ)
modified so that the (presumed) birth of Jesus falls into year 1.

The Julian *date* is a day count starting at January 1st 4713 BC, 12:00
UTC of the Julian calendar.

I assume you are talking about the latter.

> The only suggestion I found was to write an own implementation just
> using integers for years. But building a parallel date system in this
> case would be way to cumbersome and error prone, we really like using
> the database for date operations.
>
>
> So my questions are:
> Any chance to add support for dates before 4713 BC? We really would appreciate
> that.
>
> The 4713 BC limit feels very arbitrary, what is the reason for this exact
> limit?

Any choice of epoch is arbitrary. Generally people seem to like epochs
in the past so that all dates they are interested in can be represented
by positive numbers. Unix time_t starts at 1970-01-01T00:00Z, because
the 1970's had already begun so you wouldn't need a date before that on
a computer (and 1970 is a somewhat roundish number). The Julian date
uses 4713 BC as its starting point because that was when three cycles
used by astronomers last coincided and from the point of view of a 16th
century astronomer it was also safely in the past.

I don't know why PostgreSQL uses the Julian date instead of some other
schema. My guess is that it was chosen because it's a simple day count
(so no ambiguity with leap years) and because it also covers a good
chunk of ancient history, so the problem you are facing now would be
rarely encountered.

Of course since it is a simple count, it can easily be extended into the
past. Day 0 is November 24, 4714 BC (proleptic Gregorian Calendar), so
day -1 would be November 23, 4714 BC, and so on.

The problem would be that there are probably a lot of calculations which
assume that the date can never be negative, and those would have to be
checked and if necessary corrected.

Another potential problem could be that switching from an unsigned int
to an int halves the positive range. But it seems that this is already
capped at 2**31 days (5874897 AD), so that wouldn't be a problem here.

        hp


--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"