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