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