Обсуждение: 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 
> <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

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!"

"Watzinger, Alexander" <Alexander.Watzinger@oeaw.ac.at> writes:
> Any chance to add support for dates before 4713 BC? We really would appreciate that.

I'm a little skeptical of the value of applying the Gregorian calendar
before 1582 AD, let alone thousands of years before the Romans invented
anything looking even vaguely like it.

Are you sure there's going to be any point whatsoever in trying to name
individual days that far back?  ISTM you'd be lucky to assign a year
accurately.

> The 4713 BC limit feels very arbitrary, what is the reason for this exact limit?

There are existing equations for calculating Gregorian month/day/year from
Julian day count [1].  They work back to Julian day zero, at least if
you grant that proleptic Gregorian dates are sensible that far back.
Nobody around here has looked into whether they'd work for negative Julian
day numbers (I suspect not though, at least not without work that seems
rather pointless).

            regards, tom lane

[1] https://en.wikipedia.org/wiki/Julian_day

Hello Stefan, Alexander,

On 2022-Aug-22, stefan eichert wrote:

> I can also fully support what Alex has written. I am an archaeologist at
> the Natural History Museum Vienna and PostgreSQL is a perfect Open Source
> software and we really love working with it for our archaeological and
> (pre)historical projects.
> [...]

This made me remember the stuff genealogists use -- a complex datatype
that encodes not just some number on a specific calendar, but also a
"quality" marker (precise, calculated, estimated) as well as a calendar
identifier (Gregorian, Islamic, Hebrew etc).  For example, GRAMPS has
this:
https://github.com/gramps-project/gramps/blob/master/gramps/gen/lib/date.py#L531
I'm not familiar with the details, but skimming through that source
might give you some ideas.

Judging from your description and Alexander's, it sounds like you should
dedicate some time to developing a specification for a complex datatype
to use with archaeological or what have you.  I suppose you would want
to store type of date it is, its precision, the value itself, a degree
of fuzziness, and maybe some other properties that you know better than
I do.  Then get someone to implement an open source custom datatype
based on that spec, which everybody then uses.

Using "regular" calendars is unlikely to be a good match.

-- 
Álvaro Herrera

On Mon, 22 Aug 2022 at 11:14, stefan eichert <stefaneichert@gmail.com> wrote:

> I can also fully support what Alex has written. I am an archaeologist at the Natural History Museum Vienna and
PostgreSQLis a perfect Open Source software and we really love working with it for our archaeological and
(pre)historicalprojects. 

We are very glad to hear that and I would be happy to help you further.


> The limitation of dates before 4713 BC however is a bit of a bottleneck and we need to use certain workarounds, that,
asAlex has written, are error prone and cumbersome. 
> The need for dates before 4713 has various reasons:
>
> For example if we have certain dates, like dendrochronological ones, that in some cases can give you a certain time
spanin which a tree has been cut, like in autumn 6000 BC (so lets say between March and beginning of July), then we
wouldlike to map this information in the database with an earliest and latest timestamp that would in that case be
6000BC,March 1st and 6000BC, June 30th. 
>
> Radiocarbon dates are similar, even if they only provide a timespan in a format of years before present with a
certain+/- range. 
> They would be mapped again with an earliest date, e.g. 6322 BC, Jan. 1st and a latest possible one: 6262, Dec. 31st.
>
> In many other cases we are using years as starting point for periodisations, that of course are arbitrary concepts,
butstill are needed for statistically working with data and for various calculations. 
>
> In order to deal with all dates, historical and prehistoric ones, in a consistent way, the implementation of
timestamps/datesfor before 4713 BC would be very helpful, as we really do have dates before 4713 BC we are working
with,that in some cases also have information on months respectively days. 

One possibility is to store dates as the INTERVAL datatype, using the
convention for Before Present, rather than worrying about BC/AD.

create table arch (i interval year);
insert into arch values ('-5000 years');
select * from arch;

      i
-------------
 -5000 years

