Re: Patch to extend range of timestamps

I believe this patch is the conclusion of several discussions.

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

John Cochran wrote:
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073