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