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