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