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

From stefan eichert
Subject Re: Support for dates before 4713 BC
Date
Msg-id CAJuUXVeNQWm9x7d2_cvzj2rEvtQgNeGF7vog9dmaN1W5e7EDCQ@mail.gmail.com
Whole thread Raw
In response to Support for dates before 4713 BC  ("Watzinger, Alexander" <Alexander.Watzinger@oeaw.ac.at>)
Responses Re: Support for dates before 4713 BC
Re: Support for dates before 4713 BC
List pgsql-general
Dear All,

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.

The limitation of dates before 4713 BC however is a bit of a bottleneck and we need to use certain workarounds, that, as Alex 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 span in which a tree has been cut, like in autumn 6000 BC (so lets say between March and beginning of July), then we would like 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, but still 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/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.

Thank you for your feedback!
Stefan




Am Mo., 22. Aug. 2022 um 11:48 Uhr schrieb Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>:

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 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?


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

pgsql-general by date:

Previous
From: Francisco Olarte
Date:
Subject: Re: Creating constraint dynamically
Next
From: jian he
Date:
Subject: Re: Creating constraint dynamically