000001 /* 000002 ** 2003 October 31 000003 ** 000004 ** The author disclaims copyright to this source code. In place of 000005 ** a legal notice, here is a blessing: 000006 ** 000007 ** May you do good and not evil. 000008 ** May you find forgiveness for yourself and forgive others. 000009 ** May you share freely, never taking more than you give. 000010 ** 000011 ************************************************************************* 000012 ** This file contains the C functions that implement date and time 000013 ** functions for SQLite. 000014 ** 000015 ** There is only one exported symbol in this file - the function 000016 ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file. 000017 ** All other code has file scope. 000018 ** 000019 ** SQLite processes all times and dates as julian day numbers. The 000020 ** dates and times are stored as the number of days since noon 000021 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian 000022 ** calendar system. 000023 ** 000024 ** 1970-01-01 00:00:00 is JD 2440587.5 000025 ** 2000-01-01 00:00:00 is JD 2451544.5 000026 ** 000027 ** This implementation requires years to be expressed as a 4-digit number 000028 ** which means that only dates between 0000-01-01 and 9999-12-31 can 000029 ** be represented, even though julian day numbers allow a much wider 000030 ** range of dates. 000031 ** 000032 ** The Gregorian calendar system is used for all dates and times, 000033 ** even those that predate the Gregorian calendar. Historians usually 000034 ** use the julian calendar for dates prior to 1582-10-15 and for some 000035 ** dates afterwards, depending on locale. Beware of this difference. 000036 ** 000037 ** The conversion algorithms are implemented based on descriptions 000038 ** in the following text: 000039 ** 000040 ** Jean Meeus 000041 ** Astronomical Algorithms, 2nd Edition, 1998 000042 ** ISBN 0-943396-61-1 000043 ** Willmann-Bell, Inc 000044 ** Richmond, Virginia (USA) 000045 */ 000046 #include "sqliteInt.h" 000047 #include <stdlib.h> 000048 #include <assert.h> 000049 #include <time.h> 000050 000051 #ifndef SQLITE_OMIT_DATETIME_FUNCS 000052 000053 /* 000054 ** The MSVC CRT on Windows CE may not have a localtime() function. 000055 ** So declare a substitute. The substitute function itself is 000056 ** defined in "os_win.c". 000057 */ 000058 #if !defined(SQLITE_OMIT_LOCALTIME) && defined(_WIN32_WCE) && \ 000059 (!defined(SQLITE_MSVC_LOCALTIME_API) || !SQLITE_MSVC_LOCALTIME_API) 000060 struct tm *__cdecl localtime(const time_t *); 000061 #endif 000062 000063 /* 000064 ** A structure for holding a single date and time. 000065 */ 000066 typedef struct DateTime DateTime; 000067 struct DateTime { 000068 sqlite3_int64 iJD; /* The julian day number times 86400000 */ 000069 int Y, M, D; /* Year, month, and day */ 000070 int h, m; /* Hour and minutes */ 000071 int tz; /* Timezone offset in minutes */ 000072 double s; /* Seconds */ 000073 char validJD; /* True (1) if iJD is valid */ 000074 char rawS; /* Raw numeric value stored in s */ 000075 char validYMD; /* True (1) if Y,M,D are valid */ 000076 char validHMS; /* True (1) if h,m,s are valid */ 000077 char validTZ; /* True (1) if tz is valid */ 000078 char tzSet; /* Timezone was set explicitly */ 000079 char isError; /* An overflow has occurred */ 000080 char useSubsec; /* Display subsecond precision */ 000081 }; 000082 000083 000084 /* 000085 ** Convert zDate into one or more integers according to the conversion 000086 ** specifier zFormat. 000087 ** 000088 ** zFormat[] contains 4 characters for each integer converted, except for 000089 ** the last integer which is specified by three characters. The meaning 000090 ** of a four-character format specifiers ABCD is: 000091 ** 000092 ** A: number of digits to convert. Always "2" or "4". 000093 ** B: minimum value. Always "0" or "1". 000094 ** C: maximum value, decoded as: 000095 ** a: 12 000096 ** b: 14 000097 ** c: 24 000098 ** d: 31 000099 ** e: 59 000100 ** f: 9999 000101 ** D: the separator character, or \000 to indicate this is the 000102 ** last number to convert. 000103 ** 000104 ** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would 000105 ** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-". 000106 ** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates 000107 ** the 2-digit day which is the last integer in the set. 000108 ** 000109 ** The function returns the number of successful conversions. 000110 */ 000111 static int getDigits(const char *zDate, const char *zFormat, ...){ 000112 /* The aMx[] array translates the 3rd character of each format 000113 ** spec into a max size: a b c d e f */ 000114 static const u16 aMx[] = { 12, 14, 24, 31, 59, 14712 }; 000115 va_list ap; 000116 int cnt = 0; 000117 char nextC; 000118 va_start(ap, zFormat); 000119 do{ 000120 char N = zFormat[0] - '0'; 000121 char min = zFormat[1] - '0'; 000122 int val = 0; 000123 u16 max; 000124 000125 assert( zFormat[2]>='a' && zFormat[2]<='f' ); 000126 max = aMx[zFormat[2] - 'a']; 000127 nextC = zFormat[3]; 000128 val = 0; 000129 while( N-- ){ 000130 if( !sqlite3Isdigit(*zDate) ){ 000131 goto end_getDigits; 000132 } 000133 val = val*10 + *zDate - '0'; 000134 zDate++; 000135 } 000136 if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){ 000137 goto end_getDigits; 000138 } 000139 *va_arg(ap,int*) = val; 000140 zDate++; 000141 cnt++; 000142 zFormat += 4; 000143 }while( nextC ); 000144 end_getDigits: 000145 va_end(ap); 000146 return cnt; 000147 } 000148 000149 /* 000150 ** Parse a timezone extension on the end of a date-time. 000151 ** The extension is of the form: 000152 ** 000153 ** (+/-)HH:MM 000154 ** 000155 ** Or the "zulu" notation: 000156 ** 000157 ** Z 000158 ** 000159 ** If the parse is successful, write the number of minutes 000160 ** of change in p->tz and return 0. If a parser error occurs, 000161 ** return non-zero. 000162 ** 000163 ** A missing specifier is not considered an error. 000164 */ 000165 static int parseTimezone(const char *zDate, DateTime *p){ 000166 int sgn = 0; 000167 int nHr, nMn; 000168 int c; 000169 while( sqlite3Isspace(*zDate) ){ zDate++; } 000170 p->tz = 0; 000171 c = *zDate; 000172 if( c=='-' ){ 000173 sgn = -1; 000174 }else if( c=='+' ){ 000175 sgn = +1; 000176 }else if( c=='Z' || c=='z' ){ 000177 zDate++; 000178 goto zulu_time; 000179 }else{ 000180 return c!=0; 000181 } 000182 zDate++; 000183 if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){ 000184 return 1; 000185 } 000186 zDate += 5; 000187 p->tz = sgn*(nMn + nHr*60); 000188 zulu_time: 000189 while( sqlite3Isspace(*zDate) ){ zDate++; } 000190 p->tzSet = 1; 000191 return *zDate!=0; 000192 } 000193 000194 /* 000195 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF. 000196 ** The HH, MM, and SS must each be exactly 2 digits. The 000197 ** fractional seconds FFFF can be one or more digits. 000198 ** 000199 ** Return 1 if there is a parsing error and 0 on success. 000200 */ 000201 static int parseHhMmSs(const char *zDate, DateTime *p){ 000202 int h, m, s; 000203 double ms = 0.0; 000204 if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){ 000205 return 1; 000206 } 000207 zDate += 5; 000208 if( *zDate==':' ){ 000209 zDate++; 000210 if( getDigits(zDate, "20e", &s)!=1 ){ 000211 return 1; 000212 } 000213 zDate += 2; 000214 if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){ 000215 double rScale = 1.0; 000216 zDate++; 000217 while( sqlite3Isdigit(*zDate) ){ 000218 ms = ms*10.0 + *zDate - '0'; 000219 rScale *= 10.0; 000220 zDate++; 000221 } 000222 ms /= rScale; 000223 } 000224 }else{ 000225 s = 0; 000226 } 000227 p->validJD = 0; 000228 p->rawS = 0; 000229 p->validHMS = 1; 000230 p->h = h; 000231 p->m = m; 000232 p->s = s + ms; 000233 if( parseTimezone(zDate, p) ) return 1; 000234 p->validTZ = (p->tz!=0)?1:0; 000235 return 0; 000236 } 000237 000238 /* 000239 ** Put the DateTime object into its error state. 000240 */ 000241 static void datetimeError(DateTime *p){ 000242 memset(p, 0, sizeof(*p)); 000243 p->isError = 1; 000244 } 000245 000246 /* 000247 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume 000248 ** that the YYYY-MM-DD is according to the Gregorian calendar. 000249 ** 000250 ** Reference: Meeus page 61 000251 */ 000252 static void computeJD(DateTime *p){ 000253 int Y, M, D, A, B, X1, X2; 000254 000255 if( p->validJD ) return; 000256 if( p->validYMD ){ 000257 Y = p->Y; 000258 M = p->M; 000259 D = p->D; 000260 }else{ 000261 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */ 000262 M = 1; 000263 D = 1; 000264 } 000265 if( Y<-4713 || Y>9999 || p->rawS ){ 000266 datetimeError(p); 000267 return; 000268 } 000269 if( M<=2 ){ 000270 Y--; 000271 M += 12; 000272 } 000273 A = Y/100; 000274 B = 2 - A + (A/4); 000275 X1 = 36525*(Y+4716)/100; 000276 X2 = 306001*(M+1)/10000; 000277 p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000); 000278 p->validJD = 1; 000279 if( p->validHMS ){ 000280 p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000 + 0.5); 000281 if( p->validTZ ){ 000282 p->iJD -= p->tz*60000; 000283 p->validYMD = 0; 000284 p->validHMS = 0; 000285 p->validTZ = 0; 000286 } 000287 } 000288 } 000289 000290 /* 000291 ** Parse dates of the form 000292 ** 000293 ** YYYY-MM-DD HH:MM:SS.FFF 000294 ** YYYY-MM-DD HH:MM:SS 000295 ** YYYY-MM-DD HH:MM 000296 ** YYYY-MM-DD 000297 ** 000298 ** Write the result into the DateTime structure and return 0 000299 ** on success and 1 if the input string is not a well-formed 000300 ** date. 000301 */ 000302 static int parseYyyyMmDd(const char *zDate, DateTime *p){ 000303 int Y, M, D, neg; 000304 000305 if( zDate[0]=='-' ){ 000306 zDate++; 000307 neg = 1; 000308 }else{ 000309 neg = 0; 000310 } 000311 if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){ 000312 return 1; 000313 } 000314 zDate += 10; 000315 while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; } 000316 if( parseHhMmSs(zDate, p)==0 ){ 000317 /* We got the time */ 000318 }else if( *zDate==0 ){ 000319 p->validHMS = 0; 000320 }else{ 000321 return 1; 000322 } 000323 p->validJD = 0; 000324 p->validYMD = 1; 000325 p->Y = neg ? -Y : Y; 000326 p->M = M; 000327 p->D = D; 000328 if( p->validTZ ){ 000329 computeJD(p); 000330 } 000331 return 0; 000332 } 000333 000334 /* 000335 ** Set the time to the current time reported by the VFS. 000336 ** 000337 ** Return the number of errors. 000338 */ 000339 static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){ 000340 p->iJD = sqlite3StmtCurrentTime(context); 000341 if( p->iJD>0 ){ 000342 p->validJD = 1; 000343 return 0; 000344 }else{ 000345 return 1; 000346 } 000347 } 000348 000349 /* 000350 ** Input "r" is a numeric quantity which might be a julian day number, 000351 ** or the number of seconds since 1970. If the value if r is within 000352 ** range of a julian day number, install it as such and set validJD. 000353 ** If the value is a valid unix timestamp, put it in p->s and set p->rawS. 000354 */ 000355 static void setRawDateNumber(DateTime *p, double r){ 000356 p->s = r; 000357 p->rawS = 1; 000358 if( r>=0.0 && r<5373484.5 ){ 000359 p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5); 000360 p->validJD = 1; 000361 } 000362 } 000363 000364 /* 000365 ** Attempt to parse the given string into a julian day number. Return 000366 ** the number of errors. 000367 ** 000368 ** The following are acceptable forms for the input string: 000369 ** 000370 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM 000371 ** DDDD.DD 000372 ** now 000373 ** 000374 ** In the first form, the +/-HH:MM is always optional. The fractional 000375 ** seconds extension (the ".FFF") is optional. The seconds portion 000376 ** (":SS.FFF") is option. The year and date can be omitted as long 000377 ** as there is a time string. The time string can be omitted as long 000378 ** as there is a year and date. 000379 */ 000380 static int parseDateOrTime( 000381 sqlite3_context *context, 000382 const char *zDate, 000383 DateTime *p 000384 ){ 000385 double r; 000386 if( parseYyyyMmDd(zDate,p)==0 ){ 000387 return 0; 000388 }else if( parseHhMmSs(zDate, p)==0 ){ 000389 return 0; 000390 }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){ 000391 return setDateTimeToCurrent(context, p); 000392 }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){ 000393 setRawDateNumber(p, r); 000394 return 0; 000395 }else if( (sqlite3StrICmp(zDate,"subsec")==0 000396 || sqlite3StrICmp(zDate,"subsecond")==0) 000397 && sqlite3NotPureFunc(context) ){ 000398 p->useSubsec = 1; 000399 return setDateTimeToCurrent(context, p); 000400 } 000401 return 1; 000402 } 000403 000404 /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999. 000405 ** Multiplying this by 86400000 gives 464269060799999 as the maximum value 000406 ** for DateTime.iJD. 000407 ** 000408 ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with 000409 ** such a large integer literal, so we have to encode it. 000410 */ 000411 #define INT_464269060799999 ((((i64)0x1a640)<<32)|0x1072fdff) 000412 000413 /* 000414 ** Return TRUE if the given julian day number is within range. 000415 ** 000416 ** The input is the JulianDay times 86400000. 000417 */ 000418 static int validJulianDay(sqlite3_int64 iJD){ 000419 return iJD>=0 && iJD<=INT_464269060799999; 000420 } 000421 000422 /* 000423 ** Compute the Year, Month, and Day from the julian day number. 000424 */ 000425 static void computeYMD(DateTime *p){ 000426 int Z, A, B, C, D, E, X1; 000427 if( p->validYMD ) return; 000428 if( !p->validJD ){ 000429 p->Y = 2000; 000430 p->M = 1; 000431 p->D = 1; 000432 }else if( !validJulianDay(p->iJD) ){ 000433 datetimeError(p); 000434 return; 000435 }else{ 000436 Z = (int)((p->iJD + 43200000)/86400000); 000437 A = (int)((Z - 1867216.25)/36524.25); 000438 A = Z + 1 + A - (A/4); 000439 B = A + 1524; 000440 C = (int)((B - 122.1)/365.25); 000441 D = (36525*(C&32767))/100; 000442 E = (int)((B-D)/30.6001); 000443 X1 = (int)(30.6001*E); 000444 p->D = B - D - X1; 000445 p->M = E<14 ? E-1 : E-13; 000446 p->Y = p->M>2 ? C - 4716 : C - 4715; 000447 } 000448 p->validYMD = 1; 000449 } 000450 000451 /* 000452 ** Compute the Hour, Minute, and Seconds from the julian day number. 000453 */ 000454 static void computeHMS(DateTime *p){ 000455 int day_ms, day_min; /* milliseconds, minutes into the day */ 000456 if( p->validHMS ) return; 000457 computeJD(p); 000458 day_ms = (int)((p->iJD + 43200000) % 86400000); 000459 p->s = (day_ms % 60000)/1000.0; 000460 day_min = day_ms/60000; 000461 p->m = day_min % 60; 000462 p->h = day_min / 60; 000463 p->rawS = 0; 000464 p->validHMS = 1; 000465 } 000466 000467 /* 000468 ** Compute both YMD and HMS 000469 */ 000470 static void computeYMD_HMS(DateTime *p){ 000471 computeYMD(p); 000472 computeHMS(p); 000473 } 000474 000475 /* 000476 ** Clear the YMD and HMS and the TZ 000477 */ 000478 static void clearYMD_HMS_TZ(DateTime *p){ 000479 p->validYMD = 0; 000480 p->validHMS = 0; 000481 p->validTZ = 0; 000482 } 000483 000484 #ifndef SQLITE_OMIT_LOCALTIME 000485 /* 000486 ** On recent Windows platforms, the localtime_s() function is available 000487 ** as part of the "Secure CRT". It is essentially equivalent to 000488 ** localtime_r() available under most POSIX platforms, except that the 000489 ** order of the parameters is reversed. 000490 ** 000491 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx. 000492 ** 000493 ** If the user has not indicated to use localtime_r() or localtime_s() 000494 ** already, check for an MSVC build environment that provides 000495 ** localtime_s(). 000496 */ 000497 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \ 000498 && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE) 000499 #undef HAVE_LOCALTIME_S 000500 #define HAVE_LOCALTIME_S 1 000501 #endif 000502 000503 /* 000504 ** The following routine implements the rough equivalent of localtime_r() 000505 ** using whatever operating-system specific localtime facility that 000506 ** is available. This routine returns 0 on success and 000507 ** non-zero on any kind of error. 000508 ** 000509 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is non-zero then this 000510 ** routine will always fail. If bLocaltimeFault is nonzero and 000511 ** sqlite3GlobalConfig.xAltLocaltime is not NULL, then xAltLocaltime() is 000512 ** invoked in place of the OS-defined localtime() function. 000513 ** 000514 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C 000515 ** library function localtime_r() is used to assist in the calculation of 000516 ** local time. 000517 */ 000518 static int osLocaltime(time_t *t, struct tm *pTm){ 000519 int rc; 000520 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S 000521 struct tm *pX; 000522 #if SQLITE_THREADSAFE>0 000523 sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN); 000524 #endif 000525 sqlite3_mutex_enter(mutex); 000526 pX = localtime(t); 000527 #ifndef SQLITE_UNTESTABLE 000528 if( sqlite3GlobalConfig.bLocaltimeFault ){ 000529 if( sqlite3GlobalConfig.xAltLocaltime!=0 000530 && 0==sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm) 000531 ){ 000532 pX = pTm; 000533 }else{ 000534 pX = 0; 000535 } 000536 } 000537 #endif 000538 if( pX ) *pTm = *pX; 000539 #if SQLITE_THREADSAFE>0 000540 sqlite3_mutex_leave(mutex); 000541 #endif 000542 rc = pX==0; 000543 #else 000544 #ifndef SQLITE_UNTESTABLE 000545 if( sqlite3GlobalConfig.bLocaltimeFault ){ 000546 if( sqlite3GlobalConfig.xAltLocaltime!=0 ){ 000547 return sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm); 000548 }else{ 000549 return 1; 000550 } 000551 } 000552 #endif 000553 #if HAVE_LOCALTIME_R 000554 rc = localtime_r(t, pTm)==0; 000555 #else 000556 rc = localtime_s(pTm, t); 000557 #endif /* HAVE_LOCALTIME_R */ 000558 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */ 000559 return rc; 000560 } 000561 #endif /* SQLITE_OMIT_LOCALTIME */ 000562 000563 000564 #ifndef SQLITE_OMIT_LOCALTIME 000565 /* 000566 ** Assuming the input DateTime is UTC, move it to its localtime equivalent. 000567 */ 000568 static int toLocaltime( 000569 DateTime *p, /* Date at which to calculate offset */ 000570 sqlite3_context *pCtx /* Write error here if one occurs */ 000571 ){ 000572 time_t t; 000573 struct tm sLocal; 000574 int iYearDiff; 000575 000576 /* Initialize the contents of sLocal to avoid a compiler warning. */ 000577 memset(&sLocal, 0, sizeof(sLocal)); 000578 000579 computeJD(p); 000580 if( p->iJD<2108667600*(i64)100000 /* 1970-01-01 */ 000581 || p->iJD>2130141456*(i64)100000 /* 2038-01-18 */ 000582 ){ 000583 /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only 000584 ** works for years between 1970 and 2037. For dates outside this range, 000585 ** SQLite attempts to map the year into an equivalent year within this 000586 ** range, do the calculation, then map the year back. 000587 */ 000588 DateTime x = *p; 000589 computeYMD_HMS(&x); 000590 iYearDiff = (2000 + x.Y%4) - x.Y; 000591 x.Y += iYearDiff; 000592 x.validJD = 0; 000593 computeJD(&x); 000594 t = (time_t)(x.iJD/1000 - 21086676*(i64)10000); 000595 }else{ 000596 iYearDiff = 0; 000597 t = (time_t)(p->iJD/1000 - 21086676*(i64)10000); 000598 } 000599 if( osLocaltime(&t, &sLocal) ){ 000600 sqlite3_result_error(pCtx, "local time unavailable", -1); 000601 return SQLITE_ERROR; 000602 } 000603 p->Y = sLocal.tm_year + 1900 - iYearDiff; 000604 p->M = sLocal.tm_mon + 1; 000605 p->D = sLocal.tm_mday; 000606 p->h = sLocal.tm_hour; 000607 p->m = sLocal.tm_min; 000608 p->s = sLocal.tm_sec + (p->iJD%1000)*0.001; 000609 p->validYMD = 1; 000610 p->validHMS = 1; 000611 p->validJD = 0; 000612 p->rawS = 0; 000613 p->validTZ = 0; 000614 p->isError = 0; 000615 return SQLITE_OK; 000616 } 000617 #endif /* SQLITE_OMIT_LOCALTIME */ 000618 000619 /* 000620 ** The following table defines various date transformations of the form 000621 ** 000622 ** 'NNN days' 000623 ** 000624 ** Where NNN is an arbitrary floating-point number and "days" can be one 000625 ** of several units of time. 000626 */ 000627 static const struct { 000628 u8 nName; /* Length of the name */ 000629 char zName[7]; /* Name of the transformation */ 000630 float rLimit; /* Maximum NNN value for this transform */ 000631 float rXform; /* Constant used for this transform */ 000632 } aXformType[] = { 000633 { 6, "second", 4.6427e+14, 1.0 }, 000634 { 6, "minute", 7.7379e+12, 60.0 }, 000635 { 4, "hour", 1.2897e+11, 3600.0 }, 000636 { 3, "day", 5373485.0, 86400.0 }, 000637 { 5, "month", 176546.0, 2592000.0 }, 000638 { 4, "year", 14713.0, 31536000.0 }, 000639 }; 000640 000641 /* 000642 ** If the DateTime p is raw number, try to figure out if it is 000643 ** a julian day number of a unix timestamp. Set the p value 000644 ** appropriately. 000645 */ 000646 static void autoAdjustDate(DateTime *p){ 000647 if( !p->rawS || p->validJD ){ 000648 p->rawS = 0; 000649 }else if( p->s>=-21086676*(i64)10000 /* -4713-11-24 12:00:00 */ 000650 && p->s<=(25340230*(i64)10000)+799 /* 9999-12-31 23:59:59 */ 000651 ){ 000652 double r = p->s*1000.0 + 210866760000000.0; 000653 clearYMD_HMS_TZ(p); 000654 p->iJD = (sqlite3_int64)(r + 0.5); 000655 p->validJD = 1; 000656 p->rawS = 0; 000657 } 000658 } 000659 000660 /* 000661 ** Process a modifier to a date-time stamp. The modifiers are 000662 ** as follows: 000663 ** 000664 ** NNN days 000665 ** NNN hours 000666 ** NNN minutes 000667 ** NNN.NNNN seconds 000668 ** NNN months 000669 ** NNN years 000670 ** start of month 000671 ** start of year 000672 ** start of week 000673 ** start of day 000674 ** weekday N 000675 ** unixepoch 000676 ** localtime 000677 ** utc 000678 ** 000679 ** Return 0 on success and 1 if there is any kind of error. If the error 000680 ** is in a system call (i.e. localtime()), then an error message is written 000681 ** to context pCtx. If the error is an unrecognized modifier, no error is 000682 ** written to pCtx. 000683 */ 000684 static int parseModifier( 000685 sqlite3_context *pCtx, /* Function context */ 000686 const char *z, /* The text of the modifier */ 000687 int n, /* Length of zMod in bytes */ 000688 DateTime *p, /* The date/time value to be modified */ 000689 int idx /* Parameter index of the modifier */ 000690 ){ 000691 int rc = 1; 000692 double r; 000693 switch(sqlite3UpperToLower[(u8)z[0]] ){ 000694 case 'a': { 000695 /* 000696 ** auto 000697 ** 000698 ** If rawS is available, then interpret as a julian day number, or 000699 ** a unix timestamp, depending on its magnitude. 000700 */ 000701 if( sqlite3_stricmp(z, "auto")==0 ){ 000702 if( idx>1 ) return 1; /* IMP: R-33611-57934 */ 000703 autoAdjustDate(p); 000704 rc = 0; 000705 } 000706 break; 000707 } 000708 case 'j': { 000709 /* 000710 ** julianday 000711 ** 000712 ** Always interpret the prior number as a julian-day value. If this 000713 ** is not the first modifier, or if the prior argument is not a numeric 000714 ** value in the allowed range of julian day numbers understood by 000715 ** SQLite (0..5373484.5) then the result will be NULL. 000716 */ 000717 if( sqlite3_stricmp(z, "julianday")==0 ){ 000718 if( idx>1 ) return 1; /* IMP: R-31176-64601 */ 000719 if( p->validJD && p->rawS ){ 000720 rc = 0; 000721 p->rawS = 0; 000722 } 000723 } 000724 break; 000725 } 000726 #ifndef SQLITE_OMIT_LOCALTIME 000727 case 'l': { 000728 /* localtime 000729 ** 000730 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to 000731 ** show local time. 000732 */ 000733 if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){ 000734 rc = toLocaltime(p, pCtx); 000735 } 000736 break; 000737 } 000738 #endif 000739 case 'u': { 000740 /* 000741 ** unixepoch 000742 ** 000743 ** Treat the current value of p->s as the number of 000744 ** seconds since 1970. Convert to a real julian day number. 000745 */ 000746 if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){ 000747 if( idx>1 ) return 1; /* IMP: R-49255-55373 */ 000748 r = p->s*1000.0 + 210866760000000.0; 000749 if( r>=0.0 && r<464269060800000.0 ){ 000750 clearYMD_HMS_TZ(p); 000751 p->iJD = (sqlite3_int64)(r + 0.5); 000752 p->validJD = 1; 000753 p->rawS = 0; 000754 rc = 0; 000755 } 000756 } 000757 #ifndef SQLITE_OMIT_LOCALTIME 000758 else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){ 000759 if( p->tzSet==0 ){ 000760 i64 iOrigJD; /* Original localtime */ 000761 i64 iGuess; /* Guess at the corresponding utc time */ 000762 int cnt = 0; /* Safety to prevent infinite loop */ 000763 i64 iErr; /* Guess is off by this much */ 000764 000765 computeJD(p); 000766 iGuess = iOrigJD = p->iJD; 000767 iErr = 0; 000768 do{ 000769 DateTime new; 000770 memset(&new, 0, sizeof(new)); 000771 iGuess -= iErr; 000772 new.iJD = iGuess; 000773 new.validJD = 1; 000774 rc = toLocaltime(&new, pCtx); 000775 if( rc ) return rc; 000776 computeJD(&new); 000777 iErr = new.iJD - iOrigJD; 000778 }while( iErr && cnt++<3 ); 000779 memset(p, 0, sizeof(*p)); 000780 p->iJD = iGuess; 000781 p->validJD = 1; 000782 p->tzSet = 1; 000783 } 000784 rc = SQLITE_OK; 000785 } 000786 #endif 000787 break; 000788 } 000789 case 'w': { 000790 /* 000791 ** weekday N 000792 ** 000793 ** Move the date to the same time on the next occurrence of 000794 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the 000795 ** date is already on the appropriate weekday, this is a no-op. 000796 */ 000797 if( sqlite3_strnicmp(z, "weekday ", 8)==0 000798 && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0 000799 && r>=0.0 && r<7.0 && (n=(int)r)==r ){ 000800 sqlite3_int64 Z; 000801 computeYMD_HMS(p); 000802 p->validTZ = 0; 000803 p->validJD = 0; 000804 computeJD(p); 000805 Z = ((p->iJD + 129600000)/86400000) % 7; 000806 if( Z>n ) Z -= 7; 000807 p->iJD += (n - Z)*86400000; 000808 clearYMD_HMS_TZ(p); 000809 rc = 0; 000810 } 000811 break; 000812 } 000813 case 's': { 000814 /* 000815 ** start of TTTTT 000816 ** 000817 ** Move the date backwards to the beginning of the current day, 000818 ** or month or year. 000819 ** 000820 ** subsecond 000821 ** subsec 000822 ** 000823 ** Show subsecond precision in the output of datetime() and 000824 ** unixepoch() and strftime('%s'). 000825 */ 000826 if( sqlite3_strnicmp(z, "start of ", 9)!=0 ){ 000827 if( sqlite3_stricmp(z, "subsec")==0 000828 || sqlite3_stricmp(z, "subsecond")==0 000829 ){ 000830 p->useSubsec = 1; 000831 rc = 0; 000832 } 000833 break; 000834 } 000835 if( !p->validJD && !p->validYMD && !p->validHMS ) break; 000836 z += 9; 000837 computeYMD(p); 000838 p->validHMS = 1; 000839 p->h = p->m = 0; 000840 p->s = 0.0; 000841 p->rawS = 0; 000842 p->validTZ = 0; 000843 p->validJD = 0; 000844 if( sqlite3_stricmp(z,"month")==0 ){ 000845 p->D = 1; 000846 rc = 0; 000847 }else if( sqlite3_stricmp(z,"year")==0 ){ 000848 p->M = 1; 000849 p->D = 1; 000850 rc = 0; 000851 }else if( sqlite3_stricmp(z,"day")==0 ){ 000852 rc = 0; 000853 } 000854 break; 000855 } 000856 case '+': 000857 case '-': 000858 case '0': 000859 case '1': 000860 case '2': 000861 case '3': 000862 case '4': 000863 case '5': 000864 case '6': 000865 case '7': 000866 case '8': 000867 case '9': { 000868 double rRounder; 000869 int i; 000870 int Y,M,D,h,m,x; 000871 const char *z2 = z; 000872 char z0 = z[0]; 000873 for(n=1; z[n]; n++){ 000874 if( z[n]==':' ) break; 000875 if( sqlite3Isspace(z[n]) ) break; 000876 if( z[n]=='-' ){ 000877 if( n==5 && getDigits(&z[1], "40f", &Y)==1 ) break; 000878 if( n==6 && getDigits(&z[1], "50f", &Y)==1 ) break; 000879 } 000880 } 000881 if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){ 000882 assert( rc==1 ); 000883 break; 000884 } 000885 if( z[n]=='-' ){ 000886 /* A modifier of the form (+|-)YYYY-MM-DD adds or subtracts the 000887 ** specified number of years, months, and days. MM is limited to 000888 ** the range 0-11 and DD is limited to 0-30. 000889 */ 000890 if( z0!='+' && z0!='-' ) break; /* Must start with +/- */ 000891 if( n==5 ){ 000892 if( getDigits(&z[1], "40f-20a-20d", &Y, &M, &D)!=3 ) break; 000893 }else{ 000894 assert( n==6 ); 000895 if( getDigits(&z[1], "50f-20a-20d", &Y, &M, &D)!=3 ) break; 000896 z++; 000897 } 000898 if( M>=12 ) break; /* M range 0..11 */ 000899 if( D>=31 ) break; /* D range 0..30 */ 000900 computeYMD_HMS(p); 000901 p->validJD = 0; 000902 if( z0=='-' ){ 000903 p->Y -= Y; 000904 p->M -= M; 000905 D = -D; 000906 }else{ 000907 p->Y += Y; 000908 p->M += M; 000909 } 000910 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; 000911 p->Y += x; 000912 p->M -= x*12; 000913 computeJD(p); 000914 p->validHMS = 0; 000915 p->validYMD = 0; 000916 p->iJD += (i64)D*86400000; 000917 if( z[11]==0 ){ 000918 rc = 0; 000919 break; 000920 } 000921 if( sqlite3Isspace(z[11]) 000922 && getDigits(&z[12], "20c:20e", &h, &m)==2 000923 ){ 000924 z2 = &z[12]; 000925 n = 2; 000926 }else{ 000927 break; 000928 } 000929 } 000930 if( z2[n]==':' ){ 000931 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the 000932 ** specified number of hours, minutes, seconds, and fractional seconds 000933 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be 000934 ** omitted. 000935 */ 000936 000937 DateTime tx; 000938 sqlite3_int64 day; 000939 if( !sqlite3Isdigit(*z2) ) z2++; 000940 memset(&tx, 0, sizeof(tx)); 000941 if( parseHhMmSs(z2, &tx) ) break; 000942 computeJD(&tx); 000943 tx.iJD -= 43200000; 000944 day = tx.iJD/86400000; 000945 tx.iJD -= day*86400000; 000946 if( z0=='-' ) tx.iJD = -tx.iJD; 000947 computeJD(p); 000948 clearYMD_HMS_TZ(p); 000949 p->iJD += tx.iJD; 000950 rc = 0; 000951 break; 000952 } 000953 000954 /* If control reaches this point, it means the transformation is 000955 ** one of the forms like "+NNN days". */ 000956 z += n; 000957 while( sqlite3Isspace(*z) ) z++; 000958 n = sqlite3Strlen30(z); 000959 if( n>10 || n<3 ) break; 000960 if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--; 000961 computeJD(p); 000962 assert( rc==1 ); 000963 rRounder = r<0 ? -0.5 : +0.5; 000964 for(i=0; i<ArraySize(aXformType); i++){ 000965 if( aXformType[i].nName==n 000966 && sqlite3_strnicmp(aXformType[i].zName, z, n)==0 000967 && r>-aXformType[i].rLimit && r<aXformType[i].rLimit 000968 ){ 000969 switch( i ){ 000970 case 4: { /* Special processing to add months */ 000971 assert( strcmp(aXformType[i].zName,"month")==0 ); 000972 computeYMD_HMS(p); 000973 p->M += (int)r; 000974 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; 000975 p->Y += x; 000976 p->M -= x*12; 000977 p->validJD = 0; 000978 r -= (int)r; 000979 break; 000980 } 000981 case 5: { /* Special processing to add years */ 000982 int y = (int)r; 000983 assert( strcmp(aXformType[i].zName,"year")==0 ); 000984 computeYMD_HMS(p); 000985 p->Y += y; 000986 p->validJD = 0; 000987 r -= (int)r; 000988 break; 000989 } 000990 } 000991 computeJD(p); 000992 p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder); 000993 rc = 0; 000994 break; 000995 } 000996 } 000997 clearYMD_HMS_TZ(p); 000998 break; 000999 } 001000 default: { 001001 break; 001002 } 001003 } 001004 return rc; 001005 } 001006 001007 /* 001008 ** Process time function arguments. argv[0] is a date-time stamp. 001009 ** argv[1] and following are modifiers. Parse them all and write 001010 ** the resulting time into the DateTime structure p. Return 0 001011 ** on success and 1 if there are any errors. 001012 ** 001013 ** If there are zero parameters (if even argv[0] is undefined) 001014 ** then assume a default value of "now" for argv[0]. 001015 */ 001016 static int isDate( 001017 sqlite3_context *context, 001018 int argc, 001019 sqlite3_value **argv, 001020 DateTime *p 001021 ){ 001022 int i, n; 001023 const unsigned char *z; 001024 int eType; 001025 memset(p, 0, sizeof(*p)); 001026 if( argc==0 ){ 001027 if( !sqlite3NotPureFunc(context) ) return 1; 001028 return setDateTimeToCurrent(context, p); 001029 } 001030 if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT 001031 || eType==SQLITE_INTEGER ){ 001032 setRawDateNumber(p, sqlite3_value_double(argv[0])); 001033 }else{ 001034 z = sqlite3_value_text(argv[0]); 001035 if( !z || parseDateOrTime(context, (char*)z, p) ){ 001036 return 1; 001037 } 001038 } 001039 for(i=1; i<argc; i++){ 001040 z = sqlite3_value_text(argv[i]); 001041 n = sqlite3_value_bytes(argv[i]); 001042 if( z==0 || parseModifier(context, (char*)z, n, p, i) ) return 1; 001043 } 001044 computeJD(p); 001045 if( p->isError || !validJulianDay(p->iJD) ) return 1; 001046 if( argc==1 && p->validYMD && p->D>28 ){ 001047 /* Make sure a YYYY-MM-DD is normalized. 001048 ** Example: 2023-02-31 -> 2023-03-03 */ 001049 assert( p->validJD ); 001050 p->validYMD = 0; 001051 } 001052 return 0; 001053 } 001054 001055 001056 /* 001057 ** The following routines implement the various date and time functions 001058 ** of SQLite. 001059 */ 001060 001061 /* 001062 ** julianday( TIMESTRING, MOD, MOD, ...) 001063 ** 001064 ** Return the julian day number of the date specified in the arguments 001065 */ 001066 static void juliandayFunc( 001067 sqlite3_context *context, 001068 int argc, 001069 sqlite3_value **argv 001070 ){ 001071 DateTime x; 001072 if( isDate(context, argc, argv, &x)==0 ){ 001073 computeJD(&x); 001074 sqlite3_result_double(context, x.iJD/86400000.0); 001075 } 001076 } 001077 001078 /* 001079 ** unixepoch( TIMESTRING, MOD, MOD, ...) 001080 ** 001081 ** Return the number of seconds (including fractional seconds) since 001082 ** the unix epoch of 1970-01-01 00:00:00 GMT. 001083 */ 001084 static void unixepochFunc( 001085 sqlite3_context *context, 001086 int argc, 001087 sqlite3_value **argv 001088 ){ 001089 DateTime x; 001090 if( isDate(context, argc, argv, &x)==0 ){ 001091 computeJD(&x); 001092 if( x.useSubsec ){ 001093 sqlite3_result_double(context, (x.iJD - 21086676*(i64)10000000)/1000.0); 001094 }else{ 001095 sqlite3_result_int64(context, x.iJD/1000 - 21086676*(i64)10000); 001096 } 001097 } 001098 } 001099 001100 /* 001101 ** datetime( TIMESTRING, MOD, MOD, ...) 001102 ** 001103 ** Return YYYY-MM-DD HH:MM:SS 001104 */ 001105 static void datetimeFunc( 001106 sqlite3_context *context, 001107 int argc, 001108 sqlite3_value **argv 001109 ){ 001110 DateTime x; 001111 if( isDate(context, argc, argv, &x)==0 ){ 001112 int Y, s, n; 001113 char zBuf[32]; 001114 computeYMD_HMS(&x); 001115 Y = x.Y; 001116 if( Y<0 ) Y = -Y; 001117 zBuf[1] = '0' + (Y/1000)%10; 001118 zBuf[2] = '0' + (Y/100)%10; 001119 zBuf[3] = '0' + (Y/10)%10; 001120 zBuf[4] = '0' + (Y)%10; 001121 zBuf[5] = '-'; 001122 zBuf[6] = '0' + (x.M/10)%10; 001123 zBuf[7] = '0' + (x.M)%10; 001124 zBuf[8] = '-'; 001125 zBuf[9] = '0' + (x.D/10)%10; 001126 zBuf[10] = '0' + (x.D)%10; 001127 zBuf[11] = ' '; 001128 zBuf[12] = '0' + (x.h/10)%10; 001129 zBuf[13] = '0' + (x.h)%10; 001130 zBuf[14] = ':'; 001131 zBuf[15] = '0' + (x.m/10)%10; 001132 zBuf[16] = '0' + (x.m)%10; 001133 zBuf[17] = ':'; 001134 if( x.useSubsec ){ 001135 s = (int)(1000.0*x.s + 0.5); 001136 zBuf[18] = '0' + (s/10000)%10; 001137 zBuf[19] = '0' + (s/1000)%10; 001138 zBuf[20] = '.'; 001139 zBuf[21] = '0' + (s/100)%10; 001140 zBuf[22] = '0' + (s/10)%10; 001141 zBuf[23] = '0' + (s)%10; 001142 zBuf[24] = 0; 001143 n = 24; 001144 }else{ 001145 s = (int)x.s; 001146 zBuf[18] = '0' + (s/10)%10; 001147 zBuf[19] = '0' + (s)%10; 001148 zBuf[20] = 0; 001149 n = 20; 001150 } 001151 if( x.Y<0 ){ 001152 zBuf[0] = '-'; 001153 sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT); 001154 }else{ 001155 sqlite3_result_text(context, &zBuf[1], n-1, SQLITE_TRANSIENT); 001156 } 001157 } 001158 } 001159 001160 /* 001161 ** time( TIMESTRING, MOD, MOD, ...) 001162 ** 001163 ** Return HH:MM:SS 001164 */ 001165 static void timeFunc( 001166 sqlite3_context *context, 001167 int argc, 001168 sqlite3_value **argv 001169 ){ 001170 DateTime x; 001171 if( isDate(context, argc, argv, &x)==0 ){ 001172 int s, n; 001173 char zBuf[16]; 001174 computeHMS(&x); 001175 zBuf[0] = '0' + (x.h/10)%10; 001176 zBuf[1] = '0' + (x.h)%10; 001177 zBuf[2] = ':'; 001178 zBuf[3] = '0' + (x.m/10)%10; 001179 zBuf[4] = '0' + (x.m)%10; 001180 zBuf[5] = ':'; 001181 if( x.useSubsec ){ 001182 s = (int)(1000.0*x.s + 0.5); 001183 zBuf[6] = '0' + (s/10000)%10; 001184 zBuf[7] = '0' + (s/1000)%10; 001185 zBuf[8] = '.'; 001186 zBuf[9] = '0' + (s/100)%10; 001187 zBuf[10] = '0' + (s/10)%10; 001188 zBuf[11] = '0' + (s)%10; 001189 zBuf[12] = 0; 001190 n = 12; 001191 }else{ 001192 s = (int)x.s; 001193 zBuf[6] = '0' + (s/10)%10; 001194 zBuf[7] = '0' + (s)%10; 001195 zBuf[8] = 0; 001196 n = 8; 001197 } 001198 sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT); 001199 } 001200 } 001201 001202 /* 001203 ** date( TIMESTRING, MOD, MOD, ...) 001204 ** 001205 ** Return YYYY-MM-DD 001206 */ 001207 static void dateFunc( 001208 sqlite3_context *context, 001209 int argc, 001210 sqlite3_value **argv 001211 ){ 001212 DateTime x; 001213 if( isDate(context, argc, argv, &x)==0 ){ 001214 int Y; 001215 char zBuf[16]; 001216 computeYMD(&x); 001217 Y = x.Y; 001218 if( Y<0 ) Y = -Y; 001219 zBuf[1] = '0' + (Y/1000)%10; 001220 zBuf[2] = '0' + (Y/100)%10; 001221 zBuf[3] = '0' + (Y/10)%10; 001222 zBuf[4] = '0' + (Y)%10; 001223 zBuf[5] = '-'; 001224 zBuf[6] = '0' + (x.M/10)%10; 001225 zBuf[7] = '0' + (x.M)%10; 001226 zBuf[8] = '-'; 001227 zBuf[9] = '0' + (x.D/10)%10; 001228 zBuf[10] = '0' + (x.D)%10; 001229 zBuf[11] = 0; 001230 if( x.Y<0 ){ 001231 zBuf[0] = '-'; 001232 sqlite3_result_text(context, zBuf, 11, SQLITE_TRANSIENT); 001233 }else{ 001234 sqlite3_result_text(context, &zBuf[1], 10, SQLITE_TRANSIENT); 001235 } 001236 } 001237 } 001238 001239 /* 001240 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...) 001241 ** 001242 ** Return a string described by FORMAT. Conversions as follows: 001243 ** 001244 ** %d day of month 001245 ** %f ** fractional seconds SS.SSS 001246 ** %H hour 00-24 001247 ** %j day of year 000-366 001248 ** %J ** julian day number 001249 ** %m month 01-12 001250 ** %M minute 00-59 001251 ** %s seconds since 1970-01-01 001252 ** %S seconds 00-59 001253 ** %w day of week 0-6 Sunday==0 001254 ** %W week of year 00-53 001255 ** %Y year 0000-9999 001256 ** %% % 001257 */ 001258 static void strftimeFunc( 001259 sqlite3_context *context, 001260 int argc, 001261 sqlite3_value **argv 001262 ){ 001263 DateTime x; 001264 size_t i,j; 001265 sqlite3 *db; 001266 const char *zFmt; 001267 sqlite3_str sRes; 001268 001269 001270 if( argc==0 ) return; 001271 zFmt = (const char*)sqlite3_value_text(argv[0]); 001272 if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return; 001273 db = sqlite3_context_db_handle(context); 001274 sqlite3StrAccumInit(&sRes, 0, 0, 0, db->aLimit[SQLITE_LIMIT_LENGTH]); 001275 001276 computeJD(&x); 001277 computeYMD_HMS(&x); 001278 for(i=j=0; zFmt[i]; i++){ 001279 char cf; 001280 if( zFmt[i]!='%' ) continue; 001281 if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j)); 001282 i++; 001283 j = i + 1; 001284 cf = zFmt[i]; 001285 switch( cf ){ 001286 case 'd': /* Fall thru */ 001287 case 'e': { 001288 sqlite3_str_appendf(&sRes, cf=='d' ? "%02d" : "%2d", x.D); 001289 break; 001290 } 001291 case 'f': { 001292 double s = x.s; 001293 if( s>59.999 ) s = 59.999; 001294 sqlite3_str_appendf(&sRes, "%06.3f", s); 001295 break; 001296 } 001297 case 'F': { 001298 sqlite3_str_appendf(&sRes, "%04d-%02d-%02d", x.Y, x.M, x.D); 001299 break; 001300 } 001301 case 'H': 001302 case 'k': { 001303 sqlite3_str_appendf(&sRes, cf=='H' ? "%02d" : "%2d", x.h); 001304 break; 001305 } 001306 case 'I': /* Fall thru */ 001307 case 'l': { 001308 int h = x.h; 001309 if( h>12 ) h -= 12; 001310 if( h==0 ) h = 12; 001311 sqlite3_str_appendf(&sRes, cf=='I' ? "%02d" : "%2d", h); 001312 break; 001313 } 001314 case 'W': /* Fall thru */ 001315 case 'j': { 001316 int nDay; /* Number of days since 1st day of year */ 001317 DateTime y = x; 001318 y.validJD = 0; 001319 y.M = 1; 001320 y.D = 1; 001321 computeJD(&y); 001322 nDay = (int)((x.iJD-y.iJD+43200000)/86400000); 001323 if( cf=='W' ){ 001324 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */ 001325 wd = (int)(((x.iJD+43200000)/86400000)%7); 001326 sqlite3_str_appendf(&sRes,"%02d",(nDay+7-wd)/7); 001327 }else{ 001328 sqlite3_str_appendf(&sRes,"%03d",nDay+1); 001329 } 001330 break; 001331 } 001332 case 'J': { 001333 sqlite3_str_appendf(&sRes,"%.16g",x.iJD/86400000.0); 001334 break; 001335 } 001336 case 'm': { 001337 sqlite3_str_appendf(&sRes,"%02d",x.M); 001338 break; 001339 } 001340 case 'M': { 001341 sqlite3_str_appendf(&sRes,"%02d",x.m); 001342 break; 001343 } 001344 case 'p': /* Fall thru */ 001345 case 'P': { 001346 if( x.h>=12 ){ 001347 sqlite3_str_append(&sRes, cf=='p' ? "PM" : "pm", 2); 001348 }else{ 001349 sqlite3_str_append(&sRes, cf=='p' ? "AM" : "am", 2); 001350 } 001351 break; 001352 } 001353 case 'R': { 001354 sqlite3_str_appendf(&sRes, "%02d:%02d", x.h, x.m); 001355 break; 001356 } 001357 case 's': { 001358 if( x.useSubsec ){ 001359 sqlite3_str_appendf(&sRes,"%.3f", 001360 (x.iJD - 21086676*(i64)10000000)/1000.0); 001361 }else{ 001362 i64 iS = (i64)(x.iJD/1000 - 21086676*(i64)10000); 001363 sqlite3_str_appendf(&sRes,"%lld",iS); 001364 } 001365 break; 001366 } 001367 case 'S': { 001368 sqlite3_str_appendf(&sRes,"%02d",(int)x.s); 001369 break; 001370 } 001371 case 'T': { 001372 sqlite3_str_appendf(&sRes,"%02d:%02d:%02d", x.h, x.m, (int)x.s); 001373 break; 001374 } 001375 case 'u': /* Fall thru */ 001376 case 'w': { 001377 char c = (char)(((x.iJD+129600000)/86400000) % 7) + '0'; 001378 if( c=='0' && cf=='u' ) c = '7'; 001379 sqlite3_str_appendchar(&sRes, 1, c); 001380 break; 001381 } 001382 case 'Y': { 001383 sqlite3_str_appendf(&sRes,"%04d",x.Y); 001384 break; 001385 } 001386 case '%': { 001387 sqlite3_str_appendchar(&sRes, 1, '%'); 001388 break; 001389 } 001390 default: { 001391 sqlite3_str_reset(&sRes); 001392 return; 001393 } 001394 } 001395 } 001396 if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j)); 001397 sqlite3ResultStrAccum(context, &sRes); 001398 } 001399 001400 /* 001401 ** current_time() 001402 ** 001403 ** This function returns the same value as time('now'). 001404 */ 001405 static void ctimeFunc( 001406 sqlite3_context *context, 001407 int NotUsed, 001408 sqlite3_value **NotUsed2 001409 ){ 001410 UNUSED_PARAMETER2(NotUsed, NotUsed2); 001411 timeFunc(context, 0, 0); 001412 } 001413 001414 /* 001415 ** current_date() 001416 ** 001417 ** This function returns the same value as date('now'). 001418 */ 001419 static void cdateFunc( 001420 sqlite3_context *context, 001421 int NotUsed, 001422 sqlite3_value **NotUsed2 001423 ){ 001424 UNUSED_PARAMETER2(NotUsed, NotUsed2); 001425 dateFunc(context, 0, 0); 001426 } 001427 001428 /* 001429 ** timediff(DATE1, DATE2) 001430 ** 001431 ** Return the amount of time that must be added to DATE2 in order to 001432 ** convert it into DATE2. The time difference format is: 001433 ** 001434 ** +YYYY-MM-DD HH:MM:SS.SSS 001435 ** 001436 ** The initial "+" becomes "-" if DATE1 occurs before DATE2. For 001437 ** date/time values A and B, the following invariant should hold: 001438 ** 001439 ** datetime(A) == (datetime(B, timediff(A,B)) 001440 ** 001441 ** Both DATE arguments must be either a julian day number, or an 001442 ** ISO-8601 string. The unix timestamps are not supported by this 001443 ** routine. 001444 */ 001445 static void timediffFunc( 001446 sqlite3_context *context, 001447 int NotUsed1, 001448 sqlite3_value **argv 001449 ){ 001450 char sign; 001451 int Y, M; 001452 DateTime d1, d2; 001453 sqlite3_str sRes; 001454 UNUSED_PARAMETER(NotUsed1); 001455 if( isDate(context, 1, &argv[0], &d1) ) return; 001456 if( isDate(context, 1, &argv[1], &d2) ) return; 001457 computeYMD_HMS(&d1); 001458 computeYMD_HMS(&d2); 001459 if( d1.iJD>=d2.iJD ){ 001460 sign = '+'; 001461 Y = d1.Y - d2.Y; 001462 if( Y ){ 001463 d2.Y = d1.Y; 001464 d2.validJD = 0; 001465 computeJD(&d2); 001466 } 001467 M = d1.M - d2.M; 001468 if( M<0 ){ 001469 Y--; 001470 M += 12; 001471 } 001472 if( M!=0 ){ 001473 d2.M = d1.M; 001474 d2.validJD = 0; 001475 computeJD(&d2); 001476 } 001477 while( d1.iJD<d2.iJD ){ 001478 M--; 001479 if( M<0 ){ 001480 M = 11; 001481 Y--; 001482 } 001483 d2.M--; 001484 if( d2.M<1 ){ 001485 d2.M = 12; 001486 d2.Y--; 001487 } 001488 d2.validJD = 0; 001489 computeJD(&d2); 001490 } 001491 d1.iJD -= d2.iJD; 001492 d1.iJD += (u64)1486995408 * (u64)100000; 001493 }else /* d1<d2 */{ 001494 sign = '-'; 001495 Y = d2.Y - d1.Y; 001496 if( Y ){ 001497 d2.Y = d1.Y; 001498 d2.validJD = 0; 001499 computeJD(&d2); 001500 } 001501 M = d2.M - d1.M; 001502 if( M<0 ){ 001503 Y--; 001504 M += 12; 001505 } 001506 if( M!=0 ){ 001507 d2.M = d1.M; 001508 d2.validJD = 0; 001509 computeJD(&d2); 001510 } 001511 while( d1.iJD>d2.iJD ){ 001512 M--; 001513 if( M<0 ){ 001514 M = 11; 001515 Y--; 001516 } 001517 d2.M++; 001518 if( d2.M>12 ){ 001519 d2.M = 1; 001520 d2.Y++; 001521 } 001522 d2.validJD = 0; 001523 computeJD(&d2); 001524 } 001525 d1.iJD = d2.iJD - d1.iJD; 001526 d1.iJD += (u64)1486995408 * (u64)100000; 001527 } 001528 d1.validYMD = 0; 001529 d1.validHMS = 0; 001530 d1.validTZ = 0; 001531 computeYMD_HMS(&d1); 001532 sqlite3StrAccumInit(&sRes, 0, 0, 0, 100); 001533 sqlite3_str_appendf(&sRes, "%c%04d-%02d-%02d %02d:%02d:%06.3f", 001534 sign, Y, M, d1.D-1, d1.h, d1.m, d1.s); 001535 sqlite3ResultStrAccum(context, &sRes); 001536 } 001537 001538 001539 /* 001540 ** current_timestamp() 001541 ** 001542 ** This function returns the same value as datetime('now'). 001543 */ 001544 static void ctimestampFunc( 001545 sqlite3_context *context, 001546 int NotUsed, 001547 sqlite3_value **NotUsed2 001548 ){ 001549 UNUSED_PARAMETER2(NotUsed, NotUsed2); 001550 datetimeFunc(context, 0, 0); 001551 } 001552 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */ 001553 001554 #ifdef SQLITE_OMIT_DATETIME_FUNCS 001555 /* 001556 ** If the library is compiled to omit the full-scale date and time 001557 ** handling (to get a smaller binary), the following minimal version 001558 ** of the functions current_time(), current_date() and current_timestamp() 001559 ** are included instead. This is to support column declarations that 001560 ** include "DEFAULT CURRENT_TIME" etc. 001561 ** 001562 ** This function uses the C-library functions time(), gmtime() 001563 ** and strftime(). The format string to pass to strftime() is supplied 001564 ** as the user-data for the function. 001565 */ 001566 static void currentTimeFunc( 001567 sqlite3_context *context, 001568 int argc, 001569 sqlite3_value **argv 001570 ){ 001571 time_t t; 001572 char *zFormat = (char *)sqlite3_user_data(context); 001573 sqlite3_int64 iT; 001574 struct tm *pTm; 001575 struct tm sNow; 001576 char zBuf[20]; 001577 001578 UNUSED_PARAMETER(argc); 001579 UNUSED_PARAMETER(argv); 001580 001581 iT = sqlite3StmtCurrentTime(context); 001582 if( iT<=0 ) return; 001583 t = iT/1000 - 10000*(sqlite3_int64)21086676; 001584 #if HAVE_GMTIME_R 001585 pTm = gmtime_r(&t, &sNow); 001586 #else 001587 sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN)); 001588 pTm = gmtime(&t); 001589 if( pTm ) memcpy(&sNow, pTm, sizeof(sNow)); 001590 sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN)); 001591 #endif 001592 if( pTm ){ 001593 strftime(zBuf, 20, zFormat, &sNow); 001594 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 001595 } 001596 } 001597 #endif 001598 001599 /* 001600 ** This function registered all of the above C functions as SQL 001601 ** functions. This should be the only routine in this file with 001602 ** external linkage. 001603 */ 001604 void sqlite3RegisterDateTimeFunctions(void){ 001605 static FuncDef aDateTimeFuncs[] = { 001606 #ifndef SQLITE_OMIT_DATETIME_FUNCS 001607 PURE_DATE(julianday, -1, 0, 0, juliandayFunc ), 001608 PURE_DATE(unixepoch, -1, 0, 0, unixepochFunc ), 001609 PURE_DATE(date, -1, 0, 0, dateFunc ), 001610 PURE_DATE(time, -1, 0, 0, timeFunc ), 001611 PURE_DATE(datetime, -1, 0, 0, datetimeFunc ), 001612 PURE_DATE(strftime, -1, 0, 0, strftimeFunc ), 001613 PURE_DATE(timediff, 2, 0, 0, timediffFunc ), 001614 DFUNCTION(current_time, 0, 0, 0, ctimeFunc ), 001615 DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc), 001616 DFUNCTION(current_date, 0, 0, 0, cdateFunc ), 001617 #else 001618 STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc), 001619 STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc), 001620 STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc), 001621 #endif 001622 }; 001623 sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs)); 001624 }