src/date.c

   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 ** SQLite processes all times and dates as julian day numbers.  The
  20 ** dates and times are stored as the number of days since noon
  21 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian
  22 ** calendar system.
  23 **
  24 ** 1970-01-01 00:00:00 is JD 2440587.5
  25 ** 2000-01-01 00:00:00 is JD 2451544.5
  26 **
  27 ** This implementation requires years to be expressed as a 4-digit number
  28 ** which means that only dates between 0000-01-01 and 9999-12-31 can
  29 ** be represented, even though julian day numbers allow a much wider
  30 ** range of dates.
  31 **
  32 ** The Gregorian calendar system is used for all dates and times,
  33 ** even those that predate the Gregorian calendar.  Historians usually
  34 ** use the julian calendar for dates prior to 1582-10-15 and for some
  35 ** dates afterwards, depending on locale.  Beware of this difference.
  36 **
  37 ** The conversion algorithms are implemented based on descriptions
  38 ** in the following text:
  39 **
  40 **      Jean Meeus
  41 **      Astronomical Algorithms, 2nd Edition, 1998
  42 **      ISBM 0-943396-61-1
  43 **      Willmann-Bell, Inc
  44 **      Richmond, Virginia (USA)
  45 */
  46 #include "sqliteInt.h"
  47 #include <stdlib.h>
  48 #include <assert.h>
  49 #include <time.h>
  50
  51 #ifndef SQLITE_OMIT_DATETIME_FUNCS
  52
  53 /*
  54 ** The MSVC CRT on Windows CE may not have a localtime() function.
  55 ** So declare a substitute.  The substitute function itself is
  56 ** defined in "os_win.c".
  57 */
  58 #if !defined(SQLITE_OMIT_LOCALTIME) && defined(_WIN32_WCE) && \
  59     (!defined(SQLITE_MSVC_LOCALTIME_API) || !SQLITE_MSVC_LOCALTIME_API)
  60 struct tm *__cdecl localtime(const time_t *);
  61 #endif
  62
  63 /*
  64 ** A structure for holding a single date and time.
  65 */
  66 typedef struct DateTime DateTime;
  67 struct DateTime {
  68   sqlite3_int64 iJD;  /* The julian day number times 86400000 */
  69   int Y, M, D;        /* Year, month, and day */
  70   int h, m;           /* Hour and minutes */
  71   int tz;             /* Timezone offset in minutes */
  72   double s;           /* Seconds */
  73   char validJD;       /* True (1) if iJD is valid */
  74   char rawS;          /* Raw numeric value stored in s */
  75   char validYMD;      /* True (1) if Y,M,D are valid */
  76   char validHMS;      /* True (1) if h,m,s are valid */
  77   char validTZ;       /* True (1) if tz is valid */
  78   char tzSet;         /* Timezone was set explicitly */
  79   char isError;       /* An overflow has occurred */
  80 };
  81
  82
  83 /*
  84 ** Convert zDate into one or more integers according to the conversion
  85 ** specifier zFormat.
  86 **
  87 ** zFormat[] contains 4 characters for each integer converted, except for
  88 ** the last integer which is specified by three characters.  The meaning
  89 ** of a four-character format specifiers ABCD is:
  90 **
  91 **    A:   number of digits to convert.  Always "2" or "4".
  92 **    B:   minimum value.  Always "0" or "1".
  93 **    C:   maximum value, decoded as:
  94 **           a:  12
  95 **           b:  14
  96 **           c:  24
  97 **           d:  31
  98 **           e:  59
  99 **           f:  9999
 100 **    D:   the separator character, or \000 to indicate this is the
 101 **         last number to convert.
 102 **
 103 ** Example:  To translate an ISO-8601 date YYYY-MM-DD, the format would
 104 ** be "40f-21a-20c".  The "40f-" indicates the 4-digit year followed by "-".
 105 ** The "21a-" indicates the 2-digit month followed by "-".  The "20c" indicates
 106 ** the 2-digit day which is the last integer in the set.
 107 **
 108 ** The function returns the number of successful conversions.
 109 */
 110 static int getDigits(const char *zDate, const char *zFormat, ...){
 111   /* The aMx[] array translates the 3rd character of each format
 112   ** spec into a max size:    a   b   c   d   e     f */
 113   static const u16 aMx[] = { 12, 14, 24, 31, 59, 9999 };
 114   va_list ap;
 115   int cnt = 0;
 116   char nextC;
 117   va_start(ap, zFormat);
 118   do{
 119     char N = zFormat[0] - '0';
 120     char min = zFormat[1] - '0';
 121     int val = 0;
 122     u16 max;
 123
 124     assert( zFormat[2]>='a' && zFormat[2]<='f' );
 125     max = aMx[zFormat[2] - 'a'];
 126     nextC = zFormat[3];
 127     val = 0;
 128     while( N-- ){
 129       if( !sqlite3Isdigit(*zDate) ){
 130         goto end_getDigits;
 131       }
 132       val = val*10 + *zDate - '0';
 133       zDate++;
 134     }
 135     if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){
 136       goto end_getDigits;
 137     }
 138     *va_arg(ap,int*) = val;
 139     zDate++;
 140     cnt++;
 141     zFormat += 4;
 142   }while( nextC );
 143 end_getDigits:
 144   va_end(ap);
 145   return cnt;
 146 }
 147
 148 /*
 149 ** Parse a timezone extension on the end of a date-time.
 150 ** The extension is of the form:
 151 **
 152 **        (+/-)HH:MM
 153 **
 154 ** Or the "zulu" notation:
 155 **
 156 **        Z
 157 **
 158 ** If the parse is successful, write the number of minutes
 159 ** of change in p->tz and return 0.  If a parser error occurs,
 160 ** return non-zero.
 161 **
 162 ** A missing specifier is not considered an error.
 163 */
 164 static int parseTimezone(const char *zDate, DateTime *p){
 165   int sgn = 0;
 166   int nHr, nMn;
 167   int c;
 168   while( sqlite3Isspace(*zDate) ){ zDate++; }
 169   p->tz = 0;
 170   c = *zDate;
 171   if( c=='-' ){
 172     sgn = -1;
 173   }else if( c=='+' ){
 174     sgn = +1;
 175   }else if( c=='Z' || c=='z' ){
 176     zDate++;
 177     goto zulu_time;
 178   }else{
 179     return c!=0;
 180   }
 181   zDate++;
 182   if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){
 183     return 1;
 184   }
 185   zDate += 5;
 186   p->tz = sgn*(nMn + nHr*60);
 187 zulu_time:
 188   while( sqlite3Isspace(*zDate) ){ zDate++; }
 189   p->tzSet = 1;
 190   return *zDate!=0;
 191 }
 192
 193 /*
 194 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
 195 ** The HH, MM, and SS must each be exactly 2 digits.  The
 196 ** fractional seconds FFFF can be one or more digits.
 197 **
 198 ** Return 1 if there is a parsing error and 0 on success.
 199 */
 200 static int parseHhMmSs(const char *zDate, DateTime *p){
 201   int h, m, s;
 202   double ms = 0.0;
 203   if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){
 204     return 1;
 205   }
 206   zDate += 5;
 207   if( *zDate==':' ){
 208     zDate++;
 209     if( getDigits(zDate, "20e", &s)!=1 ){
 210       return 1;
 211     }
 212     zDate += 2;
 213     if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){
 214       double rScale = 1.0;
 215       zDate++;
 216       while( sqlite3Isdigit(*zDate) ){
 217         ms = ms*10.0 + *zDate - '0';
 218         rScale *= 10.0;
 219         zDate++;
 220       }
 221       ms /= rScale;
 222     }
 223   }else{
 224     s = 0;
 225   }
 226   p->validJD = 0;
 227   p->rawS = 0;
 228   p->validHMS = 1;
 229   p->h = h;
 230   p->m = m;
 231   p->s = s + ms;
 232   if( parseTimezone(zDate, p) ) return 1;
 233   p->validTZ = (p->tz!=0)?1:0;
 234   return 0;
 235 }
 236
 237 /*
 238 ** Put the DateTime object into its error state.
 239 */
 240 static void datetimeError(DateTime *p){
 241   memset(p, 0, sizeof(*p));
 242   p->isError = 1;
 243 }
 244
 245 /*
 246 ** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume
 247 ** that the YYYY-MM-DD is according to the Gregorian calendar.
 248 **
 249 ** Reference:  Meeus page 61
 250 */
 251 static void computeJD(DateTime *p){
 252   int Y, M, D, A, B, X1, X2;
 253
 254   if( p->validJD ) return;
 255   if( p->validYMD ){
 256     Y = p->Y;
 257     M = p->M;
 258     D = p->D;
 259   }else{
 260     Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */
 261     M = 1;
 262     D = 1;
 263   }
 264   if( Y<-4713 || Y>9999 || p->rawS ){
 265     datetimeError(p);
 266     return;
 267   }
 268   if( M<=2 ){
 269     Y--;
 270     M += 12;
 271   }
 272   A = Y/100;
 273   B = 2 - A + (A/4);
 274   X1 = 36525*(Y+4716)/100;
 275   X2 = 306001*(M+1)/10000;
 276   p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
 277   p->validJD = 1;
 278   if( p->validHMS ){
 279     p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000);
 280     if( p->validTZ ){
 281       p->iJD -= p->tz*60000;
 282       p->validYMD = 0;
 283       p->validHMS = 0;
 284       p->validTZ = 0;
 285     }
 286   }
 287 }
 288
 289 /*
 290 ** Parse dates of the form
 291 **
 292 **     YYYY-MM-DD HH:MM:SS.FFF
 293 **     YYYY-MM-DD HH:MM:SS
 294 **     YYYY-MM-DD HH:MM
 295 **     YYYY-MM-DD
 296 **
 297 ** Write the result into the DateTime structure and return 0
 298 ** on success and 1 if the input string is not a well-formed
 299 ** date.
 300 */
 301 static int parseYyyyMmDd(const char *zDate, DateTime *p){
 302   int Y, M, D, neg;
 303
 304   if( zDate[0]=='-' ){
 305     zDate++;
 306     neg = 1;
 307   }else{
 308     neg = 0;
 309   }
 310   if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){
 311     return 1;
 312   }
 313   zDate += 10;
 314   while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }
 315   if( parseHhMmSs(zDate, p)==0 ){
 316     /* We got the time */
 317   }else if( *zDate==0 ){
 318     p->validHMS = 0;
 319   }else{
 320     return 1;
 321   }
 322   p->validJD = 0;
 323   p->validYMD = 1;
 324   p->Y = neg ? -Y : Y;
 325   p->M = M;
 326   p->D = D;
 327   if( p->validTZ ){
 328     computeJD(p);
 329   }
 330   return 0;
 331 }
 332
 333 /*
 334 ** Set the time to the current time reported by the VFS.
 335 **
 336 ** Return the number of errors.
 337 */
 338 static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
 339   p->iJD = sqlite3StmtCurrentTime(context);
 340   if( p->iJD>0 ){
 341     p->validJD = 1;
 342     return 0;
 343   }else{
 344     return 1;
 345   }
 346 }
 347
 348 /*
 349 ** Input "r" is a numeric quantity which might be a julian day number,
 350 ** or the number of seconds since 1970.  If the value if r is within
 351 ** range of a julian day number, install it as such and set validJD.
 352 ** If the value is a valid unix timestamp, put it in p->s and set p->rawS.
 353 */
 354 static void setRawDateNumber(DateTime *p, double r){
 355   p->s = r;
 356   p->rawS = 1;
 357   if( r>=0.0 && r<5373484.5 ){
 358     p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
 359     p->validJD = 1;
 360   }
 361 }
 362
 363 /*
 364 ** Attempt to parse the given string into a julian day number.  Return
 365 ** the number of errors.
 366 **
 367 ** The following are acceptable forms for the input string:
 368 **
 369 **      YYYY-MM-DD HH:MM:SS.FFF  +/-HH:MM
 370 **      DDDD.DD
 371 **      now
 372 **
 373 ** In the first form, the +/-HH:MM is always optional.  The fractional
 374 ** seconds extension (the ".FFF") is optional.  The seconds portion
 375 ** (":SS.FFF") is option.  The year and date can be omitted as long
 376 ** as there is a time string.  The time string can be omitted as long
 377 ** as there is a year and date.
 378 */
 379 static int parseDateOrTime(
 380   sqlite3_context *context,
 381   const char *zDate,
 382   DateTime *p
 383 ){
 384   double r;
 385   if( parseYyyyMmDd(zDate,p)==0 ){
 386     return 0;
 387   }else if( parseHhMmSs(zDate, p)==0 ){
 388     return 0;
 389   }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
 390     return setDateTimeToCurrent(context, p);
 391   }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){
 392     setRawDateNumber(p, r);
 393     return 0;
 394   }
 395   return 1;
 396 }
 397
 398 /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999.
 399 ** Multiplying this by 86400000 gives 464269060799999 as the maximum value
 400 ** for DateTime.iJD.
 401 **
 402 ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with
 403 ** such a large integer literal, so we have to encode it.
 404 */
 405 #define INT_464269060799999  ((((i64)0x1a640)<<32)|0x1072fdff)
 406
 407 /*
 408 ** Return TRUE if the given julian day number is within range.
 409 **
 410 ** The input is the JulianDay times 86400000.
 411 */
 412 static int validJulianDay(sqlite3_int64 iJD){
 413   return iJD>=0 && iJD<=INT_464269060799999;
 414 }
 415
 416 /*
 417 ** Compute the Year, Month, and Day from the julian day number.
 418 */
 419 static void computeYMD(DateTime *p){
 420   int Z, A, B, C, D, E, X1;
 421   if( p->validYMD ) return;
 422   if( !p->validJD ){
 423     p->Y = 2000;
 424     p->M = 1;
 425     p->D = 1;
 426   }else if( !validJulianDay(p->iJD) ){
 427     datetimeError(p);
 428     return;
 429   }else{
 430     Z = (int)((p->iJD + 43200000)/86400000);
 431     A = (int)((Z - 1867216.25)/36524.25);
 432     A = Z + 1 + A - (A/4);
 433     B = A + 1524;
 434     C = (int)((B - 122.1)/365.25);
 435     D = (36525*(C&32767))/100;
 436     E = (int)((B-D)/30.6001);
 437     X1 = (int)(30.6001*E);
 438     p->D = B - D - X1;
 439     p->M = E<14 ? E-1 : E-13;
 440     p->Y = p->M>2 ? C - 4716 : C - 4715;
 441   }
 442   p->validYMD = 1;
 443 }
 444
 445 /*
 446 ** Compute the Hour, Minute, and Seconds from the julian day number.
 447 */
 448 static void computeHMS(DateTime *p){
 449   int s;
 450   if( p->validHMS ) return;
 451   computeJD(p);
 452   s = (int)((p->iJD + 43200000) % 86400000);
 453   p->s = s/1000.0;
 454   s = (int)p->s;
 455   p->s -= s;
 456   p->h = s/3600;
 457   s -= p->h*3600;
 458   p->m = s/60;
 459   p->s += s - p->m*60;
 460   p->rawS = 0;
 461   p->validHMS = 1;
 462 }
 463
 464 /*
 465 ** Compute both YMD and HMS
 466 */
 467 static void computeYMD_HMS(DateTime *p){
 468   computeYMD(p);
 469   computeHMS(p);
 470 }
 471
 472 /*
 473 ** Clear the YMD and HMS and the TZ
 474 */
 475 static void clearYMD_HMS_TZ(DateTime *p){
 476   p->validYMD = 0;
 477   p->validHMS = 0;
 478   p->validTZ = 0;
 479 }
 480
 481 #ifndef SQLITE_OMIT_LOCALTIME
 482 /*
 483 ** On recent Windows platforms, the localtime_s() function is available
 484 ** as part of the "Secure CRT". It is essentially equivalent to
 485 ** localtime_r() available under most POSIX platforms, except that the
 486 ** order of the parameters is reversed.
 487 **
 488 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
 489 **
 490 ** If the user has not indicated to use localtime_r() or localtime_s()
 491 ** already, check for an MSVC build environment that provides
 492 ** localtime_s().
 493 */
 494 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \
 495     && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
 496 #undef  HAVE_LOCALTIME_S
 497 #define HAVE_LOCALTIME_S 1
 498 #endif
 499
 500 /*
 501 ** The following routine implements the rough equivalent of localtime_r()
 502 ** using whatever operating-system specific localtime facility that
 503 ** is available.  This routine returns 0 on success and
 504 ** non-zero on any kind of error.
 505 **
 506 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is true then this
 507 ** routine will always fail.
 508 **
 509 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
 510 ** library function localtime_r() is used to assist in the calculation of
 511 ** local time.
 512 */
 513 static int osLocaltime(time_t *t, struct tm *pTm){
 514   int rc;
 515 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S
 516   struct tm *pX;
 517 #if SQLITE_THREADSAFE>0
 518   sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER);
 519 #endif
 520   sqlite3_mutex_enter(mutex);
 521   pX = localtime(t);
 522 #ifndef SQLITE_UNTESTABLE
 523   if( sqlite3GlobalConfig.bLocaltimeFault ) pX = 0;
 524 #endif
 525   if( pX ) *pTm = *pX;
 526   sqlite3_mutex_leave(mutex);
 527   rc = pX==0;
 528 #else
 529 #ifndef SQLITE_UNTESTABLE
 530   if( sqlite3GlobalConfig.bLocaltimeFault ) return 1;
 531 #endif
 532 #if HAVE_LOCALTIME_R
 533   rc = localtime_r(t, pTm)==0;
 534 #else
 535   rc = localtime_s(pTm, t);
 536 #endif /* HAVE_LOCALTIME_R */
 537 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
 538   return rc;
 539 }
 540 #endif /* SQLITE_OMIT_LOCALTIME */
 541
 542
 543 #ifndef SQLITE_OMIT_LOCALTIME
 544 /*
 545 ** Compute the difference (in milliseconds) between localtime and UTC
 546 ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs,
 547 ** return this value and set *pRc to SQLITE_OK.
 548 **
 549 ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value
 550 ** is undefined in this case.
 551 */
 552 static sqlite3_int64 localtimeOffset(
 553   DateTime *p,                    /* Date at which to calculate offset */
 554   sqlite3_context *pCtx,          /* Write error here if one occurs */
 555   int *pRc                        /* OUT: Error code. SQLITE_OK or ERROR */
 556 ){
 557   DateTime x, y;
 558   time_t t;
 559   struct tm sLocal;
 560
 561   /* Initialize the contents of sLocal to avoid a compiler warning. */
 562   memset(&sLocal, 0, sizeof(sLocal));
 563
 564   x = *p;
 565   computeYMD_HMS(&x);
 566   if( x.Y<1971 || x.Y>=2038 ){
 567     /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
 568     ** works for years between 1970 and 2037. For dates outside this range,
 569     ** SQLite attempts to map the year into an equivalent year within this
 570     ** range, do the calculation, then map the year back.
 571     */
 572     x.Y = 2000;
 573     x.M = 1;
 574     x.D = 1;
 575     x.h = 0;
 576     x.m = 0;
 577     x.s = 0.0;
 578   } else {
 579     int s = (int)(x.s + 0.5);
 580     x.s = s;
 581   }
 582   x.tz = 0;
 583   x.validJD = 0;
 584   computeJD(&x);
 585   t = (time_t)(x.iJD/1000 - 21086676*(i64)10000);
 586   if( osLocaltime(&t, &sLocal) ){
 587     sqlite3_result_error(pCtx, "local time unavailable", -1);
 588     *pRc = SQLITE_ERROR;
 589     return 0;
 590   }
 591   y.Y = sLocal.tm_year + 1900;
 592   y.M = sLocal.tm_mon + 1;
 593   y.D = sLocal.tm_mday;
 594   y.h = sLocal.tm_hour;
 595   y.m = sLocal.tm_min;
 596   y.s = sLocal.tm_sec;
 597   y.validYMD = 1;
 598   y.validHMS = 1;
 599   y.validJD = 0;
 600   y.rawS = 0;
 601   y.validTZ = 0;
 602   y.isError = 0;
 603   computeJD(&y);
 604   *pRc = SQLITE_OK;
 605   return y.iJD - x.iJD;
 606 }
 607 #endif /* SQLITE_OMIT_LOCALTIME */
 608
 609 /*
 610 ** The following table defines various date transformations of the form
 611 **
 612 **            'NNN days'
 613 **
 614 ** Where NNN is an arbitrary floating-point number and "days" can be one
 615 ** of several units of time.
 616 */
 617 static const struct {
 618   u8 eType;           /* Transformation type code */
 619   u8 nName;           /* Length of th name */
 620   char *zName;        /* Name of the transformation */
 621   double rLimit;      /* Maximum NNN value for this transform */
 622   double rXform;      /* Constant used for this transform */
 623 } aXformType[] = {
 624   { 0, 6, "second", 464269060800.0, 86400000.0/(24.0*60.0*60.0) },
 625   { 0, 6, "minute", 7737817680.0,   86400000.0/(24.0*60.0)      },
 626   { 0, 4, "hour",   128963628.0,    86400000.0/24.0             },
 627   { 0, 3, "day",    5373485.0,      86400000.0                  },
 628   { 1, 5, "month",  176546.0,       30.0*86400000.0             },
 629   { 2, 4, "year",   14713.0,        365.0*86400000.0            },
 630 };
 631
 632 /*
 633 ** Process a modifier to a date-time stamp.  The modifiers are
 634 ** as follows:
 635 **
 636 **     NNN days
 637 **     NNN hours
 638 **     NNN minutes
 639 **     NNN.NNNN seconds
 640 **     NNN months
 641 **     NNN years
 642 **     start of month
 643 **     start of year
 644 **     start of week
 645 **     start of day
 646 **     weekday N
 647 **     unixepoch
 648 **     localtime
 649 **     utc
 650 **
 651 ** Return 0 on success and 1 if there is any kind of error. If the error
 652 ** is in a system call (i.e. localtime()), then an error message is written
 653 ** to context pCtx. If the error is an unrecognized modifier, no error is
 654 ** written to pCtx.
 655 */
 656 static int parseModifier(
 657   sqlite3_context *pCtx,      /* Function context */
 658   const char *z,              /* The text of the modifier */
 659   int n,                      /* Length of zMod in bytes */
 660   DateTime *p                 /* The date/time value to be modified */
 661 ){
 662   int rc = 1;
 663   double r;
 664   switch(sqlite3UpperToLower[(u8)z[0]] ){
 665 #ifndef SQLITE_OMIT_LOCALTIME
 666     case 'l': {
 667       /*    localtime
 668       **
 669       ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
 670       ** show local time.
 671       */
 672       if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){
 673         computeJD(p);
 674         p->iJD += localtimeOffset(p, pCtx, &rc);
 675         clearYMD_HMS_TZ(p);
 676       }
 677       break;
 678     }
 679 #endif
 680     case 'u': {
 681       /*
 682       **    unixepoch
 683       **
 684       ** Treat the current value of p->s as the number of
 685       ** seconds since 1970.  Convert to a real julian day number.
 686       */
 687       if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){
 688         r = p->s*1000.0 + 210866760000000.0;
 689         if( r>=0.0 && r<464269060800000.0 ){
 690           clearYMD_HMS_TZ(p);
 691           p->iJD = (sqlite3_int64)r;
 692           p->validJD = 1;
 693           p->rawS = 0;
 694           rc = 0;
 695         }
 696       }
 697 #ifndef SQLITE_OMIT_LOCALTIME
 698       else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){
 699         if( p->tzSet==0 ){
 700           sqlite3_int64 c1;
 701           computeJD(p);
 702           c1 = localtimeOffset(p, pCtx, &rc);
 703           if( rc==SQLITE_OK ){
 704             p->iJD -= c1;
 705             clearYMD_HMS_TZ(p);
 706             p->iJD += c1 - localtimeOffset(p, pCtx, &rc);
 707           }
 708           p->tzSet = 1;
 709         }else{
 710           rc = SQLITE_OK;
 711         }
 712       }
 713 #endif
 714       break;
 715     }
 716     case 'w': {
 717       /*
 718       **    weekday N
 719       **
 720       ** Move the date to the same time on the next occurrence of
 721       ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
 722       ** date is already on the appropriate weekday, this is a no-op.
 723       */
 724       if( sqlite3_strnicmp(z, "weekday ", 8)==0
 725                && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)
 726                && (n=(int)r)==r && n>=0 && r<7 ){
 727         sqlite3_int64 Z;
 728         computeYMD_HMS(p);
 729         p->validTZ = 0;
 730         p->validJD = 0;
 731         computeJD(p);
 732         Z = ((p->iJD + 129600000)/86400000) % 7;
 733         if( Z>n ) Z -= 7;
 734         p->iJD += (n - Z)*86400000;
 735         clearYMD_HMS_TZ(p);
 736         rc = 0;
 737       }
 738       break;
 739     }
 740     case 's': {
 741       /*
 742       **    start of TTTTT
 743       **
 744       ** Move the date backwards to the beginning of the current day,
 745       ** or month or year.
 746       */
 747       if( sqlite3_strnicmp(z, "start of ", 9)!=0 ) break;
 748       if( !p->validJD && !p->validYMD && !p->validHMS ) break;
 749       z += 9;
 750       computeYMD(p);
 751       p->validHMS = 1;
 752       p->h = p->m = 0;
 753       p->s = 0.0;
 754       p->rawS = 0;
 755       p->validTZ = 0;
 756       p->validJD = 0;
 757       if( sqlite3_stricmp(z,"month")==0 ){
 758         p->D = 1;
 759         rc = 0;
 760       }else if( sqlite3_stricmp(z,"year")==0 ){
 761         p->M = 1;
 762         p->D = 1;
 763         rc = 0;
 764       }else if( sqlite3_stricmp(z,"day")==0 ){
 765         rc = 0;
 766       }
 767       break;
 768     }
 769     case '+':
 770     case '-':
 771     case '0':
 772     case '1':
 773     case '2':
 774     case '3':
 775     case '4':
 776     case '5':
 777     case '6':
 778     case '7':
 779     case '8':
 780     case '9': {
 781       double rRounder;
 782       int i;
 783       for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){}
 784       if( !sqlite3AtoF(z, &r, n, SQLITE_UTF8) ){
 785         rc = 1;
 786         break;
 787       }
 788       if( z[n]==':' ){
 789         /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
 790         ** specified number of hours, minutes, seconds, and fractional seconds
 791         ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
 792         ** omitted.
 793         */
 794         const char *z2 = z;
 795         DateTime tx;
 796         sqlite3_int64 day;
 797         if( !sqlite3Isdigit(*z2) ) z2++;
 798         memset(&tx, 0, sizeof(tx));
 799         if( parseHhMmSs(z2, &tx) ) break;
 800         computeJD(&tx);
 801         tx.iJD -= 43200000;
 802         day = tx.iJD/86400000;
 803         tx.iJD -= day*86400000;
 804         if( z[0]=='-' ) tx.iJD = -tx.iJD;
 805         computeJD(p);
 806         clearYMD_HMS_TZ(p);
 807         p->iJD += tx.iJD;
 808         rc = 0;
 809         break;
 810       }
 811
 812       /* If control reaches this point, it means the transformation is
 813       ** one of the forms like "+NNN days".  */
 814       z += n;
 815       while( sqlite3Isspace(*z) ) z++;
 816       n = sqlite3Strlen30(z);
 817       if( n>10 || n<3 ) break;
 818       if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
 819       computeJD(p);
 820       rc = 1;
 821       rRounder = r<0 ? -0.5 : +0.5;
 822       for(i=0; i<ArraySize(aXformType); i++){
 823         if( aXformType[i].nName==n
 824          && sqlite3_strnicmp(aXformType[i].zName, z, n)==0
 825          && r>-aXformType[i].rLimit && r<aXformType[i].rLimit
 826         ){
 827           switch( aXformType[i].eType ){
 828             case 1: { /* Special processing to add months */
 829               int x;
 830               computeYMD_HMS(p);
 831               p->M += (int)r;
 832               x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
 833               p->Y += x;
 834               p->M -= x*12;
 835               p->validJD = 0;
 836               r -= (int)r;
 837               break;
 838             }
 839             case 2: { /* Special processing to add years */
 840               int y = (int)r;
 841               computeYMD_HMS(p);
 842               p->Y += y;
 843               p->validJD = 0;
 844               r -= (int)r;
 845               break;
 846             }
 847           }
 848           computeJD(p);
 849           p->iJD += (sqlite3_int64)(r*aXformType[i].rXform + rRounder);
 850           rc = 0;
 851           break;
 852         }
 853       }
 854       clearYMD_HMS_TZ(p);
 855       break;
 856     }
 857     default: {
 858       break;
 859     }
 860   }
 861   return rc;
 862 }
 863
 864 /*
 865 ** Process time function arguments.  argv[0] is a date-time stamp.
 866 ** argv[1] and following are modifiers.  Parse them all and write
 867 ** the resulting time into the DateTime structure p.  Return 0
 868 ** on success and 1 if there are any errors.
 869 **
 870 ** If there are zero parameters (if even argv[0] is undefined)
 871 ** then assume a default value of "now" for argv[0].
 872 */
 873 static int isDate(
 874   sqlite3_context *context,
 875   int argc,
 876   sqlite3_value **argv,
 877   DateTime *p
 878 ){
 879   int i, n;
 880   const unsigned char *z;
 881   int eType;
 882   memset(p, 0, sizeof(*p));
 883   if( argc==0 ){
 884     return setDateTimeToCurrent(context, p);
 885   }
 886   if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
 887                    || eType==SQLITE_INTEGER ){
 888     setRawDateNumber(p, sqlite3_value_double(argv[0]));
 889   }else{
 890     z = sqlite3_value_text(argv[0]);
 891     if( !z || parseDateOrTime(context, (char*)z, p) ){
 892       return 1;
 893     }
 894   }
 895   for(i=1; i<argc; i++){
 896     z = sqlite3_value_text(argv[i]);
 897     n = sqlite3_value_bytes(argv[i]);
 898     if( z==0 || parseModifier(context, (char*)z, n, p) ) return 1;
 899   }
 900   computeJD(p);
 901   if( p->isError || !validJulianDay(p->iJD) ) return 1;
 902   return 0;
 903 }
 904
 905
 906 /*
 907 ** The following routines implement the various date and time functions
 908 ** of SQLite.
 909 */
 910
 911 /*
 912 **    julianday( TIMESTRING, MOD, MOD, ...)
 913 **
 914 ** Return the julian day number of the date specified in the arguments
 915 */
 916 static void juliandayFunc(
 917   sqlite3_context *context,
 918   int argc,
 919   sqlite3_value **argv
 920 ){
 921   DateTime x;
 922   if( isDate(context, argc, argv, &x)==0 ){
 923     computeJD(&x);
 924     sqlite3_result_double(context, x.iJD/86400000.0);
 925   }
 926 }
 927
 928 /*
 929 **    datetime( TIMESTRING, MOD, MOD, ...)
 930 **
 931 ** Return YYYY-MM-DD HH:MM:SS
 932 */
 933 static void datetimeFunc(
 934   sqlite3_context *context,
 935   int argc,
 936   sqlite3_value **argv
 937 ){
 938   DateTime x;
 939   if( isDate(context, argc, argv, &x)==0 ){
 940     char zBuf[100];
 941     computeYMD_HMS(&x);
 942     sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d",
 943                      x.Y, x.M, x.D, x.h, x.m, (int)(x.s));
 944     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
 945   }
 946 }
 947
 948 /*
 949 **    time( TIMESTRING, MOD, MOD, ...)
 950 **
 951 ** Return HH:MM:SS
 952 */
 953 static void timeFunc(
 954   sqlite3_context *context,
 955   int argc,
 956   sqlite3_value **argv
 957 ){
 958   DateTime x;
 959   if( isDate(context, argc, argv, &x)==0 ){
 960     char zBuf[100];
 961     computeHMS(&x);
 962     sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
 963     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
 964   }
 965 }
 966
 967 /*
 968 **    date( TIMESTRING, MOD, MOD, ...)
 969 **
 970 ** Return YYYY-MM-DD
 971 */
 972 static void dateFunc(
 973   sqlite3_context *context,
 974   int argc,
 975   sqlite3_value **argv
 976 ){
 977   DateTime x;
 978   if( isDate(context, argc, argv, &x)==0 ){
 979     char zBuf[100];
 980     computeYMD(&x);
 981     sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
 982     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
 983   }
 984 }
 985
 986 /*
 987 **    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
 988 **
 989 ** Return a string described by FORMAT.  Conversions as follows:
 990 **
 991 **   %d  day of month
 992 **   %f  ** fractional seconds  SS.SSS
 993 **   %H  hour 00-24
 994 **   %j  day of year 000-366
 995 **   %J  ** julian day number
 996 **   %m  month 01-12
 997 **   %M  minute 00-59
 998 **   %s  seconds since 1970-01-01
 999 **   %S  seconds 00-59
1000 **   %w  day of week 0-6  sunday==0
1001 **   %W  week of year 00-53
1002 **   %Y  year 0000-9999
1003 **   %%  %
1004 */
1005 static void strftimeFunc(
1006   sqlite3_context *context,
1007   int argc,
1008   sqlite3_value **argv
1009 ){
1010   DateTime x;
1011   u64 n;
1012   size_t i,j;
1013   char *z;
1014   sqlite3 *db;
1015   const char *zFmt;
1016   char zBuf[100];
1017   if( argc==0 ) return;
1018   zFmt = (const char*)sqlite3_value_text(argv[0]);
1019   if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
1020   db = sqlite3_context_db_handle(context);
1021   for(i=0, n=1; zFmt[i]; i++, n++){
1022     if( zFmt[i]=='%' ){
1023       switch( zFmt[i+1] ){
1024         case 'd':
1025         case 'H':
1026         case 'm':
1027         case 'M':
1028         case 'S':
1029         case 'W':
1030           n++;
1031           /* fall thru */
1032         case 'w':
1033         case '%':
1034           break;
1035         case 'f':
1036           n += 8;
1037           break;
1038         case 'j':
1039           n += 3;
1040           break;
1041         case 'Y':
1042           n += 8;
1043           break;
1044         case 's':
1045         case 'J':
1046           n += 50;
1047           break;
1048         default:
1049           return;  /* ERROR.  return a NULL */
1050       }
1051       i++;
1052     }
1053   }
1054   testcase( n==sizeof(zBuf)-1 );
1055   testcase( n==sizeof(zBuf) );
1056   testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH]+1 );
1057   testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH] );
1058   if( n<sizeof(zBuf) ){
1059     z = zBuf;
1060   }else if( n>(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ){
1061     sqlite3_result_error_toobig(context);
1062     return;
1063   }else{
1064     z = sqlite3DbMallocRawNN(db, (int)n);
1065     if( z==0 ){
1066       sqlite3_result_error_nomem(context);
1067       return;
1068     }
1069   }
1070   computeJD(&x);
1071   computeYMD_HMS(&x);
1072   for(i=j=0; zFmt[i]; i++){
1073     if( zFmt[i]!='%' ){
1074       z[j++] = zFmt[i];
1075     }else{
1076       i++;
1077       switch( zFmt[i] ){
1078         case 'd':  sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break;
1079         case 'f': {
1080           double s = x.s;
1081           if( s>59.999 ) s = 59.999;
1082           sqlite3_snprintf(7, &z[j],"%06.3f", s);
1083           j += sqlite3Strlen30(&z[j]);
1084           break;
1085         }
1086         case 'H':  sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break;
1087         case 'W': /* Fall thru */
1088         case 'j': {
1089           int nDay;             /* Number of days since 1st day of year */
1090           DateTime y = x;
1091           y.validJD = 0;
1092           y.M = 1;
1093           y.D = 1;
1094           computeJD(&y);
1095           nDay = (int)((x.iJD-y.iJD+43200000)/86400000);
1096           if( zFmt[i]=='W' ){
1097             int wd;   /* 0=Monday, 1=Tuesday, ... 6=Sunday */
1098             wd = (int)(((x.iJD+43200000)/86400000)%7);
1099             sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);
1100             j += 2;
1101           }else{
1102             sqlite3_snprintf(4, &z[j],"%03d",nDay+1);
1103             j += 3;
1104           }
1105           break;
1106         }
1107         case 'J': {
1108           sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0);
1109           j+=sqlite3Strlen30(&z[j]);
1110           break;
1111         }
1112         case 'm':  sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;
1113         case 'M':  sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;
1114         case 's': {
1115           sqlite3_snprintf(30,&z[j],"%lld",
1116                            (i64)(x.iJD/1000 - 21086676*(i64)10000));
1117           j += sqlite3Strlen30(&z[j]);
1118           break;
1119         }
1120         case 'S':  sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
1121         case 'w': {
1122           z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
1123           break;
1124         }
1125         case 'Y': {
1126           sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
1127           break;
1128         }
1129         default:   z[j++] = '%'; break;
1130       }
1131     }
1132   }
1133   z[j] = 0;
1134   sqlite3_result_text(context, z, -1,
1135                       z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC);
1136 }
1137
1138 /*
1139 ** current_time()
1140 **
1141 ** This function returns the same value as time('now').
1142 */
1143 static void ctimeFunc(
1144   sqlite3_context *context,
1145   int NotUsed,
1146   sqlite3_value **NotUsed2
1147 ){
1148   UNUSED_PARAMETER2(NotUsed, NotUsed2);
1149   timeFunc(context, 0, 0);
1150 }
1151
1152 /*
1153 ** current_date()
1154 **
1155 ** This function returns the same value as date('now').
1156 */
1157 static void cdateFunc(
1158   sqlite3_context *context,
1159   int NotUsed,
1160   sqlite3_value **NotUsed2
1161 ){
1162   UNUSED_PARAMETER2(NotUsed, NotUsed2);
1163   dateFunc(context, 0, 0);
1164 }
1165
1166 /*
1167 ** current_timestamp()
1168 **
1169 ** This function returns the same value as datetime('now').
1170 */
1171 static void ctimestampFunc(
1172   sqlite3_context *context,
1173   int NotUsed,
1174   sqlite3_value **NotUsed2
1175 ){
1176   UNUSED_PARAMETER2(NotUsed, NotUsed2);
1177   datetimeFunc(context, 0, 0);
1178 }
1179 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
1180
1181 #ifdef SQLITE_OMIT_DATETIME_FUNCS
1182 /*
1183 ** If the library is compiled to omit the full-scale date and time
1184 ** handling (to get a smaller binary), the following minimal version
1185 ** of the functions current_time(), current_date() and current_timestamp()
1186 ** are included instead. This is to support column declarations that
1187 ** include "DEFAULT CURRENT_TIME" etc.
1188 **
1189 ** This function uses the C-library functions time(), gmtime()
1190 ** and strftime(). The format string to pass to strftime() is supplied
1191 ** as the user-data for the function.
1192 */
1193 static void currentTimeFunc(
1194   sqlite3_context *context,
1195   int argc,
1196   sqlite3_value **argv
1197 ){
1198   time_t t;
1199   char *zFormat = (char *)sqlite3_user_data(context);
1200   sqlite3_int64 iT;
1201   struct tm *pTm;
1202   struct tm sNow;
1203   char zBuf[20];
1204
1205   UNUSED_PARAMETER(argc);
1206   UNUSED_PARAMETER(argv);
1207
1208   iT = sqlite3StmtCurrentTime(context);
1209   if( iT<=0 ) return;
1210   t = iT/1000 - 10000*(sqlite3_int64)21086676;
1211 #if HAVE_GMTIME_R
1212   pTm = gmtime_r(&t, &sNow);
1213 #else
1214   sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1215   pTm = gmtime(&t);
1216   if( pTm ) memcpy(&sNow, pTm, sizeof(sNow));
1217   sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1218 #endif
1219   if( pTm ){
1220     strftime(zBuf, 20, zFormat, &sNow);
1221     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
1222   }
1223 }
1224 #endif
1225
1226 /*
1227 ** This function registered all of the above C functions as SQL
1228 ** functions.  This should be the only routine in this file with
1229 ** external linkage.
1230 */
1231 void sqlite3RegisterDateTimeFunctions(void){
1232   static FuncDef aDateTimeFuncs[] = {
1233 #ifndef SQLITE_OMIT_DATETIME_FUNCS
1234     PURE_DATE(julianday,        -1, 0, 0, juliandayFunc ),
1235     PURE_DATE(date,             -1, 0, 0, dateFunc      ),
1236     PURE_DATE(time,             -1, 0, 0, timeFunc      ),
1237     PURE_DATE(datetime,         -1, 0, 0, datetimeFunc  ),
1238     PURE_DATE(strftime,         -1, 0, 0, strftimeFunc  ),
1239     DFUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
1240     DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
1241     DFUNCTION(current_date,      0, 0, 0, cdateFunc     ),
1242 #else
1243     STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
1244     STR_FUNCTION(current_date,      0, "%Y-%m-%d",          0, currentTimeFunc),
1245     STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
1246 #endif
1247   };
1248   sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
1249 }