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