Re: Support for dates before 4713 BC

On Mon, 22 Aug 2022 at 11:14, stefan eichert <stefaneichert(at)gmail(dot)com> 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.

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, 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.

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/