On 8/20/22 07:05, Watzinger, Alexander wrote:
Hi all,
I'm working on the open source project OpenAtlas (https://openatlas.eu
<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. 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 cumbersomeand 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?
From include/datatype/timestamp.h:
/*
* Julian date support.
*
* date2j() and j2date() nominally handle the Julian date range 0..INT_MAX,
* or 4714-11-24 BC to 5874898-06-03 AD. In practice, date2j() will
work and
* give correct negative Julian dates for dates before 4714-11-24 BC as
well.
* We rely on it to do so back to 4714-11-01 BC. Allowing at least one
day's
* slop is necessary so that timestamp rotation doesn't produce dates that
* would be rejected on input. For example, '4714-11-24 00:00 GMT BC' is a
* legal timestamptz value, but in zones east of Greenwich it would
print as
* sometime in the afternoon of 4714-11-23 BC; if we couldn't process
such a
* date we'd have a dump/reload failure. So the idea is for
IS_VALID_JULIAN
* to accept a slightly wider range of dates than we really support, and
* then we apply the exact checks in IS_VALID_DATE or IS_VALID_TIMESTAMP,
* after timezone rotation if any. To save a few cycles, we can make
* IS_VALID_JULIAN check only to the month boundary, since its exact
cutoffs
* are not very critical in this scheme.
*
* It is correct that JULIAN_MINYEAR is -4713, not -4714; it is defined to
* allow easy comparison to tm_year values, in which we follow the
convention
* that tm_year <= 0 represents abs(tm_year)+1 BC.
*/
#define JULIAN_MINYEAR (-4713)
#define JULIAN_MINMONTH (11)
#define JULIAN_MINDAY (24)
#define JULIAN_MAXYEAR (5874898)
#define JULIAN_MAXMONTH (6)
#define JULIAN_MAXDAY (3)
#define IS_VALID_JULIAN(y,m,d) \
(((y) > JULIAN_MINYEAR || \
((y) == JULIAN_MINYEAR && ((m) >= JULIAN_MINMONTH))) && \
((y) < JULIAN_MAXYEAR || \
((y) == JULIAN_MAXYEAR && ((m) < JULIAN_MAXMONTH))))
/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */
/*
* 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.
*/
/* First allowed date, and first disallowed date, in Julian-date form */
#define DATETIME_MIN_JULIAN (0)
#define DATE_END_JULIAN (2147483494) /* == date2j(JULIAN_MAXYEAR, 1,
1) */
#define TIMESTAMP_END_JULIAN (109203528) /* == date2j(294277, 1,
1) */
/* Timestamp limits */
#define MIN_TIMESTAMP INT64CONST(-211813488000000000)
/* == (DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */
#define END_TIMESTAMP INT64CONST(9223371331200000000)
/* == (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */
/* Range-check a date (given in Postgres, not Julian, numbering) */
#define IS_VALID_DATE(d) \
((DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) <= (d) && \
(d) < (DATE_END_JULIAN - POSTGRES_EPOCH_JDATE))
/* Range-check a timestamp */
#define IS_VALID_TIMESTAMP(t) (MIN_TIMESTAMP <= (t) && (t) < END_TIMESTAMP)
#endif /*
DATATYPE_TIMESTAMP_H */
Feedback and pointers are very welcome,
Alex
--
Alexander Watzinger
Austrian Academy of Sciences
Austrian Centre for Digital Humanities and Cultural Heritage
alexander.watzinger@xxxxxxxxxx | www.oeaw.ac.at/acdh
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
