MIME-Version: 1.0
References: <0af9ebc00dca444cbd3c5e07752bc5f9@oeaw.ac.at> <CAApHDvomL7e49Y0ZyCu4ZrSq-mF7bcnq6cz+8_BwvNM4bL+Ruw@mail.gmail.com>
 <505768.1729125372@sss.pgh.pa.us>
In-Reply-To: <505768.1729125372@sss.pgh.pa.us>
From: Asad Ali <asadalinagri@gmail.com>
Date: Thu, 17 Oct 2024 11:43:28 +0500
Message-ID: <CAJ9xe=tEOuGvy_GrF9vzwOka=wasEQSQhOX-=CfCm+pKy6NDAQ@mail.gmail.com>
Subject: Re: Support for dates before 4713 BC
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Rowley <dgrowleyml@gmail.com>, "Richards, Nina" <Nina.Richards@oeaw.ac.at>, 
	"Watzinger, Alexander" <Alexander.Watzinger@oeaw.ac.at>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c8d5940624a68133"
Archived-At: <https://www.postgresql.org/message-id/CAJ9xe%3DtEOuGvy_GrF9vzwOka%3DwasEQSQhOX-%3DCfCm%2BpKy6NDAQ%40mail.gmail.com>
Precedence: bulk

--000000000000c8d5940624a68133
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM 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
>
>
>

--000000000000c8d5940624a68133
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><p>Hi Nina Richards,</p><p>Instead of using PostgreSQL&#39=
;s DATE or TIMESTAMP types, you can store years as NUMERIC or BIGINT values=
.<br>You can manually represent dates before 4713 BC and build custom funct=
ions for date operations like addition, subtraction, or comparison.<br></p>=
<p>To facilitate comparisons or operations on your custom dates (BC/AD), yo=
u can create user-defined functions in PostgreSQL for adding, subtracting, =
or comparing dates.</p><p>Best Regards,</p><p>Asad Ali</p><div><br></div></=
div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On=
 Thu, Oct 17, 2024 at 5:36=E2=80=AFAM Tom Lane &lt;<a href=3D"mailto:tgl@ss=
s.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex">David Rowley &lt;<a href=3D"mailto:dgrowleyml=
@gmail.com" target=3D"_blank">dgrowleyml@gmail.com</a>&gt; writes:<br>
&gt; It&#39;s by no means a trivial thing to do, but it is possible to<br>
&gt; implement new types in PostgreSQL [1]. If you invented your own type,<=
br>
&gt; you could significantly widen the upper and lower bounds when compared=
<br>
&gt; with the standard date type.<br>
<br>
However, you&#39;d then have to reimplement some large fraction of the<br>
existing datetime support to have something useful.<br>
<br>
We&#39;re already inventing freely to use the Gregorian calendar for<br>
millenia before Pope Gregory lived, so I see no conceptual argument<br>
not to extend that back even further.<br>
<br>
IIRC the stumbling block for not going back past Julian day 0 was<br>
uncertainty about whether the date2j and j2date algorithms behave<br>
correctly for negative Julian dates --- which at the time was<br>
compounded by the fact that C90 was vague about the rounding direction<br>
for integer division with negative inputs.=C2=A0 Now that we assume C99<br>
with its well-defined rule for that, at least some of the uncertainty<br>
is gone.=C2=A0 Somebody would still have to study that code and either<br>
prove that it&#39;s OK or correct it.=C2=A0 And then there would be a<br>
nontrivial amount of effort to work outwards and fix anything else<br>
that is assuming that limitation.=C2=A0 So it would take somebody with<br>
considerable motivation to make it happen, but if such a somebody were<br>
to appear with a patch, we&#39;d likely take it.=C2=A0 (To be clear, I doub=
t<br>
any of the principal current hackers are interested in doing this.)<br>
<br>
Now, this would still only get you to a lower-bound date somewhere<br>
around 300000 BC.=C2=A0 If you need to deal with geological or astronomical=
<br>
time spans, then yeah you need a new type --- but presumably you would<br>
not feel a need to tie it to Gregorian calendar dates, so the need to<br>
reimplement a ton of related logic would not be there.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
<br>
<br>
</blockquote></div>

--000000000000c8d5940624a68133--