On Thu, 17 Oct 2024 at 01:26, Richards, Nina <Nina.Richards@oeaw.ac.at> wrote:
> Even though we know there was no calendar at that time, it would make our work much easier if we could use data
before4713 BC in the same way. Especially for statistical analyses and scientific dating methods (14C,
dendrochronology),this would be a great benefit for us as well as the users of OpenAtlas. 
>
>
> Are there, by any chance, news about this issue? Or are you aware of any suitable workarounds on the database level
inthe meantime? 

It's by no means a trivial thing to do, but it is possible to
implement new types in PostgreSQL [1]. If you invented your own type,
you could significantly widen the upper and lower bounds when compared
with the standard date type.

David

[1] https://www.postgresql.org/docs/current/sql-createtype.html

Re: Support for dates before 4713 BC

From

Tom Lane

Date:

17 October 2024, 03:36:12

David Rowley <dgrowleyml@gmail.com> writes:
> It's by no means a trivial thing to do, but it is possible to
> implement new types in PostgreSQL [1]. If you invented your own type,
> you could significantly widen the upper and lower bounds when compared
> with the standard date type.

However, you'd then have to reimplement some large fraction of the
existing datetime support to have something useful.

We're already inventing freely to use the Gregorian calendar for
millenia before Pope Gregory lived, so I see no conceptual argument
not to extend that back even further.

IIRC the stumbling block for not going back past Julian day 0 was
uncertainty about whether the date2j and j2date algorithms behave
correctly for negative Julian dates --- which at the time was
compounded by the fact that C90 was vague about the rounding direction
for integer division with negative inputs.  Now that we assume C99
with its well-defined rule for that, at least some of the uncertainty
is gone.  Somebody would still have to study that code and either
prove that it's OK or correct it.  And then there would be a
nontrivial amount of effort to work outwards and fix anything else
that is assuming that limitation.  So it would take somebody with
considerable motivation to make it happen, but if such a somebody were
to appear with a patch, we'd likely take it.  (To be clear, I doubt
any of the principal current hackers are interested in doing this.)

Now, this would still only get you to a lower-bound date somewhere
around 300000 BC.  If you need to deal with geological or astronomical
time spans, then yeah you need a new type --- but presumably you would
not feel a need to tie it to Gregorian calendar dates, so the need to
reimplement a ton of related logic would not be there.

            regards, tom lane

Re: Support for dates before 4713 BC

From

Asad Ali

Date:

17 October 2024, 09:43:28

Hi Nina Richards,

Instead of using PostgreSQL's DATE or TIMESTAMP types, you can store years as NUMERIC or BIGINT values.
You can manually represent dates before 4713 BC and build custom functions for date operations like addition, subtraction, or comparison.

To facilitate comparisons or operations on your custom dates (BC/AD), you can create user-defined functions in PostgreSQL for adding, subtracting, or comparing dates.

Best Regards,

Asad Ali

On Thu, Oct 17, 2024 at 5:36 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Rowley <dgrowleyml@gmail.com> writes:
> It's by no means a trivial thing to do, but it is possible to
> implement new types in PostgreSQL [1]. If you invented your own type,
> you could significantly widen the upper and lower bounds when compared
> with the standard date type.

However, you'd then have to reimplement some large fraction of the
existing datetime support to have something useful.

We're already inventing freely to use the Gregorian calendar for
millenia before Pope Gregory lived, so I see no conceptual argument
not to extend that back even further.

IIRC the stumbling block for not going back past Julian day 0 was
uncertainty about whether the date2j and j2date algorithms behave
correctly for negative Julian dates --- which at the time was
compounded by the fact that C90 was vague about the rounding direction
for integer division with negative inputs. Now that we assume C99
with its well-defined rule for that, at least some of the uncertainty
is gone. Somebody would still have to study that code and either
prove that it's OK or correct it. And then there would be a
nontrivial amount of effort to work outwards and fix anything else
that is assuming that limitation. So it would take somebody with
considerable motivation to make it happen, but if such a somebody were
to appear with a patch, we'd likely take it. (To be clear, I doubt
any of the principal current hackers are interested in doing this.)

Now, this would still only get you to a lower-bound date somewhere
around 300000 BC. If you need to deal with geological or astronomical
time spans, then yeah you need a new type --- but presumably you would
not feel a need to tie it to Gregorian calendar dates, so the need to
reimplement a ton of related logic would not be there.

regards, tom lane