Date: Tue, 23 Aug 2022 16:13:10 +0200 (CEST)
From: Erik Wienhold <ewie@ewie.name>
To: "Peter J. Holzer" <hjp-pgsql@hjp.at>, pgsql-general@lists.postgresql.org
Message-ID: <1524824843.599258.1661263990654@office.mailbox.org>
In-Reply-To: <20220823123852.xfws3vcxbmdlxrns@hjp.at>
References: <ca438ff8331c4e109aa1b75a130948ac@oeaw.ac.at>
 <CAJuUXVeNQWm9x7d2_cvzj2rEvtQgNeGF7vog9dmaN1W5e7EDCQ@mail.gmail.com>
 <CANbhV-G_7Kj+09nnPToccnLBqhnN6RdwQLJVaO1G=vJOtmOQJA@mail.gmail.com>
 <20220823123852.xfws3vcxbmdlxrns@hjp.at>
Subject: Re: Support for dates before 4713 BC
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/1524824843.599258.1661263990654%40office.mailbox.org>
Precedence: bulk


> 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