Index: src/date.c ================================================================== --- src/date.c +++ src/date.c @@ -54,19 +54,19 @@ /* ** A structure for holding a single date and time. */ typedef struct DateTime DateTime; struct DateTime { - sqlite4_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 */ + sqlite4_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 */ + sqlite4_num 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 */ }; /* ** Convert zDate into one or more integers. Additional arguments @@ -168,11 +168,11 @@ ** ** Return 1 if there is a parsing error and 0 on success. */ static int parseHhMmSs(const char *zDate, DateTime *p){ int h, m, s; - double ms = 0.0; + sqlite4_num ms = {0,0,0,0}; if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){ return 1; } zDate += 5; if( *zDate==':' ){ @@ -179,28 +179,33 @@ zDate++; if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){ return 1; } zDate += 2; - if( *zDate=='.' && sqlite4Isdigit(zDate[1]) ){ - double rScale = 1.0; - zDate++; - while( sqlite4Isdigit(*zDate) ){ - ms = ms*10.0 + *zDate - '0'; - rScale *= 10.0; - zDate++; + + if( *zDate=='.' ){ + int iMs = 0; + int nDigit = 1; + while( sqlite4Isdigit(zDate[nDigit]) ){ + iMs = iMs * 10 + (zDate[nDigit] - '0'); + nDigit++; } - ms /= rScale; + if( nDigit>1 ){ + ms = sqlite4_num_from_int64(iMs); + assert( ms.e==0 ); + ms.e += (1-nDigit); + zDate += nDigit; + } } }else{ s = 0; } p->validJD = 0; p->validHMS = 1; p->h = h; p->m = m; - p->s = s + ms; + p->s = sqlite4_num_add(sqlite4_num_from_int64(s), ms); if( parseTimezone(zDate, p) ) return 1; p->validTZ = (p->tz!=0)?1:0; return 0; } @@ -232,11 +237,14 @@ X1 = 36525*(Y+4716)/100; X2 = 306001*(M+1)/10000; p->iJD = (sqlite4_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000); p->validJD = 1; if( p->validHMS ){ - p->iJD += p->h*3600000 + p->m*60000 + (sqlite4_int64)(p->s*1000); + p->iJD += p->h*3600000 + p->m*60000; + p->iJD += sqlite4_num_to_int64( + sqlite4_num_mul(p->s, sqlite4_num_from_int64(1000)), 0 + ); if( p->validTZ ){ p->iJD -= p->tz*60000; p->validYMD = 0; p->validHMS = 0; p->validTZ = 0; @@ -321,21 +329,28 @@ static int parseDateOrTime( sqlite4_context *context, const char *zDate, DateTime *p ){ - double r; if( parseYyyyMmDd(zDate,p)==0 ){ return 0; }else if( parseHhMmSs(zDate, p)==0 ){ return 0; }else if( sqlite4_stricmp(zDate,"now")==0){ return setDateTimeToCurrent(context, p); - }else if( sqlite4AtoF(zDate, &r, sqlite4Strlen30(zDate), SQLITE4_UTF8) ){ - p->iJD = (sqlite4_int64)(r*86400000.0 + 0.5); - p->validJD = 1; - return 0; + }else{ + sqlite4_num num; + num = sqlite4_num_from_text(zDate, -1, SQLITE4_IGNORE_WHITESPACE, 0); + if( sqlite4_num_isnan(num)==0 ){ + static const sqlite4_num one_half = {0, 0, -1, 5}; + + num = sqlite4_num_mul(num, sqlite4_num_from_int64(86400000)); + num = sqlite4_num_add(num, one_half); + p->iJD = sqlite4_num_to_int64(num, 0); + p->validJD = 1; + return 0; + } } return 1; } /* @@ -370,17 +385,19 @@ static void computeHMS(DateTime *p){ int s; if( p->validHMS ) return; computeJD(p); s = (int)((p->iJD + 43200000) % 86400000); - p->s = s/1000.0; - s = (int)p->s; - p->s -= s; + p->s = sqlite4_num_div( + sqlite4_num_from_int64(s), sqlite4_num_from_int64(1000) + ); + s = (int)sqlite4_num_to_int64(p->s, 0); + p->s = sqlite4_num_sub(p->s, sqlite4_num_from_int64(s)); p->h = s/3600; s -= p->h*3600; p->m = s/60; - p->s += s - p->m*60; + p->s = sqlite4_num_add(p->s, sqlite4_num_from_int64(s - p->m*60)); p->validHMS = 1; } /* ** Compute both YMD and HMS @@ -513,10 +530,20 @@ computeJD(&y); *pRc = SQLITE4_OK; return y.iJD - x.iJD; } #endif /* SQLITE4_OMIT_LOCALTIME */ + +static sqlite4_int64 multiplyAndRound(sqlite4_num a, i64 b){ + static const sqlite4_num aRnd[2] = { {0, 0, -1, 5}, {1, 0, -1, 5} }; + sqlite4_num res; + + res = sqlite4_num_mul(a, sqlite4_num_from_int64(b)); + assert( res.sign==0 || res.sign==1 ); + res = sqlite4_num_add(res, aRnd[res.sign]); + return sqlite4_num_to_int64(res, 0); +} /* ** Process a modifier to a date-time stamp. The modifiers are ** as follows: ** @@ -538,14 +565,17 @@ ** Return 0 on success and 1 if there is any kind of error. If the error ** is in a system call (i.e. localtime()), then an error message is written ** to context pCtx. If the error is an unrecognized modifier, no error is ** written to pCtx. */ -static int parseModifier(sqlite4_context *pCtx, const char *zMod, DateTime *p){ +static int parseModifier( + sqlite4_context *pCtx, /* Leave error message here */ + const char *zMod, /* date-time modifier */ + DateTime *p /* Update the value this points to */ +){ int rc = 1; int n; - double r; char *z, zBuf[30]; z = zBuf; for(n=0; n=0 && r<7 ){ + if( strncmp(z, "weekday ", 8)==0 ){ + int bLossy; + int iWeekday; sqlite4_int64 Z; + sqlite4_num w; + int n; + + for(n=8; sqlite4Isspace(z[n]); n++); + if( z[n]==0 ) break; + w = sqlite4_num_from_text(&z[8], -1, SQLITE4_IGNORE_WHITESPACE, 0); + if( sqlite4_num_isnan(w) ) break; + iWeekday = (int)sqlite4_num_to_int64(w, &bLossy); + if( bLossy || iWeekday<0 || iWeekday>6 ) break; + computeYMD_HMS(p); p->validTZ = 0; p->validJD = 0; computeJD(p); Z = ((p->iJD + 129600000)/86400000) % 7; - if( Z>n ) Z -= 7; - p->iJD += (n - Z)*86400000; + if( Z>iWeekday ) Z -= 7; + p->iJD += (iWeekday - Z)*86400000; clearYMD_HMS_TZ(p); rc = 0; } break; } @@ -626,11 +666,11 @@ if( strncmp(z, "start of ", 9)!=0 ) break; z += 9; computeYMD(p); p->validHMS = 1; p->h = p->m = 0; - p->s = 0.0; + memset(&p->s, 0, sizeof(sqlite4_num)); p->validTZ = 0; p->validJD = 0; if( strcmp(z,"month")==0 ){ p->D = 1; rc = 0; @@ -654,16 +694,19 @@ case '5': case '6': case '7': case '8': case '9': { - double rRounder; + sqlite4_num num; + for(n=1; z[n] && z[n]!=':' && !sqlite4Isspace(z[n]); n++){} - if( !sqlite4AtoF(z, &r, n, SQLITE4_UTF8) ){ + num = sqlite4_num_from_text(z, n, SQLITE4_IGNORE_WHITESPACE, 0); + if( sqlite4_num_isnan(num) ){ rc = 1; break; } + if( z[n]==':' ){ /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the ** specified number of hours, minutes, seconds, and fractional seconds ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be ** omitted. @@ -683,47 +726,54 @@ clearYMD_HMS_TZ(p); p->iJD += tx.iJD; rc = 0; break; } + z += n; while( sqlite4Isspace(*z) ) z++; n = sqlite4Strlen30(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; + if( n==3 && strcmp(z,"day")==0 ){ - p->iJD += (sqlite4_int64)(r*86400000.0 + rRounder); + p->iJD += multiplyAndRound(num, 86400000); }else if( n==4 && strcmp(z,"hour")==0 ){ - p->iJD += (sqlite4_int64)(r*(86400000.0/24.0) + rRounder); + p->iJD += multiplyAndRound(num, 86400000 / 24); }else if( n==6 && strcmp(z,"minute")==0 ){ - p->iJD += (sqlite4_int64)(r*(86400000.0/(24.0*60.0)) + rRounder); + p->iJD += multiplyAndRound(num, 86400000 / (24 * 60)); }else if( n==6 && strcmp(z,"second")==0 ){ - p->iJD += (sqlite4_int64)(r*(86400000.0/(24.0*60.0*60.0)) + rRounder); + p->iJD += multiplyAndRound(num, 86400000 / (24 * 60 * 60)); }else if( n==5 && strcmp(z,"month")==0 ){ - int x, y; + int bLossy; + int nMonth; + int x; + nMonth = sqlite4_num_to_int64(num, &bLossy); computeYMD_HMS(p); - p->M += (int)r; + p->M += nMonth; 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 += (sqlite4_int64)((r - y)*30.0*86400000.0 + rRounder); + if( bLossy ){ + num = sqlite4_num_sub(num, sqlite4_num_from_int64(nMonth)); + p->iJD += multiplyAndRound(num, (i64)30*86400000); } }else if( n==4 && strcmp(z,"year")==0 ){ - int y = (int)r; + int bLossy; + int nYear; + nYear = sqlite4_num_to_int64(num, &bLossy); computeYMD_HMS(p); - p->Y += y; + p->Y += nYear; p->validJD = 0; computeJD(p); - if( y!=r ){ - p->iJD += (sqlite4_int64)((r - y)*365.0*86400000.0 + rRounder); + if( bLossy ){ + num = sqlite4_num_sub(num, sqlite4_num_from_int64(nYear)); + p->iJD += multiplyAndRound(num, (i64)365*86400000); } }else{ rc = 1; } clearYMD_HMS_TZ(p); @@ -749,20 +799,23 @@ sqlite4_context *context, int argc, sqlite4_value **argv, DateTime *p ){ + static const sqlite4_num ms_per_day = {0, 0, 0, 86400000}; + int i; const char *z; int eType; memset(p, 0, sizeof(*p)); if( argc==0 ){ return setDateTimeToCurrent(context, p); } - if( (eType = sqlite4_value_type(argv[0]))==SQLITE4_FLOAT - || eType==SQLITE4_INTEGER ){ - p->iJD = (sqlite4_int64)(sqlite4_value_double(argv[0])*86400000.0 + 0.5); + eType = sqlite4_value_type(argv[0]); + if( eType==SQLITE4_FLOAT || eType==SQLITE4_INTEGER ){ + sqlite4_num jd = sqlite4_num_mul(sqlite4_value_num(argv[0]), ms_per_day); + p->iJD = sqlite4_num_to_int64(sqlite4_num_round(jd, 0), 0); p->validJD = 1; }else{ z = sqlite4_value_text(argv[0], 0); if( !z || parseDateOrTime(context, z, p) ){ return 1; @@ -789,14 +842,18 @@ static void juliandayFunc( sqlite4_context *context, int argc, sqlite4_value **argv ){ + static const sqlite4_num ms_per_day = {0, 0, 0, 86400000}; DateTime x; + if( isDate(context, argc, argv, &x)==0 ){ computeJD(&x); - sqlite4_result_double(context, x.iJD/86400000.0); + sqlite4_result_num(context, + sqlite4_num_div(sqlite4_num_from_int64(x.iJD), ms_per_day) + ); } } /* ** datetime( TIMESTRING, MOD, MOD, ...) @@ -811,11 +868,12 @@ DateTime x; if( isDate(context, argc, argv, &x)==0 ){ char zBuf[100]; computeYMD_HMS(&x); sqlite4_snprintf(zBuf,sizeof(zBuf), "%04d-%02d-%02d %02d:%02d:%02d", - x.Y, x.M, x.D, x.h, x.m, (int)(x.s)); + x.Y, x.M, x.D, x.h, x.m, (int)(sqlite4_num_to_int64(x.s,0)) + ); sqlite4_result_text(context, zBuf, -1, SQLITE4_TRANSIENT, 0); } } /* @@ -830,11 +888,13 @@ ){ DateTime x; if( isDate(context, argc, argv, &x)==0 ){ char zBuf[100]; computeHMS(&x); - sqlite4_snprintf(zBuf,sizeof(zBuf), "%02d:%02d:%02d", x.h, x.m, (int)x.s); + sqlite4_snprintf(zBuf,sizeof(zBuf), "%02d:%02d:%02d", x.h, x.m, + (int)(sqlite4_num_to_int64(x.s,0)) + ); sqlite4_result_text(context, zBuf, -1, SQLITE4_TRANSIENT, 0); } } /* @@ -946,13 +1006,15 @@ }else{ i++; switch( zFmt[i] ){ case 'd': sqlite4_snprintf(&z[j],3,"%02d",x.D); j+=2; break; case 'f': { - double s = x.s; - if( s>59.999 ) s = 59.999; - j += sqlite4_snprintf(&z[j],7,"%06.3f", s); + sqlite4_num rnd = x.s; + int i1; + rnd.e += 3; + i1 = sqlite4_num_to_int64(rnd, 0); + j += sqlite4_snprintf(&z[j], 7, "%02d.%03d", i1 / 1000, i1 % 1000); break; } case 'H': sqlite4_snprintf(&z[j],3,"%02d",x.h); j+=2; break; case 'W': /* Fall thru */ case 'j': { @@ -983,11 +1045,14 @@ case 's': { j += sqlite4_snprintf(&z[j],30,"%lld", (i64)(x.iJD/1000 - 21086676*(i64)10000)); break; } - case 'S': sqlite4_snprintf(&z[j],3,"%02d",(int)x.s); j+=2; break; + case 'S': + sqlite4_snprintf(&z[j], 3, "%02d", (int)sqlite4_num_to_int64(x.s, 0)); + j+=2; + break; case 'w': { z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0'; break; } case 'Y': { Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -268,22 +268,11 @@ if( n>30 ) n = 30; if( n<0 ) n = 0; } if( sqlite4_value_type(argv[0])==SQLITE4_NULL ) return; - num = sqlite4_value_num(argv[0]); - p10 = (num.e*-1) - n; - if( p10>0 ){ - int rnd; - int i; - u64 div = 1; - for(i=0; i= (div/2); - num.m = ((num.m / div) + rnd) * div; - } - num.approx = 0; - sqlite4_result_num(context, num); + sqlite4_result_num(context, sqlite4_num_round(sqlite4_value_num(argv[0]), n)); } /* ** Allocate nByte bytes of space using sqlite4_malloc(). If the ** allocation fails, call sqlite4_result_error_nomem() to notify Index: src/os.c ================================================================== --- src/os.c +++ src/os.c @@ -26,17 +26,17 @@ ** the number of seconds since 1970 and is used to set the result of ** sqlite4OsCurrentTime() during testing. */ unsigned int sqlite4_current_time = 0; /* Fake system time */ int sqlite4OsCurrentTime(sqlite4_env *pEnv, sqlite4_uint64 *pTimeOut){ + static const sqlite4_uint64 unixEpoch = 24405875*(sqlite4_int64)8640000; int rc = SQLITE4_OK; if( sqlite4_current_time ){ - *pTimeOut = (sqlite4_uint64)sqlite4_current_time * 1000; + *pTimeOut = unixEpoch + (sqlite4_uint64)sqlite4_current_time * 1000; return SQLITE4_OK; } #if SQLITE4_OS_UNIX - static const sqlite4_int64 unixEpoch = 24405875*(sqlite4_int64)8640000; struct timeval sNow; if( gettimeofday(&sNow, 0)==0 ){ *pTimeOut = unixEpoch + 1000*(sqlite4_int64)sNow.tv_sec + sNow.tv_usec/1000; }else{ rc = SQLITE4_ERROR; Index: test/date.test ================================================================== --- test/date.test +++ test/date.test @@ -67,11 +67,10 @@ 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 - datetest 2.1 datetime(0,'unixepoch') {1970-01-01 00:00:00} datetest 2.1b datetime(0,'unixepoc') NULL datetest 2.1c datetime(0,'unixepochx') NULL datetest 2.1d datetime('2003-10-22','unixepoch') NULL datetest 2.2 datetime(946684800,'unixepoch') {2000-01-01 00:00:00} @@ -492,40 +491,6 @@ datetest 13.31 {date('2001-01-01','+1.5 years')} {2002-07-02} datetest 13.32 {date('2002-01-01','+1.5 years')} {2003-07-02} datetest 13.33 {date('2002-01-01','-1.5 years')} {2000-07-02} datetest 13.34 {date('2001-01-01','-1.5 years')} {1999-07-02} -# Test for issues reported by BareFeet (list.sql at tandb.com.au) -# on mailing list on 2008-06-12. -# -# Put a floating point number in the database so that we can manipulate -# raw bits using the hexio interface. -# -if {0==[sqlite4 -has-codec]} { - do_test date-14.1 { - execsql { - PRAGMA auto_vacuum=OFF; - PRAGMA page_size = 1024; - CREATE TABLE t1(x); - INSERT INTO t1 VALUES(1.1); - } - db close - hexio_write test.db 2040 4142ba32bffffff9 - sqlite4 db test.db - db eval {SELECT * FROM t1} - } {2454629.5} - - # Changing the least significant byte of the floating point value between - # 00 and FF should always generate a time of either 23:59:59 or 00:00:00, - # never 24:00:00 - # - for {set i 0} {$i<=255} {incr i} { - db close - hexio_write test.db 2047 [format %02x $i] - sqlite4 db test.db - do_test date-14.2.$i { - set date [db one {SELECT datetime(x) FROM t1}] - expr {$date eq "2008-06-12 00:00:00" || $date eq "2008-06-11 23:59:59"} - } {1} - } -} finish_test Index: test/permutations.test ================================================================== --- test/permutations.test +++ test/permutations.test @@ -158,10 +158,11 @@ collate6.test collate7.test collate8.test collate9.test collateA.test conflict.test count.test cse.test ctime.test + date.test delete.test delete2.test distinct.test distinctagg.test exists.test e_droptrigger.test e_dropview.test e_resolve.test e_dropview.test