Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Further changes to the date/time functions to suppress harmless signed integer overflow warnings that could have occurred when doing out-of-range date calculations which, according to the docs, give undefined results. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
dc453b3403450b1d8cc53daf0721fed0 |
User & Date: | drh 2016-11-30 00:48:28.495 |
Context
2016-11-30
| ||
01:05 | Prevent a warning about integer overflow when using a very large negative LIMIT. (check-in: 96106d5620 user: drh tags: trunk) | |
00:48 | Further changes to the date/time functions to suppress harmless signed integer overflow warnings that could have occurred when doing out-of-range date calculations which, according to the docs, give undefined results. (check-in: dc453b3403 user: drh tags: trunk) | |
2016-11-29
| ||
20:39 | The documentation says that the built-in date-time functions give undefined results for dates before 0000-01-01 and after 9999-12-31. Change the actually implementation so that the answer given is really NULL. This also avoids unnecessary hand-wringing over an signed integer overflow that might otherwise occur when processing out-of-bound dates. (check-in: d410a83975 user: drh tags: trunk) | |
Changes
Changes to src/date.c.
︙ | ︙ | |||
61 62 63 64 65 66 67 | #endif /* ** A structure for holding a single date and time. */ typedef struct DateTime DateTime; struct DateTime { | | | | | | | | | | | | | 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | #endif /* ** A structure for holding a single date and time. */ typedef struct DateTime DateTime; struct DateTime { sqlite3_uint64 iJD; /* The julian day number times 86400000 */ int Y, M, D; /* Year, month, and day */ int h, m; /* Hour and minutes */ int tz; /* Timezone offset in minutes */ double s; /* Seconds */ char validYMD; /* True (1) if Y,M,D are valid */ char validHMS; /* True (1) if h,m,s are valid */ char validJD; /* True (1) if iJD is valid */ char validTZ; /* True (1) if tz is valid */ char tzSet; /* Timezone was set explicitly */ char isError; /* An overflow has occurred */ }; /* ** Convert zDate into one or more integers according to the conversion ** specifier zFormat. ** |
︙ | ︙ | |||
227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 | p->h = h; p->m = m; p->s = s + ms; if( parseTimezone(zDate, p) ) return 1; p->validTZ = (p->tz!=0)?1:0; return 0; } /* ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume ** that the YYYY-MM-DD is according to the Gregorian calendar. ** ** Reference: Meeus page 61 */ static void computeJD(DateTime *p){ int Y, M, D, A, B, X1, X2; if( p->validJD ) return; if( p->validYMD ){ Y = p->Y; M = p->M; D = p->D; }else{ Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */ M = 1; D = 1; } if( M<=2 ){ Y--; M += 12; } A = Y/100; B = 2 - A + (A/4); | > > > > > > > > > > > > | 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 | p->h = h; p->m = m; p->s = s + ms; if( parseTimezone(zDate, p) ) return 1; p->validTZ = (p->tz!=0)?1:0; return 0; } /* ** Put the DateTime object into its error state. */ static void datetimeError(DateTime *p){ memset(p, 0, sizeof(*p)); p->isError = 1; } /* ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume ** that the YYYY-MM-DD is according to the Gregorian calendar. ** ** Reference: Meeus page 61 */ static void computeJD(DateTime *p){ int Y, M, D, A, B, X1, X2; if( p->validJD ) return; if( p->validYMD ){ Y = p->Y; M = p->M; D = p->D; }else{ Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */ M = 1; D = 1; } if( Y<-4713 || Y>9999 ){ datetimeError(p); return; } if( M<=2 ){ Y--; M += 12; } A = Y/100; B = 2 - A + (A/4); |
︙ | ︙ | |||
369 370 371 372 373 374 375 | /* ** Return TRUE if the given julian day number is within range. ** ** The input is the JulianDay times 86400000. */ static int validJulianDay(sqlite3_int64 iJD){ | | < | | 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 | /* ** Return TRUE if the given julian day number is within range. ** ** The input is the JulianDay times 86400000. */ static int validJulianDay(sqlite3_int64 iJD){ return iJD>=0 && iJD<=464269060799999; } /* ** Compute the Year, Month, and Day from the julian day number. */ static void computeYMD(DateTime *p){ int Z, A, B, C, D, E, X1; if( p->validYMD ) return; if( !p->validJD ){ p->Y = 2000; p->M = 1; p->D = 1; }else if( !validJulianDay(p->iJD) ){ datetimeError(p); return; }else{ Z = (int)((p->iJD + 43200000)/86400000); A = (int)((Z - 1867216.25)/36524.25); A = Z + 1 + A - (A/4); B = A + 1524; C = (int)((B - 122.1)/365.25); |
︙ | ︙ | |||
709 710 711 712 713 714 715 716 717 718 719 720 721 722 | case '4': case '5': case '6': case '7': case '8': case '9': { double rRounder; for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){} if( !sqlite3AtoF(z, &r, n, SQLITE_UTF8) ){ rc = 1; break; } if( z[n]==':' ){ /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the | > | 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 | case '4': case '5': case '6': case '7': case '8': case '9': { double rRounder; double rAbs; for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){} if( !sqlite3AtoF(z, &r, n, SQLITE_UTF8) ){ rc = 1; break; } if( z[n]==':' ){ /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the |
︙ | ︙ | |||
745 746 747 748 749 750 751 | while( sqlite3Isspace(*z) ) z++; n = sqlite3Strlen30(z); if( n>10 || n<3 ) break; if( z[n-1]=='s' ){ z[n-1] = 0; n--; } computeJD(p); rc = 0; rRounder = r<0 ? -0.5 : +0.5; | > | | | | | | | 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 | while( sqlite3Isspace(*z) ) z++; n = sqlite3Strlen30(z); if( n>10 || n<3 ) break; if( z[n-1]=='s' ){ z[n-1] = 0; n--; } computeJD(p); rc = 0; rRounder = r<0 ? -0.5 : +0.5; rAbs = r<0 ? -r : r; if( n==3 && strcmp(z,"day")==0 && rAbs<5373485.0 ){ p->iJD += (sqlite3_int64)(r*86400000.0 + rRounder); }else if( n==4 && strcmp(z,"hour")==0 && rAbs<128963628.0 ){ p->iJD += (sqlite3_int64)(r*(86400000.0/24.0) + rRounder); }else if( n==6 && strcmp(z,"minute")==0 && rAbs<7737817680.0 ){ p->iJD += (sqlite3_int64)(r*(86400000.0/(24.0*60.0)) + rRounder); }else if( n==6 && strcmp(z,"second")==0 && rAbs<464269060800.0 ){ p->iJD += (sqlite3_int64)(r*(86400000.0/(24.0*60.0*60.0)) + rRounder); }else if( n==5 && strcmp(z,"month")==0 && rAbs<176546.0 ){ int x, y; computeYMD_HMS(p); p->M += (int)r; x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; p->Y += x; p->M -= x*12; p->validJD = 0; computeJD(p); y = (int)r; if( y!=r ){ p->iJD += (sqlite3_int64)((r - y)*30.0*86400000.0 + rRounder); } }else if( n==4 && strcmp(z,"year")==0 && rAbs<14713.0 ){ int y = (int)r; computeYMD_HMS(p); p->Y += y; p->validJD = 0; computeJD(p); if( y!=r ){ p->iJD += (sqlite3_int64)((r - y)*365.0*86400000.0 + rRounder); |
︙ | ︙ |
Changes to test/date.test.
︙ | ︙ | |||
57 58 59 60 61 62 63 | datetest 1.18.3 {julianday('2000-01-01 T12:00:00')} 2451545.0 datetest 1.18.4 {julianday('2000-01-01T 12:00:00')} 2451545.0 datetest 1.18.4 {julianday('2000-01-01 T 12:00:00')} 2451545.0 datetest 1.19 {julianday('2000-01-01 12:00:00.1')} 2451545.00000116 datetest 1.20 {julianday('2000-01-01 12:00:00.01')} 2451545.00000012 datetest 1.21 {julianday('2000-01-01 12:00:00.001')} 2451545.00000001 datetest 1.22 {julianday('2000-01-01 12:00:00.')} NULL | | | 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | datetest 1.18.3 {julianday('2000-01-01 T12:00:00')} 2451545.0 datetest 1.18.4 {julianday('2000-01-01T 12:00:00')} 2451545.0 datetest 1.18.4 {julianday('2000-01-01 T 12:00:00')} 2451545.0 datetest 1.19 {julianday('2000-01-01 12:00:00.1')} 2451545.00000116 datetest 1.20 {julianday('2000-01-01 12:00:00.01')} 2451545.00000012 datetest 1.21 {julianday('2000-01-01 12:00:00.001')} 2451545.00000001 datetest 1.22 {julianday('2000-01-01 12:00:00.')} NULL datetest 1.23 julianday(12345.6) 12345.6 datetest 1.23b julianday(1721059.5) 1721059.5 datetest 1.24 {julianday('2001-01-01 12:00:00 bogus')} NULL datetest 1.25 {julianday('2001-01-01 bogus')} NULL datetest 1.26 {julianday('2001-01-01 12:60:00')} NULL datetest 1.27 {julianday('2001-01-01 12:59:60')} NULL datetest 1.28 {julianday('2001-00-01')} NULL datetest 1.29 {julianday('2001-01-00')} NULL |
︙ | ︙ | |||
413 414 415 416 417 418 419 420 421 422 423 424 425 426 | datetest 8.15 {datetime('now','1.5 months')} {2003-12-07 12:34:00} datetest 8.16 {datetime('now','-5 years')} {1998-10-22 12:34:00} datetest 8.17 {datetime('now','+10.5 minutes')} {2003-10-22 12:44:30} datetest 8.18 {datetime('now','-1.25 hours')} {2003-10-22 11:19:00} datetest 8.19 {datetime('now','11.25 seconds')} {2003-10-22 12:34:11} datetest 8.90 {datetime('now','abcdefghijklmnopqrstuvwyxzABCDEFGHIJLMNOP')} NULL set sqlite_current_time 0 # datetime() with just an HH:MM:SS correctly inserts the date 2000-01-01. # datetest 10.1 {datetime('01:02:03')} {2000-01-01 01:02:03} datetest 10.2 {date('01:02:03')} {2000-01-01} datetest 10.3 {strftime('%Y-%m-%d %H:%M','01:02:03')} {2000-01-01 01:02} | > > > > > > > > > > | 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 | datetest 8.15 {datetime('now','1.5 months')} {2003-12-07 12:34:00} datetest 8.16 {datetime('now','-5 years')} {1998-10-22 12:34:00} datetest 8.17 {datetime('now','+10.5 minutes')} {2003-10-22 12:44:30} datetest 8.18 {datetime('now','-1.25 hours')} {2003-10-22 11:19:00} datetest 8.19 {datetime('now','11.25 seconds')} {2003-10-22 12:34:11} datetest 8.90 {datetime('now','abcdefghijklmnopqrstuvwyxzABCDEFGHIJLMNOP')} NULL set sqlite_current_time 0 # Negative years work. Example: '-4713-11-26' is JD 1.5. # datetest 9.1 {julianday('-4713-11-24 12:00:00')} {0.0} datetest 9.2 {julianday(datetime(5))} {5.0} datetest 9.3 {julianday(datetime(10))} {10.0} datetest 9.4 {julianday(datetime(100))} {100.0} datetest 9.5 {julianday(datetime(1000))} {1000.0} datetest 9.6 {julianday(datetime(10000))} {10000.0} datetest 9.7 {julianday(datetime(100000))} {100000.0} # datetime() with just an HH:MM:SS correctly inserts the date 2000-01-01. # datetest 10.1 {datetime('01:02:03')} {2000-01-01 01:02:03} datetest 10.2 {date('01:02:03')} {2000-01-01} datetest 10.3 {strftime('%Y-%m-%d %H:%M','01:02:03')} {2000-01-01 01:02} |
︙ | ︙ | |||
545 546 547 548 549 550 551 552 553 | } {0.0} do_test date-15.2 { db eval { SELECT a==b FROM (SELECT current_timestamp AS a, sleeper(), current_timestamp AS b); } } {1} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 | } {0.0} do_test date-15.2 { db eval { SELECT a==b FROM (SELECT current_timestamp AS a, sleeper(), current_timestamp AS b); } } {1} # Tests of extreme values in date/time functions. Run with UBSan or the # equivalent to verify no signed interger overflow warnings. # datetest 16.1 {date(147483649)} NULL datetest 16.2 {datetime(0)} {-4713-11-24 12:00:00} datetest 16.3 {datetime(5373484.49999999)} {9999-12-31 23:59:59} datetest 16.4 {julianday('-4713-11-24 12:00:00')} 0.0 datetest 16.5 {julianday('9999-12-31 23:59:59.999')} 5373484.49999999 datetest 16.6 {datetime(0,'+464269060799 seconds')} {9999-12-31 23:59:59} datetest 16.7 {datetime(0,'+464269060800 seconds')} NULL datetest 16.8 {datetime(0,'+7737817679 minutes')} {9999-12-31 23:59:00} datetest 16.9 {datetime(0,'+7737817680 minutes')} NULL datetest 16.10 {datetime(0,'+128963627 hours')} {9999-12-31 23:00:00} datetest 16.11 {datetime(0,'+128963628 hours')} NULL datetest 16.12 {datetime(0,'+5373484 days')} {9999-12-31 12:00:00} datetest 16.13 {datetime(0,'+5373485 days')} NULL datetest 16.14 {datetime(0,'+176545 months')} {9999-12-24 12:00:00} datetest 16.15 {datetime(0,'+176546 months')} NULL datetest 16.16 {datetime(0,'+14712 years')} {9999-11-24 12:00:00} datetest 16.17 {datetime(0,'+14713 years')} NULL datetest 16.20 {datetime(5373484.4999999,'-464269060799 seconds')} \ {-4713-11-24 12:00:00} datetest 16.21 {datetime(5373484,'-464269060800 seconds')} NULL datetest 16.22 {datetime(5373484.4999999,'-7737817679 minutes')} \ {-4713-11-24 12:00:59} datetest 16.23 {datetime(5373484,'-7737817680 minutes')} NULL datetest 16.24 {datetime(5373484.4999999,'-128963627 hours')} \ {-4713-11-24 12:59:59} datetest 16.25 {datetime(5373484,'-128963628 hours')} NULL datetest 16.26 {datetime(5373484,'-5373484 days')} {-4713-11-24 12:00:00} datetest 16.27 {datetime(5373484,'-5373485 days')} NULL datetest 16.28 {datetime(5373484,'-176545 months')} {-4713-12-01 12:00:00} datetest 16.29 {datetime(5373484,'-176546 months')} NULL datetest 16.30 {datetime(5373484,'-14712 years')} {-4713-12-31 12:00:00} datetest 16.31 {datetime(5373484,'-14713 years')} NULL finish_test |