Re: Patch to extend range of timestamps
От | Bruce Momjian |
---|---|
Тема | Re: Patch to extend range of timestamps |
Дата | |
Msg-id | 200302190348.h1J3m5V25671@candle.pha.pa.us обсуждение исходный текст |
Ответ на | Patch to extend range of timestamps (John Cochran <jdc@fiawol.org>) |
Список | pgsql-patches |
Patch applied. Thanks. --------------------------------------------------------------------------- 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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
В списке pgsql-patches по дате отправления: