Patch to extend range of timestamps - Mailing list pgsql-patches

Greetings once again,

The following patches eliminate the overflows in the j2date() and date2j()
functions which limited the maximum date for a timestamp to AD 1465001.
The new limit is AD 5874897.
The files affected are:

doc/src/sgml/datatype.sgml:
    Documentation change due to patch. Included is a notice about
    the reduced range when using an eight-byte integer for timestamps.

src/backend/utils/adt/datetime.c:
    Replacement functions for j2date() and date2j() functions.

src/include/utils/datetime.h:
    Corrected a bug with the limit on the earliest possible date,
    Nov 23,-4713 has a Julian day count of -1. The earliest possible
    date should be Nov 24, -4713 with a day count of 0.

src/test/regress/expected/horology-no-DST-before-1970.out:
src/test/regress/expected/horology-solaris-1947.out:
src/test/regress/expected/horology.out:
    Copies of expected output for regression testing.
    Note: Only horology.out has been physically tested. I do not have access
    to a Solaris box and I don't know how to provoke the "pre-1970" test.

src/test/regress/sql/horology.sql:
    Added some test cases to check extended range.

---- CUT HERE ----

Index: doc/src/sgml/datatype.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v
retrieving revision 1.113
diff -c -r1.113 datatype.sgml
*** doc/src/sgml/datatype.sgml    2003/01/31 01:08:07    1.113
--- doc/src/sgml/datatype.sgml    2003/02/06 01:27:00
***************
*** 1243,1249 ****
          <entry>both date and time</entry>
          <entry>8 bytes</entry>
          <entry>4713 BC</entry>
!         <entry>AD 1465001</entry>
          <entry>1 microsecond / 14 digits</entry>
         </row>
         <row>
--- 1243,1249 ----
          <entry>both date and time</entry>
          <entry>8 bytes</entry>
          <entry>4713 BC</entry>
!         <entry>AD 5874897</entry>
          <entry>1 microsecond / 14 digits</entry>
         </row>
         <row>
***************
*** 1251,1257 ****
          <entry>both date and time</entry>
          <entry>8 bytes</entry>
          <entry>4713 BC</entry>
!         <entry>AD 1465001</entry>
          <entry>1 microsecond / 14 digits</entry>
         </row>
         <row>
--- 1251,1257 ----
          <entry>both date and time</entry>
          <entry>8 bytes</entry>
          <entry>4713 BC</entry>
!         <entry>AD 5874897</entry>
          <entry>1 microsecond / 14 digits</entry>
         </row>
         <row>
***************
*** 1309,1315 ****
      a few years of 2000-01-01, but the precision degrades for dates further
      away.  When timestamps are stored as eight-byte integers (a compile-time
      option), microsecond precision is available over the full range of
!     values.
     </para>
     </note>

--- 1309,1316 ----
      a few years of 2000-01-01, but the precision degrades for dates further
      away.  When timestamps are stored as eight-byte integers (a compile-time
      option), microsecond precision is available over the full range of
!     values. However eight-byte integer timestamps have a reduced range of
!     dates from 4713 BC up to 294276 AD.
     </para>
     </note>

Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
retrieving revision 1.99
diff -c -r1.99 datetime.c
*** src/backend/utils/adt/datetime.c    2003/01/29 01:08:42    1.99
--- src/backend/utils/adt/datetime.c    2003/02/06 01:27:30
***************
*** 587,652 ****
   *    since it is numerically accurate and computationally simple.
   * The algorithms here will accurately convert between Julian day
   *    and calendar date for all non-negative Julian days
!  *    (i.e. from Nov 23, -4713 on).
   *
-  * Ref: Explanatory Supplement to the Astronomical Almanac, 1992.
-  *    University Science Books, 20 Edgehill Rd. Mill Valley CA 94941.
-  *
-  * Use the algorithm by Henry Fliegel, a former NASA/JPL colleague
-  *    now at Aerospace Corp. (hi, Henry!)
-  *
   * These routines will be used by other date/time packages
   * - 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)
  {
!     int            day;

!     day = (date + 1) % 7;

!     return day;
  }    /* j2day() */


--- 587,663 ----
   *    since it is numerically accurate and computationally simple.
   * The algorithms here will accurately convert between Julian day
   *    and calendar date for all non-negative Julian days
!  *    (i.e. from Nov 24, -4713 on).
   *
   * These routines will be used by other date/time packages
   * - thomas 97/02/25
+  *
+  * Rewritten to eliminate overflow problems. This now allows the
+  * routines to work correctly for all Julian day counts from
+  * 0 to 2147483647  (Nov 24, -4713 to Jun 3, 5874898) assuming
+  * a 32-bit integer. Longer types should also work to the limits
+  * of their precision.
   */

  int
  date2j(int y, int m, int d)
  {
!     int            julian;
!     int            century;

!     if (m > 2) {
!         m += 1;
!         y += 4800;
!     } else {
!         m += 13;
!         y += 4799;
!     }
!
!     century = y/100;
!     julian  = y*365 - 32167;
!     julian += y/4 - century + century/4;
!     julian += 7834*m/256 + d;
!
!     return julian;
  }    /* date2j() */

  void
  j2date(int jd, int *year, int *month, int *day)
  {
!     unsigned int        julian;
!     unsigned int        quad;
!     unsigned int        extra;
!     int            y;
!
!     julian = jd;
!     julian += 32044;
!     quad = julian/146097;
!     extra = (julian - quad*146097)*4 + 3;
!     julian += 60 + quad*3 + extra/146097;
!     quad = julian/1461;
!     julian -= quad*1461;
!     y = julian * 4 / 1461;
!     julian = ((y != 0) ? ((julian + 305) % 365) : ((julian + 306) % 366))
!         + 123;
!     y += quad*4;
!     *year = y - 4800;
!     quad = julian * 2141 / 65536;
!     *day = julian - 7834*quad/256;
!     *month = (quad + 10) % 12 + 1;
!
      return;
  }    /* j2date() */

  int
  j2day(int date)
  {
!     unsigned int day;

!     day = date;
!     day += 1;
!     day %= 7;

!     return (int) day;
  }    /* j2day() */


