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