1 /* 2 ** 2003 October 31 3 ** 4 ** The author disclaims copyright to this source code. In place of 5 ** a legal notice, here is a blessing: 6 ** 7 ** May you do good and not evil. 8 ** May you find forgiveness for yourself and forgive others. 9 ** May you share freely, never taking more than you give. 10 ** 11 ************************************************************************* 12 ** This file contains the C functions that implement date and time 13 ** functions for SQLite. 14 ** 15 ** There is only one exported symbol in this file - the function 16 ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file. 17 ** All other code has file scope. 18 ** 19 ** SQLite processes all times and dates as julian day numbers. The 20 ** dates and times are stored as the number of days since noon 21 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian 22 ** calendar system. 23 ** 24 ** 1970-01-01 00:00:00 is JD 2440587.5 25 ** 2000-01-01 00:00:00 is JD 2451544.5 26 ** 27 ** This implementation requires years to be expressed as a 4-digit number 28 ** which means that only dates between 0000-01-01 and 9999-12-31 can 29 ** be represented, even though julian day numbers allow a much wider 30 ** range of dates. 31 ** 32 ** The Gregorian calendar system is used for all dates and times, 33 ** even those that predate the Gregorian calendar. Historians usually 34 ** use the julian calendar for dates prior to 1582-10-15 and for some 35 ** dates afterwards, depending on locale. Beware of this difference. 36 ** 37 ** The conversion algorithms are implemented based on descriptions 38 ** in the following text: 39 ** 40 ** Jean Meeus 41 ** Astronomical Algorithms, 2nd Edition, 1998 42 ** ISBM 0-943396-61-1 43 ** Willmann-Bell, Inc 44 ** Richmond, Virginia (USA) 45 */ 46 #include "sqliteInt.h" 47 #include <stdlib.h> 48 #include <assert.h> 49 #include <time.h> 50 51 #ifndef SQLITE_OMIT_DATETIME_FUNCS 52 53 54 /* 55 ** A structure for holding a single date and time. 56 */ 57 typedef struct DateTime DateTime; 58 struct DateTime { 59 sqlite3_int64 iJD; /* The julian day number times 86400000 */ 60 int Y, M, D; /* Year, month, and day */ 61 int h, m; /* Hour and minutes */ 62 int tz; /* Timezone offset in minutes */ 63 double s; /* Seconds */ 64 char validYMD; /* True (1) if Y,M,D are valid */ 65 char validHMS; /* True (1) if h,m,s are valid */ 66 char validJD; /* True (1) if iJD is valid */ 67 char validTZ; /* True (1) if tz is valid */ 68 char tzSet; /* Timezone was set explicitly */ 69 }; 70 71 72 /* 73 ** Convert zDate into one or more integers according to the conversion 74 ** specifier zFormat. 75 ** 76 ** zFormat[] contains 4 characters for each integer converted, except for 77 ** the last integer which is specified by three characters. The meaning 78 ** of a four-character format specifiers ABCD is: 79 ** 80 ** A: number of digits to convert. Always "2" or "4". 81 ** B: minimum value. Always "0" or "1". 82 ** C: maximum value, decoded as: 83 ** a: 12 84 ** b: 14 85 ** c: 24 86 ** d: 31 87 ** e: 59 88 ** f: 9999 89 ** D: the separator character, or \000 to indicate this is the 90 ** last number to convert. 91 ** 92 ** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would 93 ** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-". 94 ** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates 95 ** the 2-digit day which is the last integer in the set. 96 ** 97 ** The function returns the number of successful conversions. 98 */ 99 static int getDigits(const char *zDate, const char *zFormat, ...){ 100 /* The aMx[] array translates the 3rd character of each format 101 ** spec into a max size: a b c d e f */ 102 static const u16 aMx[] = { 12, 14, 24, 31, 59, 9999 }; 103 va_list ap; 104 int cnt = 0; 105 char nextC; 106 va_start(ap, zFormat); 107 do{ 108 char N = zFormat[0] - '0'; 109 char min = zFormat[1] - '0'; 110 int val = 0; 111 u16 max; 112 113 assert( zFormat[2]>='a' && zFormat[2]<='f' ); 114 max = aMx[zFormat[2] - 'a']; 115 nextC = zFormat[3]; 116 val = 0; 117 while( N-- ){ 118 if( !sqlite3Isdigit(*zDate) ){ 119 goto end_getDigits; 120 } 121 val = val*10 + *zDate - '0'; 122 zDate++; 123 } 124 if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){ 125 goto end_getDigits; 126 } 127 *va_arg(ap,int*) = val; 128 zDate++; 129 cnt++; 130 zFormat += 4; 131 }while( nextC ); 132 end_getDigits: 133 va_end(ap); 134 return cnt; 135 } 136 137 /* 138 ** Parse a timezone extension on the end of a date-time. 139 ** The extension is of the form: 140 ** 141 ** (+/-)HH:MM 142 ** 143 ** Or the "zulu" notation: 144 ** 145 ** Z 146 ** 147 ** If the parse is successful, write the number of minutes 148 ** of change in p->tz and return 0. If a parser error occurs, 149 ** return non-zero. 150 ** 151 ** A missing specifier is not considered an error. 152 */ 153 static int parseTimezone(const char *zDate, DateTime *p){ 154 int sgn = 0; 155 int nHr, nMn; 156 int c; 157 while( sqlite3Isspace(*zDate) ){ zDate++; } 158 p->tz = 0; 159 c = *zDate; 160 if( c=='-' ){ 161 sgn = -1; 162 }else if( c=='+' ){ 163 sgn = +1; 164 }else if( c=='Z' || c=='z' ){ 165 zDate++; 166 goto zulu_time; 167 }else{ 168 return c!=0; 169 } 170 zDate++; 171 if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){ 172 return 1; 173 } 174 zDate += 5; 175 p->tz = sgn*(nMn + nHr*60); 176 zulu_time: 177 while( sqlite3Isspace(*zDate) ){ zDate++; } 178 p->tzSet = 1; 179 return *zDate!=0; 180 } 181 182 /* 183 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF. 184 ** The HH, MM, and SS must each be exactly 2 digits. The 185 ** fractional seconds FFFF can be one or more digits. 186 ** 187 ** Return 1 if there is a parsing error and 0 on success. 188 */ 189 static int parseHhMmSs(const char *zDate, DateTime *p){ 190 int h, m, s; 191 double ms = 0.0; 192 if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){ 193 return 1; 194 } 195 zDate += 5; 196 if( *zDate==':' ){ 197 zDate++; 198 if( getDigits(zDate, "20e", &s)!=1 ){ 199 return 1; 200 } 201 zDate += 2; 202 if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){ 203 double rScale = 1.0; 204 zDate++; 205 while( sqlite3Isdigit(*zDate) ){ 206 ms = ms*10.0 + *zDate - '0'; 207 rScale *= 10.0; 208 zDate++; 209 } 210 ms /= rScale; 211 } 212 }else{ 213 s = 0; 214 } 215 p->validJD = 0; 216 p->validHMS = 1; 217 p->h = h; 218 p->m = m; 219 p->s = s + ms; 220 if( parseTimezone(zDate, p) ) return 1; 221 p->validTZ = (p->tz!=0)?1:0; 222 return 0; 223 } 224 225 /* 226 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume 227 ** that the YYYY-MM-DD is according to the Gregorian calendar. 228 ** 229 ** Reference: Meeus page 61 230 */ 231 static void computeJD(DateTime *p){ 232 int Y, M, D, A, B, X1, X2; 233 234 if( p->validJD ) return; 235 if( p->validYMD ){ 236 Y = p->Y; 237 M = p->M; 238 D = p->D; 239 }else{ 240 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */ 241 M = 1; 242 D = 1; 243 } 244 if( M<=2 ){ 245 Y--; 246 M += 12; 247 } 248 A = Y/100; 249 B = 2 - A + (A/4); 250 X1 = 36525*(Y+4716)/100; 251 X2 = 306001*(M+1)/10000; 252 p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000); 253 p->validJD = 1; 254 if( p->validHMS ){ 255 p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000); 256 if( p->validTZ ){ 257 p->iJD -= p->tz*60000; 258 p->validYMD = 0; 259 p->validHMS = 0; 260 p->validTZ = 0; 261 } 262 } 263 } 264 265 /* 266 ** Parse dates of the form 267 ** 268 ** YYYY-MM-DD HH:MM:SS.FFF 269 ** YYYY-MM-DD HH:MM:SS 270 ** YYYY-MM-DD HH:MM 271 ** YYYY-MM-DD 272 ** 273 ** Write the result into the DateTime structure and return 0 274 ** on success and 1 if the input string is not a well-formed 275 ** date. 276 */ 277 static int parseYyyyMmDd(const char *zDate, DateTime *p){ 278 int Y, M, D, neg; 279 280 if( zDate[0]=='-' ){ 281 zDate++; 282 neg = 1; 283 }else{ 284 neg = 0; 285 } 286 if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){ 287 return 1; 288 } 289 zDate += 10; 290 while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; } 291 if( parseHhMmSs(zDate, p)==0 ){ 292 /* We got the time */ 293 }else if( *zDate==0 ){ 294 p->validHMS = 0; 295 }else{ 296 return 1; 297 } 298 p->validJD = 0; 299 p->validYMD = 1; 300 p->Y = neg ? -Y : Y; 301 p->M = M; 302 p->D = D; 303 if( p->validTZ ){ 304 computeJD(p); 305 } 306 return 0; 307 } 308 309 /* 310 ** Set the time to the current time reported by the VFS. 311 ** 312 ** Return the number of errors. 313 */ 314 static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){ 315 p->iJD = sqlite3StmtCurrentTime(context); 316 if( p->iJD>0 ){ 317 p->validJD = 1; 318 return 0; 319 }else{ 320 return 1; 321 } 322 } 323 324 /* 325 ** Attempt to parse the given string into a julian day number. Return 326 ** the number of errors. 327 ** 328 ** The following are acceptable forms for the input string: 329 ** 330 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM 331 ** DDDD.DD 332 ** now 333 ** 334 ** In the first form, the +/-HH:MM is always optional. The fractional 335 ** seconds extension (the ".FFF") is optional. The seconds portion 336 ** (":SS.FFF") is option. The year and date can be omitted as long 337 ** as there is a time string. The time string can be omitted as long 338 ** as there is a year and date. 339 */ 340 static int parseDateOrTime( 341 sqlite3_context *context, 342 const char *zDate, 343 DateTime *p 344 ){ 345 double r; 346 if( parseYyyyMmDd(zDate,p)==0 ){ 347 return 0; 348 }else if( parseHhMmSs(zDate, p)==0 ){ 349 return 0; 350 }else if( sqlite3StrICmp(zDate,"now")==0){ 351 return setDateTimeToCurrent(context, p); 352 }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){ 353 p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5); 354 p->validJD = 1; 355 return 0; 356 } 357 return 1; 358 } 359 360 /* 361 ** Compute the Year, Month, and Day from the julian day number. 362 */ 363 static void computeYMD(DateTime *p){ 364 int Z, A, B, C, D, E, X1; 365 if( p->validYMD ) return; 366 if( !p->validJD ){ 367 p->Y = 2000; 368 p->M = 1; 369 p->D = 1; 370 }else{ 371 Z = (int)((p->iJD + 43200000)/86400000); 372 A = (int)((Z - 1867216.25)/36524.25); 373 A = Z + 1 + A - (A/4); 374 B = A + 1524; 375 C = (int)((B - 122.1)/365.25); 376 D = (36525*(C&32767))/100; 377 E = (int)((B-D)/30.6001); 378 X1 = (int)(30.6001*E); 379 p->D = B - D - X1; 380 p->M = E<14 ? E-1 : E-13; 381 p->Y = p->M>2 ? C - 4716 : C - 4715; 382 } 383 p->validYMD = 1; 384 } 385 386 /* 387 ** Compute the Hour, Minute, and Seconds from the julian day number. 388 */ 389 static void computeHMS(DateTime *p){ 390 int s; 391 if( p->validHMS ) return; 392 computeJD(p); 393 s = (int)((p->iJD + 43200000) % 86400000); 394 p->s = s/1000.0; 395 s = (int)p->s; 396 p->s -= s; 397 p->h = s/3600; 398 s -= p->h*3600; 399 p->m = s/60; 400 p->s += s - p->m*60; 401 p->validHMS = 1; 402 } 403 404 /* 405 ** Compute both YMD and HMS 406 */ 407 static void computeYMD_HMS(DateTime *p){ 408 computeYMD(p); 409 computeHMS(p); 410 } 411 412 /* 413 ** Clear the YMD and HMS and the TZ 414 */ 415 static void clearYMD_HMS_TZ(DateTime *p){ 416 p->validYMD = 0; 417 p->validHMS = 0; 418 p->validTZ = 0; 419 } 420 421 /* 422 ** On recent Windows platforms, the localtime_s() function is available 423 ** as part of the "Secure CRT". It is essentially equivalent to 424 ** localtime_r() available under most POSIX platforms, except that the 425 ** order of the parameters is reversed. 426 ** 427 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx. 428 ** 429 ** If the user has not indicated to use localtime_r() or localtime_s() 430 ** already, check for an MSVC build environment that provides 431 ** localtime_s(). 432 */ 433 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \ 434 && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE) 435 #undef HAVE_LOCALTIME_S 436 #define HAVE_LOCALTIME_S 1 437 #endif 438 439 #ifndef SQLITE_OMIT_LOCALTIME 440 /* 441 ** The following routine implements the rough equivalent of localtime_r() 442 ** using whatever operating-system specific localtime facility that 443 ** is available. This routine returns 0 on success and 444 ** non-zero on any kind of error. 445 ** 446 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is true then this 447 ** routine will always fail. 448 ** 449 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C 450 ** library function localtime_r() is used to assist in the calculation of 451 ** local time. 452 */ 453 static int osLocaltime(time_t *t, struct tm *pTm){ 454 int rc; 455 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S 456 struct tm *pX; 457 #if SQLITE_THREADSAFE>0 458 sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER); 459 #endif 460 sqlite3_mutex_enter(mutex); 461 pX = localtime(t); 462 #ifndef SQLITE_OMIT_BUILTIN_TEST 463 if( sqlite3GlobalConfig.bLocaltimeFault ) pX = 0; 464 #endif 465 if( pX ) *pTm = *pX; 466 sqlite3_mutex_leave(mutex); 467 rc = pX==0; 468 #else 469 #ifndef SQLITE_OMIT_BUILTIN_TEST 470 if( sqlite3GlobalConfig.bLocaltimeFault ) return 1; 471 #endif 472 #if HAVE_LOCALTIME_R 473 rc = localtime_r(t, pTm)==0; 474 #else 475 rc = localtime_s(pTm, t); 476 #endif /* HAVE_LOCALTIME_R */ 477 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */ 478 return rc; 479 } 480 #endif /* SQLITE_OMIT_LOCALTIME */ 481 482 483 #ifndef SQLITE_OMIT_LOCALTIME 484 /* 485 ** Compute the difference (in milliseconds) between localtime and UTC 486 ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs, 487 ** return this value and set *pRc to SQLITE_OK. 488 ** 489 ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value 490 ** is undefined in this case. 491 */ 492 static sqlite3_int64 localtimeOffset( 493 DateTime *p, /* Date at which to calculate offset */ 494 sqlite3_context *pCtx, /* Write error here if one occurs */ 495 int *pRc /* OUT: Error code. SQLITE_OK or ERROR */ 496 ){ 497 DateTime x, y; 498 time_t t; 499 struct tm sLocal; 500 501 /* Initialize the contents of sLocal to avoid a compiler warning. */ 502 memset(&sLocal, 0, sizeof(sLocal)); 503 504 x = *p; 505 computeYMD_HMS(&x); 506 if( x.Y<1971 || x.Y>=2038 ){ 507 /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only 508 ** works for years between 1970 and 2037. For dates outside this range, 509 ** SQLite attempts to map the year into an equivalent year within this 510 ** range, do the calculation, then map the year back. 511 */ 512 x.Y = 2000; 513 x.M = 1; 514 x.D = 1; 515 x.h = 0; 516 x.m = 0; 517 x.s = 0.0; 518 } else { 519 int s = (int)(x.s + 0.5); 520 x.s = s; 521 } 522 x.tz = 0; 523 x.validJD = 0; 524 computeJD(&x); 525 t = (time_t)(x.iJD/1000 - 21086676*(i64)10000); 526 if( osLocaltime(&t, &sLocal) ){ 527 sqlite3_result_error(pCtx, "local time unavailable", -1); 528 *pRc = SQLITE_ERROR; 529 return 0; 530 } 531 y.Y = sLocal.tm_year + 1900; 532 y.M = sLocal.tm_mon + 1; 533 y.D = sLocal.tm_mday; 534 y.h = sLocal.tm_hour; 535 y.m = sLocal.tm_min; 536 y.s = sLocal.tm_sec; 537 y.validYMD = 1; 538 y.validHMS = 1; 539 y.validJD = 0; 540 y.validTZ = 0; 541 computeJD(&y); 542 *pRc = SQLITE_OK; 543 return y.iJD - x.iJD; 544 } 545 #endif /* SQLITE_OMIT_LOCALTIME */ 546 547 /* 548 ** Process a modifier to a date-time stamp. The modifiers are 549 ** as follows: 550 ** 551 ** NNN days 552 ** NNN hours 553 ** NNN minutes 554 ** NNN.NNNN seconds 555 ** NNN months 556 ** NNN years 557 ** start of month 558 ** start of year 559 ** start of week 560 ** start of day 561 ** weekday N 562 ** unixepoch 563 ** localtime 564 ** utc 565 ** 566 ** Return 0 on success and 1 if there is any kind of error. If the error 567 ** is in a system call (i.e. localtime()), then an error message is written 568 ** to context pCtx. If the error is an unrecognized modifier, no error is 569 ** written to pCtx. 570 */ 571 static int parseModifier(sqlite3_context *pCtx, const char *zMod, DateTime *p){ 572 int rc = 1; 573 int n; 574 double r; 575 char *z, zBuf[30]; 576 z = zBuf; 577 for(n=0; n<ArraySize(zBuf)-1 && zMod[n]; n++){ 578 z[n] = (char)sqlite3UpperToLower[(u8)zMod[n]]; 579 } 580 z[n] = 0; 581 switch( z[0] ){ 582 #ifndef SQLITE_OMIT_LOCALTIME 583 case 'l': { 584 /* localtime 585 ** 586 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to 587 ** show local time. 588 */ 589 if( strcmp(z, "localtime")==0 ){ 590 computeJD(p); 591 p->iJD += localtimeOffset(p, pCtx, &rc); 592 clearYMD_HMS_TZ(p); 593 } 594 break; 595 } 596 #endif 597 case 'u': { 598 /* 599 ** unixepoch 600 ** 601 ** Treat the current value of p->iJD as the number of 602 ** seconds since 1970. Convert to a real julian day number. 603 */ 604 if( strcmp(z, "unixepoch")==0 && p->validJD ){ 605 p->iJD = (p->iJD + 43200)/86400 + 21086676*(i64)10000000; 606 clearYMD_HMS_TZ(p); 607 rc = 0; 608 } 609 #ifndef SQLITE_OMIT_LOCALTIME 610 else if( strcmp(z, "utc")==0 ){ 611 if( p->tzSet==0 ){ 612 sqlite3_int64 c1; 613 computeJD(p); 614 c1 = localtimeOffset(p, pCtx, &rc); 615 if( rc==SQLITE_OK ){ 616 p->iJD -= c1; 617 clearYMD_HMS_TZ(p); 618 p->iJD += c1 - localtimeOffset(p, pCtx, &rc); 619 } 620 p->tzSet = 1; 621 }else{ 622 rc = SQLITE_OK; 623 } 624 } 625 #endif 626 break; 627 } 628 case 'w': { 629 /* 630 ** weekday N 631 ** 632 ** Move the date to the same time on the next occurrence of 633 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the 634 ** date is already on the appropriate weekday, this is a no-op. 635 */ 636 if( strncmp(z, "weekday ", 8)==0 637 && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8) 638 && (n=(int)r)==r && n>=0 && r<7 ){ 639 sqlite3_int64 Z; 640 computeYMD_HMS(p); 641 p->validTZ = 0; 642 p->validJD = 0; 643 computeJD(p); 644 Z = ((p->iJD + 129600000)/86400000) % 7; 645 if( Z>n ) Z -= 7; 646 p->iJD += (n - Z)*86400000; 647 clearYMD_HMS_TZ(p); 648 rc = 0; 649 } 650 break; 651 } 652 case 's': { 653 /* 654 ** start of TTTTT 655 ** 656 ** Move the date backwards to the beginning of the current day, 657 ** or month or year. 658 */ 659 if( strncmp(z, "start of ", 9)!=0 ) break; 660 z += 9; 661 computeYMD(p); 662 p->validHMS = 1; 663 p->h = p->m = 0; 664 p->s = 0.0; 665 p->validTZ = 0; 666 p->validJD = 0; 667 if( strcmp(z,"month")==0 ){ 668 p->D = 1; 669 rc = 0; 670 }else if( strcmp(z,"year")==0 ){ 671 computeYMD(p); 672 p->M = 1; 673 p->D = 1; 674 rc = 0; 675 }else if( strcmp(z,"day")==0 ){ 676 rc = 0; 677 } 678 break; 679 } 680 case '+': 681 case '-': 682 case '0': 683 case '1': 684 case '2': 685 case '3': 686 case '4': 687 case '5': 688 case '6': 689 case '7': 690 case '8': 691 case '9': { 692 double rRounder; 693 for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){} 694 if( !sqlite3AtoF(z, &r, n, SQLITE_UTF8) ){ 695 rc = 1; 696 break; 697 } 698 if( z[n]==':' ){ 699 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the 700 ** specified number of hours, minutes, seconds, and fractional seconds 701 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be 702 ** omitted. 703 */ 704 const char *z2 = z; 705 DateTime tx; 706 sqlite3_int64 day; 707 if( !sqlite3Isdigit(*z2) ) z2++; 708 memset(&tx, 0, sizeof(tx)); 709 if( parseHhMmSs(z2, &tx) ) break; 710 computeJD(&tx); 711 tx.iJD -= 43200000; 712 day = tx.iJD/86400000; 713 tx.iJD -= day*86400000; 714 if( z[0]=='-' ) tx.iJD = -tx.iJD; 715 computeJD(p); 716 clearYMD_HMS_TZ(p); 717 p->iJD += tx.iJD; 718 rc = 0; 719 break; 720 } 721 z += n; 722 while( sqlite3Isspace(*z) ) z++; 723 n = sqlite3Strlen30(z); 724 if( n>10 || n<3 ) break; 725 if( z[n-1]=='s' ){ z[n-1] = 0; n--; } 726 computeJD(p); 727 rc = 0; 728 rRounder = r<0 ? -0.5 : +0.5; 729 if( n==3 && strcmp(z,"day")==0 ){ 730 p->iJD += (sqlite3_int64)(r*86400000.0 + rRounder); 731 }else if( n==4 && strcmp(z,"hour")==0 ){ 732 p->iJD += (sqlite3_int64)(r*(86400000.0/24.0) + rRounder); 733 }else if( n==6 && strcmp(z,"minute")==0 ){ 734 p->iJD += (sqlite3_int64)(r*(86400000.0/(24.0*60.0)) + rRounder); 735 }else if( n==6 && strcmp(z,"second")==0 ){ 736 p->iJD += (sqlite3_int64)(r*(86400000.0/(24.0*60.0*60.0)) + rRounder); 737 }else if( n==5 && strcmp(z,"month")==0 ){ 738 int x, y; 739 computeYMD_HMS(p); 740 p->M += (int)r; 741 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; 742 p->Y += x; 743 p->M -= x*12; 744 p->validJD = 0; 745 computeJD(p); 746 y = (int)r; 747 if( y!=r ){ 748 p->iJD += (sqlite3_int64)((r - y)*30.0*86400000.0 + rRounder); 749 } 750 }else if( n==4 && strcmp(z,"year")==0 ){ 751 int y = (int)r; 752 computeYMD_HMS(p); 753 p->Y += y; 754 p->validJD = 0; 755 computeJD(p); 756 if( y!=r ){ 757 p->iJD += (sqlite3_int64)((r - y)*365.0*86400000.0 + rRounder); 758 } 759 }else{ 760 rc = 1; 761 } 762 clearYMD_HMS_TZ(p); 763 break; 764 } 765 default: { 766 break; 767 } 768 } 769 return rc; 770 } 771 772 /* 773 ** Process time function arguments. argv[0] is a date-time stamp. 774 ** argv[1] and following are modifiers. Parse them all and write 775 ** the resulting time into the DateTime structure p. Return 0 776 ** on success and 1 if there are any errors. 777 ** 778 ** If there are zero parameters (if even argv[0] is undefined) 779 ** then assume a default value of "now" for argv[0]. 780 */ 781 static int isDate( 782 sqlite3_context *context, 783 int argc, 784 sqlite3_value **argv, 785 DateTime *p 786 ){ 787 int i; 788 const unsigned char *z; 789 int eType; 790 memset(p, 0, sizeof(*p)); 791 if( argc==0 ){ 792 return setDateTimeToCurrent(context, p); 793 } 794 if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT 795 || eType==SQLITE_INTEGER ){ 796 p->iJD = (sqlite3_int64)(sqlite3_value_double(argv[0])*86400000.0 + 0.5); 797 p->validJD = 1; 798 }else{ 799 z = sqlite3_value_text(argv[0]); 800 if( !z || parseDateOrTime(context, (char*)z, p) ){ 801 return 1; 802 } 803 } 804 for(i=1; i<argc; i++){ 805 z = sqlite3_value_text(argv[i]); 806 if( z==0 || parseModifier(context, (char*)z, p) ) return 1; 807 } 808 return 0; 809 } 810 811 812 /* 813 ** The following routines implement the various date and time functions 814 ** of SQLite. 815 */ 816 817 /* 818 ** julianday( TIMESTRING, MOD, MOD, ...) 819 ** 820 ** Return the julian day number of the date specified in the arguments 821 */ 822 static void juliandayFunc( 823 sqlite3_context *context, 824 int argc, 825 sqlite3_value **argv 826 ){ 827 DateTime x; 828 if( isDate(context, argc, argv, &x)==0 ){ 829 computeJD(&x); 830 sqlite3_result_double(context, x.iJD/86400000.0); 831 } 832 } 833 834 /* 835 ** datetime( TIMESTRING, MOD, MOD, ...) 836 ** 837 ** Return YYYY-MM-DD HH:MM:SS 838 */ 839 static void datetimeFunc( 840 sqlite3_context *context, 841 int argc, 842 sqlite3_value **argv 843 ){ 844 DateTime x; 845 if( isDate(context, argc, argv, &x)==0 ){ 846 char zBuf[100]; 847 computeYMD_HMS(&x); 848 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d", 849 x.Y, x.M, x.D, x.h, x.m, (int)(x.s)); 850 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 851 } 852 } 853 854 /* 855 ** time( TIMESTRING, MOD, MOD, ...) 856 ** 857 ** Return HH:MM:SS 858 */ 859 static void timeFunc( 860 sqlite3_context *context, 861 int argc, 862 sqlite3_value **argv 863 ){ 864 DateTime x; 865 if( isDate(context, argc, argv, &x)==0 ){ 866 char zBuf[100]; 867 computeHMS(&x); 868 sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s); 869 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 870 } 871 } 872 873 /* 874 ** date( TIMESTRING, MOD, MOD, ...) 875 ** 876 ** Return YYYY-MM-DD 877 */ 878 static void dateFunc( 879 sqlite3_context *context, 880 int argc, 881 sqlite3_value **argv 882 ){ 883 DateTime x; 884 if( isDate(context, argc, argv, &x)==0 ){ 885 char zBuf[100]; 886 computeYMD(&x); 887 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D); 888 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 889 } 890 } 891 892 /* 893 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...) 894 ** 895 ** Return a string described by FORMAT. Conversions as follows: 896 ** 897 ** %d day of month 898 ** %f ** fractional seconds SS.SSS 899 ** %H hour 00-24 900 ** %j day of year 000-366 901 ** %J ** julian day number 902 ** %m month 01-12 903 ** %M minute 00-59 904 ** %s seconds since 1970-01-01 905 ** %S seconds 00-59 906 ** %w day of week 0-6 sunday==0 907 ** %W week of year 00-53 908 ** %Y year 0000-9999 909 ** %% % 910 */ 911 static void strftimeFunc( 912 sqlite3_context *context, 913 int argc, 914 sqlite3_value **argv 915 ){ 916 DateTime x; 917 u64 n; 918 size_t i,j; 919 char *z; 920 sqlite3 *db; 921 const char *zFmt; 922 char zBuf[100]; 923 if( argc==0 ) return; 924 zFmt = (const char*)sqlite3_value_text(argv[0]); 925 if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return; 926 db = sqlite3_context_db_handle(context); 927 for(i=0, n=1; zFmt[i]; i++, n++){ 928 if( zFmt[i]=='%' ){ 929 switch( zFmt[i+1] ){ 930 case 'd': 931 case 'H': 932 case 'm': 933 case 'M': 934 case 'S': 935 case 'W': 936 n++; 937 /* fall thru */ 938 case 'w': 939 case '%': 940 break; 941 case 'f': 942 n += 8; 943 break; 944 case 'j': 945 n += 3; 946 break; 947 case 'Y': 948 n += 8; 949 break; 950 case 's': 951 case 'J': 952 n += 50; 953 break; 954 default: 955 return; /* ERROR. return a NULL */ 956 } 957 i++; 958 } 959 } 960 testcase( n==sizeof(zBuf)-1 ); 961 testcase( n==sizeof(zBuf) ); 962 testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH]+1 ); 963 testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ); 964 if( n<sizeof(zBuf) ){ 965 z = zBuf; 966 }else if( n>(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ){ 967 sqlite3_result_error_toobig(context); 968 return; 969 }else{ 970 z = sqlite3DbMallocRawNN(db, (int)n); 971 if( z==0 ){ 972 sqlite3_result_error_nomem(context); 973 return; 974 } 975 } 976 computeJD(&x); 977 computeYMD_HMS(&x); 978 for(i=j=0; zFmt[i]; i++){ 979 if( zFmt[i]!='%' ){ 980 z[j++] = zFmt[i]; 981 }else{ 982 i++; 983 switch( zFmt[i] ){ 984 case 'd': sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break; 985 case 'f': { 986 double s = x.s; 987 if( s>59.999 ) s = 59.999; 988 sqlite3_snprintf(7, &z[j],"%06.3f", s); 989 j += sqlite3Strlen30(&z[j]); 990 break; 991 } 992 case 'H': sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break; 993 case 'W': /* Fall thru */ 994 case 'j': { 995 int nDay; /* Number of days since 1st day of year */ 996 DateTime y = x; 997 y.validJD = 0; 998 y.M = 1; 999 y.D = 1; 1000 computeJD(&y); 1001 nDay = (int)((x.iJD-y.iJD+43200000)/86400000); 1002 if( zFmt[i]=='W' ){ 1003 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */ 1004 wd = (int)(((x.iJD+43200000)/86400000)%7); 1005 sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7); 1006 j += 2; 1007 }else{ 1008 sqlite3_snprintf(4, &z[j],"%03d",nDay+1); 1009 j += 3; 1010 } 1011 break; 1012 } 1013 case 'J': { 1014 sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0); 1015 j+=sqlite3Strlen30(&z[j]); 1016 break; 1017 } 1018 case 'm': sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break; 1019 case 'M': sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break; 1020 case 's': { 1021 sqlite3_snprintf(30,&z[j],"%lld", 1022 (i64)(x.iJD/1000 - 21086676*(i64)10000)); 1023 j += sqlite3Strlen30(&z[j]); 1024 break; 1025 } 1026 case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break; 1027 case 'w': { 1028 z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0'; 1029 break; 1030 } 1031 case 'Y': { 1032 sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]); 1033 break; 1034 } 1035 default: z[j++] = '%'; break; 1036 } 1037 } 1038 } 1039 z[j] = 0; 1040 sqlite3_result_text(context, z, -1, 1041 z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC); 1042 } 1043 1044 /* 1045 ** current_time() 1046 ** 1047 ** This function returns the same value as time('now'). 1048 */ 1049 static void ctimeFunc( 1050 sqlite3_context *context, 1051 int NotUsed, 1052 sqlite3_value **NotUsed2 1053 ){ 1054 UNUSED_PARAMETER2(NotUsed, NotUsed2); 1055 timeFunc(context, 0, 0); 1056 } 1057 1058 /* 1059 ** current_date() 1060 ** 1061 ** This function returns the same value as date('now'). 1062 */ 1063 static void cdateFunc( 1064 sqlite3_context *context, 1065 int NotUsed, 1066 sqlite3_value **NotUsed2 1067 ){ 1068 UNUSED_PARAMETER2(NotUsed, NotUsed2); 1069 dateFunc(context, 0, 0); 1070 } 1071 1072 /* 1073 ** current_timestamp() 1074 ** 1075 ** This function returns the same value as datetime('now'). 1076 */ 1077 static void ctimestampFunc( 1078 sqlite3_context *context, 1079 int NotUsed, 1080 sqlite3_value **NotUsed2 1081 ){ 1082 UNUSED_PARAMETER2(NotUsed, NotUsed2); 1083 datetimeFunc(context, 0, 0); 1084 } 1085 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */ 1086 1087 #ifdef SQLITE_OMIT_DATETIME_FUNCS 1088 /* 1089 ** If the library is compiled to omit the full-scale date and time 1090 ** handling (to get a smaller binary), the following minimal version 1091 ** of the functions current_time(), current_date() and current_timestamp() 1092 ** are included instead. This is to support column declarations that 1093 ** include "DEFAULT CURRENT_TIME" etc. 1094 ** 1095 ** This function uses the C-library functions time(), gmtime() 1096 ** and strftime(). The format string to pass to strftime() is supplied 1097 ** as the user-data for the function. 1098 */ 1099 static void currentTimeFunc( 1100 sqlite3_context *context, 1101 int argc, 1102 sqlite3_value **argv 1103 ){ 1104 time_t t; 1105 char *zFormat = (char *)sqlite3_user_data(context); 1106 sqlite3 *db; 1107 sqlite3_int64 iT; 1108 struct tm *pTm; 1109 struct tm sNow; 1110 char zBuf[20]; 1111 1112 UNUSED_PARAMETER(argc); 1113 UNUSED_PARAMETER(argv); 1114 1115 iT = sqlite3StmtCurrentTime(context); 1116 if( iT<=0 ) return; 1117 t = iT/1000 - 10000*(sqlite3_int64)21086676; 1118 #if HAVE_GMTIME_R 1119 pTm = gmtime_r(&t, &sNow); 1120 #else 1121 sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER)); 1122 pTm = gmtime(&t); 1123 if( pTm ) memcpy(&sNow, pTm, sizeof(sNow)); 1124 sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER)); 1125 #endif 1126 if( pTm ){ 1127 strftime(zBuf, 20, zFormat, &sNow); 1128 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 1129 } 1130 } 1131 #endif 1132 1133 /* 1134 ** This function registered all of the above C functions as SQL 1135 ** functions. This should be the only routine in this file with 1136 ** external linkage. 1137 */ 1138 void sqlite3RegisterDateTimeFunctions(void){ 1139 static SQLITE_WSD FuncDef aDateTimeFuncs[] = { 1140 #ifndef SQLITE_OMIT_DATETIME_FUNCS 1141 DFUNCTION(julianday, -1, 0, 0, juliandayFunc ), 1142 DFUNCTION(date, -1, 0, 0, dateFunc ), 1143 DFUNCTION(time, -1, 0, 0, timeFunc ), 1144 DFUNCTION(datetime, -1, 0, 0, datetimeFunc ), 1145 DFUNCTION(strftime, -1, 0, 0, strftimeFunc ), 1146 DFUNCTION(current_time, 0, 0, 0, ctimeFunc ), 1147 DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc), 1148 DFUNCTION(current_date, 0, 0, 0, cdateFunc ), 1149 #else 1150 STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc), 1151 STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc), 1152 STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc), 1153 #endif 1154 }; 1155 int i; 1156 FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions); 1157 FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aDateTimeFuncs); 1158 1159 for(i=0; i<ArraySize(aDateTimeFuncs); i++){ 1160 sqlite3FuncDefInsert(pHash, &aFunc[i]); 1161 } 1162 } 1163