Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | 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. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
d410a839752153c6d8be08f758abfbc1 |
User & Date: | drh 2016-11-29 20:39:48.413 |
Context
2016-11-30
| ||
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) | |
2016-11-28
| ||
21:22 | Fix broken hyperlinks to the sqlite3_snapshot objection definition in the documentation. No changes to code. (check-in: 9021f6875f user: drh tags: trunk) | |
Changes
Changes to src/date.c.
︙ | ︙ | |||
71 72 73 74 75 76 77 78 79 80 81 82 83 84 | 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 */ }; /* ** Convert zDate into one or more integers according to the conversion ** specifier zFormat. ** | > | 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | 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. ** |
︙ | ︙ | |||
361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 | }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){ p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5); p->validJD = 1; return 0; } return 1; } /* ** 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{ 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); D = (36525*(C&32767))/100; | > > > > > > > > > > > > > | 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 | }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){ p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5); p->validJD = 1; return 0; } return 1; } /* ** 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>=148699540800000 && 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) ){ memset(p, 0, sizeof(*p)); p->isError = 1; 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); D = (36525*(C&32767))/100; |
︙ | ︙ | |||
810 811 812 813 814 815 816 817 818 819 820 821 822 823 | return 1; } } for(i=1; i<argc; i++){ z = sqlite3_value_text(argv[i]); if( z==0 || parseModifier(context, (char*)z, p) ) return 1; } return 0; } /* ** The following routines implement the various date and time functions ** of SQLite. | > | 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 | return 1; } } for(i=1; i<argc; i++){ z = sqlite3_value_text(argv[i]); if( z==0 || parseModifier(context, (char*)z, p) ) return 1; } if( p->isError || (p->validJD && !validJulianDay(p->iJD)) ) return 1; return 0; } /* ** The following routines implement the various date and time functions ** of SQLite. |
︙ | ︙ |
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 72 | 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) NULL 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 |
︙ | ︙ | |||
414 415 416 417 418 419 420 | 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 | < < < < < < < < < < | 414 415 416 417 418 419 420 421 422 423 424 425 426 427 | 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} # Test the new HH:MM:SS modifier |
︙ | ︙ |