Index: src/include/utils/datetime.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/datetime.h,v
retrieving revision 1.34
diff -c -r1.34 datetime.h
*** src/include/utils/datetime.h    2003/01/16 00:26:49    1.34
--- src/include/utils/datetime.h    2003/02/06 01:27:44
***************
*** 239,245 ****

  #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) \
--- 239,245 ----

  #define JULIAN_MINYEAR (-4713)
  #define JULIAN_MINMONTH (11)
! #define JULIAN_MINDAY (24)

  #define IS_VALID_JULIAN(y,m,d) (((y) > JULIAN_MINYEAR) \
   || (((y) == JULIAN_MINYEAR) && (((m) > JULIAN_MINMONTH) \
Index: src/test/regress/expected/horology-no-DST-before-1970.out
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology-no-DST-before-1970.out,v
retrieving revision 1.27
diff -c -r1.27 horology-no-DST-before-1970.out
*** src/test/regress/expected/horology-no-DST-before-1970.out    2003/01/31 01:08:08    1.27
--- src/test/regress/expected/horology-no-DST-before-1970.out    2003/02/06 01:28:17
***************
*** 328,333 ****
--- 328,357 ----
   Fri Dec 31 23:59:59 1999
  (1 row)

+ SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
+        Nov 27, 2733194
+ -----------------------------
+  Sun Nov 27 00:00:00 2733194
+ (1 row)
+
+ SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
+        Nov 30, 5471101
+ -----------------------------
+  Sat Nov 30 00:00:00 5471101
+ (1 row)
+
+ SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
+        Dec 31, 5874897
+ -----------------------------
+  Tue Dec 31 00:00:00 5874897
+ (1 row)
+
+ SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
+   2147483493 Days
+ -------------------
+  @ 2147483493 days
+ (1 row)
+
  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.
  -- So, just try to test parser and hope for the best - thomas 97/04/26
Index: src/test/regress/expected/horology-solaris-1947.out
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology-solaris-1947.out,v
retrieving revision 1.21
diff -c -r1.21 horology-solaris-1947.out
*** src/test/regress/expected/horology-solaris-1947.out    2003/01/31 01:08:08    1.21
--- src/test/regress/expected/horology-solaris-1947.out    2003/02/06 01:28:34
***************
*** 328,333 ****
--- 328,357 ----
   Fri Dec 31 23:59:59 1999
  (1 row)

+ SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
+        Nov 27, 2733194
+ -----------------------------
+  Sun Nov 27 00:00:00 2733194
+ (1 row)
+
+ SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
+        Nov 30, 5471101
+ -----------------------------
+  Sat Nov 30 00:00:00 5471101
+ (1 row)
+
+ SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
+        Dec 31, 5874897
+ -----------------------------
+  Tue Dec 31 00:00:00 5874897
+ (1 row)
+
+ SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
+   2147483493 Days
+ -------------------
+  @ 2147483493 days
+ (1 row)
+
  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.
  -- So, just try to test parser and hope for the best - thomas 97/04/26
Index: src/test/regress/expected/horology.out
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/horology.out,v
retrieving revision 1.40
diff -c -r1.40 horology.out
*** src/test/regress/expected/horology.out    2003/01/31 01:08:08    1.40
--- src/test/regress/expected/horology.out    2003/02/06 01:28:56
***************
*** 328,333 ****
--- 328,357 ----
   Fri Dec 31 23:59:59 1999
  (1 row)

+ SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
+        Nov 27, 2733194
+ -----------------------------
+  Sun Nov 27 00:00:00 2733194
+ (1 row)
+
+ SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
+        Nov 30, 5471101
+ -----------------------------
+  Sat Nov 30 00:00:00 5471101
+ (1 row)
+
+ SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
+        Dec 31, 5874897
+ -----------------------------
+  Tue Dec 31 00:00:00 5874897
+ (1 row)
+
+ SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";
+   2147483493 Days
+ -------------------
+  @ 2147483493 days
+ (1 row)
+
  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.
  -- So, just try to test parser and hope for the best - thomas 97/04/26
Index: src/test/regress/sql/horology.sql
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/horology.sql,v
retrieving revision 1.25
diff -c -r1.25 horology.sql
*** src/test/regress/sql/horology.sql    2003/01/31 01:08:08    1.25
--- src/test/regress/sql/horology.sql    2003/02/06 01:28:58
***************
*** 76,81 ****
--- 76,85 ----
  SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
  SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
  SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
+ SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
+ SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
+ SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
+ SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493
Days";

  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.