pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedSupport for dates before 4713 BC
20+ messages / 13 participants
[nested] [flat]
* Support for dates before 4713 BC
@ 2022-08-20 14:05 Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-21 17:15 ` Re: Support for dates before 4713 BC Adrian Klaver <adrian.klaver@aklaver.com>
2022-08-21 17:54 ` Re: Support for dates before 4713 BC Peter J. Holzer <hjp-pgsql@hjp.at>
2022-08-21 18:04 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
2022-08-22 10:14 ` Re: Support for dates before 4713 BC stefan eichert <stefaneichert@gmail.com>
0 siblings, 4 replies; 20+ messages in thread
From: Watzinger, Alexander @ 2022-08-20 14:05 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi all,
I'm working on the open source project OpenAtlas (https://openatlas.eu) which is used to enter historical and archeological data.
Although we really appreciate the wonderful PostgreSQL software, the limitation of not being able to use dates before 4713 BC is problematic in regard to archeological data.
The only reason for this limitation I found was that the Julian Calendar wasn't created with this in mind. The only suggestion I found was to write an own implementation just using integers for years. But building a parallel date system in this case would be way to cumbersome and error prone, we really like using the database for date operations.
So my questions are:
Any chance to add support for dates before 4713 BC? We really would appreciate that.
The 4713 BC limit feels very arbitrary, what is the reason for this exact limit?
Feedback and pointers are very welcome,
Alex
--
Alexander Watzinger
Austrian Academy of Sciences
Austrian Centre for Digital Humanities and Cultural Heritage
alexander.watzinger@oeaw.ac.at | www.oeaw.ac.at/acdh
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
@ 2022-08-21 17:15 ` Adrian Klaver <adrian.klaver@aklaver.com>
2022-09-12 12:22 ` Re: Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
3 siblings, 1 reply; 20+ messages in thread
From: Adrian Klaver @ 2022-08-21 17:15 UTC (permalink / raw)
To: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 8/20/22 07:05, Watzinger, Alexander wrote:
> Hi all,
>
> I'm working on the open source project OpenAtlas (https://openatlas.eu
> <https://openatlas.eu;) which is used to enter historical and
> archeological data.
> Although we really appreciate the wonderful PostgreSQL software, the
> limitation of not being able to use dates before 4713 BC is problematic
> in regard to archeological data.
> The only reason for this limitation I found was that the Julian Calendar
> wasn't created with this in mind. The only suggestion I found was to
> write an own implementation just using integers for years. But building
> a parallel date system in this case would be way to cumbersomeand error
> prone, we really like using the database for date operations.
>
>
> So my questions are:
> Any chance to add support for dates before 4713 BC? We really would
> appreciate that.
>
> The 4713 BC limit feels very arbitrary, what is the reason for this
> exact limit?
From include/datatype/timestamp.h:
/*
* Julian date support.
*
* date2j() and j2date() nominally handle the Julian date range 0..INT_MAX,
* or 4714-11-24 BC to 5874898-06-03 AD. In practice, date2j() will
work and
* give correct negative Julian dates for dates before 4714-11-24 BC as
well.
* We rely on it to do so back to 4714-11-01 BC. Allowing at least one
day's
* slop is necessary so that timestamp rotation doesn't produce dates that
* would be rejected on input. For example, '4714-11-24 00:00 GMT BC' is a
* legal timestamptz value, but in zones east of Greenwich it would
print as
* sometime in the afternoon of 4714-11-23 BC; if we couldn't process
such a
* date we'd have a dump/reload failure. So the idea is for
IS_VALID_JULIAN
* to accept a slightly wider range of dates than we really support, and
* then we apply the exact checks in IS_VALID_DATE or IS_VALID_TIMESTAMP,
* after timezone rotation if any. To save a few cycles, we can make
* IS_VALID_JULIAN check only to the month boundary, since its exact
cutoffs
* are not very critical in this scheme.
*
* It is correct that JULIAN_MINYEAR is -4713, not -4714; it is defined to
* allow easy comparison to tm_year values, in which we follow the
convention
* that tm_year <= 0 represents abs(tm_year)+1 BC.
*/
#define JULIAN_MINYEAR (-4713)
#define JULIAN_MINMONTH (11)
#define JULIAN_MINDAY (24)
#define JULIAN_MAXYEAR (5874898)
#define JULIAN_MAXMONTH (6)
#define JULIAN_MAXDAY (3)
#define IS_VALID_JULIAN(y,m,d) \
(((y) > JULIAN_MINYEAR || \
((y) == JULIAN_MINYEAR && ((m) >= JULIAN_MINMONTH))) && \
((y) < JULIAN_MAXYEAR || \
((y) == JULIAN_MAXYEAR && ((m) < JULIAN_MAXMONTH))))
/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */
/*
* Range limits for dates and timestamps.
*
* We have traditionally allowed Julian day zero as a valid datetime value,
* so that is the lower bound for both dates and timestamps.
*
* The upper limit for dates is 5874897-12-31, which is a bit less than
what
* the Julian-date code can allow. For timestamps, the upper limit is
* 294276-12-31. The int64 overflow limit would be a few days later;
again,
* leaving some slop avoids worries about corner-case overflow, and
provides
* a simpler user-visible definition.
*/
/* First allowed date, and first disallowed date, in Julian-date form */
#define DATETIME_MIN_JULIAN (0)
#define DATE_END_JULIAN (2147483494) /* == date2j(JULIAN_MAXYEAR, 1,
1) */
#define TIMESTAMP_END_JULIAN (109203528) /* == date2j(294277, 1,
1) */
/* Timestamp limits */
#define MIN_TIMESTAMP INT64CONST(-211813488000000000)
/* == (DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */
#define END_TIMESTAMP INT64CONST(9223371331200000000)
/* == (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */
/* Range-check a date (given in Postgres, not Julian, numbering) */
#define IS_VALID_DATE(d) \
((DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) <= (d) && \
(d) < (DATE_END_JULIAN - POSTGRES_EPOCH_JDATE))
/* Range-check a timestamp */
#define IS_VALID_TIMESTAMP(t) (MIN_TIMESTAMP <= (t) && (t) < END_TIMESTAMP)
#endif /*
DATATYPE_TIMESTAMP_H */
>
>
> Feedback and pointers are very welcome,
>
> Alex
>
>
> --
> Alexander Watzinger
>
> Austrian Academy of Sciences
> Austrian Centre for Digital Humanities and Cultural Heritage
> alexander.watzinger@oeaw.ac.at | www.oeaw.ac.at/acdh
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-21 17:15 ` Re: Support for dates before 4713 BC Adrian Klaver <adrian.klaver@aklaver.com>
@ 2022-09-12 12:22 ` Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
0 siblings, 0 replies; 20+ messages in thread
From: Watzinger, Alexander @ 2022-09-12 12:22 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Dear Adrian,
Thank you for your insights and taking the time. It is always very interesting to see where other software projects limitations come from.
All the best,
Alex
--
Alexander Watzinger
Austrian Academy of Sciences
Austrian Centre for Digital Humanities and Cultural Heritage
Bäckerstraße 13, 1010 Vienna, Austria
alexander.watzinger@oeaw.ac.at | www.oeaw.ac.at/acdh
________________________________
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Sunday, August 21, 2022 19:15
To: Watzinger, Alexander; pgsql-general@lists.postgresql.org
Subject: Re: Support for dates before 4713 BC
On 8/20/22 07:05, Watzinger, Alexander wrote:
> Hi all,
>
> I'm working on the open source project OpenAtlas (https://openatlas.eu
> <https://openatlas.eu;) which is used to enter historical and
> archeological data.
> Although we really appreciate the wonderful PostgreSQL software, the
> limitation of not being able to use dates before 4713 BC is problematic
> in regard to archeological data.
> The only reason for this limitation I found was that the Julian Calendar
> wasn't created with this in mind. The only suggestion I found was to
> write an own implementation just using integers for years. But building
> a parallel date system in this case would be way to cumbersomeand error
> prone, we really like using the database for date operations.
>
>
> So my questions are:
> Any chance to add support for dates before 4713 BC? We really would
> appreciate that.
>
> The 4713 BC limit feels very arbitrary, what is the reason for this
> exact limit?
From include/datatype/timestamp.h:
/*
* Julian date support.
*
* date2j() and j2date() nominally handle the Julian date range 0..INT_MAX,
* or 4714-11-24 BC to 5874898-06-03 AD. In practice, date2j() will
work and
* give correct negative Julian dates for dates before 4714-11-24 BC as
well.
* We rely on it to do so back to 4714-11-01 BC. Allowing at least one
day's
* slop is necessary so that timestamp rotation doesn't produce dates that
* would be rejected on input. For example, '4714-11-24 00:00 GMT BC' is a
* legal timestamptz value, but in zones east of Greenwich it would
print as
* sometime in the afternoon of 4714-11-23 BC; if we couldn't process
such a
* date we'd have a dump/reload failure. So the idea is for
IS_VALID_JULIAN
* to accept a slightly wider range of dates than we really support, and
* then we apply the exact checks in IS_VALID_DATE or IS_VALID_TIMESTAMP,
* after timezone rotation if any. To save a few cycles, we can make
* IS_VALID_JULIAN check only to the month boundary, since its exact
cutoffs
* are not very critical in this scheme.
*
* It is correct that JULIAN_MINYEAR is -4713, not -4714; it is defined to
* allow easy comparison to tm_year values, in which we follow the
convention
* that tm_year <= 0 represents abs(tm_year)+1 BC.
*/
#define JULIAN_MINYEAR (-4713)
#define JULIAN_MINMONTH (11)
#define JULIAN_MINDAY (24)
#define JULIAN_MAXYEAR (5874898)
#define JULIAN_MAXMONTH (6)
#define JULIAN_MAXDAY (3)
#define IS_VALID_JULIAN(y,m,d) \
(((y) > JULIAN_MINYEAR || \
((y) == JULIAN_MINYEAR && ((m) >= JULIAN_MINMONTH))) && \
((y) < JULIAN_MAXYEAR || \
((y) == JULIAN_MAXYEAR && ((m) < JULIAN_MAXMONTH))))
/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */
/*
* Range limits for dates and timestamps.
*
* We have traditionally allowed Julian day zero as a valid datetime value,
* so that is the lower bound for both dates and timestamps.
*
* The upper limit for dates is 5874897-12-31, which is a bit less than
what
* the Julian-date code can allow. For timestamps, the upper limit is
* 294276-12-31. The int64 overflow limit would be a few days later;
again,
* leaving some slop avoids worries about corner-case overflow, and
provides
* a simpler user-visible definition.
*/
/* First allowed date, and first disallowed date, in Julian-date form */
#define DATETIME_MIN_JULIAN (0)
#define DATE_END_JULIAN (2147483494) /* == date2j(JULIAN_MAXYEAR, 1,
1) */
#define TIMESTAMP_END_JULIAN (109203528) /* == date2j(294277, 1,
1) */
/* Timestamp limits */
#define MIN_TIMESTAMP INT64CONST(-211813488000000000)
/* == (DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */
#define END_TIMESTAMP INT64CONST(9223371331200000000)
/* == (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */
/* Range-check a date (given in Postgres, not Julian, numbering) */
#define IS_VALID_DATE(d) \
((DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) <= (d) && \
(d) < (DATE_END_JULIAN - POSTGRES_EPOCH_JDATE))
/* Range-check a timestamp */
#define IS_VALID_TIMESTAMP(t) (MIN_TIMESTAMP <= (t) && (t) < END_TIMESTAMP)
#endif /*
DATATYPE_TIMESTAMP_H */
>
>
> Feedback and pointers are very welcome,
>
> Alex
>
>
> --
> Alexander Watzinger
>
> Austrian Academy of Sciences
> Austrian Centre for Digital Humanities and Cultural Heritage
> alexander.watzinger@oeaw.ac.at | www.oeaw.ac.at/acdh<http://www.oeaw.ac.at/acdh;
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
@ 2022-08-21 17:54 ` Peter J. Holzer <hjp-pgsql@hjp.at>
3 siblings, 0 replies; 20+ messages in thread
From: Peter J. Holzer @ 2022-08-21 17:54 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2022-08-20 14:05:42 +0000, Watzinger, Alexander wrote:
> I'm working on the open source project OpenAtlas (https://openatlas.eu) which
> is used to enter historical and archeological data.
> Although we really appreciate the wonderful PostgreSQL software, the limitation
> of not being able to use dates before 4713 BC is problematic in regard to
> archeological data.
> The only reason for this limitation I found was that the Julian Calendar wasn't
> created with this in mind.
Nitpick:
The Julian *calendar* is the calendar (with 365 days per year and leap
years every 4 years) devised by Julius Caesar (or somebody in his employ)
modified so that the (presumed) birth of Jesus falls into year 1.
The Julian *date* is a day count starting at January 1st 4713 BC, 12:00
UTC of the Julian calendar.
I assume you are talking about the latter.
> The only suggestion I found was to write an own implementation just
> using integers for years. But building a parallel date system in this
> case would be way to cumbersome and error prone, we really like using
> the database for date operations.
>
>
> So my questions are:
> Any chance to add support for dates before 4713 BC? We really would appreciate
> that.
>
> The 4713 BC limit feels very arbitrary, what is the reason for this exact
> limit?
Any choice of epoch is arbitrary. Generally people seem to like epochs
in the past so that all dates they are interested in can be represented
by positive numbers. Unix time_t starts at 1970-01-01T00:00Z, because
the 1970's had already begun so you wouldn't need a date before that on
a computer (and 1970 is a somewhat roundish number). The Julian date
uses 4713 BC as its starting point because that was when three cycles
used by astronomers last coincided and from the point of view of a 16th
century astronomer it was also safely in the past.
I don't know why PostgreSQL uses the Julian date instead of some other
schema. My guess is that it was chosen because it's a simple day count
(so no ambiguity with leap years) and because it also covers a good
chunk of ancient history, so the problem you are facing now would be
rarely encountered.
Of course since it is a simple count, it can easily be extended into the
past. Day 0 is November 24, 4714 BC (proleptic Gregorian Calendar), so
day -1 would be November 23, 4714 BC, and so on.
The problem would be that there are probably a lot of calculations which
assume that the date can never be negative, and those would have to be
checked and if necessary corrected.
Another potential problem could be that switching from an unsigned int
to an int halves the positive range. But it seems that this is already
capped at 2**31 days (5874897 AD), so that wouldn't be a problem here.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
@ 2022-08-21 18:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-09-12 14:40 ` Re: Support for dates before 4713 BC Simon Riggs <simon.riggs@enterprisedb.com>
3 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2022-08-21 18:04 UTC (permalink / raw)
To: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
"Watzinger, Alexander" <Alexander.Watzinger@oeaw.ac.at> writes:
> Any chance to add support for dates before 4713 BC? We really would appreciate that.
I'm a little skeptical of the value of applying the Gregorian calendar
before 1582 AD, let alone thousands of years before the Romans invented
anything looking even vaguely like it.
Are you sure there's going to be any point whatsoever in trying to name
individual days that far back? ISTM you'd be lucky to assign a year
accurately.
> The 4713 BC limit feels very arbitrary, what is the reason for this exact limit?
There are existing equations for calculating Gregorian month/day/year from
Julian day count [1]. They work back to Julian day zero, at least if
you grant that proleptic Gregorian dates are sensible that far back.
Nobody around here has looked into whether they'd work for negative Julian
day numbers (I suspect not though, at least not without work that seems
rather pointless).
regards, tom lane
[1] https://en.wikipedia.org/wiki/Julian_day
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-21 18:04 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-09-12 14:40 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-12 15:00 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: Simon Riggs @ 2022-09-12 14:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Sun, 21 Aug 2022 at 19:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Watzinger, Alexander" <Alexander.Watzinger@oeaw.ac.at> writes:
> > Any chance to add support for dates before 4713 BC? We really would appreciate that.
>
> I'm a little skeptical of the value of applying the Gregorian calendar
> before 1582 AD, let alone thousands of years before the Romans invented
> anything looking even vaguely like it.
>
> Are you sure there's going to be any point whatsoever in trying to name
> individual days that far back? ISTM you'd be lucky to assign a year
> accurately.
>
> > The 4713 BC limit feels very arbitrary, what is the reason for this exact limit?
>
> There are existing equations for calculating Gregorian month/day/year from
> Julian day count [1]. They work back to Julian day zero, at least if
> you grant that proleptic Gregorian dates are sensible that far back.
> Nobody around here has looked into whether they'd work for negative Julian
> day numbers (I suspect not though, at least not without work that seems
> rather pointless).
Sounds reasonable. So the 4713BC limit applies because of the
resolution of 1 day.
Could we allow dates earlier than that, as long as we fix them to the
1st day of any particular month, so the effective resolution becomes
"1 month" before 4713BC? (With various and appropriate restrictions).
--
Simon Riggs http://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-21 18:04 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
2022-09-12 14:40 ` Re: Support for dates before 4713 BC Simon Riggs <simon.riggs@enterprisedb.com>
@ 2022-09-12 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-09-12 15:41 ` Re: Support for dates before 4713 BC Simon Riggs <simon.riggs@enterprisedb.com>
0 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2022-09-12 15:00 UTC (permalink / raw)
To: Simon Riggs <simon.riggs@enterprisedb.com>; +Cc: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Simon Riggs <simon.riggs@enterprisedb.com> writes:
> On Sun, 21 Aug 2022 at 19:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> There are existing equations for calculating Gregorian month/day/year from
>> Julian day count [1]. They work back to Julian day zero, at least if
>> you grant that proleptic Gregorian dates are sensible that far back.
>> Nobody around here has looked into whether they'd work for negative Julian
>> day numbers (I suspect not though, at least not without work that seems
>> rather pointless).
> Sounds reasonable. So the 4713BC limit applies because of the
> resolution of 1 day.
No, it applies because we aren't sure that the math would operate
correctly with negative Julian day numbers --- for instance, division
roundoffs might happen in the wrong direction. If somebody wanted to go
through and check/fix all that, we could probably relax the restriction.
I'm still failing to see the point though. As already discussed upthread,
the SQL datetime types aren't very suitable for dealing with approximate
dates, multiple calendars, etc.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-21 18:04 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
2022-09-12 14:40 ` Re: Support for dates before 4713 BC Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-12 15:00 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-09-12 15:41 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-12 15:53 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: Simon Riggs @ 2022-09-12 15:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, 12 Sept 2022 at 16:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Simon Riggs <simon.riggs@enterprisedb.com> writes:
> > On Sun, 21 Aug 2022 at 19:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> There are existing equations for calculating Gregorian month/day/year from
> >> Julian day count [1]. They work back to Julian day zero, at least if
> >> you grant that proleptic Gregorian dates are sensible that far back.
> >> Nobody around here has looked into whether they'd work for negative Julian
> >> day numbers (I suspect not though, at least not without work that seems
> >> rather pointless).
>
> > Sounds reasonable. So the 4713BC limit applies because of the
> > resolution of 1 day.
>
> No, it applies because we aren't sure that the math would operate
> correctly with negative Julian day numbers --- for instance, division
> roundoffs might happen in the wrong direction. If somebody wanted to go
> through and check/fix all that, we could probably relax the restriction.
OK, challenge seems clear.
> I'm still failing to see the point though. As already discussed upthread,
> the SQL datetime types aren't very suitable for dealing with approximate
> dates, multiple calendars, etc.
The problem is that support for custom datatypes is hard. First, you
have to find a cloud provider that will allow user extensions. Second,
you have to make that work with all the other software that isn't
expecting them.
Not insurmountable, but big enough to make it a barrier for something
fairly small like this.
--
Simon Riggs http://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-21 18:04 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
2022-09-12 14:40 ` Re: Support for dates before 4713 BC Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-12 15:00 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
2022-09-12 15:41 ` Re: Support for dates before 4713 BC Simon Riggs <simon.riggs@enterprisedb.com>
@ 2022-09-12 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 20+ messages in thread
From: Tom Lane @ 2022-09-12 15:53 UTC (permalink / raw)
To: Simon Riggs <simon.riggs@enterprisedb.com>; +Cc: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Simon Riggs <simon.riggs@enterprisedb.com> writes:
> On Mon, 12 Sept 2022 at 16:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> No, it applies because we aren't sure that the math would operate
>> correctly with negative Julian day numbers --- for instance, division
>> roundoffs might happen in the wrong direction. If somebody wanted to go
>> through and check/fix all that, we could probably relax the restriction.
> OK, challenge seems clear.
FWIW, I think this decision dates to a time when we still worried about
C89's lack of specificity about which direction integer division will
round negative results in. Now that we're targeting only C99-compliant
platforms, that part should be easier.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
@ 2022-08-22 10:14 ` stefan eichert <stefaneichert@gmail.com>
2022-08-22 10:42 ` Re: Support for dates before 4713 BC Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-23 09:25 ` Re: Support for dates before 4713 BC Simon Riggs <simon.riggs@enterprisedb.com>
3 siblings, 2 replies; 20+ messages in thread
From: stefan eichert @ 2022-08-22 10:14 UTC (permalink / raw)
To: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Dear All,
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.
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.
Thank you for your feedback!
Stefan
Am Mo., 22. Aug. 2022 um 11:48 Uhr schrieb Watzinger, Alexander <
Alexander.Watzinger@oeaw.ac.at>:
> Hi all,
>
> I'm working on the open source project OpenAtlas (https://openatlas.eu)
> which is used to enter historical and archeological data.
> Although we really appreciate the wonderful PostgreSQL software, the
> limitation of not being able to use dates before 4713 BC is problematic in
> regard to archeological data.
> The only reason for this limitation I found was that the Julian Calendar
> wasn't created with this in mind. The only suggestion I found was to write
> an own implementation just using integers for years. But building a
> parallel date system in this case would be way to cumbersome and error
> prone, we really like using the database for date operations.
>
>
> So my questions are:
> Any chance to add support for dates before 4713 BC? We really would
> appreciate that.
>
> The 4713 BC limit feels very arbitrary, what is the reason for this exact
> limit?
>
>
> Feedback and pointers are very welcome,
>
> Alex
>
>
> --
> Alexander Watzinger
>
> Austrian Academy of Sciences
> Austrian Centre for Digital Humanities and Cultural Heritage
> alexander.watzinger@oeaw.ac.at | www.oeaw.ac.at/acdh
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-22 10:14 ` Re: Support for dates before 4713 BC stefan eichert <stefaneichert@gmail.com>
@ 2022-08-22 10:42 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-22 10:59 ` Re: Support for dates before 4713 BC Tom Browder <tom.browder@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Alvaro Herrera @ 2022-08-22 10:42 UTC (permalink / raw)
To: stefan eichert <stefaneichert@gmail.com>; +Cc: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hello Stefan, Alexander,
On 2022-Aug-22, stefan eichert 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.
> [...]
This made me remember the stuff genealogists use -- a complex datatype
that encodes not just some number on a specific calendar, but also a
"quality" marker (precise, calculated, estimated) as well as a calendar
identifier (Gregorian, Islamic, Hebrew etc). For example, GRAMPS has
this:
https://github.com/gramps-project/gramps/blob/master/gramps/gen/lib/date.py#L531
I'm not familiar with the details, but skimming through that source
might give you some ideas.
Judging from your description and Alexander's, it sounds like you should
dedicate some time to developing a specification for a complex datatype
to use with archaeological or what have you. I suppose you would want
to store type of date it is, its precision, the value itself, a degree
of fuzziness, and maybe some other properties that you know better than
I do. Then get someone to implement an open source custom datatype
based on that spec, which everybody then uses.
Using "regular" calendars is unlikely to be a good match.
--
Álvaro Herrera
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-22 10:14 ` Re: Support for dates before 4713 BC stefan eichert <stefaneichert@gmail.com>
2022-08-22 10:42 ` Re: Support for dates before 4713 BC Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-08-22 10:59 ` Tom Browder <tom.browder@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Tom Browder @ 2022-08-22 10:59 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
This is a bit off topic, but Raku, the sister language of Perl, has a
built-in Date object that should be able to handle the situation. I wonder
if there is a possibility of someday having an interface with Raku as with
Perl.
Note Raku has an excellent module to use PostgreSQL.
Blessings.
-Tom
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-22 10:14 ` Re: Support for dates before 4713 BC stefan eichert <stefaneichert@gmail.com>
@ 2022-08-23 09:25 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-08-23 12:38 ` Re: Support for dates before 4713 BC Peter J. Holzer <hjp-pgsql@hjp.at>
1 sibling, 1 reply; 20+ messages in thread
From: Simon Riggs @ 2022-08-23 09:25 UTC (permalink / raw)
To: stefan eichert <stefaneichert@gmail.com>; +Cc: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, 22 Aug 2022 at 11:14, stefan eichert <stefaneichert@gmail.com> 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/
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-22 10:14 ` Re: Support for dates before 4713 BC stefan eichert <stefaneichert@gmail.com>
2022-08-23 09:25 ` Re: Support for dates before 4713 BC Simon Riggs <simon.riggs@enterprisedb.com>
@ 2022-08-23 12:38 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2022-08-23 14:13 ` Re: Support for dates before 4713 BC Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 20+ messages in thread
From: Peter J. Holzer @ 2022-08-23 12:38 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-22 10:14 ` Re: Support for dates before 4713 BC stefan eichert <stefaneichert@gmail.com>
2022-08-23 09:25 ` Re: Support for dates before 4713 BC Simon Riggs <simon.riggs@enterprisedb.com>
2022-08-23 12:38 ` Re: Support for dates before 4713 BC Peter J. Holzer <hjp-pgsql@hjp.at>
@ 2022-08-23 14:13 ` Erik Wienhold <ewie@ewie.name>
0 siblings, 0 replies; 20+ messages in thread
From: Erik Wienhold @ 2022-08-23 14:13 UTC (permalink / raw)
To: Peter J. Holzer <hjp-pgsql@hjp.at>; pgsql-general@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Support for dates before 4713 BC
@ 2024-03-04 16:01 Koschicek-Krombholz, Bernhard <Bernhard.Koschicek-Krombholz@oeaw.ac.at>
0 siblings, 0 replies; 20+ messages in thread
From: Koschicek-Krombholz, Bernhard @ 2024-03-04 16:01 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
It would be very useful if PostgreSQL would support dates before 4713 BC.
Out of curiosity, are there any plans to implement it in the near future?
All the best,
Bernhard Koschicek-Krombholz
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
@ 2024-10-16 11:57 Richards, Nina <Nina.Richards@oeaw.ac.at>
2024-10-17 00:17 ` Re: Support for dates before 4713 BC David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Richards, Nina @ 2024-10-16 11:57 UTC (permalink / raw)
To: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Dear all,
As Alex, I'm involved with the OpenAtlas system (https://openatlas.eu), and in a recent meeting the issue about tracking dates before 4713 BC came up again. We appreciate all the work you put into developing PostgreSQL and love working with it. But as already mentioned in the discussion two years ago by Alex and Stefan Eichert (Message ID https://www.postgresql.org/message-id/CAJuUXVeNQWm9x7d2_cvzj2rEvtQgNeGF7vog9dmaN1W5e7EDCQ%40mail.gma...), working with dates before 4713 would facilitate our work tremendously.
Even though we know there was no calendar at that time, it would make our work much easier if we could use data before 4713 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 in the meantime?
Thank you so much in advance for all your work. It is highly appreciated! All the best,
Nina
[1674051323205]
Nina Richards MA BSc
Technische Mitarbeiterin OpenAtlas|Technical Staff OpenAtlas
Austrian Centre for Digital Humanities and Cultural Heritage Österreichische Akademie der Wissenschaften | Austrian Academy of Sciences
Bäckerstraße 13, 1010 Wien, Österreich | Vienna, Austria
https://www.oeaw.ac.at/acdh/
<https://dhcr.clarin-dariah.eu/>https://openatlas.eu
<https://www.oeaw.ac.at/acdh/;
Attachments:
OutlookEmoji-1674051323205bec142cd-7cf1-45a9-a595-7bafd8d8d9a0.png (image/png, 24.0K) download
view image
* Re: Support for dates before 4713 BC
2024-10-16 11:57 Re: Support for dates before 4713 BC Richards, Nina <Nina.Richards@oeaw.ac.at>
@ 2024-10-17 00:17 ` David Rowley <dgrowleyml@gmail.com>
2024-10-17 00:36 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: David Rowley @ 2024-10-17 00:17 UTC (permalink / raw)
To: Richards, Nina <Nina.Richards@oeaw.ac.at>; +Cc: Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 before 4713 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 in the 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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2024-10-16 11:57 Re: Support for dates before 4713 BC Richards, Nina <Nina.Richards@oeaw.ac.at>
2024-10-17 00:17 ` Re: Support for dates before 4713 BC David Rowley <dgrowleyml@gmail.com>
@ 2024-10-17 00:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-10-17 06:43 ` Re: Support for dates before 4713 BC Asad Ali <asadalinagri@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2024-10-17 00:36 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Richards, Nina <Nina.Richards@oeaw.ac.at>; Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Support for dates before 4713 BC
2024-10-16 11:57 Re: Support for dates before 4713 BC Richards, Nina <Nina.Richards@oeaw.ac.at>
2024-10-17 00:17 ` Re: Support for dates before 4713 BC David Rowley <dgrowleyml@gmail.com>
2024-10-17 00:36 ` Re: Support for dates before 4713 BC Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-10-17 06:43 ` Asad Ali <asadalinagri@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Asad Ali @ 2024-10-17 06:43 UTC (permalink / raw)
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>
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
>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2024-10-17 06:43 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-20 14:05 Support for dates before 4713 BC Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-21 17:15 ` Adrian Klaver <adrian.klaver@aklaver.com>
2022-09-12 12:22 ` Watzinger, Alexander <Alexander.Watzinger@oeaw.ac.at>
2022-08-21 17:54 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2022-08-21 18:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-09-12 14:40 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-12 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-09-12 15:41 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-12 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-22 10:14 ` stefan eichert <stefaneichert@gmail.com>
2022-08-22 10:42 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-22 10:59 ` Tom Browder <tom.browder@gmail.com>
2022-08-23 09:25 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-08-23 12:38 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2022-08-23 14:13 ` Erik Wienhold <ewie@ewie.name>
2024-03-04 16:01 Support for dates before 4713 BC Koschicek-Krombholz, Bernhard <Bernhard.Koschicek-Krombholz@oeaw.ac.at>
2024-10-16 11:57 Re: Support for dates before 4713 BC Richards, Nina <Nina.Richards@oeaw.ac.at>
2024-10-17 00:17 ` David Rowley <dgrowleyml@gmail.com>
2024-10-17 00:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-10-17 06:43 ` Asad Ali <asadalinagri@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox