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 }; 000081 000082 000083 /* 000084 ** Convert zDate into one or more integers according to the conversion 000085 ** specifier zFormat. 000086 ** 000087 ** zFormat[] contains 4 characters for each integer converted, except for 000088 ** the last integer which is specified by three characters. The meaning 000089 ** of a four-character format specifiers ABCD is: 000090 ** 000091 ** A: number of digits to convert. Always "2" or "4". 000092 ** B: minimum value. Always "0" or "1". 000093 ** C: maximum value, decoded as: 000094 ** a: 12 000095 ** b: 14 000096 ** c: 24 000097 ** d: 31 000098 ** e: 59 000099 ** f: 9999 000100 ** D: the separator character, or \000 to indicate this is the 000101 ** last number to convert. 000102 ** 000103 ** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would 000104 ** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-". 000105 ** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates 000106 ** the 2-digit day which is the last integer in the set. 000107 ** 000108 ** The function returns the number of successful conversions. 000109 */ 000110 static int getDigits(const char *zDate, const char *zFormat, ...){ 000111 /* The aMx[] array translates the 3rd character of each format 000112 ** spec into a max size: a b c d e f */ 000113 static const u16 aMx[] = { 12, 14, 24, 31, 59, 9999 }; 000114 va_list ap; 000115 int cnt = 0; 000116 char nextC; 000117 va_start(ap, zFormat); 000118 do{ 000119 char N = zFormat[0] - '0'; 000120 char min = zFormat[1] - '0'; 000121 int val = 0; 000122 u16 max; 000123 000124 assert( zFormat[2]>='a' && zFormat[2]<='f' ); 000125 max = aMx[zFormat[2] - 'a']; 000126 nextC = zFormat[3]; 000127 val = 0; 000128 while( N-- ){ 000129 if( !sqlite3Isdigit(*zDate) ){ 000130 goto end_getDigits; 000131 } 000132 val = val*10 + *zDate - '0'; 000133 zDate++; 000134 } 000135 if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){ 000136 goto end_getDigits; 000137 } 000138 *va_arg(ap,int*) = val; 000139 zDate++; 000140 cnt++; 000141 zFormat += 4; 000142 }while( nextC ); 000143 end_getDigits: 000144 va_end(ap); 000145 return cnt; 000146 } 000147 000148 /* 000149 ** Parse a timezone extension on the end of a date-time. 000150 ** The extension is of the form: 000151 ** 000152 ** (+/-)HH:MM 000153 ** 000154 ** Or the "zulu" notation: 000155 ** 000156 ** Z 000157 ** 000158 ** If the parse is successful, write the number of minutes 000159 ** of change in p->tz and return 0. If a parser error occurs, 000160 ** return non-zero. 000161 ** 000162 ** A missing specifier is not considered an error. 000163 */ 000164 static int parseTimezone(const char *zDate, DateTime *p){ 000165 int sgn = 0; 000166 int nHr, nMn; 000167 int c; 000168 while( sqlite3Isspace(*zDate) ){ zDate++; } 000169 p->tz = 0; 000170 c = *zDate; 000171 if( c=='-' ){ 000172 sgn = -1; 000173 }else if( c=='+' ){ 000174 sgn = +1; 000175 }else if( c=='Z' || c=='z' ){ 000176 zDate++; 000177 goto zulu_time; 000178 }else{ 000179 return c!=0; 000180 } 000181 zDate++; 000182 if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){ 000183 return 1; 000184 } 000185 zDate += 5; 000186 p->tz = sgn*(nMn + nHr*60); 000187 zulu_time: 000188 while( sqlite3Isspace(*zDate) ){ zDate++; } 000189 p->tzSet = 1; 000190 return *zDate!=0; 000191 } 000192 000193 /* 000194 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF. 000195 ** The HH, MM, and SS must each be exactly 2 digits. The 000196 ** fractional seconds FFFF can be one or more digits. 000197 ** 000198 ** Return 1 if there is a parsing error and 0 on success. 000199 */ 000200 static int parseHhMmSs(const char *zDate, DateTime *p){ 000201 int h, m, s; 000202 double ms = 0.0; 000203 if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){ 000204 return 1; 000205 } 000206 zDate += 5; 000207 if( *zDate==':' ){ 000208 zDate++; 000209 if( getDigits(zDate, "20e", &s)!=1 ){ 000210 return 1; 000211 } 000212 zDate += 2; 000213 if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){ 000214 double rScale = 1.0; 000215 zDate++; 000216 while( sqlite3Isdigit(*zDate) ){ 000217 ms = ms*10.0 + *zDate - '0'; 000218 rScale *= 10.0; 000219 zDate++; 000220 } 000221 ms /= rScale; 000222 } 000223 }else{ 000224 s = 0; 000225 } 000226 p->validJD = 0; 000227 p->rawS = 0; 000228 p->validHMS = 1; 000229 p->h = h; 000230 p->m = m; 000231 p->s = s + ms; 000232 if( parseTimezone(zDate, p) ) return 1; 000233 p->validTZ = (p->tz!=0)?1:0; 000234 return 0; 000235 } 000236 000237 /* 000238 ** Put the DateTime object into its error state. 000239 */ 000240 static void datetimeError(DateTime *p){ 000241 memset(p, 0, sizeof(*p)); 000242 p->isError = 1; 000243 } 000244 000245 /* 000246 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume 000247 ** that the YYYY-MM-DD is according to the Gregorian calendar. 000248 ** 000249 ** Reference: Meeus page 61 000250 */ 000251 static void computeJD(DateTime *p){ 000252 int Y, M, D, A, B, X1, X2; 000253 000254 if( p->validJD ) return; 000255 if( p->validYMD ){ 000256 Y = p->Y; 000257 M = p->M; 000258 D = p->D; 000259 }else{ 000260 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */ 000261 M = 1; 000262 D = 1; 000263 } 000264 if( Y<-4713 || Y>9999 || p->rawS ){ 000265 datetimeError(p); 000266 return; 000267 } 000268 if( M<=2 ){ 000269 Y--; 000270 M += 12; 000271 } 000272 A = Y/100; 000273 B = 2 - A + (A/4); 000274 X1 = 36525*(Y+4716)/100; 000275 X2 = 306001*(M+1)/10000; 000276 p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000); 000277 p->validJD = 1; 000278 if( p->validHMS ){ 000279 p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000); 000280 if( p->validTZ ){ 000281 p->iJD -= p->tz*60000; 000282 p->validYMD = 0; 000283 p->validHMS = 0; 000284 p->validTZ = 0; 000285 } 000286 } 000287 } 000288 000289 /* 000290 ** Parse dates of the form 000291 ** 000292 ** YYYY-MM-DD HH:MM:SS.FFF 000293 ** YYYY-MM-DD HH:MM:SS 000294 ** YYYY-MM-DD HH:MM 000295 ** YYYY-MM-DD 000296 ** 000297 ** Write the result into the DateTime structure and return 0 000298 ** on success and 1 if the input string is not a well-formed 000299 ** date. 000300 */ 000301 static int parseYyyyMmDd(const char *zDate, DateTime *p){ 000302 int Y, M, D, neg; 000303 000304 if( zDate[0]=='-' ){ 000305 zDate++; 000306 neg = 1; 000307 }else{ 000308 neg = 0; 000309 } 000310 if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){ 000311 return 1; 000312 } 000313 zDate += 10; 000314 while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; } 000315 if( parseHhMmSs(zDate, p)==0 ){ 000316 /* We got the time */ 000317 }else if( *zDate==0 ){ 000318 p->validHMS = 0; 000319 }else{ 000320 return 1; 000321 } 000322 p->validJD = 0; 000323 p->validYMD = 1; 000324 p->Y = neg ? -Y : Y; 000325 p->M = M; 000326 p->D = D; 000327 if( p->validTZ ){ 000328 computeJD(p); 000329 } 000330 return 0; 000331 } 000332 000333 /* 000334 ** Set the time to the current time reported by the VFS. 000335 ** 000336 ** Return the number of errors. 000337 */ 000338 static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){ 000339 p->iJD = sqlite3StmtCurrentTime(context); 000340 if( p->iJD>0 ){ 000341 p->validJD = 1; 000342 return 0; 000343 }else{ 000344 return 1; 000345 } 000346 } 000347 000348 /* 000349 ** Input "r" is a numeric quantity which might be a julian day number, 000350 ** or the number of seconds since 1970. If the value if r is within 000351 ** range of a julian day number, install it as such and set validJD. 000352 ** If the value is a valid unix timestamp, put it in p->s and set p->rawS. 000353 */ 000354 static void setRawDateNumber(DateTime *p, double r){ 000355 p->s = r; 000356 p->rawS = 1; 000357 if( r>=0.0 && r<5373484.5 ){ 000358 p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5); 000359 p->validJD = 1; 000360 } 000361 } 000362 000363 /* 000364 ** Attempt to parse the given string into a julian day number. Return 000365 ** the number of errors. 000366 ** 000367 ** The following are acceptable forms for the input string: 000368 ** 000369 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM 000370 ** DDDD.DD 000371 ** now 000372 ** 000373 ** In the first form, the +/-HH:MM is always optional. The fractional 000374 ** seconds extension (the ".FFF") is optional. The seconds portion 000375 ** (":SS.FFF") is option. The year and date can be omitted as long 000376 ** as there is a time string. The time string can be omitted as long 000377 ** as there is a year and date. 000378 */ 000379 static int parseDateOrTime( 000380 sqlite3_context *context, 000381 const char *zDate, 000382 DateTime *p 000383 ){ 000384 double r; 000385 if( parseYyyyMmDd(zDate,p)==0 ){ 000386 return 0; 000387 }else if( parseHhMmSs(zDate, p)==0 ){ 000388 return 0; 000389 }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){ 000390 return setDateTimeToCurrent(context, p); 000391 }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){ 000392 setRawDateNumber(p, r); 000393 return 0; 000394 } 000395 return 1; 000396 } 000397 000398 /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999. 000399 ** Multiplying this by 86400000 gives 464269060799999 as the maximum value 000400 ** for DateTime.iJD. 000401 ** 000402 ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with 000403 ** such a large integer literal, so we have to encode it. 000404 */ 000405 #define INT_464269060799999 ((((i64)0x1a640)<<32)|0x1072fdff) 000406 000407 /* 000408 ** Return TRUE if the given julian day number is within range. 000409 ** 000410 ** The input is the JulianDay times 86400000. 000411 */ 000412 static int validJulianDay(sqlite3_int64 iJD){ 000413 return iJD>=0 && iJD<=INT_464269060799999; 000414 } 000415 000416 /* 000417 ** Compute the Year, Month, and Day from the julian day number. 000418 */ 000419 static void computeYMD(DateTime *p){ 000420 int Z, A, B, C, D, E, X1; 000421 if( p->validYMD ) return; 000422 if( !p->validJD ){ 000423 p->Y = 2000; 000424 p->M = 1; 000425 p->D = 1; 000426 }else if( !validJulianDay(p->iJD) ){ 000427 datetimeError(p); 000428 return; 000429 }else{ 000430 Z = (int)((p->iJD + 43200000)/86400000); 000431 A = (int)((Z - 1867216.25)/36524.25); 000432 A = Z + 1 + A - (A/4); 000433 B = A + 1524; 000434 C = (int)((B - 122.1)/365.25); 000435 D = (36525*(C&32767))/100; 000436 E = (int)((B-D)/30.6001); 000437 X1 = (int)(30.6001*E); 000438 p->D = B - D - X1; 000439 p->M = E<14 ? E-1 : E-13; 000440 p->Y = p->M>2 ? C - 4716 : C - 4715; 000441 } 000442 p->validYMD = 1; 000443 } 000444 000445 /* 000446 ** Compute the Hour, Minute, and Seconds from the julian day number. 000447 */ 000448 static void computeHMS(DateTime *p){ 000449 int s; 000450 if( p->validHMS ) return; 000451 computeJD(p); 000452 s = (int)((p->iJD + 43200000) % 86400000); 000453 p->s = s/1000.0; 000454 s = (int)p->s; 000455 p->s -= s; 000456 p->h = s/3600; 000457 s -= p->h*3600; 000458 p->m = s/60; 000459 p->s += s - p->m*60; 000460 p->rawS = 0; 000461 p->validHMS = 1; 000462 } 000463 000464 /* 000465 ** Compute both YMD and HMS 000466 */ 000467 static void computeYMD_HMS(DateTime *p){ 000468 computeYMD(p); 000469 computeHMS(p); 000470 } 000471 000472 /* 000473 ** Clear the YMD and HMS and the TZ 000474 */ 000475 static void clearYMD_HMS_TZ(DateTime *p){ 000476 p->validYMD = 0; 000477 p->validHMS = 0; 000478 p->validTZ = 0; 000479 } 000480 000481 #ifndef SQLITE_OMIT_LOCALTIME 000482 /* 000483 ** On recent Windows platforms, the localtime_s() function is available 000484 ** as part of the "Secure CRT". It is essentially equivalent to 000485 ** localtime_r() available under most POSIX platforms, except that the 000486 ** order of the parameters is reversed. 000487 ** 000488 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx. 000489 ** 000490 ** If the user has not indicated to use localtime_r() or localtime_s() 000491 ** already, check for an MSVC build environment that provides 000492 ** localtime_s(). 000493 */ 000494 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \ 000495 && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE) 000496 #undef HAVE_LOCALTIME_S 000497 #define HAVE_LOCALTIME_S 1 000498 #endif 000499 000500 /* 000501 ** The following routine implements the rough equivalent of localtime_r() 000502 ** using whatever operating-system specific localtime facility that 000503 ** is available. This routine returns 0 on success and 000504 ** non-zero on any kind of error. 000505 ** 000506 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is true then this 000507 ** routine will always fail. 000508 ** 000509 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C 000510 ** library function localtime_r() is used to assist in the calculation of 000511 ** local time. 000512 */ 000513 static int osLocaltime(time_t *t, struct tm *pTm){ 000514 int rc; 000515 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S 000516 struct tm *pX; 000517 #if SQLITE_THREADSAFE>0 000518 sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER); 000519 #endif 000520 sqlite3_mutex_enter(mutex); 000521 pX = localtime(t); 000522 #ifndef SQLITE_UNTESTABLE 000523 if( sqlite3GlobalConfig.bLocaltimeFault ) pX = 0; 000524 #endif 000525 if( pX ) *pTm = *pX; 000526 sqlite3_mutex_leave(mutex); 000527 rc = pX==0; 000528 #else 000529 #ifndef SQLITE_UNTESTABLE 000530 if( sqlite3GlobalConfig.bLocaltimeFault ) return 1; 000531 #endif 000532 #if HAVE_LOCALTIME_R 000533 rc = localtime_r(t, pTm)==0; 000534 #else 000535 rc = localtime_s(pTm, t); 000536 #endif /* HAVE_LOCALTIME_R */ 000537 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */ 000538 return rc; 000539 } 000540 #endif /* SQLITE_OMIT_LOCALTIME */ 000541 000542 000543 #ifndef SQLITE_OMIT_LOCALTIME 000544 /* 000545 ** Compute the difference (in milliseconds) between localtime and UTC 000546 ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs, 000547 ** return this value and set *pRc to SQLITE_OK. 000548 ** 000549 ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value 000550 ** is undefined in this case. 000551 */ 000552 static sqlite3_int64 localtimeOffset( 000553 DateTime *p, /* Date at which to calculate offset */ 000554 sqlite3_context *pCtx, /* Write error here if one occurs */ 000555 int *pRc /* OUT: Error code. SQLITE_OK or ERROR */ 000556 ){ 000557 DateTime x, y; 000558 time_t t; 000559 struct tm sLocal; 000560 000561 /* Initialize the contents of sLocal to avoid a compiler warning. */ 000562 memset(&sLocal, 0, sizeof(sLocal)); 000563 000564 x = *p; 000565 computeYMD_HMS(&x); 000566 if( x.Y<1971 || x.Y>=2038 ){ 000567 /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only 000568 ** works for years between 1970 and 2037. For dates outside this range, 000569 ** SQLite attempts to map the year into an equivalent year within this 000570 ** range, do the calculation, then map the year back. 000571 */ 000572 x.Y = 2000; 000573 x.M = 1; 000574 x.D = 1; 000575 x.h = 0; 000576 x.m = 0; 000577 x.s = 0.0; 000578 } else { 000579 int s = (int)(x.s + 0.5); 000580 x.s = s; 000581 } 000582 x.tz = 0; 000583 x.validJD = 0; 000584 computeJD(&x); 000585 t = (time_t)(x.iJD/1000 - 21086676*(i64)10000); 000586 if( osLocaltime(&t, &sLocal) ){ 000587 sqlite3_result_error(pCtx, "local time unavailable", -1); 000588 *pRc = SQLITE_ERROR; 000589 return 0; 000590 } 000591 y.Y = sLocal.tm_year + 1900; 000592 y.M = sLocal.tm_mon + 1; 000593 y.D = sLocal.tm_mday; 000594 y.h = sLocal.tm_hour; 000595 y.m = sLocal.tm_min; 000596 y.s = sLocal.tm_sec; 000597 y.validYMD = 1; 000598 y.validHMS = 1; 000599 y.validJD = 0; 000600 y.rawS = 0; 000601 y.validTZ = 0; 000602 y.isError = 0; 000603 computeJD(&y); 000604 *pRc = SQLITE_OK; 000605 return y.iJD - x.iJD; 000606 } 000607 #endif /* SQLITE_OMIT_LOCALTIME */ 000608 000609 /* 000610 ** The following table defines various date transformations of the form 000611 ** 000612 ** 'NNN days' 000613 ** 000614 ** Where NNN is an arbitrary floating-point number and "days" can be one 000615 ** of several units of time. 000616 */ 000617 static const struct { 000618 u8 eType; /* Transformation type code */ 000619 u8 nName; /* Length of th name */ 000620 char *zName; /* Name of the transformation */ 000621 double rLimit; /* Maximum NNN value for this transform */ 000622 double rXform; /* Constant used for this transform */ 000623 } aXformType[] = { 000624 { 0, 6, "second", 464269060800.0, 86400000.0/(24.0*60.0*60.0) }, 000625 { 0, 6, "minute", 7737817680.0, 86400000.0/(24.0*60.0) }, 000626 { 0, 4, "hour", 128963628.0, 86400000.0/24.0 }, 000627 { 0, 3, "day", 5373485.0, 86400000.0 }, 000628 { 1, 5, "month", 176546.0, 30.0*86400000.0 }, 000629 { 2, 4, "year", 14713.0, 365.0*86400000.0 }, 000630 }; 000631 000632 /* 000633 ** Process a modifier to a date-time stamp. The modifiers are 000634 ** as follows: 000635 ** 000636 ** NNN days 000637 ** NNN hours 000638 ** NNN minutes 000639 ** NNN.NNNN seconds 000640 ** NNN months 000641 ** NNN years 000642 ** start of month 000643 ** start of year 000644 ** start of week 000645 ** start of day 000646 ** weekday N 000647 ** unixepoch 000648 ** localtime 000649 ** utc 000650 ** 000651 ** Return 0 on success and 1 if there is any kind of error. If the error 000652 ** is in a system call (i.e. localtime()), then an error message is written 000653 ** to context pCtx. If the error is an unrecognized modifier, no error is 000654 ** written to pCtx. 000655 */ 000656 static int parseModifier( 000657 sqlite3_context *pCtx, /* Function context */ 000658 const char *z, /* The text of the modifier */ 000659 int n, /* Length of zMod in bytes */ 000660 DateTime *p /* The date/time value to be modified */ 000661 ){ 000662 int rc = 1; 000663 double r; 000664 switch(sqlite3UpperToLower[(u8)z[0]] ){ 000665 #ifndef SQLITE_OMIT_LOCALTIME 000666 case 'l': { 000667 /* localtime 000668 ** 000669 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to 000670 ** show local time. 000671 */ 000672 if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){ 000673 computeJD(p); 000674 p->iJD += localtimeOffset(p, pCtx, &rc); 000675 clearYMD_HMS_TZ(p); 000676 } 000677 break; 000678 } 000679 #endif 000680 case 'u': { 000681 /* 000682 ** unixepoch 000683 ** 000684 ** Treat the current value of p->s as the number of 000685 ** seconds since 1970. Convert to a real julian day number. 000686 */ 000687 if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){ 000688 r = p->s*1000.0 + 210866760000000.0; 000689 if( r>=0.0 && r<464269060800000.0 ){ 000690 clearYMD_HMS_TZ(p); 000691 p->iJD = (sqlite3_int64)r; 000692 p->validJD = 1; 000693 p->rawS = 0; 000694 rc = 0; 000695 } 000696 } 000697 #ifndef SQLITE_OMIT_LOCALTIME 000698 else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){ 000699 if( p->tzSet==0 ){ 000700 sqlite3_int64 c1; 000701 computeJD(p); 000702 c1 = localtimeOffset(p, pCtx, &rc); 000703 if( rc==SQLITE_OK ){ 000704 p->iJD -= c1; 000705 clearYMD_HMS_TZ(p); 000706 p->iJD += c1 - localtimeOffset(p, pCtx, &rc); 000707 } 000708 p->tzSet = 1; 000709 }else{ 000710 rc = SQLITE_OK; 000711 } 000712 } 000713 #endif 000714 break; 000715 } 000716 case 'w': { 000717 /* 000718 ** weekday N 000719 ** 000720 ** Move the date to the same time on the next occurrence of 000721 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the 000722 ** date is already on the appropriate weekday, this is a no-op. 000723 */ 000724 if( sqlite3_strnicmp(z, "weekday ", 8)==0 000725 && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0 000726 && (n=(int)r)==r && n>=0 && r<7 ){ 000727 sqlite3_int64 Z; 000728 computeYMD_HMS(p); 000729 p->validTZ = 0; 000730 p->validJD = 0; 000731 computeJD(p); 000732 Z = ((p->iJD + 129600000)/86400000) % 7; 000733 if( Z>n ) Z -= 7; 000734 p->iJD += (n - Z)*86400000; 000735 clearYMD_HMS_TZ(p); 000736 rc = 0; 000737 } 000738 break; 000739 } 000740 case 's': { 000741 /* 000742 ** start of TTTTT 000743 ** 000744 ** Move the date backwards to the beginning of the current day, 000745 ** or month or year. 000746 */ 000747 if( sqlite3_strnicmp(z, "start of ", 9)!=0 ) break; 000748 if( !p->validJD && !p->validYMD && !p->validHMS ) break; 000749 z += 9; 000750 computeYMD(p); 000751 p->validHMS = 1; 000752 p->h = p->m = 0; 000753 p->s = 0.0; 000754 p->rawS = 0; 000755 p->validTZ = 0; 000756 p->validJD = 0; 000757 if( sqlite3_stricmp(z,"month")==0 ){ 000758 p->D = 1; 000759 rc = 0; 000760 }else if( sqlite3_stricmp(z,"year")==0 ){ 000761 p->M = 1; 000762 p->D = 1; 000763 rc = 0; 000764 }else if( sqlite3_stricmp(z,"day")==0 ){ 000765 rc = 0; 000766 } 000767 break; 000768 } 000769 case '+': 000770 case '-': 000771 case '0': 000772 case '1': 000773 case '2': 000774 case '3': 000775 case '4': 000776 case '5': 000777 case '6': 000778 case '7': 000779 case '8': 000780 case '9': { 000781 double rRounder; 000782 int i; 000783 for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){} 000784 if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){ 000785 rc = 1; 000786 break; 000787 } 000788 if( z[n]==':' ){ 000789 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the 000790 ** specified number of hours, minutes, seconds, and fractional seconds 000791 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be 000792 ** omitted. 000793 */ 000794 const char *z2 = z; 000795 DateTime tx; 000796 sqlite3_int64 day; 000797 if( !sqlite3Isdigit(*z2) ) z2++; 000798 memset(&tx, 0, sizeof(tx)); 000799 if( parseHhMmSs(z2, &tx) ) break; 000800 computeJD(&tx); 000801 tx.iJD -= 43200000; 000802 day = tx.iJD/86400000; 000803 tx.iJD -= day*86400000; 000804 if( z[0]=='-' ) tx.iJD = -tx.iJD; 000805 computeJD(p); 000806 clearYMD_HMS_TZ(p); 000807 p->iJD += tx.iJD; 000808 rc = 0; 000809 break; 000810 } 000811 000812 /* If control reaches this point, it means the transformation is 000813 ** one of the forms like "+NNN days". */ 000814 z += n; 000815 while( sqlite3Isspace(*z) ) z++; 000816 n = sqlite3Strlen30(z); 000817 if( n>10 || n<3 ) break; 000818 if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--; 000819 computeJD(p); 000820 rc = 1; 000821 rRounder = r<0 ? -0.5 : +0.5; 000822 for(i=0; i<ArraySize(aXformType); i++){ 000823 if( aXformType[i].nName==n 000824 && sqlite3_strnicmp(aXformType[i].zName, z, n)==0 000825 && r>-aXformType[i].rLimit && r<aXformType[i].rLimit 000826 ){ 000827 switch( aXformType[i].eType ){ 000828 case 1: { /* Special processing to add months */ 000829 int x; 000830 computeYMD_HMS(p); 000831 p->M += (int)r; 000832 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; 000833 p->Y += x; 000834 p->M -= x*12; 000835 p->validJD = 0; 000836 r -= (int)r; 000837 break; 000838 } 000839 case 2: { /* Special processing to add years */ 000840 int y = (int)r; 000841 computeYMD_HMS(p); 000842 p->Y += y; 000843 p->validJD = 0; 000844 r -= (int)r; 000845 break; 000846 } 000847 } 000848 computeJD(p); 000849 p->iJD += (sqlite3_int64)(r*aXformType[i].rXform + rRounder); 000850 rc = 0; 000851 break; 000852 } 000853 } 000854 clearYMD_HMS_TZ(p); 000855 break; 000856 } 000857 default: { 000858 break; 000859 } 000860 } 000861 return rc; 000862 } 000863 000864 /* 000865 ** Process time function arguments. argv[0] is a date-time stamp. 000866 ** argv[1] and following are modifiers. Parse them all and write 000867 ** the resulting time into the DateTime structure p. Return 0 000868 ** on success and 1 if there are any errors. 000869 ** 000870 ** If there are zero parameters (if even argv[0] is undefined) 000871 ** then assume a default value of "now" for argv[0]. 000872 */ 000873 static int isDate( 000874 sqlite3_context *context, 000875 int argc, 000876 sqlite3_value **argv, 000877 DateTime *p 000878 ){ 000879 int i, n; 000880 const unsigned char *z; 000881 int eType; 000882 memset(p, 0, sizeof(*p)); 000883 if( argc==0 ){ 000884 return setDateTimeToCurrent(context, p); 000885 } 000886 if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT 000887 || eType==SQLITE_INTEGER ){ 000888 setRawDateNumber(p, sqlite3_value_double(argv[0])); 000889 }else{ 000890 z = sqlite3_value_text(argv[0]); 000891 if( !z || parseDateOrTime(context, (char*)z, p) ){ 000892 return 1; 000893 } 000894 } 000895 for(i=1; i<argc; i++){ 000896 z = sqlite3_value_text(argv[i]); 000897 n = sqlite3_value_bytes(argv[i]); 000898 if( z==0 || parseModifier(context, (char*)z, n, p) ) return 1; 000899 } 000900 computeJD(p); 000901 if( p->isError || !validJulianDay(p->iJD) ) return 1; 000902 return 0; 000903 } 000904 000905 000906 /* 000907 ** The following routines implement the various date and time functions 000908 ** of SQLite. 000909 */ 000910 000911 /* 000912 ** julianday( TIMESTRING, MOD, MOD, ...) 000913 ** 000914 ** Return the julian day number of the date specified in the arguments 000915 */ 000916 static void juliandayFunc( 000917 sqlite3_context *context, 000918 int argc, 000919 sqlite3_value **argv 000920 ){ 000921 DateTime x; 000922 if( isDate(context, argc, argv, &x)==0 ){ 000923 computeJD(&x); 000924 sqlite3_result_double(context, x.iJD/86400000.0); 000925 } 000926 } 000927 000928 /* 000929 ** datetime( TIMESTRING, MOD, MOD, ...) 000930 ** 000931 ** Return YYYY-MM-DD HH:MM:SS 000932 */ 000933 static void datetimeFunc( 000934 sqlite3_context *context, 000935 int argc, 000936 sqlite3_value **argv 000937 ){ 000938 DateTime x; 000939 if( isDate(context, argc, argv, &x)==0 ){ 000940 char zBuf[100]; 000941 computeYMD_HMS(&x); 000942 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d", 000943 x.Y, x.M, x.D, x.h, x.m, (int)(x.s)); 000944 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 000945 } 000946 } 000947 000948 /* 000949 ** time( TIMESTRING, MOD, MOD, ...) 000950 ** 000951 ** Return HH:MM:SS 000952 */ 000953 static void timeFunc( 000954 sqlite3_context *context, 000955 int argc, 000956 sqlite3_value **argv 000957 ){ 000958 DateTime x; 000959 if( isDate(context, argc, argv, &x)==0 ){ 000960 char zBuf[100]; 000961 computeHMS(&x); 000962 sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s); 000963 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 000964 } 000965 } 000966 000967 /* 000968 ** date( TIMESTRING, MOD, MOD, ...) 000969 ** 000970 ** Return YYYY-MM-DD 000971 */ 000972 static void dateFunc( 000973 sqlite3_context *context, 000974 int argc, 000975 sqlite3_value **argv 000976 ){ 000977 DateTime x; 000978 if( isDate(context, argc, argv, &x)==0 ){ 000979 char zBuf[100]; 000980 computeYMD(&x); 000981 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D); 000982 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 000983 } 000984 } 000985 000986 /* 000987 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...) 000988 ** 000989 ** Return a string described by FORMAT. Conversions as follows: 000990 ** 000991 ** %d day of month 000992 ** %f ** fractional seconds SS.SSS 000993 ** %H hour 00-24 000994 ** %j day of year 000-366 000995 ** %J ** julian day number 000996 ** %m month 01-12 000997 ** %M minute 00-59 000998 ** %s seconds since 1970-01-01 000999 ** %S seconds 00-59 001000 ** %w day of week 0-6 sunday==0 001001 ** %W week of year 00-53 001002 ** %Y year 0000-9999 001003 ** %% % 001004 */ 001005 static void strftimeFunc( 001006 sqlite3_context *context, 001007 int argc, 001008 sqlite3_value **argv 001009 ){ 001010 DateTime x; 001011 u64 n; 001012 size_t i,j; 001013 char *z; 001014 sqlite3 *db; 001015 const char *zFmt; 001016 char zBuf[100]; 001017 if( argc==0 ) return; 001018 zFmt = (const char*)sqlite3_value_text(argv[0]); 001019 if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return; 001020 db = sqlite3_context_db_handle(context); 001021 for(i=0, n=1; zFmt[i]; i++, n++){ 001022 if( zFmt[i]=='%' ){ 001023 switch( zFmt[i+1] ){ 001024 case 'd': 001025 case 'H': 001026 case 'm': 001027 case 'M': 001028 case 'S': 001029 case 'W': 001030 n++; 001031 /* fall thru */ 001032 case 'w': 001033 case '%': 001034 break; 001035 case 'f': 001036 n += 8; 001037 break; 001038 case 'j': 001039 n += 3; 001040 break; 001041 case 'Y': 001042 n += 8; 001043 break; 001044 case 's': 001045 case 'J': 001046 n += 50; 001047 break; 001048 default: 001049 return; /* ERROR. return a NULL */ 001050 } 001051 i++; 001052 } 001053 } 001054 testcase( n==sizeof(zBuf)-1 ); 001055 testcase( n==sizeof(zBuf) ); 001056 testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH]+1 ); 001057 testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ); 001058 if( n<sizeof(zBuf) ){ 001059 z = zBuf; 001060 }else if( n>(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ){ 001061 sqlite3_result_error_toobig(context); 001062 return; 001063 }else{ 001064 z = sqlite3DbMallocRawNN(db, (int)n); 001065 if( z==0 ){ 001066 sqlite3_result_error_nomem(context); 001067 return; 001068 } 001069 } 001070 computeJD(&x); 001071 computeYMD_HMS(&x); 001072 for(i=j=0; zFmt[i]; i++){ 001073 if( zFmt[i]!='%' ){ 001074 z[j++] = zFmt[i]; 001075 }else{ 001076 i++; 001077 switch( zFmt[i] ){ 001078 case 'd': sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break; 001079 case 'f': { 001080 double s = x.s; 001081 if( s>59.999 ) s = 59.999; 001082 sqlite3_snprintf(7, &z[j],"%06.3f", s); 001083 j += sqlite3Strlen30(&z[j]); 001084 break; 001085 } 001086 case 'H': sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break; 001087 case 'W': /* Fall thru */ 001088 case 'j': { 001089 int nDay; /* Number of days since 1st day of year */ 001090 DateTime y = x; 001091 y.validJD = 0; 001092 y.M = 1; 001093 y.D = 1; 001094 computeJD(&y); 001095 nDay = (int)((x.iJD-y.iJD+43200000)/86400000); 001096 if( zFmt[i]=='W' ){ 001097 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */ 001098 wd = (int)(((x.iJD+43200000)/86400000)%7); 001099 sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7); 001100 j += 2; 001101 }else{ 001102 sqlite3_snprintf(4, &z[j],"%03d",nDay+1); 001103 j += 3; 001104 } 001105 break; 001106 } 001107 case 'J': { 001108 sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0); 001109 j+=sqlite3Strlen30(&z[j]); 001110 break; 001111 } 001112 case 'm': sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break; 001113 case 'M': sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break; 001114 case 's': { 001115 sqlite3_snprintf(30,&z[j],"%lld", 001116 (i64)(x.iJD/1000 - 21086676*(i64)10000)); 001117 j += sqlite3Strlen30(&z[j]); 001118 break; 001119 } 001120 case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break; 001121 case 'w': { 001122 z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0'; 001123 break; 001124 } 001125 case 'Y': { 001126 sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]); 001127 break; 001128 } 001129 default: z[j++] = '%'; break; 001130 } 001131 } 001132 } 001133 z[j] = 0; 001134 sqlite3_result_text(context, z, -1, 001135 z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC); 001136 } 001137 001138 /* 001139 ** current_time() 001140 ** 001141 ** This function returns the same value as time('now'). 001142 */ 001143 static void ctimeFunc( 001144 sqlite3_context *context, 001145 int NotUsed, 001146 sqlite3_value **NotUsed2 001147 ){ 001148 UNUSED_PARAMETER2(NotUsed, NotUsed2); 001149 timeFunc(context, 0, 0); 001150 } 001151 001152 /* 001153 ** current_date() 001154 ** 001155 ** This function returns the same value as date('now'). 001156 */ 001157 static void cdateFunc( 001158 sqlite3_context *context, 001159 int NotUsed, 001160 sqlite3_value **NotUsed2 001161 ){ 001162 UNUSED_PARAMETER2(NotUsed, NotUsed2); 001163 dateFunc(context, 0, 0); 001164 } 001165 001166 /* 001167 ** current_timestamp() 001168 ** 001169 ** This function returns the same value as datetime('now'). 001170 */ 001171 static void ctimestampFunc( 001172 sqlite3_context *context, 001173 int NotUsed, 001174 sqlite3_value **NotUsed2 001175 ){ 001176 UNUSED_PARAMETER2(NotUsed, NotUsed2); 001177 datetimeFunc(context, 0, 0); 001178 } 001179 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */ 001180 001181 #ifdef SQLITE_OMIT_DATETIME_FUNCS 001182 /* 001183 ** If the library is compiled to omit the full-scale date and time 001184 ** handling (to get a smaller binary), the following minimal version 001185 ** of the functions current_time(), current_date() and current_timestamp() 001186 ** are included instead. This is to support column declarations that 001187 ** include "DEFAULT CURRENT_TIME" etc. 001188 ** 001189 ** This function uses the C-library functions time(), gmtime() 001190 ** and strftime(). The format string to pass to strftime() is supplied 001191 ** as the user-data for the function. 001192 */ 001193 static void currentTimeFunc( 001194 sqlite3_context *context, 001195 int argc, 001196 sqlite3_value **argv 001197 ){ 001198 time_t t; 001199 char *zFormat = (char *)sqlite3_user_data(context); 001200 sqlite3_int64 iT; 001201 struct tm *pTm; 001202 struct tm sNow; 001203 char zBuf[20]; 001204 001205 UNUSED_PARAMETER(argc); 001206 UNUSED_PARAMETER(argv); 001207 001208 iT = sqlite3StmtCurrentTime(context); 001209 if( iT<=0 ) return; 001210 t = iT/1000 - 10000*(sqlite3_int64)21086676; 001211 #if HAVE_GMTIME_R 001212 pTm = gmtime_r(&t, &sNow); 001213 #else 001214 sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER)); 001215 pTm = gmtime(&t); 001216 if( pTm ) memcpy(&sNow, pTm, sizeof(sNow)); 001217 sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER)); 001218 #endif 001219 if( pTm ){ 001220 strftime(zBuf, 20, zFormat, &sNow); 001221 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 001222 } 001223 } 001224 #endif 001225 001226 /* 001227 ** This function registered all of the above C functions as SQL 001228 ** functions. This should be the only routine in this file with 001229 ** external linkage. 001230 */ 001231 void sqlite3RegisterDateTimeFunctions(void){ 001232 static FuncDef aDateTimeFuncs[] = { 001233 #ifndef SQLITE_OMIT_DATETIME_FUNCS 001234 PURE_DATE(julianday, -1, 0, 0, juliandayFunc ), 001235 PURE_DATE(date, -1, 0, 0, dateFunc ), 001236 PURE_DATE(time, -1, 0, 0, timeFunc ), 001237 PURE_DATE(datetime, -1, 0, 0, datetimeFunc ), 001238 PURE_DATE(strftime, -1, 0, 0, strftimeFunc ), 001239 DFUNCTION(current_time, 0, 0, 0, ctimeFunc ), 001240 DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc), 001241 DFUNCTION(current_date, 0, 0, 0, cdateFunc ), 001242 #else 001243 STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc), 001244 STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc), 001245 STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc), 001246 #endif 001247 }; 001248 sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs)); 001249 }