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