From eadccaa9269560e567b4ae0440de6e24745e6142 Mon Sep 17 00:00:00 2001 From: drh <> Date: Thu, 10 Feb 2022 15:40:40 +0000 Subject: [PATCH] Performance improve to the 'localtime' and 'utc' modifiers for date/time functions. FossilOrigin-Name: 85cb6014751a0572d28ebd839331d5d7a78de45c9e522adcd834a8a85746f32e --- manifest | 14 +++---- manifest.uuid | 2 +- src/date.c | 112 ++++++++++++++++++++++++------------------------- test/date.test | 14 +++---- 4 files changed, 71 insertions(+), 71 deletions(-) diff --git a/manifest b/manifest index a69fef3bd2..36eecc3606 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Faster\simplementation\sof\sthe\sdate(),\stime(),\sand\sdatetime()\sfunctions. -D 2022-02-10T12:57:53.736 +C Performance\simprove\sto\sthe\s'localtime'\sand\s'utc'\smodifiers\sfor\sdate/time\nfunctions. +D 2022-02-10T15:40:40.522 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -499,7 +499,7 @@ F src/build.c b59ff41525c10b429adc277d3bca6e433b09d055b0df8c1529385763cea8bb04 F src/callback.c 4c19af69835787bfe790ac560f3071a824eb629f34e41f97b52ce5235c77de1c F src/complete.c a3634ab1e687055cd002e11b8f43eb75c17da23e F src/ctime.c 2cce39df1a13e05b7633e6d21b651f21492471f991dd7b323a4ee4e7b7f0b7f1 -F src/date.c c9275705b097fe875ad4d194ed20e0af115f6d50058038e03ac8f8e417ee06eb +F src/date.c 9d865dac4a796035a8cfb027c7c8450bc7ec54f8b51ebedbc5b8369e032e1cb9 F src/dbpage.c 8a01e865bf8bc6d7b1844b4314443a6436c07c3efe1d488ed89e81719047833a F src/dbstat.c 861e08690fcb0f2ee1165eff0060ea8d4f3e2ea10f80dab7d32ad70443a6ff2d F src/delete.c b5f1716b4d723db48254ee0f896e362cd029e865e05414139ea7f539f3884e1d @@ -837,7 +837,7 @@ F test/ctime.test 340f362f41f92972bbd71f44e10569a5cc694062b692231bd08aa6fe6c1c47 F test/cursorhint.test 0175e4404181ace3ceca8b114eb0a98eae600d565aa4e2705abbe6614c7fe201 F test/cursorhint2.test 6f3aa9cb19e7418967a10ec6905209bcbb5968054da855fc36c8beee9ae9c42f F test/dataversion1.test 6e5e86ac681f0782e766ebcb56c019ae001522d114e0e111e5ebf68ccf2a7bb8 -F test/date.test 9b73bbeb1b82d9c1f44dec5cf563bf7da58d2373 +F test/date.test 7e1da08fca5492081605a2bf445370b06be6a95db1150f3133b70eb672b33cb3 F test/date2.test 7e12ec14aaf4d5e6294b4ba140445b0eca06ea50062a9c3a69c4ee13d0b6f8b1 F test/date3.test a1b77abf05c6772fe5ca2337cac1398892f2a41e62bce7e6be0f4a08a0e64ae5 F test/dbdata.test 042f49acff3438f940eeba5868d3af080ae64ddf26ae78f80c92bec3ca7d8603 @@ -1944,8 +1944,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 4565f711291bae5daaff6dd2b7b2991b17e139a576b1b73bb1f81f08a4c1a13f -R 83b35984d6e76589cbc31defad98bf4a +P fa1b393bdb66b985f6552190a8242ed878f91d653a03352f65aa8d750de3cec4 +R 67dc667c505ca83a0a8211aab2594ab6 U drh -Z f2060761086c3825d641957e607d18e2 +Z f3fee6ce5a9f7a29390c33b813554b1e # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index f9285f75ac..6a69ddd7b7 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -fa1b393bdb66b985f6552190a8242ed878f91d653a03352f65aa8d750de3cec4 \ No newline at end of file +85cb6014751a0572d28ebd839331d5d7a78de45c9e522adcd834a8a85746f32e \ No newline at end of file diff --git a/src/date.c b/src/date.c index 2001d50ab4..3225a5528d 100644 --- a/src/date.c +++ b/src/date.c @@ -544,67 +544,56 @@ static int osLocaltime(time_t *t, struct tm *pTm){ #ifndef SQLITE_OMIT_LOCALTIME /* -** Compute the difference (in milliseconds) between localtime and UTC -** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs, -** return this value and set *pRc to SQLITE_OK. -** -** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value -** is undefined in this case. +** Assuming the input DateTime is UTC, move it to its localtime equivalent. */ -static sqlite3_int64 localtimeOffset( - DateTime *p, /* Date at which to calculate offset */ - sqlite3_context *pCtx, /* Write error here if one occurs */ - int *pRc /* OUT: Error code. SQLITE_OK or ERROR */ +static int toLocaltime( + DateTime *p, /* Date at which to calculate offset */ + sqlite3_context *pCtx /* Write error here if one occurs */ ){ - DateTime x, y; time_t t; struct tm sLocal; + int iYearDiff; /* Initialize the contents of sLocal to avoid a compiler warning. */ memset(&sLocal, 0, sizeof(sLocal)); - x = *p; - computeYMD_HMS(&x); - if( x.Y<1971 || x.Y>=2038 ){ + computeJD(p); + if( p->iJD<21086676000*(i64)10000 /* 1970-01-01 */ + || p->iJD>21301414560*(i64)10000 /* 2038-01-18 */ + ){ /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only ** works for years between 1970 and 2037. For dates outside this range, ** SQLite attempts to map the year into an equivalent year within this ** range, do the calculation, then map the year back. */ - x.Y = 2000; - x.M = 1; - x.D = 1; - x.h = 0; - x.m = 0; - x.s = 0.0; - } else { - int s = (int)(x.s + 0.5); - x.s = s; + DateTime x = *p; + computeYMD_HMS(&x); + iYearDiff = (2000 + x.Y%4) - x.Y; + x.Y += iYearDiff; + x.validJD = 0; + computeJD(&x); + t = (time_t)(x.iJD/1000 - 21086676*(i64)10000); + }else{ + iYearDiff = 0; + t = (time_t)(p->iJD/1000 - 21086676*(i64)10000); } - x.tz = 0; - x.validJD = 0; - computeJD(&x); - t = (time_t)(x.iJD/1000 - 21086676*(i64)10000); if( osLocaltime(&t, &sLocal) ){ sqlite3_result_error(pCtx, "local time unavailable", -1); - *pRc = SQLITE_ERROR; - return 0; + return SQLITE_ERROR; } - y.Y = sLocal.tm_year + 1900; - y.M = sLocal.tm_mon + 1; - y.D = sLocal.tm_mday; - y.h = sLocal.tm_hour; - y.m = sLocal.tm_min; - y.s = sLocal.tm_sec; - y.validYMD = 1; - y.validHMS = 1; - y.validJD = 0; - y.rawS = 0; - y.validTZ = 0; - y.isError = 0; - computeJD(&y); - *pRc = SQLITE_OK; - return y.iJD - x.iJD; + p->Y = sLocal.tm_year + 1900 - iYearDiff; + p->M = sLocal.tm_mon + 1; + p->D = sLocal.tm_mday; + p->h = sLocal.tm_hour; + p->m = sLocal.tm_min; + p->s = sLocal.tm_sec; + p->validYMD = 1; + p->validHMS = 1; + p->validJD = 0; + p->rawS = 0; + p->validTZ = 0; + p->isError = 0; + return SQLITE_OK; } #endif /* SQLITE_OMIT_LOCALTIME */ @@ -713,9 +702,7 @@ static int parseModifier( ** show local time. */ if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){ - computeJD(p); - p->iJD += localtimeOffset(p, pCtx, &rc); - clearYMD_HMS_TZ(p); + rc = toLocaltime(p, pCtx); } break; } @@ -741,18 +728,31 @@ static int parseModifier( #ifndef SQLITE_OMIT_LOCALTIME else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){ if( p->tzSet==0 ){ - sqlite3_int64 c1; + i64 iOrigJD; /* Original localtime */ + i64 iGuess; /* Guess at the corresponding utc time */ + int cnt = 0; /* Safety to prevent infinite loop */ + int iErr; /* Guess is off by this much */ + computeJD(p); - c1 = localtimeOffset(p, pCtx, &rc); - if( rc==SQLITE_OK ){ - p->iJD -= c1; - clearYMD_HMS_TZ(p); - p->iJD += c1 - localtimeOffset(p, pCtx, &rc); - } + iGuess = iOrigJD = p->iJD; + iErr = 0; + do{ + DateTime new; + memset(&new, 0, sizeof(new)); + iGuess -= iErr; + new.iJD = iGuess; + new.validJD = 1; + rc = toLocaltime(&new, pCtx); + if( rc ) return rc; + computeJD(&new); + iErr = new.iJD - iOrigJD; + }while( iErr && cnt++<3 ); + memset(p, 0, sizeof(*p)); + p->iJD = iGuess; + p->validJD = 1; p->tzSet = 1; - }else{ - rc = SQLITE_OK; } + rc = SQLITE_OK; } #endif break; diff --git a/test/date.test b/test/date.test index f002334d17..2e5859ad29 100644 --- a/test/date.test +++ b/test/date.test @@ -332,9 +332,9 @@ if {$tzoffset_new==4} { } datetest 6.7.2 {datetime('2007-03-11 01:59:00','utc')} {2007-03-11 06:59:00} - datetest 6.8 {datetime('2000-04-02 02:00:00','utc')} {2000-04-02 06:00:00} - datetest 6.8.1 {datetime('2006-04-02 02:00:00','utc')} {2006-04-02 06:00:00} - datetest 6.8.2 {datetime('2007-03-11 02:00:00','utc')} {2007-03-11 06:00:00} + datetest 6.8 {datetime('2000-04-02 02:00:00','utc')} {2000-04-02 07:00:00} + datetest 6.8.1 {datetime('2006-04-02 02:00:00','utc')} {2006-04-02 07:00:00} + datetest 6.8.2 {datetime('2007-03-11 02:00:00','utc')} {2007-03-11 07:00:00} # The 'utc' modifier is a no-op if the LHS is known to already be in UTC datetest 6.9.1 {datetime('2015-12-23 12:00:00','utc')} {2015-12-23 17:00:00} @@ -353,10 +353,10 @@ if {$tzoffset_new==4} { {2039-01-01 07:00:00} datetest 6.13 {datetime('2000-07-01 12:00:00','localtime')} \ {2000-07-01 08:00:00} - datetest 6.14 {datetime('1969-07-01 12:00:00','localtime')} \ - {1969-07-01 07:00:00} - datetest 6.15 {datetime('2039-07-01 12:00:00','localtime')} \ - {2039-07-01 07:00:00} + datetest 6.14 {datetime('1969-11-01 12:00:00','localtime')} \ + {1969-11-01 07:00:00} + datetest 6.15 {datetime('2039-02-01 12:00:00','localtime')} \ + {2039-02-01 07:00:00} set sqlite_current_time \ [db eval {SELECT strftime('%s','2000-07-01 12:34:56')}] datetest 6.16 {datetime('now','localtime')} {2000-07-01 08:34:56} -- 2.47.2