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

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@oeaw.ac.at | www.oeaw.ac.at/acdh
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com