This can also be used in a column specification like this INTERVAL
YEAR TO MONTH, which would store only years and months.
e.g.
CREATE TABLE arch (age INTERVAL YEAR TO MONTH);


Will that be sufficient, or do you need or want more?

--
Simon Riggs                http://www.EnterpriseDB.com/

On 2022-08-23 10:25:12 +0100, Simon Riggs wrote:
> On Mon, 22 Aug 2022 at 11:14, stefan eichert <stefaneichert@gmail.com> wrote:
> > In order to deal with all dates, historical and prehistoric ones, in
> > a consistent way, the implementation of timestamps/dates for before
> > 4713 BC would be very helpful, as we really do have dates before
> > 4713 BC we are working with, that in some cases also have
> > information on months respectively days.
>
> One possibility is to store dates as the INTERVAL datatype, using the
> convention for Before Present, rather than worrying about BC/AD.
>
> create table arch (i interval year);
> insert into arch values ('-5000 years');
> select * from arch;
>
>       i
> -------------
>  -5000 years

[Disclaimer: I am not an archeologist]

I think this works well if you know an approximate age. If something is
about 5000 years old now, it will still be about 5000 years old next
year and even in ten years.

But it breaks down if you already have a relatively precise date.

4980 years before now probably should be 4981 years before now next year
and definitely 4990 years before now in ten years. So you would have to
continuosly update those values.

You can get around this by defining an epoch (e.g. all relative dates
are relative to 2000 CE). So something which is 4980 years old now would
be stored as 4958 years before 2000.

(Of course this now starts look awfully like time_t (seconds
before/after 1970). Store that in a float8 and you can probably cover
the age of the universe in sufficient precision.)

In any case you probably want to convert to regular dates for display,
and you would run into PostgreSQL's limitations if you use PostgreSQL's
DATE type for that (you may be fine if you do it in the application,
depending on the programming language).

I'm also not sure whether one data type is sufficient for archeological
data. For example, what do you store if you know that something happened
in spring (because you found pollen or whatever), but the year has an
uncertainty of +/- 50 years?

I guess to really store "what do I know about when something happened"
you would have to be able to store a number of constraints (like
"between year x and y", "at least d years after event e", "between month
m and n", etc.)

        hp

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

> On 23/08/2022 14:38 CEST Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> 
>  
> On 2022-08-23 10:25:12 +0100, Simon Riggs wrote:
> > On Mon, 22 Aug 2022 at 11:14, stefan eichert <stefaneichert@gmail.com> wrote:
> > > In order to deal with all dates, historical and prehistoric ones, in
> > > a consistent way, the implementation of timestamps/dates for before
> > > 4713 BC would be very helpful, as we really do have dates before
> > > 4713 BC we are working with, that in some cases also have
> > > information on months respectively days.
> > 
> > One possibility is to store dates as the INTERVAL datatype, using the
> > convention for Before Present, rather than worrying about BC/AD.
> > 
> > create table arch (i interval year);
> > insert into arch values ('-5000 years');
> > select * from arch;
> > 
> >       i
> > -------------
> >  -5000 years
> 
> [Disclaimer: I am not an archeologist]

Me neither ;)

> I think this works well if you know an approximate age. If something is
> about 5000 years old now, it will still be about 5000 years old next
> year and even in ten years.
> 
> But it breaks down if you already have a relatively precise date.
> 
> 4980 years before now probably should be 4981 years before now next year
> and definitely 4990 years before now in ten years. So you would have to
> continuosly update those values.

Would be easier to store the estimated age and the assessment date so the age
doesn't have to be updated until it's reassessed.  The time from assessment to
now can be handled as you described: either the age is still considered roughly
the same or it increases as time between assessment and now increases.

--
Erik

