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.85 2008/06/18 17:09:10 danielk1977 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 }else if( strcmp(z, "utc")==0 ){ 557 double c1; 558 computeJD(p); 559 c1 = localtimeOffset(p); 560 p->iJD -= c1; 561 clearYMD_HMS_TZ(p); 562 p->iJD += c1 - localtimeOffset(p); 563 rc = 0; 564 } 565 break; 566 } 567 case 'w': { 568 /* 569 ** weekday N 570 ** 571 ** Move the date to the same time on the next occurrence of 572 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the 573 ** date is already on the appropriate weekday, this is a no-op. 574 */ 575 if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0 576 && (n=r)==r && n>=0 && r<7 ){ 577 sqlite3_int64 Z; 578 computeYMD_HMS(p); 579 p->validTZ = 0; 580 p->validJD = 0; 581 computeJD(p); 582 Z = ((p->iJD + 129600000)/86400000) % 7; 583 if( Z>n ) Z -= 7; 584 p->iJD += (n - Z)*86400000; 585 clearYMD_HMS_TZ(p); 586 rc = 0; 587 } 588 break; 589 } 590 case 's': { 591 /* 592 ** start of TTTTT 593 ** 594 ** Move the date backwards to the beginning of the current day, 595 ** or month or year. 596 */ 597 if( strncmp(z, "start of ", 9)!=0 ) break; 598 z += 9; 599 computeYMD(p); 600 p->validHMS = 1; 601 p->h = p->m = 0; 602 p->s = 0.0; 603 p->validTZ = 0; 604 p->validJD = 0; 605 if( strcmp(z,"month")==0 ){ 606 p->D = 1; 607 rc = 0; 608 }else if( strcmp(z,"year")==0 ){ 609 computeYMD(p); 610 p->M = 1; 611 p->D = 1; 612 rc = 0; 613 }else if( strcmp(z,"day")==0 ){ 614 rc = 0; 615 } 616 break; 617 } 618 case '+': 619 case '-': 620 case '0': 621 case '1': 622 case '2': 623 case '3': 624 case '4': 625 case '5': 626 case '6': 627 case '7': 628 case '8': 629 case '9': { 630 n = getValue(z, &r); 631 assert( n>=1 ); 632 if( z[n]==':' ){ 633 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the 634 ** specified number of hours, minutes, seconds, and fractional seconds 635 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be 636 ** omitted. 637 */ 638 const char *z2 = z; 639 DateTime tx; 640 sqlite3_int64 day; 641 if( !isdigit(*(u8*)z2) ) z2++; 642 memset(&tx, 0, sizeof(tx)); 643 if( parseHhMmSs(z2, &tx) ) break; 644 computeJD(&tx); 645 tx.iJD -= 43200000; 646 day = tx.iJD/86400000; 647 tx.iJD -= day*86400000; 648 if( z[0]=='-' ) tx.iJD = -tx.iJD; 649 computeJD(p); 650 clearYMD_HMS_TZ(p); 651 p->iJD += tx.iJD; 652 rc = 0; 653 break; 654 } 655 z += n; 656 while( isspace(*(u8*)z) ) z++; 657 n = strlen(z); 658 if( n>10 || n<3 ) break; 659 if( z[n-1]=='s' ){ z[n-1] = 0; n--; } 660 computeJD(p); 661 rc = 0; 662 if( n==3 && strcmp(z,"day")==0 ){ 663 p->iJD += r*86400000.0 + 0.5; 664 }else if( n==4 && strcmp(z,"hour")==0 ){ 665 p->iJD += r*(86400000.0/24.0) + 0.5; 666 }else if( n==6 && strcmp(z,"minute")==0 ){ 667 p->iJD += r*(86400000.0/(24.0*60.0)) + 0.5; 668 }else if( n==6 && strcmp(z,"second")==0 ){ 669 p->iJD += r*(86400000.0/(24.0*60.0*60.0)) + 0.5; 670 }else if( n==5 && strcmp(z,"month")==0 ){ 671 int x, y; 672 computeYMD_HMS(p); 673 p->M += r; 674 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; 675 p->Y += x; 676 p->M -= x*12; 677 p->validJD = 0; 678 computeJD(p); 679 y = r; 680 if( y!=r ){ 681 p->iJD += (r - y)*30.0*86400000.0 + 0.5; 682 } 683 }else if( n==4 && strcmp(z,"year")==0 ){ 684 computeYMD_HMS(p); 685 p->Y += r; 686 p->validJD = 0; 687 computeJD(p); 688 }else{ 689 rc = 1; 690 } 691 clearYMD_HMS_TZ(p); 692 break; 693 } 694 default: { 695 break; 696 } 697 } 698 return rc; 699 } 700 701 /* 702 ** Process time function arguments. argv[0] is a date-time stamp. 703 ** argv[1] and following are modifiers. Parse them all and write 704 ** the resulting time into the DateTime structure p. Return 0 705 ** on success and 1 if there are any errors. 706 ** 707 ** If there are zero parameters (if even argv[0] is undefined) 708 ** then assume a default value of "now" for argv[0]. 709 */ 710 static int isDate( 711 sqlite3_context *context, 712 int argc, 713 sqlite3_value **argv, 714 DateTime *p 715 ){ 716 int i; 717 const unsigned char *z; 718 int eType; 719 memset(p, 0, sizeof(*p)); 720 if( argc==0 ){ 721 setDateTimeToCurrent(context, p); 722 }else if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT 723 || eType==SQLITE_INTEGER ){ 724 p->iJD = sqlite3_value_double(argv[0])*86400000.0 + 0.5; 725 p->validJD = 1; 726 }else{ 727 z = sqlite3_value_text(argv[0]); 728 if( !z || parseDateOrTime(context, (char*)z, p) ){ 729 return 1; 730 } 731 } 732 for(i=1; i<argc; i++){ 733 if( (z = sqlite3_value_text(argv[i]))==0 || parseModifier((char*)z, p) ){ 734 return 1; 735 } 736 } 737 return 0; 738 } 739 740 741 /* 742 ** The following routines implement the various date and time functions 743 ** of SQLite. 744 */ 745 746 /* 747 ** julianday( TIMESTRING, MOD, MOD, ...) 748 ** 749 ** Return the julian day number of the date specified in the arguments 750 */ 751 static void juliandayFunc( 752 sqlite3_context *context, 753 int argc, 754 sqlite3_value **argv 755 ){ 756 DateTime x; 757 if( isDate(context, argc, argv, &x)==0 ){ 758 computeJD(&x); 759 sqlite3_result_double(context, x.iJD/86400000.0); 760 } 761 } 762 763 /* 764 ** datetime( TIMESTRING, MOD, MOD, ...) 765 ** 766 ** Return YYYY-MM-DD HH:MM:SS 767 */ 768 static void datetimeFunc( 769 sqlite3_context *context, 770 int argc, 771 sqlite3_value **argv 772 ){ 773 DateTime x; 774 if( isDate(context, argc, argv, &x)==0 ){ 775 char zBuf[100]; 776 computeYMD_HMS(&x); 777 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d", 778 x.Y, x.M, x.D, x.h, x.m, (int)(x.s)); 779 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 780 } 781 } 782 783 /* 784 ** time( TIMESTRING, MOD, MOD, ...) 785 ** 786 ** Return HH:MM:SS 787 */ 788 static void timeFunc( 789 sqlite3_context *context, 790 int argc, 791 sqlite3_value **argv 792 ){ 793 DateTime x; 794 if( isDate(context, argc, argv, &x)==0 ){ 795 char zBuf[100]; 796 computeHMS(&x); 797 sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s); 798 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 799 } 800 } 801 802 /* 803 ** date( TIMESTRING, MOD, MOD, ...) 804 ** 805 ** Return YYYY-MM-DD 806 */ 807 static void dateFunc( 808 sqlite3_context *context, 809 int argc, 810 sqlite3_value **argv 811 ){ 812 DateTime x; 813 if( isDate(context, argc, argv, &x)==0 ){ 814 char zBuf[100]; 815 computeYMD(&x); 816 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D); 817 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 818 } 819 } 820 821 /* 822 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...) 823 ** 824 ** Return a string described by FORMAT. Conversions as follows: 825 ** 826 ** %d day of month 827 ** %f ** fractional seconds SS.SSS 828 ** %H hour 00-24 829 ** %j day of year 000-366 830 ** %J ** Julian day number 831 ** %m month 01-12 832 ** %M minute 00-59 833 ** %s seconds since 1970-01-01 834 ** %S seconds 00-59 835 ** %w day of week 0-6 sunday==0 836 ** %W week of year 00-53 837 ** %Y year 0000-9999 838 ** %% % 839 */ 840 static void strftimeFunc( 841 sqlite3_context *context, 842 int argc, 843 sqlite3_value **argv 844 ){ 845 DateTime x; 846 u64 n; 847 int i, j; 848 char *z; 849 const char *zFmt = (const char*)sqlite3_value_text(argv[0]); 850 char zBuf[100]; 851 if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return; 852 for(i=0, n=1; zFmt[i]; i++, n++){ 853 if( zFmt[i]=='%' ){ 854 switch( zFmt[i+1] ){ 855 case 'd': 856 case 'H': 857 case 'm': 858 case 'M': 859 case 'S': 860 case 'W': 861 n++; 862 /* fall thru */ 863 case 'w': 864 case '%': 865 break; 866 case 'f': 867 n += 8; 868 break; 869 case 'j': 870 n += 3; 871 break; 872 case 'Y': 873 n += 8; 874 break; 875 case 's': 876 case 'J': 877 n += 50; 878 break; 879 default: 880 return; /* ERROR. return a NULL */ 881 } 882 i++; 883 } 884 } 885 if( n<sizeof(zBuf) ){ 886 z = zBuf; 887 }else if( n>sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH] ){ 888 sqlite3_result_error_toobig(context); 889 return; 890 }else{ 891 z = sqlite3Malloc( n ); 892 if( z==0 ){ 893 sqlite3_result_error_nomem(context); 894 return; 895 } 896 } 897 computeJD(&x); 898 computeYMD_HMS(&x); 899 for(i=j=0; zFmt[i]; i++){ 900 if( zFmt[i]!='%' ){ 901 z[j++] = zFmt[i]; 902 }else{ 903 i++; 904 switch( zFmt[i] ){ 905 case 'd': sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break; 906 case 'f': { 907 double s = x.s; 908 if( s>59.999 ) s = 59.999; 909 sqlite3_snprintf(7, &z[j],"%06.3f", s); 910 j += strlen(&z[j]); 911 break; 912 } 913 case 'H': sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break; 914 case 'W': /* Fall thru */ 915 case 'j': { 916 int nDay; /* Number of days since 1st day of year */ 917 DateTime y = x; 918 y.validJD = 0; 919 y.M = 1; 920 y.D = 1; 921 computeJD(&y); 922 nDay = (x.iJD - y.iJD)/86400000.0 + 0.5; 923 if( zFmt[i]=='W' ){ 924 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */ 925 wd = ((x.iJD+43200000)/86400000) % 7; 926 sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7); 927 j += 2; 928 }else{ 929 sqlite3_snprintf(4, &z[j],"%03d",nDay+1); 930 j += 3; 931 } 932 break; 933 } 934 case 'J': { 935 sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0); 936 j+=strlen(&z[j]); 937 break; 938 } 939 case 'm': sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break; 940 case 'M': sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break; 941 case 's': { 942 sqlite3_snprintf(30,&z[j],"%d", 943 (int)(x.iJD/1000.0 - 210866760000.0)); 944 j += strlen(&z[j]); 945 break; 946 } 947 case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break; 948 case 'w': z[j++] = (((x.iJD+129600000)/86400000) % 7) + '0'; break; 949 case 'Y': sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=strlen(&z[j]);break; 950 default: z[j++] = '%'; break; 951 } 952 } 953 } 954 z[j] = 0; 955 sqlite3_result_text(context, z, -1, 956 z==zBuf ? SQLITE_TRANSIENT : sqlite3_free); 957 } 958 959 /* 960 ** current_time() 961 ** 962 ** This function returns the same value as time('now'). 963 */ 964 static void ctimeFunc( 965 sqlite3_context *context, 966 int argc, 967 sqlite3_value **argv 968 ){ 969 timeFunc(context, 0, 0); 970 } 971 972 /* 973 ** current_date() 974 ** 975 ** This function returns the same value as date('now'). 976 */ 977 static void cdateFunc( 978 sqlite3_context *context, 979 int argc, 980 sqlite3_value **argv 981 ){ 982 dateFunc(context, 0, 0); 983 } 984 985 /* 986 ** current_timestamp() 987 ** 988 ** This function returns the same value as datetime('now'). 989 */ 990 static void ctimestampFunc( 991 sqlite3_context *context, 992 int argc, 993 sqlite3_value **argv 994 ){ 995 datetimeFunc(context, 0, 0); 996 } 997 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */ 998 999 #ifdef SQLITE_OMIT_DATETIME_FUNCS 1000 /* 1001 ** If the library is compiled to omit the full-scale date and time 1002 ** handling (to get a smaller binary), the following minimal version 1003 ** of the functions current_time(), current_date() and current_timestamp() 1004 ** are included instead. This is to support column declarations that 1005 ** include "DEFAULT CURRENT_TIME" etc. 1006 ** 1007 ** This function uses the C-library functions time(), gmtime() 1008 ** and strftime(). The format string to pass to strftime() is supplied 1009 ** as the user-data for the function. 1010 */ 1011 static void currentTimeFunc( 1012 sqlite3_context *context, 1013 int argc, 1014 sqlite3_value **argv 1015 ){ 1016 time_t t; 1017 char *zFormat = (char *)sqlite3_user_data(context); 1018 sqlite3 *db; 1019 double rT; 1020 char zBuf[20]; 1021 1022 db = sqlite3_context_db_handle(context); 1023 sqlite3OsCurrentTime(db->pVfs, &rT); 1024 t = 86400.0*(rT - 2440587.5) + 0.5; 1025 #ifdef HAVE_GMTIME_R 1026 { 1027 struct tm sNow; 1028 gmtime_r(&t, &sNow); 1029 strftime(zBuf, 20, zFormat, &sNow); 1030 } 1031 #else 1032 { 1033 struct tm *pTm; 1034 sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER)); 1035 pTm = gmtime(&t); 1036 strftime(zBuf, 20, zFormat, pTm); 1037 sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER)); 1038 } 1039 #endif 1040 1041 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 1042 } 1043 #endif 1044 1045 /* 1046 ** This function registered all of the above C functions as SQL 1047 ** functions. This should be the only routine in this file with 1048 ** external linkage. 1049 */ 1050 void sqlite3RegisterDateTimeFunctions(sqlite3 *db){ 1051 #ifndef SQLITE_OMIT_DATETIME_FUNCS 1052 static const struct { 1053 char *zName; 1054 int nArg; 1055 void (*xFunc)(sqlite3_context*,int,sqlite3_value**); 1056 } aFuncs[] = { 1057 { "julianday", -1, juliandayFunc }, 1058 { "date", -1, dateFunc }, 1059 { "time", -1, timeFunc }, 1060 { "datetime", -1, datetimeFunc }, 1061 { "strftime", -1, strftimeFunc }, 1062 { "current_time", 0, ctimeFunc }, 1063 { "current_timestamp", 0, ctimestampFunc }, 1064 { "current_date", 0, cdateFunc }, 1065 }; 1066 int i; 1067 1068 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ 1069 sqlite3CreateFunc(db, aFuncs[i].zName, aFuncs[i].nArg, 1070 SQLITE_UTF8, 0, aFuncs[i].xFunc, 0, 0); 1071 } 1072 #else 1073 static const struct { 1074 char *zName; 1075 char *zFormat; 1076 } aFuncs[] = { 1077 { "current_time", "%H:%M:%S" }, 1078 { "current_date", "%Y-%m-%d" }, 1079 { "current_timestamp", "%Y-%m-%d %H:%M:%S" } 1080 }; 1081 int i; 1082 1083 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ 1084 sqlite3CreateFunc(db, aFuncs[i].zName, 0, SQLITE_UTF8, 1085 aFuncs[i].zFormat, currentTimeFunc, 0, 0); 1086 } 1087 #endif 1088 } 1089