Thread: Support for dates before 4713 BC
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
Austrian Academy of Sciences
Austrian Centre for Digital Humanities and Cultural Heritage
alexander.watzinger@oeaw.ac.at | www.oeaw.ac.at/acdh
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
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!"
Attachment
"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
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
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
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 PostgreSQLis a perfect Open Source software and we really love working with it for our archaeological and (pre)historicalprojects. 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, asAlex 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 spanin which a tree has been cut, like in autumn 6000 BC (so lets say between March and beginning of July), then we wouldlike 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, butstill 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/datesfor 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/
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!"
Attachment
> 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
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
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
Sent: Sunday, August 21, 2022 19:15
To: Watzinger, Alexander; pgsql-general@lists.postgresql.org
Subject: Re: Support for dates before 4713 BC
> 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
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/
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
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/
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