Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita286f73

Browse files
committed
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.John Cochran
1 parent4efbbd7 commita286f73

File tree

7 files changed

+132
-44
lines changed

7 files changed

+132
-44
lines changed

‎doc/src/sgml/datatype.sgml‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.113 2003/01/31 01:08:07 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.114 2003/02/19 03:48:10 momjian Exp $
33
-->
44

55
<chapter id="datatype">
@@ -1243,15 +1243,15 @@ SELECT b, char_length(b) FROM test2;
12431243
<entry>both date and time</entry>
12441244
<entry>8 bytes</entry>
12451245
<entry>4713 BC</entry>
1246-
<entry>AD1465001</entry>
1246+
<entry>AD5874897</entry>
12471247
<entry>1 microsecond / 14 digits</entry>
12481248
</row>
12491249
<row>
12501250
<entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
12511251
<entry>both date and time</entry>
12521252
<entry>8 bytes</entry>
12531253
<entry>4713 BC</entry>
1254-
<entry>AD1465001</entry>
1254+
<entry>AD5874897</entry>
12551255
<entry>1 microsecond / 14 digits</entry>
12561256
</row>
12571257
<row>
@@ -1309,7 +1309,8 @@ SELECT b, char_length(b) FROM test2;
13091309
a few years of 2000-01-01, but the precision degrades for dates further
13101310
away. When timestamps are stored as eight-byte integers (a compile-time
13111311
option), microsecond precision is available over the full range of
1312-
values.
1312+
values. However eight-byte integer timestamps have a reduced range of
1313+
dates from 4713 BC up to 294276 AD.
13131314
</para>
13141315
</note>
13151316

‎src/backend/utils/adt/datetime.c‎

Lines changed: 49 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v 1.99 2003/01/29 01:08:42 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v 1.100 2003/02/19 03:48:10 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -587,66 +587,77 @@ static datetkn *deltacache[MAXDATEFIELDS] = {NULL};
587587
*since it is numerically accurate and computationally simple.
588588
* The algorithms here will accurately convert between Julian day
589589
*and calendar date for all non-negative Julian days
590-
*(i.e. from Nov 23, -4713 on).
591-
*
592-
* Ref: Explanatory Supplement to the Astronomical Almanac, 1992.
593-
*University Science Books, 20 Edgehill Rd. Mill Valley CA 94941.
594-
*
595-
* Use the algorithm by Henry Fliegel, a former NASA/JPL colleague
596-
*now at Aerospace Corp. (hi, Henry!)
590+
*(i.e. from Nov 24, -4713 on).
597591
*
598592
* These routines will be used by other date/time packages
599593
* - thomas 97/02/25
594+
*
595+
* Rewritten to eliminate overflow problems. This now allows the
596+
* routines to work correctly for all Julian day counts from
597+
* 0 to 2147483647 (Nov 24, -4713 to Jun 3, 5874898) assuming
598+
* a 32-bit integer. Longer types should also work to the limits
599+
* of their precision.
600600
*/
601601

602602
int
603603
date2j(inty,intm,intd)
604604
{
605-
intm12= (m-14) /12;
605+
intjulian;
606+
intcentury;
607+
608+
if (m>2) {
609+
m+=1;
610+
y+=4800;
611+
}else {
612+
m+=13;
613+
y+=4799;
614+
}
615+
616+
century=y/100;
617+
julian=y*365-32167;
618+
julian+=y/4-century+century/4;
619+
julian+=7834*m/256+d;
606620

607-
return ((1461* (y+4800+m12)) /4
608-
+ (367* (m-2-12* (m12))) /12
609-
- (3* ((y+4900+m12) /100)) /4
610-
+d-32075);
621+
returnjulian;
611622
}/* date2j() */
612623

613624
void
614625
j2date(intjd,int*year,int*month,int*day)
615626
{
616-
intj,
617-
y,
618-
m,
619-
d;
627+
unsignedintjulian;
628+
unsignedintquad;
629+
unsignedintextra;
630+
inty;
631+
632+
julian=jd;
633+
julian+=32044;
634+
quad=julian/146097;
635+
extra= (julian-quad*146097)*4+3;
636+
julian+=60+quad*3+extra/146097;
637+
quad=julian/1461;
638+
julian-=quad*1461;
639+
y=julian*4 /1461;
640+
julian= ((y!=0) ? ((julian+305) %365) : ((julian+306) %366))
641+
+123;
642+
y+=quad*4;
643+
*year=y-4800;
644+
quad=julian*2141 /65536;
645+
*day=julian-7834*quad/256;
646+
*month= (quad+10) %12+1;
620647

621-
inti,
622-
l,
623-
n;
624-
625-
l=jd+68569;
626-
n= (4*l) /146097;
627-
l-= (146097*n+3) /4;
628-
i= (4000* (l+1)) /1461001;
629-
l+=31- (1461*i) /4;
630-
j= (80*l) /2447;
631-
d=l- (2447*j) /80;
632-
l=j /11;
633-
m= (j+2)- (12*l);
634-
y=100* (n-49)+i+l;
635-
636-
*year=y;
637-
*month=m;
638-
*day=d;
639648
return;
640649
}/* j2date() */
641650

642651
int
643652
j2day(intdate)
644653
{
645-
intday;
654+
unsignedintday;
646655

647-
day= (date+1) %7;
656+
day=date;
657+
day+=1;
658+
day %=7;
648659

649-
returnday;
660+
return(int)day;
650661
}/* j2day() */
651662

