Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oXjEU-0004bP-Fj for pgsql-general@arkaria.postgresql.org; Mon, 12 Sep 2022 13:12:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oXjET-0005RQ-AP for pgsql-general@arkaria.postgresql.org; Mon, 12 Sep 2022 13:12:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oXiSZ-0004Lq-QN for pgsql-general@lists.postgresql.org; Mon, 12 Sep 2022 12:22:43 +0000 Received: from esa.hc1004-48.eu.iphmx.com ([23.90.117.197]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oXiSW-0007vs-Kx for pgsql-general@lists.postgresql.org; Mon, 12 Sep 2022 12:22:43 +0000 X-Amp-Result: SKIPPED(no attachment in message) X-Amp-File-Uploaded: False Received: from w07ex16db2.arz.oeaw.ac.at (HELO W07EX16DB2.oeaw.ads) ([193.171.197.22]) by esa3.hc1004-48.eu.iphmx.com with ESMTP/TLS/ECDHE-RSA-AES256-GCM-SHA384; 12 Sep 2022 14:22:39 +0200 Received: from w07ex16db2.oeaw.ads (193.171.197.22) by W07EX16DB2.oeaw.ads (193.171.197.22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.1.2507.12; Mon, 12 Sep 2022 14:22:38 +0200 Received: from w07ex16db2.oeaw.ads ([fe80::91c8:cbbe:d979:c740]) by W07EX16DB2.oeaw.ads ([fe80::91c8:cbbe:d979:c740%3]) with mapi id 15.01.2507.012; Mon, 12 Sep 2022 14:22:38 +0200 From: "Watzinger, Alexander" To: Adrian Klaver , "pgsql-general@lists.postgresql.org" Subject: Re: Support for dates before 4713 BC Thread-Topic: Support for dates before 4713 BC Thread-Index: AQHYtJn21urOZ2Z+b0a+LhRWokxqH625eHwAgCJh864= Date: Mon, 12 Sep 2022 12:22:38 +0000 Message-ID: <8c3bdacc36ac409eb4c18cc0fc4ef614@oeaw.ac.at> References: ,<5c679341-3d8b-a732-d349-76753d0422c6@aklaver.com> In-Reply-To: <5c679341-3d8b-a732-d349-76753d0422c6@aklaver.com> Accept-Language: en-US, de-DE Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [193.170.80.43] Content-Type: multipart/alternative; boundary="_000_8c3bdacc36ac409eb4c18cc0fc4ef614oeawacat_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_8c3bdacc36ac409eb4c18cc0fc4ef614oeawacat_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Dear Adrian, Thank you for your insights and taking the time. It is always very interest= ing to see where other software projects limitations come from. All the best, Alex -- Alexander Watzinger Austrian Academy of Sciences Austrian Centre for Digital Humanities and Cultural Heritage B=E4ckerstra=DFe 13, 1010 Vienna, Austria alexander.watzinger@oeaw.ac.at | www.oeaw.ac.at/acdh ________________________________ From: Adrian Klaver Sent: Sunday, August 21, 2022 19:15 To: Watzinger, Alexander; pgsql-general@lists.postgresql.org Subject: Re: Support for dates before 4713 BC On 8/20/22 07:05, Watzinger, Alexander wrote: > Hi all, > > 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. 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 <=3D 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) =3D=3D JULIAN_MINYEAR && ((m) >=3D JULIAN_MINMONTH))) && \ ((y) < JULIAN_MAXYEAR || \ ((y) =3D=3D JULIAN_MAXYEAR && ((m) < JULIAN_MAXMONTH)))) /* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */ #define UNIX_EPOCH_JDATE 2440588 /* =3D=3D date2j(1970, 1, 1= ) */ #define POSTGRES_EPOCH_JDATE 2451545 /* =3D=3D 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) /* =3D=3D date2j(JULIAN_MAXYEAR, 1, 1) */ #define TIMESTAMP_END_JULIAN (109203528) /* =3D=3D date2j(294277, 1, 1) */ /* Timestamp limits */ #define MIN_TIMESTAMP INT64CONST(-211813488000000000) /* =3D=3D (DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */ #define END_TIMESTAMP INT64CONST(9223371331200000000) /* =3D=3D (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) <=3D (d) && \ (d) < (DATE_END_JULIAN - POSTGRES_EPOCH_JDATE)) /* Range-check a timestamp */ #define IS_VALID_TIMESTAMP(t) (MIN_TIMESTAMP <=3D (t) && (t) < END_TIMESTA= MP) #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 --_000_8c3bdacc36ac409eb4c18cc0fc4ef614oeawacat_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Dear Adrian,

Thank you for your insights and taking the time. It is always very interest= ing to see where other software projects limitations come from.

All the best,

Alex


--
Alexander Watzinger

Austrian Academy of Sciences
Austrian Centre for Digital Humanities and Cultural Heritage
B=E4ckerstra=DFe 13= , 1010 Vienna, Austria

alexander.watzinger@oeaw.ac.at | www.oeaw.ac.at/acdh



From: Adrian Klaver <a= drian.klaver@aklaver.com>
Sent: Sunday, August 21, 2022 19:15
To: Watzinger, Alexander; pgsql-general@lists.postgresql.org
Subject: Re: Support for dates before 4713 BC
 
On 8/20/22 07:05, Watzinger, Alexander wrote:
> Hi all,
>
> I'm working on the open source project OpenAtlas (htt= ps://openatlas.eu
> <https://openatlas.eu>) whi= ch is used to enter historical and
> archeological data.
> Although we really appreciate the wonderful PostgreSQL software, the <= br> > limitation of not being able to use dates before 4713 BC is problemati= c
> in regard to archeological data.
> The only reason for this limitation I found was that the Julian Calend= ar
> wasn't created with this in mind. The only suggestion I found was to <= br> > write an own implementation just using integers for years. But buildin= g
> a parallel date system in this case would be way to cumbersomeand erro= r
> 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..IN= T_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 le= ast 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 G= MT BC' is a
  * legal timestamptz value, but in zones east of Greenwich it would <= br> 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_TIMEST= AMP,
  * 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 defin= ed to
  * allow easy comparison to tm_year values, in which we follow the convention
  * that tm_year <=3D 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) =3D=3D JU= LIAN_MINYEAR && ((m) >=3D JULIAN_MINMONTH))) && \
          ((y) < JULIAN_MAX= YEAR || \
           ((y) =3D=3D JU= LIAN_MAXYEAR && ((m) < JULIAN_MAXMONTH))))

