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