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.99 2008/12/20 13:18:50 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 (1) if Y,M,D are valid */ 84 char validHMS; /* True (1) if h,m,s are valid */ 85 char validJD; /* True (1) if iJD is valid */ 86 char validTZ; /* True (1) 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)?1: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 = 36525*(Y+4716)/100; 258 X2 = 306001*(M+1)/10000; 259 p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000); 260 p->validJD = 1; 261 if( p->validHMS ){ 262 p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(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 = (int)((p->iJD + 43200000)/86400000); 377 A = (int)((Z - 1867216.25)/36524.25); 378 A = Z + 1 + A - (A/4); 379 B = A + 1524; 380 C = (int)((B - 122.1)/365.25); 381 D = (36525*C)/100; 382 E = (int)((B-D)/30.6001); 383 X1 = (int)(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 = (int)((p->iJD + 43200000) % 86400000); 399 p->s = s/1000.0; 400 s = (int)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 sqlite3_int64 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 = (int)(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 - 21086676*(i64)10000; 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<ArraySize(zBuf)-1 && zMod[n]; n++){ 525 z[n] = (char)sqlite3UpperToLower[(u8)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 + 21086676*(i64)10000000; 554 clearYMD_HMS_TZ(p); 555 rc = 0; 556 } 557 #ifndef SQLITE_OMIT_LOCALTIME 558 else if( strcmp(z, "utc")==0 ){ 559 sqlite3_int64 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=(int)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 = sqlite3Strlen30(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 += (sqlite3_int64)(r*86400000.0 + 0.5); 667 }else if( n==4 && strcmp(z,"hour")==0 ){ 668 p->iJD += (sqlite3_int64)(r*(86400000.0/24.0) + 0.5); 669 }else if( n==6 && strcmp(z,"minute")==0 ){ 670 p->iJD += (sqlite3_int64)(r*(86400000.0/(24.0*60.0)) + 0.5); 671 }else if( n==6 && strcmp(z,"second")==0 ){ 672 p->iJD += (sqlite3_int64)(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 += (int)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 = (int)r; 683 if( y!=r ){ 684 p->iJD += (sqlite3_int64)((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 += (int)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_int64)(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 size_t 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>(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ){ 893 sqlite3_result_error_toobig(context); 894 return; 895 }else{ 896 z = sqlite3DbMallocRaw(db, (int)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 += sqlite3Strlen30(&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 = (int)((x.iJD-y.iJD+43200000)/86400000); 928 if( zFmt[i]=='W' ){ 929 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */ 930 wd = (int)(((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+=sqlite3Strlen30(&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 += sqlite3Strlen30(&z[j]); 950 break; 951 } 952 case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break; 953 case 'w': { 954 z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0'; 955 break; 956 } 957 case 'Y': { 958 sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]); 959 break; 960 } 961 default: z[j++] = '%'; break; 962 } 963 } 964 } 965 z[j] = 0; 966 sqlite3_result_text(context, z, -1, 967 z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC); 968 } 969 970 /* 971 ** current_time() 972 ** 973 ** This function returns the same value as time('now'). 974 */ 975 static void ctimeFunc( 976 sqlite3_context *context, 977 int NotUsed, 978 sqlite3_value **NotUsed2 979 ){ 980 UNUSED_PARAMETER2(NotUsed, NotUsed2); 981 timeFunc(context, 0, 0); 982 } 983 984 /* 985 ** current_date() 986 ** 987 ** This function returns the same value as date('now'). 988 */ 989 static void cdateFunc( 990 sqlite3_context *context, 991 int NotUsed, 992 sqlite3_value **NotUsed2 993 ){ 994 UNUSED_PARAMETER2(NotUsed, NotUsed2); 995 dateFunc(context, 0, 0); 996 } 997 998 /* 999 ** current_timestamp() 1000 ** 1001 ** This function returns the same value as datetime('now'). 1002 */ 1003 static void ctimestampFunc( 1004 sqlite3_context *context, 1005 int NotUsed, 1006 sqlite3_value **NotUsed2 1007 ){ 1008 UNUSED_PARAMETER2(NotUsed, NotUsed2); 1009 datetimeFunc(context, 0, 0); 1010 } 1011 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */ 1012 1013 #ifdef SQLITE_OMIT_DATETIME_FUNCS 1014 /* 1015 ** If the library is compiled to omit the full-scale date and time 1016 ** handling (to get a smaller binary), the following minimal version 1017 ** of the functions current_time(), current_date() and current_timestamp() 1018 ** are included instead. This is to support column declarations that 1019 ** include "DEFAULT CURRENT_TIME" etc. 1020 ** 1021 ** This function uses the C-library functions time(), gmtime() 1022 ** and strftime(). The format string to pass to strftime() is supplied 1023 ** as the user-data for the function. 1024 */ 1025 static void currentTimeFunc( 1026 sqlite3_context *context, 1027 int argc, 1028 sqlite3_value **argv 1029 ){ 1030 time_t t; 1031 char *zFormat = (char *)sqlite3_user_data(context); 1032 sqlite3 *db; 1033 double rT; 1034 char zBuf[20]; 1035 1036 db = sqlite3_context_db_handle(context); 1037 sqlite3OsCurrentTime(db->pVfs, &rT); 1038 t = 86400.0*(rT - 2440587.5) + 0.5; 1039 #ifdef HAVE_GMTIME_R 1040 { 1041 struct tm sNow; 1042 gmtime_r(&t, &sNow); 1043 strftime(zBuf, 20, zFormat, &sNow); 1044 } 1045 #else 1046 { 1047 struct tm *pTm; 1048 sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER)); 1049 pTm = gmtime(&t); 1050 strftime(zBuf, 20, zFormat, pTm); 1051 sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER)); 1052 } 1053 #endif 1054 1055 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 1056 } 1057 #endif 1058 1059 /* 1060 ** This function registered all of the above C functions as SQL 1061 ** functions. This should be the only routine in this file with 1062 ** external linkage. 1063 */ 1064 void sqlite3RegisterDateTimeFunctions(void){ 1065 static SQLITE_WSD FuncDef aDateTimeFuncs[] = { 1066 #ifndef SQLITE_OMIT_DATETIME_FUNCS 1067 FUNCTION(julianday, -1, 0, 0, juliandayFunc ), 1068 FUNCTION(date, -1, 0, 0, dateFunc ), 1069 FUNCTION(time, -1, 0, 0, timeFunc ), 1070 FUNCTION(datetime, -1, 0, 0, datetimeFunc ), 1071 FUNCTION(strftime, -1, 0, 0, strftimeFunc ), 1072 FUNCTION(current_time, 0, 0, 0, ctimeFunc ), 1073 FUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc), 1074 FUNCTION(current_date, 0, 0, 0, cdateFunc ), 1075 #else 1076 STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc), 1077 STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d", 0, currentTimeFunc), 1078 STR_FUNCTION(current_date, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc), 1079 #endif 1080 }; 1081 int i; 1082 FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions); 1083 FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aDateTimeFuncs); 1084 1085 for(i=0; i<ArraySize(aDateTimeFuncs); i++){ 1086 sqlite3FuncDefInsert(pHash, &aFunc[i]); 1087 } 1088 } 1089