/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
#define UNIX_EPOCH_JDATE        &nb= sp;       2440588 /* =3D=3D date2j(1970, 1, 1= ) */
#define POSTGRES_EPOCH_JDATE    2451545 /* =3D=3D date2j(200= 0, 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 th= an
what
  * the Julian-date code can allow.  For timestamps, the upper li= mit is
  * 294276-12-31.  The int64 overflow limit would be a few days l= ater;
again,
  * leaving some slop avoids worries about corner-case overflow, and <= br> 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)    /* =3D=3D date2j(JUL= IAN_MAXYEAR, 1,
1) */
#define TIMESTAMP_END_JULIAN (109203528)      = ;  /* =3D=3D date2j(294277, 1,
1) */

/* Timestamp limits */
#define MIN_TIMESTAMP   INT64CONST(-211813488000000000)
/* =3D=3D (DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */ #define END_TIMESTAMP   INT64CONST(9223371331200000000)
/* =3D=3D (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */<= br>
/* Range-check a date (given in Postgres, not Julian, numbering) */
#define IS_VALID_DATE(d) \
         ((DATETIME_MIN_JULIAN - PO= STGRES_EPOCH_JDATE) <=3D (d) && \
          (d) < (DATE_END_J= ULIAN - POSTGRES_EPOCH_JDATE))

/* Range-check a timestamp */
#define IS_VALID_TIMESTAMP(t)  (MIN_TIMESTAMP <=3D (t) && (= t) < END_TIMESTAMP)

#endif           &nb= sp;            =             &nb= sp;            = /*
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
--_000_8c3bdacc36ac409eb4c18cc0fc4ef614oeawacat_--