On Mon, 22 Aug 2022 at 11:14, stefan eichert <[email protected]> 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/
