timestamp patch to extend legal range of dates. - Mailing list pgsql-patches

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)