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