From b75af4b779520f119e5328ee1038efa2d58f52ae Mon Sep 17 00:00:00 2001 From: drh Date: Wed, 23 Apr 2014 12:00:20 +0000 Subject: [PATCH] Enhancements to the date and time functions so that they optionally support a 5-digit year. Not sure we want to include this in trunk. Saved in a branch for future reference. FossilOrigin-Name: 2029a396dcdb928f79780ec80c808fda6e3285a1 --- manifest | 15 ++++++---- manifest.uuid | 2 +- src/date.c | 77 ++++++++++++++++++++++++++++++++++----------------- 3 files changed, 61 insertions(+), 33 deletions(-) diff --git a/manifest b/manifest index 7f4e9189fd..0ce81f42b0 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Comment\stweaks\son\sthe\stest\scase\sfor\sthe\s[b75a9ca6b0]\sbug\sfix. -D 2014-04-21T13:36:54.639 +C Enhancements\sto\sthe\sdate\sand\stime\sfunctions\sso\sthat\sthey\soptionally\ssupport\na\s5-digit\syear.\s\sNot\ssure\swe\swant\sto\sinclude\sthis\sin\strunk.\s\sSaved\sin\sa\sbranch\nfor\sfuture\sreference. +D 2014-04-23T12:00:20.142 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 2ef13430cd359f7b361bb863504e227b25cc7f81 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -171,7 +171,7 @@ F src/build.c 5bfeea8f302ec2926c9eea321a61daea92a29fa4 F src/callback.c 174e3c8656bc29f91d710ab61550d16eea34be98 F src/complete.c dc1d136c0feee03c2f7550bafc0d29075e36deac F src/ctime.c 0231df905e2c4abba4483ee18ffc05adc321df2a -F src/date.c 593c744b2623971e45affd0bde347631bdfa4625 +F src/date.c 483fd9eeb74e06c2ddc4b33ade4a8a9611230386 F src/delete.c bcf8f72126cea80fc3d5bc5494cf19b3f8935aaf F src/expr.c 4f9e497c66e2f25a4d139357a778c84d5713207c F src/fault.c 160a0c015b6c2629d3899ed2daf63d75754a32bb @@ -1161,7 +1161,10 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh d1a6de74685f360ab718efda6265994b99bbea01 F tool/win/sqlite.vsix 030f3eeaf2cb811a3692ab9c14d021a75ce41fff -P de9a490f594183f337a2ec9e0f87792eac83548b -R ce888b84132e0cad3bcca115a32951d3 +P 65d2544af9adc1e2f1d193e57f8be0422fb0d5eb +R 65a7ac109a1878f6dcc92cd296f64cbc +T *branch * five-digit-year +T *sym-five-digit-year * +T -sym-trunk * U drh -Z cf9f241149456ab1fa24984e95a412d2 +Z 20a358e6aab334ebda712a29750f60b8 diff --git a/manifest.uuid b/manifest.uuid index a2761a597b..6bf6ded2fc 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -65d2544af9adc1e2f1d193e57f8be0422fb0d5eb \ No newline at end of file +2029a396dcdb928f79780ec80c808fda6e3285a1 \ No newline at end of file diff --git a/src/date.c b/src/date.c index f8f4ee0a6b..494fd3432d 100644 --- a/src/date.c +++ b/src/date.c @@ -229,7 +229,7 @@ static void computeJD(DateTime *p){ } A = Y/100; B = 2 - A + (A/4); - X1 = 36525*(Y+4716)/100; + X1 = 365*(Y+4716) + 25*(Y+4716)/100; X2 = 306001*(M+1)/10000; p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000); p->validJD = 1; @@ -258,18 +258,23 @@ static void computeJD(DateTime *p){ */ static int parseYyyyMmDd(const char *zDate, DateTime *p){ int Y, M, D, neg; + char c; if( zDate[0]=='-' ){ zDate++; neg = 1; }else{ neg = 0; + if( zDate[0]=='+' ) zDate++; } - if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){ + if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)==3 ){ + zDate += 10; + }else if( getDigits(zDate,5,0,99999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)==3 ){ + zDate += 11; + }else{ return 1; } - zDate += 10; - while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; } + while( (c = zDate[0])=='T' || c=='t' || sqlite3Isspace(c) ){ zDate++; } if( parseHhMmSs(zDate, p)==0 ){ /* We got the time */ }else if( *zDate==0 ){ @@ -355,7 +360,7 @@ static void computeYMD(DateTime *p){ A = Z + 1 + A - (A/4); B = A + 1524; C = (int)((B - 122.1)/365.25); - D = (36525*C)/100; + D = 365*C + (25*C)/100; E = (int)((B-D)/30.6001); X1 = (int)(30.6001*E); p->D = B - D - X1; @@ -577,19 +582,8 @@ static int parseModifier(sqlite3_context *pCtx, const char *zMod, DateTime *p){ } #endif case 'u': { - /* - ** unixepoch - ** - ** Treat the current value of p->iJD as the number of - ** seconds since 1970. Convert to a real julian day number. - */ - if( strcmp(z, "unixepoch")==0 && p->validJD ){ - p->iJD = (p->iJD + 43200)/86400 + 21086676*(i64)10000000; - clearYMD_HMS_TZ(p); - rc = 0; - } #ifndef SQLITE_OMIT_LOCALTIME - else if( strcmp(z, "utc")==0 ){ + if( strcmp(z, "utc")==0 ){ sqlite3_int64 c1; computeJD(p); c1 = localtimeOffset(p, pCtx, &rc); @@ -761,7 +755,7 @@ static int isDate( sqlite3_value **argv, DateTime *p ){ - int i; + int i = 1; const unsigned char *z; int eType; memset(p, 0, sizeof(*p)); @@ -770,7 +764,14 @@ static int isDate( } if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT || eType==SQLITE_INTEGER ){ - p->iJD = (sqlite3_int64)(sqlite3_value_double(argv[0])*86400000.0 + 0.5); + if( argc>=2 && sqlite3_value_type(argv[1])==SQLITE_TEXT + && strcmp((const char*)sqlite3_value_text(argv[1]),"unixepoch")==0 ){ + i = 2; + p->iJD = (sqlite3_int64)(sqlite3_value_double(argv[0])*1000.0) + + 21086676*(i64)10000000; + }else{ + p->iJD = (sqlite3_int64)(sqlite3_value_double(argv[0])*86400000.0 + 0.5); + } p->validJD = 1; }else{ z = sqlite3_value_text(argv[0]); @@ -778,8 +779,8 @@ static int isDate( return 1; } } - for(i=1; i=10000 ? "%+05d" : "%04d", Y); + return sqlite3Strlen30(zBuf); +} + /* ** datetime( TIMESTRING, MOD, MOD, ...) ** -** Return YYYY-MM-DD HH:MM:SS +** Return YYYY-MM-DD HH:MM:SS. The YYYY might be +YYYYY or -YYYY or -YYYYY +** depending on the year. */ static void datetimeFunc( sqlite3_context *context, @@ -820,10 +838,12 @@ static void datetimeFunc( ){ DateTime x; if( isDate(context, argc, argv, &x)==0 ){ + int i; char zBuf[100]; computeYMD_HMS(&x); - sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d", - x.Y, x.M, x.D, x.h, x.m, (int)(x.s)); + i = renderYear(zBuf, x.Y); + sqlite3_snprintf(sizeof(zBuf)-i, zBuf+i, "-%02d-%02d %02d:%02d:%02d", + x.M, x.D, x.h, x.m, (int)(x.s)); sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); } } @@ -850,7 +870,10 @@ static void timeFunc( /* ** date( TIMESTRING, MOD, MOD, ...) ** -** Return YYYY-MM-DD +** Return YYYY-MM-DD +** or +YYYYY-MM-DD +** or -YYYY-MM-DD +** or -YYYYY-MM-DD */ static void dateFunc( sqlite3_context *context, @@ -859,9 +882,11 @@ static void dateFunc( ){ DateTime x; if( isDate(context, argc, argv, &x)==0 ){ + int i; char zBuf[100]; computeYMD(&x); - sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D); + i = renderYear(zBuf, x.Y); + sqlite3_snprintf(sizeof(zBuf)-i, zBuf+i, "-%02d-%02d", x.M, x.D); sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); } } -- 2.39.5