652663

‎src/include/utils/datetime.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
1010
* Portions Copyright (c) 1994, Regents of the University of California
1111
*
12-
* $Id: datetime.h,v 1.34 2003/01/16 00:26:49 tgl Exp $
12+
* $Id: datetime.h,v 1.35 2003/02/19 03:48:10 momjian Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -239,7 +239,7 @@ extern intday_tab[2][13];
239239

240240
#defineJULIAN_MINYEAR (-4713)
241241
#defineJULIAN_MINMONTH (11)
242-
#defineJULIAN_MINDAY (23)
242+
#defineJULIAN_MINDAY (24)
243243

244244
#defineIS_VALID_JULIAN(y,m,d) (((y) > JULIAN_MINYEAR) \
245245
|| (((y) == JULIAN_MINYEAR) && (((m) > JULIAN_MINMONTH) \

‎src/test/regress/expected/horology-no-DST-before-1970.out‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -328,6 +328,30 @@ SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second'
328328
Fri Dec 31 23:59:59 1999
329329
(1 row)
330330

331+
SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
332+
Nov 27, 2733194
333+
-----------------------------
334+
Sun Nov 27 00:00:00 2733194
335+
(1 row)
336+
337+
SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
338+
Nov 30, 5471101
339+
-----------------------------
340+
Sat Nov 30 00:00:00 5471101
341+
(1 row)
342+
343+
SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
344+
Dec 31, 5874897
345+
-----------------------------
346+
Tue Dec 31 00:00:00 5874897
347+
(1 row)
348+
349+
SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493 Days";
350+
2147483493 Days
351+
-------------------
352+
@ 2147483493 days
353+
(1 row)
354+
331355
-- Shorthand values
332356
-- Not directly usable for regression testing since these are not constants.
333357
-- So, just try to test parser and hope for the best - thomas 97/04/26

‎src/test/regress/expected/horology-solaris-1947.out‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -328,6 +328,30 @@ SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second'
328328
Fri Dec 31 23:59:59 1999
329329
(1 row)
330330

331+
SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
332+
Nov 27, 2733194
333+
-----------------------------
334+
Sun Nov 27 00:00:00 2733194
335+
(1 row)
336+
337+
SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
338+
Nov 30, 5471101
339+
-----------------------------
340+
Sat Nov 30 00:00:00 5471101
341+
(1 row)
342+
343+
SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
344+
Dec 31, 5874897
345+
-----------------------------
346+
Tue Dec 31 00:00:00 5874897
347+
(1 row)
348+
349+
SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493 Days";
350+
2147483493 Days
351+
-------------------
352+
@ 2147483493 days
353+
(1 row)
354+
331355
-- Shorthand values
332356
-- Not directly usable for regression testing since these are not constants.
333357
-- So, just try to test parser and hope for the best - thomas 97/04/26

‎src/test/regress/expected/horology.out‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -328,6 +328,30 @@ SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second'
328328
Fri Dec 31 23:59:59 1999
329329
(1 row)
330330

331+
SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '1000000000 days' AS "Nov 27, 2733194";
332+
Nov 27, 2733194
333+
-----------------------------
334+
Sun Nov 27 00:00:00 2733194
335+
(1 row)
336+
337+
SELECT timestamp without time zone 'Nov 24, 4714 BC' + interval '2000000000 days' AS "Nov 30, 5471101";
338+
Nov 30, 5471101
339+
-----------------------------
340+
Sat Nov 30 00:00:00 5471101
341+
(1 row)
342+
343+
SELECT timestamp without time zone 'Nov 25, 4714 BC' + interval '2147483492 days' AS "Dec 31, 5874897";
344+
Dec 31, 5874897
345+
-----------------------------
346+
Tue Dec 31 00:00:00 5874897
347+
(1 row)
348+
349+
SELECT timestamp without time zone '12/31/5874897' - timestamp without time zone 'Nov 24, 4714 BC' AS "2147483493 Days";
350+
2147483493 Days
351+
-------------------
352+
@ 2147483493 days
353+
(1 row)
354+
331355
-- Shorthand values
332356
-- Not directly usable for regression testing since these are not constants.
333357
-- So, just try to test parser and hope for the best - thomas 97/04/26

‎src/test/regress/sql/horology.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,6 +76,10 @@ SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29
7676
SELECTtimestamp without time zone'1999-03-01'- interval'1 second'AS"Feb 28";
7777
SELECTtimestamp without time zone'2000-03-01'- interval'1 second'AS"Feb 29";
7878
SELECTtimestamp without time zone'1999-12-01'+ interval'1 month - 1 second'AS"Dec 31";
79+
SELECTtimestamp without time zone'Nov 24, 4714 BC'+ interval'1000000000 days'AS"Nov 27, 2733194";
80+
SELECTtimestamp without time zone'Nov 24, 4714 BC'+ interval'2000000000 days'AS"Nov 30, 5471101";
81+
SELECTtimestamp without time zone'Nov 25, 4714 BC'+ interval'2147483492 days'AS"Dec 31, 5874897";
82+
SELECTtimestamp without time zone'12/31/5874897'-timestamp without time zone'Nov 24, 4714 BC'AS"2147483493 Days";
7983

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp