Thread: timestamp patch to extend legal range of dates.
Greetings,
Here is a patch that modifies how timestamp values are generated and
extends the legal range of timestamps. The overall effect is:
1. For dates prior to Oct 15, 1582 it uses the Julian calendar.
2. For dates on and after Oct 15, 1582, it uses the Gregorian calendar.
The implementation of date2j() and j2date() has been carefully done to
prevent integer overflow. As a result of this the legal range of dates
that this code will handle is from
January 1, 4713 BC through June 3, 5874898 AD
(Julian day numbers from 0 to 2147483647)
Reasons for patch:
1. 02/29/0100, 02/29/0200, 02/29/0300, 02/29/0500,
02/29/0600, 02/29/0700, 02/29/0900, 02/29/1000,
02/29/1100, 02/29/1300, 02/29/1400, 02/29/1500,
are all legal dates on historical records.
2. I hate overflows if I can at all avoid them.
3. Legal timestamp range for PostGreSQL can now be advertised as extending
from 4713 BC to 5874897 AD instead of only 4713 BC to 1465001 AD.
The decision to have the switchover point be Oct 15, 1582 was made to cause
the new routines return the same values as the old routines for the largest
possible range. I did consider having the switchover be Sept 14, 1752 to
be consistent with Unix, but decided against it.
*** postgresql-7.3.1/src/include/utils/datetime.h.old Sat Feb 1 15:45:08 2003
--- postgresql-7.3.1/src/include/utils/datetime.h Mon Feb 3 20:17:52 2003
***************
*** 230,249 ****
extern int day_tab[2][13];
! #define isleap(y) (((y) % 4) == 0 && (((y) % 100) != 0 || ((y) % 400) == 0))
! /* Julian date support for date2j() and j2date()
! * Set the minimum year to one greater than the year of the first valid day
! * to avoid having to check year and day both. - tgl 97/05/08
! */
! #define JULIAN_MINYEAR (-4713)
! #define JULIAN_MINMONTH (11)
! #define JULIAN_MINDAY (23)
! #define IS_VALID_JULIAN(y,m,d) (((y) > JULIAN_MINYEAR) \
! || (((y) == JULIAN_MINYEAR) && (((m) > JULIAN_MINMONTH) \
! || (((m) == JULIAN_MINMONTH) && ((d) >= JULIAN_MINDAY)))))
#define UTIME_MINYEAR (1901)
#define UTIME_MINMONTH (12)
--- 230,243 ----
extern int day_tab[2][13];
! #define isleap(y) (((y) % 4) == 0 \
! && (((y) % 100) != 0 || ((y) % 400) == 0 || (y) < 1582))
! /* Julian date support for date2j() and j2date() */
! #define JULIAN_MINYEAR (-4712)
! #define IS_VALID_JULIAN(y,m,d) ((y) >= JULIAN_MINYEAR)
#define UTIME_MINYEAR (1901)
#define UTIME_MINMONTH (12)
*** postgresql-7.3.1/src/backend/utils/adt/datetime.c.old Fri Jan 31 22:14:55 2003
--- postgresql-7.3.1/src/backend/utils/adt/datetime.c Mon Feb 3 20:19:54 2003
***************
*** 599,643 ****
* - thomas 97/02/25
*/
int
! date2j(int y, int m, int d)
{
! int m12 = (m - 14) / 12;
! return ((1461 * (y + 4800 + m12)) / 4
! + (367 * (m - 2 - 12 * (m12))) / 12
! - (3 * ((y + 4900 + m12) / 100)) / 4
! + d - 32075);
! } /* date2j() */
void
! j2date(int jd, int *year, int *month, int *day)
{
! int j,
! y,
! m,
! d;
! int i,
! l,
! n;
! l = jd + 68569;
! n = (4 * l) / 146097;
! l -= (146097 * n + 3) / 4;
! i = (4000 * (l + 1)) / 1461001;
! l += 31 - (1461 * i) / 4;
! j = (80 * l) / 2447;
! d = l - (2447 * j) / 80;
! l = j / 11;
! m = (j + 2) - (12 * l);
! y = 100 * (n - 49) + i + l;
!
! *year = y;
! *month = m;
! *day = d;
! return;
! } /* j2date() */
int
j2day(int date)
--- 599,666 ----
* - thomas 97/02/25
*/
+ /*
+ * Convert a year, month, day into a julian day number.
+ * Dates prior to Oct 15, 1582 are considered to be in the Julian Calendar.
+ * Later dates are considered to be in the Gregorian Calendar.
+ * Legal range of dates range from January 1, 4713 BC to June 3, 5874898 AD
+ * (Julian day numbers from 0 to 2147483647)
+ */
int
! date2j(int year, int month, int day)
{
! unsigned int julian;
! if (month > 2) {
! year += 4800;
! month += 1;
! } else {
! year += 4799;
! month += 13;
! }
! julian = year;
! julian *= 365;
! julian += year/4 + 7834*month/256 + day;
! julian -= 32205;
! if (julian >= 2299171) {
! int centuries;
! centuries = year / 100;
! julian -= centuries - centuries/4 - 38;
! }
! return julian;
! }
void
! j2date(int juldat, int *iyyy, int *mm, int *id)
{
! unsigned int julian;
! unsigned int quad;
! int year;
! julian = juldat;
! if (julian >= 2299161) {
! unsigned int extra;
! extra = julian - 1867217;
! quad = extra / 146097;
! extra = (extra - quad * 146097) * 4 + 3;
! julian += 1 + quad * 3 + extra / 146097;
! }
! quad = julian / 1461;
! julian -= quad * 1461;
! year = julian * 4 / 1461;
! if (year != 0) { /* Non-leap year */
! julian = (julian + 305) % 365 + 123;
! } else { /* Leap year */
! julian = (julian + 306) % 366 + 123;
! }
! year += quad*4;
! year -= 4712;
! *iyyy = year;
! quad = julian * 2141/65536;
! *id = julian - 7834*quad/256;
! *mm = (quad + 10) % 12 + 1;
! }
int
j2day(int date)
On Mon, 2003-02-03 at 21:18, John Cochran wrote: > Here is a patch that modifies how timestamp values are generated and > extends the legal range of timestamps. Without knowing anything about the timestamp code, one thing you'll need to do is update the documentation. If you're keen, updating the regression tests (and/or adding new tests for this functionality) would also be appreciated. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
John Cochran <jdc@fiawol.org> writes:
> Here is a patch that modifies how timestamp values are generated and
> extends the legal range of timestamps. The overall effect is:
> 1. For dates prior to Oct 15, 1582 it uses the Julian calendar.
> 2. For dates on and after Oct 15, 1582, it uses the Gregorian calendar.
>
> The decision to have the switchover point be Oct 15, 1582 was made to cause
> the new routines return the same values as the old routines for the largest
> possible range. I did consider having the switchover be Sept 14, 1752 to
> be consistent with Unix, but decided against it.
This seems like rather an odd choice. Isn't the 1752 date commonly
recognized as the start of Gregorian dating?
I'd be inclined to go with a changeover date that's defensible in the
long run, without regard to what we might have done before. Backwards
compatibility with mistakes isn't a good design principle IMHO ...
regards, tom lane
On Tue, 2003-02-04 at 04:41, Tom Lane wrote:
> John Cochran <jdc@fiawol.org> writes:
> > Here is a patch that modifies how timestamp values are generated and
> > extends the legal range of timestamps. The overall effect is:
> > 1. For dates prior to Oct 15, 1582 it uses the Julian calendar.
> > 2. For dates on and after Oct 15, 1582, it uses the Gregorian calendar.
> >
> > The decision to have the switchover point be Oct 15, 1582 was made to cause
> > the new routines return the same values as the old routines for the largest
> > possible range. I did consider having the switchover be Sept 14, 1752 to
> > be consistent with Unix, but decided against it.
>
> This seems like rather an odd choice. Isn't the 1752 date commonly
> recognized as the start of Gregorian dating?
In the 6th Century, Bede proposed a more accurate calendar than the
Julian calendar introduced by Julius Caesar. In 1582, Pope Gregory
accepted a similar proposal and ordered the change across all the
Catholic world. Since England had rejected papal authority and the pope
was encouraging Catholic powers to attack England, his order was not
effective here, nor in some other protestant countries. It actually
took effect that year in France, Spain, Portugal and the Italian states;
parts of Germany, Belgium, the Netherlands, and Switzerland changed
within a couple of years and Hungary in 1587. The Protestant parts of
Germany changed in 1700.
In 1752, England adopted the Gregorian calendar at last, causing popular
unrest ("Give us back our 11 days!"). The American colonies adopted it
at the same time. Sweden changed in 1753 and Russia in 1918.
In 1751/2 England also changed from a new year date of 25th March to 1st
January as part of the same calendar change. (The Inland Revenue didn't
change, which is why our tax year starts on April 6th.) So in England
and America, 1751 ran from 25th March to 31st December; 1752 ran from
1st January to 2nd September and then 14th September to 31st December.
24th March 1750 was one day before 25th March 1751.
All of which adds up to: which you choose depends on what you are using
the date for. Historical records of Catholic coutries need 1582; those
of England and English colonies need 1752. Does anyone know how
historians cope when tracking both together?
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"That at the name of Jesus every knee should bow, of
things in heaven, and things in earth, and things
under the earth; And that every tongue should confess
that Jesus Christ is Lord, to the glory of God the
Father." Philippians 2:10,11
Oliver Elphick <olly@lfix.co.uk> writes:
> On Tue, 2003-02-04 at 04:41, Tom Lane wrote:
>> This seems like rather an odd choice. Isn't the 1752 date commonly
>> recognized as the start of Gregorian dating?
> [ historical calendars are a mess ]
True. So if I've got this straight:
Oct 15, 1582 = Gregorian changeover in Catholic countries
Sep 14, 1752 = changeover in Britain and her colonies
various other dates in other places
However, that doesn't mean we should just toss a coin to decide which
behavior to follow. John says that there is a precedent for using
1752 (but which Unix are you speaking of here, John? Most of 'em don't
keep time before ~1900, period). I'd be inclined to follow that
precedent not strike out on our own.
Also, given that the majority of Postgres users are (so far as I can
tell) in English-speaking countries, the 1752 date seems most useful
to the majority.
I suppose we could contemplate making the switch occur on a date
determined by LC_TIME ;-) ... but I don't think I wanna go there ...
regards, tom lane
> 1752 (but which Unix are you speaking of here, John? Most of 'em don't
> keep time before ~1900, period). I'd be inclined to follow that
> precedent not strike out on our own.
bash-2.05b$ cal 09 1752
September 1752
Su Mo Tu We Th Fr Sa
1 2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
bash-2.05b$ uname -a
FreeBSD jester 4.7-RELEASE FreeBSD 4.7-RELEASE #10: Sat Nov 16 16:00:07
EST 2002 root@jester:/usr/obj/usr/src/sys/JESTER i386
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
On Tue, 2003-02-04 at 16:09, Rod Taylor wrote: > > 1752 (but which Unix are you speaking of here, John? Most of 'em don't > > keep time before ~1900, period). I'd be inclined to follow that > > precedent not strike out on our own. > > bash-2.05b$ cal 09 1752 > September 1752 > Su Mo Tu We Th Fr Sa > 1 2 14 15 16 > 17 18 19 20 21 22 23 > 24 25 26 27 28 29 30 > > bash-2.05b$ uname -a > FreeBSD jester 4.7-RELEASE FreeBSD 4.7-RELEASE #10: Sat Nov 16 16:00:07 > EST 2002 root@jester:/usr/obj/usr/src/sys/JESTER i386 So cal is using English dates, but it isn't consistent because it doesn't know about the change in the new year date in England in 1751/2. I got my data from http://serendipity.magnet.ch/hermetic/cal_stud/cal_art.htm which also talks about proleptic calendars; that is, the current calendar is projected backwards (or the Julian calendar is projected forwards), so that there are no breaks in the sequence of dates. Does the SQL standard define what calendar it is using? My copy of Date & Cannan: 'SQL -- The Standard Handbook' says: "YEAR - Valid values: 0001 to 9999. This permits the specification of dates prior to the invention of the Gregorian calendar and assumes that the rules of the Gregorian calendar can be applied retrospectively." So I suggest we should use the Gregorian proleptic calendar as the default. Example: England Italy Proleptic Gregorian 15 Sep 1752 15 Sep 1752 15 Sep 1752 1 Sep 1752 12 Sep 1752 12 Sep 1752 25 Mar 1751 5 Apr 1751 5 Apr 1751 24 Mar 1750 4 Apr 1751 4 Apr 1751 6 Oct 1582 15 Oct 1582 15 Oct 1582 5 Oct 1582 5 Oct 1582 14 Oct 1582 24 Mar 1581 24 Mar 1582 3 Apr 1582 Admittedly, nowhere in the world would have used the proleptic Gregorian date before 15 Oct 1582 (except for all of the third century AD in the Roman empire), but it has the virtue of consistency, and any system that changes the calendar basis at a particular date will also be wrong for a large number of users. Even if we say that most of our users are from England and its colonies, that is not to say that their uses for historical dates are limited to events in those places or seen from the point of view of their then inhabitants. It is really only historians, genealogists and astronomers who are likely to want to use such ancient dates, so we ought to consider their needs before fixing the type. Consider how you would record the marriage date of an Italian marrying a Briton in the seventeenth century, if you wanted to relate it to events in both countries. If the date type were able to have subtypes, the typmod field could be used to determine what system a date belonged to and therefore how it should be represented, or else these could be predefined function parameters. E.g.: 0 Proleptic Gregorian - default 1 Astronomical 2 England and colonies 4 Italy, Spain, France, Portugal 5 Catholic Germany 6 Protestant Germany 7 Sweden 8 Russia 9 Greece 10 Turkey 11 Eastern Orthodox Church 12 ab urbe condita 13 Jewish ... etc ... While the conversions for these could be written to be based on John's current proposal, it would be purer and simpler if they did not have to cope with any gaps at all. We don't have to define all of them, but make the structure so that they can be filled in later. The internal effect would be that the earliest possible date would be about 29th November 4714BC (Gregorian proleptic calendar); since that exceeds the SQL limit, it should not be a problem. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "That at the name of Jesus every knee should bow, of things in heaven, and things in earth, and things under the earth; And that every tongue should confess that Jesus Christ is Lord, to the glory of God the Father." Philippians 2:10,11
[snip...] > I got my data from > http://serendipity.magnet.ch/hermetic/cal_stud/cal_art.htm > which also talks about proleptic calendars; that is, the current > calendar is projected backwards (or the Julian calendar is projected > forwards), so that there are no breaks in the sequence of dates. > > Does the SQL standard define what calendar it is using? My copy of Date > & Cannan: 'SQL -- The Standard Handbook' says: > > "YEAR - Valid values: 0001 to 9999. This permits the specification of > dates prior to the invention of the Gregorian calendar and assumes that > the rules of the Gregorian calendar can be applied retrospectively." > > So I suggest we should use the Gregorian proleptic calendar as the > default. > > Example: England Italy Proleptic > Gregorian > > 15 Sep 1752 15 Sep 1752 15 Sep 1752 > 1 Sep 1752 12 Sep 1752 12 Sep 1752 > 25 Mar 1751 5 Apr 1751 5 Apr 1751 > 24 Mar 1750 4 Apr 1751 4 Apr 1751 > 6 Oct 1582 15 Oct 1582 15 Oct 1582 > 5 Oct 1582 5 Oct 1582 14 Oct 1582 > 24 Mar 1581 24 Mar 1582 3 Apr 1582 > [snip...] Point taken. I withdraw my patch. However, I'm going to examine date2j() and j2date() functions a bit closer and see if I can restructure them to eliminate the overflow problems they have. I would still like for those functions to be capable of dealing with the full range of available numbers.
John Cochran <jdc@fiawol.org> writes:
> Point taken. I withdraw my patch. However, I'm going to examine date2j()
> and j2date() functions a bit closer and see if I can restructure them to
> eliminate the overflow problems they have. I would still like for those
> functions to be capable of dealing with the full range of available numbers.
Sure, that's worth doing in any case.
regards, tom lane