On Sun, 21 Aug 2022 at 19:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Watzinger, Alexander" <Alexander.Watzinger@oeaw.ac.at> writes:
> > Any chance to add support for dates before 4713 BC? We really would appreciate that.
>
> I'm a little skeptical of the value of applying the Gregorian calendar
> before 1582 AD, let alone thousands of years before the Romans invented
> anything looking even vaguely like it.
>
> Are you sure there's going to be any point whatsoever in trying to name
> individual days that far back?  ISTM you'd be lucky to assign a year
> accurately.
>
> > The 4713 BC limit feels very arbitrary, what is the reason for this exact limit?
>
> There are existing equations for calculating Gregorian month/day/year from
> Julian day count [1].  They work back to Julian day zero, at least if
> you grant that proleptic Gregorian dates are sensible that far back.
> Nobody around here has looked into whether they'd work for negative Julian
> day numbers (I suspect not though, at least not without work that seems
> rather pointless).

Sounds reasonable. So the 4713BC limit applies because of the
resolution of 1 day.

Could we allow dates earlier than that, as long as we fix them to the
1st day of any particular month, so the effective resolution becomes
"1 month" before 4713BC? (With various and appropriate restrictions).

-- 
Simon Riggs                http://www.EnterpriseDB.com/

Simon Riggs <simon.riggs@enterprisedb.com> writes:
> On Sun, 21 Aug 2022 at 19:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> There are existing equations for calculating Gregorian month/day/year from
>> Julian day count [1].  They work back to Julian day zero, at least if
>> you grant that proleptic Gregorian dates are sensible that far back.
>> Nobody around here has looked into whether they'd work for negative Julian
>> day numbers (I suspect not though, at least not without work that seems
>> rather pointless).

> Sounds reasonable. So the 4713BC limit applies because of the
> resolution of 1 day.

No, it applies because we aren't sure that the math would operate
correctly with negative Julian day numbers --- for instance, division
roundoffs might happen in the wrong direction.  If somebody wanted to go
through and check/fix all that, we could probably relax the restriction.
I'm still failing to see the point though.  As already discussed upthread,
the SQL datetime types aren't very suitable for dealing with approximate
dates, multiple calendars, etc.

            regards, tom lane

On Mon, 12 Sept 2022 at 16:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Simon Riggs <simon.riggs@enterprisedb.com> writes:
> > On Sun, 21 Aug 2022 at 19:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> There are existing equations for calculating Gregorian month/day/year from
> >> Julian day count [1].  They work back to Julian day zero, at least if
> >> you grant that proleptic Gregorian dates are sensible that far back.
> >> Nobody around here has looked into whether they'd work for negative Julian
> >> day numbers (I suspect not though, at least not without work that seems
> >> rather pointless).
>
> > Sounds reasonable. So the 4713BC limit applies because of the
> > resolution of 1 day.
>
> No, it applies because we aren't sure that the math would operate
> correctly with negative Julian day numbers --- for instance, division
> roundoffs might happen in the wrong direction.  If somebody wanted to go
> through and check/fix all that, we could probably relax the restriction.

OK, challenge seems clear.

> I'm still failing to see the point though.  As already discussed upthread,
> the SQL datetime types aren't very suitable for dealing with approximate
> dates, multiple calendars, etc.

The problem is that support for custom datatypes is hard. First, you
have to find a cloud provider that will allow user extensions. Second,
you have to make that work with all the other software that isn't
expecting them.

Not insurmountable, but big enough to make it a barrier for something
fairly small like this.

-- 
Simon Riggs                http://www.EnterpriseDB.com/

Simon Riggs <simon.riggs@enterprisedb.com> writes:
> On Mon, 12 Sept 2022 at 16:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> No, it applies because we aren't sure that the math would operate
>> correctly with negative Julian day numbers --- for instance, division
>> roundoffs might happen in the wrong direction.  If somebody wanted to go
>> through and check/fix all that, we could probably relax the restriction.

> OK, challenge seems clear.

FWIW, I think this decision dates to a time when we still worried about
C89's lack of specificity about which direction integer division will
round negative results in.  Now that we're targeting only C99-compliant
platforms, that part should be easier.

            regards, tom lane