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 validYMD;      /* True (1) if Y,M,D are valid */
  75   char validHMS;      /* True (1) if h,m,s are valid */
  76   char nFloor;            /* Days to implement "floor" */
  77   unsigned rawS      : 1; /* Raw numeric value stored in s */
  78   unsigned isError   : 1; /* An overflow has occurred */
  79   unsigned useSubsec : 1; /* Display subsecond precision */
  80   unsigned isUtc     : 1; /* Time is known to be UTC */
  81   unsigned isLocal   : 1; /* Time is known to be localtime */
  82 };
  83
  84
  85 /*
  86 ** Convert zDate into one or more integers according to the conversion
  87 ** specifier zFormat.
  88 **
  89 ** zFormat[] contains 4 characters for each integer converted, except for
  90 ** the last integer which is specified by three characters.  The meaning
  91 ** of a four-character format specifiers ABCD is:
  92 **
  93 **    A:   number of digits to convert.  Always "2" or "4".
  94 **    B:   minimum value.  Always "0" or "1".
  95 **    C:   maximum value, decoded as:
  96 **           a:  12
  97 **           b:  14
  98 **           c:  24
  99 **           d:  31
 100 **           e:  59
 101 **           f:  9999
 102 **    D:   the separator character, or \000 to indicate this is the
 103 **         last number to convert.
 104 **
 105 ** Example:  To translate an ISO-8601 date YYYY-MM-DD, the format would
 106 ** be "40f-21a-20c".  The "40f-" indicates the 4-digit year followed by "-".
 107 ** The "21a-" indicates the 2-digit month followed by "-".  The "20c" indicates
 108 ** the 2-digit day which is the last integer in the set.
 109 **
 110 ** The function returns the number of successful conversions.
 111 */
 112 static int getDigits(const char *zDate, const char *zFormat, ...){
 113   /* The aMx[] array translates the 3rd character of each format
 114   ** spec into a max size:    a   b   c   d   e      f */
 115   static const u16 aMx[] = { 12, 14, 24, 31, 59, 14712 };
 116   va_list ap;
 117   int cnt = 0;
 118   char nextC;
 119   va_start(ap, zFormat);
 120   do{
 121     char N = zFormat[0] - '0';
 122     char min = zFormat[1] - '0';
 123     int val = 0;
 124     u16 max;
 125
 126     assert( zFormat[2]>='a' && zFormat[2]<='f' );
 127     max = aMx[zFormat[2] - 'a'];
 128     nextC = zFormat[3];
 129     val = 0;
 130     while( N-- ){
 131       if( !sqlite3Isdigit(*zDate) ){
 132         goto end_getDigits;
 133       }
 134       val = val*10 + *zDate - '0';
 135       zDate++;
 136     }
 137     if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){
 138       goto end_getDigits;
 139     }
 140     *va_arg(ap,int*) = val;
 141     zDate++;
 142     cnt++;
 143     zFormat += 4;
 144   }while( nextC );
 145 end_getDigits:
 146   va_end(ap);
 147   return cnt;
 148 }
 149
 150 /*
 151 ** Parse a timezone extension on the end of a date-time.
 152 ** The extension is of the form:
 153 **
 154 **        (+/-)HH:MM
 155 **
 156 ** Or the "zulu" notation:
 157 **
 158 **        Z
 159 **
 160 ** If the parse is successful, write the number of minutes
 161 ** of change in p->tz and return 0.  If a parser error occurs,
 162 ** return non-zero.
 163 **
 164 ** A missing specifier is not considered an error.
 165 */
 166 static int parseTimezone(const char *zDate, DateTime *p){
 167   int sgn = 0;
 168   int nHr, nMn;
 169   int c;
 170   while( sqlite3Isspace(*zDate) ){ zDate++; }
 171   p->tz = 0;
 172   c = *zDate;
 173   if( c=='-' ){
 174     sgn = -1;
 175   }else if( c=='+' ){
 176     sgn = +1;
 177   }else if( c=='Z' || c=='z' ){
 178     zDate++;
 179     p->isLocal = 0;
 180     p->isUtc = 1;
 181     goto zulu_time;
 182   }else{
 183     return c!=0;
 184   }
 185   zDate++;
 186   if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){
 187     return 1;
 188   }
 189   zDate += 5;
 190   p->tz = sgn*(nMn + nHr*60);
 191 zulu_time:
 192   while( sqlite3Isspace(*zDate) ){ zDate++; }
 193   return *zDate!=0;
 194 }
 195
 196 /*
 197 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
 198 ** The HH, MM, and SS must each be exactly 2 digits.  The
 199 ** fractional seconds FFFF can be one or more digits.
 200 **
 201 ** Return 1 if there is a parsing error and 0 on success.
 202 */
 203 static int parseHhMmSs(const char *zDate, DateTime *p){
 204   int h, m, s;
 205   double ms = 0.0;
 206   if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){
 207     return 1;
 208   }
 209   zDate += 5;
 210   if( *zDate==':' ){
 211     zDate++;
 212     if( getDigits(zDate, "20e", &s)!=1 ){
 213       return 1;
 214     }
 215     zDate += 2;
 216     if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){
 217       double rScale = 1.0;
 218       zDate++;
 219       while( sqlite3Isdigit(*zDate) ){
 220         ms = ms*10.0 + *zDate - '0';
 221         rScale *= 10.0;
 222         zDate++;
 223       }
 224       ms /= rScale;
 225     }
 226   }else{
 227     s = 0;
 228   }
 229   p->validJD = 0;
 230   p->rawS = 0;
 231   p->validHMS = 1;
 232   p->h = h;
 233   p->m = m;
 234   p->s = s + ms;
 235   if( parseTimezone(zDate, p) ) return 1;
 236   return 0;
 237 }
 238
 239 /*
 240 ** Put the DateTime object into its error state.
 241 */
 242 static void datetimeError(DateTime *p){
 243   memset(p, 0, sizeof(*p));
 244   p->isError = 1;
 245 }
 246
 247 /*
 248 ** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume
 249 ** that the YYYY-MM-DD is according to the Gregorian calendar.
 250 **
 251 ** Reference:  Meeus page 61
 252 */
 253 static void computeJD(DateTime *p){
 254   int Y, M, D, A, B, X1, X2;
 255
 256   if( p->validJD ) return;
 257   if( p->validYMD ){
 258     Y = p->Y;
 259     M = p->M;
 260     D = p->D;
 261   }else{
 262     Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */
 263     M = 1;
 264     D = 1;
 265   }
 266   if( Y<-4713 || Y>9999 || p->rawS ){
 267     datetimeError(p);
 268     return;
 269   }
 270   if( M<=2 ){
 271     Y--;
 272     M += 12;
 273   }
 274   A = Y/100;
 275   B = 2 - A + (A/4);
 276   X1 = 36525*(Y+4716)/100;
 277   X2 = 306001*(M+1)/10000;
 278   p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
 279   p->validJD = 1;
 280   if( p->validHMS ){
 281     p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000 + 0.5);
 282     if( p->tz ){
 283       p->iJD -= p->tz*60000;
 284       p->validYMD = 0;
 285       p->validHMS = 0;
 286       p->tz = 0;
 287       p->isUtc = 1;
 288       p->isLocal = 0;
 289     }
 290   }
 291 }
 292
 293 /*
 294 ** Given the YYYY-MM-DD information current in p, determine if there
 295 ** is day-of-month overflow and set nFloor to the number of days that
 296 ** would need to be subtracted from the date in order to bring the
 297 ** date back to the end of the month.
 298 */
 299 static void computeFloor(DateTime *p){
 300   assert( p->validYMD || p->isError );
 301   assert( p->D>=0 && p->D<=31 );
 302   assert( p->M>=0 && p->M<=12 );
 303   if( p->D<=28 ){
 304     p->nFloor = 0;
 305   }else if( (1<<p->M) & 0x15aa ){
 306     p->nFloor = 0;
 307   }else if( p->M!=2 ){
 308     p->nFloor = (p->D==31);
 309   }else if( p->Y%4!=0 || (p->Y%100==0 && p->Y%400!=0) ){
 310     p->nFloor = p->D - 28;
 311   }else{
 312     p->nFloor = p->D - 29;
 313   }
 314 }
 315
 316 /*
 317 ** Parse dates of the form
 318 **
 319 **     YYYY-MM-DD HH:MM:SS.FFF
 320 **     YYYY-MM-DD HH:MM:SS
 321 **     YYYY-MM-DD HH:MM
 322 **     YYYY-MM-DD
 323 **
 324 ** Write the result into the DateTime structure and return 0
 325 ** on success and 1 if the input string is not a well-formed
 326 ** date.
 327 */
 328 static int parseYyyyMmDd(const char *zDate, DateTime *p){
 329   int Y, M, D, neg;
 330
 331   if( zDate[0]=='-' ){
 332     zDate++;
 333     neg = 1;
 334   }else{
 335     neg = 0;
 336   }
 337   if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){
 338     return 1;
 339   }
 340   zDate += 10;
 341   while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }
 342   if( parseHhMmSs(zDate, p)==0 ){
 343     /* We got the time */
 344   }else if( *zDate==0 ){
 345     p->validHMS = 0;
 346   }else{
 347     return 1;
 348   }
 349   p->validJD = 0;
 350   p->validYMD = 1;
 351   p->Y = neg ? -Y : Y;
 352   p->M = M;
 353   p->D = D;
 354   computeFloor(p);
 355   if( p->tz ){
 356     computeJD(p);
 357   }
 358   return 0;
 359 }
 360
 361
 362 static void clearYMD_HMS_TZ(DateTime *p);  /* Forward declaration */
 363
 364 /*
 365 ** Set the time to the current time reported by the VFS.
 366 **
 367 ** Return the number of errors.
 368 */
 369 static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
 370   p->iJD = sqlite3StmtCurrentTime(context);
 371   if( p->iJD>0 ){
 372     p->validJD = 1;
 373     p->isUtc = 1;
 374     p->isLocal = 0;
 375     clearYMD_HMS_TZ(p);
 376     return 0;
 377   }else{
 378     return 1;
 379   }
 380 }
 381
 382 /*
 383 ** Input "r" is a numeric quantity which might be a julian day number,
 384 ** or the number of seconds since 1970.  If the value if r is within
 385 ** range of a julian day number, install it as such and set validJD.
 386 ** If the value is a valid unix timestamp, put it in p->s and set p->rawS.
 387 */
 388 static void setRawDateNumber(DateTime *p, double r){
 389   p->s = r;
 390   p->rawS = 1;
 391   if( r>=0.0 && r<5373484.5 ){
 392     p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
 393     p->validJD = 1;
 394   }
 395 }
 396
 397 /*
 398 ** Attempt to parse the given string into a julian day number.  Return
 399 ** the number of errors.
 400 **
 401 ** The following are acceptable forms for the input string:
 402 **
 403 **      YYYY-MM-DD HH:MM:SS.FFF  +/-HH:MM
 404 **      DDDD.DD
 405 **      now
 406 **
 407 ** In the first form, the +/-HH:MM is always optional.  The fractional
 408 ** seconds extension (the ".FFF") is optional.  The seconds portion
 409 ** (":SS.FFF") is option.  The year and date can be omitted as long
 410 ** as there is a time string.  The time string can be omitted as long
 411 ** as there is a year and date.
 412 */
 413 static int parseDateOrTime(
 414   sqlite3_context *context,
 415   const char *zDate,
 416   DateTime *p
 417 ){
 418   double r;
 419   if( parseYyyyMmDd(zDate,p)==0 ){
 420     return 0;
 421   }else if( parseHhMmSs(zDate, p)==0 ){
 422     return 0;
 423   }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
 424     return setDateTimeToCurrent(context, p);
 425   }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){
 426     setRawDateNumber(p, r);
 427     return 0;
 428   }else if( (sqlite3StrICmp(zDate,"subsec")==0
 429              || sqlite3StrICmp(zDate,"subsecond")==0)
 430            && sqlite3NotPureFunc(context) ){
 431     p->useSubsec = 1;
 432     return setDateTimeToCurrent(context, p);
 433   }
 434   return 1;
 435 }
 436
 437 /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999.
 438 ** Multiplying this by 86400000 gives 464269060799999 as the maximum value
 439 ** for DateTime.iJD.
 440 **
 441 ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with
 442 ** such a large integer literal, so we have to encode it.
 443 */
 444 #define INT_464269060799999  ((((i64)0x1a640)<<32)|0x1072fdff)
 445
 446 /*
 447 ** Return TRUE if the given julian day number is within range.
 448 **
 449 ** The input is the JulianDay times 86400000.
 450 */
 451 static int validJulianDay(sqlite3_int64 iJD){
 452   return iJD>=0 && iJD<=INT_464269060799999;
 453 }
 454
 455 /*
 456 ** Compute the Year, Month, and Day from the julian day number.
 457 */
 458 static void computeYMD(DateTime *p){
 459   int Z, A, B, C, D, E, X1;
 460   if( p->validYMD ) return;
 461   if( !p->validJD ){
 462     p->Y = 2000;
 463     p->M = 1;
 464     p->D = 1;
 465   }else if( !validJulianDay(p->iJD) ){
 466     datetimeError(p);
 467     return;
 468   }else{
 469     Z = (int)((p->iJD + 43200000)/86400000);
 470     A = (int)((Z - 1867216.25)/36524.25);
 471     A = Z + 1 + A - (A/4);
 472     B = A + 1524;
 473     C = (int)((B - 122.1)/365.25);
 474     D = (36525*(C&32767))/100;
 475     E = (int)((B-D)/30.6001);
 476     X1 = (int)(30.6001*E);
 477     p->D = B - D - X1;
 478     p->M = E<14 ? E-1 : E-13;
 479     p->Y = p->M>2 ? C - 4716 : C - 4715;
 480   }
 481   p->validYMD = 1;
 482 }
 483
 484 /*
 485 ** Compute the Hour, Minute, and Seconds from the julian day number.
 486 */
 487 static void computeHMS(DateTime *p){
 488   int day_ms, day_min; /* milliseconds, minutes into the day */
 489   if( p->validHMS ) return;
 490   computeJD(p);
 491   day_ms = (int)((p->iJD + 43200000) % 86400000);
 492   p->s = (day_ms % 60000)/1000.0;
 493   day_min = day_ms/60000;
 494   p->m = day_min % 60;
 495   p->h = day_min / 60;
 496   p->rawS = 0;
 497   p->validHMS = 1;
 498 }
 499
 500 /*
 501 ** Compute both YMD and HMS
 502 */
 503 static void computeYMD_HMS(DateTime *p){
 504   computeYMD(p);
 505   computeHMS(p);
 506 }
 507
 508 /*
 509 ** Clear the YMD and HMS and the TZ
 510 */
 511 static void clearYMD_HMS_TZ(DateTime *p){
 512   p->validYMD = 0;
 513   p->validHMS = 0;
 514   p->tz = 0;
 515 }
 516
 517 #ifndef SQLITE_OMIT_LOCALTIME
 518 /*
 519 ** On recent Windows platforms, the localtime_s() function is available
 520 ** as part of the "Secure CRT". It is essentially equivalent to
 521 ** localtime_r() available under most POSIX platforms, except that the
 522 ** order of the parameters is reversed.
 523 **
 524 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
 525 **
 526 ** If the user has not indicated to use localtime_r() or localtime_s()
 527 ** already, check for an MSVC build environment that provides
 528 ** localtime_s().
 529 */
 530 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \
 531     && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
 532 #undef  HAVE_LOCALTIME_S
 533 #define HAVE_LOCALTIME_S 1
 534 #endif
 535
 536 /*
 537 ** The following routine implements the rough equivalent of localtime_r()
 538 ** using whatever operating-system specific localtime facility that
 539 ** is available.  This routine returns 0 on success and
 540 ** non-zero on any kind of error.
 541 **
 542 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is non-zero then this
 543 ** routine will always fail.  If bLocaltimeFault is nonzero and
 544 ** sqlite3GlobalConfig.xAltLocaltime is not NULL, then xAltLocaltime() is
 545 ** invoked in place of the OS-defined localtime() function.
 546 **
 547 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
 548 ** library function localtime_r() is used to assist in the calculation of
 549 ** local time.
 550 */
 551 static int osLocaltime(time_t *t, struct tm *pTm){
 552   int rc;
 553 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S
 554   struct tm *pX;
 555 #if SQLITE_THREADSAFE>0
 556   sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN);
 557 #endif
 558   sqlite3_mutex_enter(mutex);
 559   pX = localtime(t);
 560 #ifndef SQLITE_UNTESTABLE
 561   if( sqlite3GlobalConfig.bLocaltimeFault ){
 562     if( sqlite3GlobalConfig.xAltLocaltime!=0
 563      && 0==sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm)
 564     ){
 565       pX = pTm;
 566     }else{
 567       pX = 0;
 568     }
 569   }
 570 #endif
 571   if( pX ) *pTm = *pX;
 572 #if SQLITE_THREADSAFE>0
 573   sqlite3_mutex_leave(mutex);
 574 #endif
 575   rc = pX==0;
 576 #else
 577 #ifndef SQLITE_UNTESTABLE
 578   if( sqlite3GlobalConfig.bLocaltimeFault ){
 579     if( sqlite3GlobalConfig.xAltLocaltime!=0 ){
 580       return sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm);
 581     }else{
 582       return 1;
 583     }
 584   }
 585 #endif
 586 #if HAVE_LOCALTIME_R
 587   rc = localtime_r(t, pTm)==0;
 588 #else
 589   rc = localtime_s(pTm, t);
 590 #endif /* HAVE_LOCALTIME_R */
 591 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
 592   return rc;
 593 }
 594 #endif /* SQLITE_OMIT_LOCALTIME */
 595
 596
 597 #ifndef SQLITE_OMIT_LOCALTIME
 598 /*
 599 ** Assuming the input DateTime is UTC, move it to its localtime equivalent.
 600 */
 601 static int toLocaltime(
 602   DateTime *p,                   /* Date at which to calculate offset */
 603   sqlite3_context *pCtx          /* Write error here if one occurs */
 604 ){
 605   time_t t;
 606   struct tm sLocal;
 607   int iYearDiff;
 608
 609   /* Initialize the contents of sLocal to avoid a compiler warning. */
 610   memset(&sLocal, 0, sizeof(sLocal));
 611
 612   computeJD(p);
 613   if( p->iJD<2108667600*(i64)100000 /* 1970-01-01 */
 614    || p->iJD>2130141456*(i64)100000 /* 2038-01-18 */
 615   ){
 616     /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
 617     ** works for years between 1970 and 2037. For dates outside this range,
 618     ** SQLite attempts to map the year into an equivalent year within this
 619     ** range, do the calculation, then map the year back.
 620     */
 621     DateTime x = *p;
 622     computeYMD_HMS(&x);
 623     iYearDiff = (2000 + x.Y%4) - x.Y;
 624     x.Y += iYearDiff;
 625     x.validJD = 0;
 626     computeJD(&x);
 627     t = (time_t)(x.iJD/1000 -  21086676*(i64)10000);
 628   }else{
 629     iYearDiff = 0;
 630     t = (time_t)(p->iJD/1000 -  21086676*(i64)10000);
 631   }
 632   if( osLocaltime(&t, &sLocal) ){
 633     sqlite3_result_error(pCtx, "local time unavailable", -1);
 634     return SQLITE_ERROR;
 635   }
 636   p->Y = sLocal.tm_year + 1900 - iYearDiff;
 637   p->M = sLocal.tm_mon + 1;
 638   p->D = sLocal.tm_mday;
 639   p->h = sLocal.tm_hour;
 640   p->m = sLocal.tm_min;
 641   p->s = sLocal.tm_sec + (p->iJD%1000)*0.001;
 642   p->validYMD = 1;
 643   p->validHMS = 1;
 644   p->validJD = 0;
 645   p->rawS = 0;
 646   p->tz = 0;
 647   p->isError = 0;
 648   return SQLITE_OK;
 649 }
 650 #endif /* SQLITE_OMIT_LOCALTIME */
 651
 652 /*
 653 ** The following table defines various date transformations of the form
 654 **
 655 **            'NNN days'
 656 **
 657 ** Where NNN is an arbitrary floating-point number and "days" can be one
 658 ** of several units of time.
 659 */
 660 static const struct {
 661   u8 nName;           /* Length of the name */
 662   char zName[7];      /* Name of the transformation */
 663   float rLimit;       /* Maximum NNN value for this transform */
 664   float rXform;       /* Constant used for this transform */
 665 } aXformType[] = {
 666   /* 0 */ { 6, "second",   4.6427e+14,         1.0  },
 667   /* 1 */ { 6, "minute",   7.7379e+12,        60.0  },
 668   /* 2 */ { 4, "hour",     1.2897e+11,      3600.0  },
 669   /* 3 */ { 3, "day",      5373485.0,      86400.0  },
 670   /* 4 */ { 5, "month",    176546.0,  30.0*86400.0  },
 671   /* 5 */ { 4, "year",     14713.0,  365.0*86400.0  },
 672 };
 673
 674 /*
 675 ** If the DateTime p is raw number, try to figure out if it is
 676 ** a julian day number of a unix timestamp.  Set the p value
 677 ** appropriately.
 678 */
 679 static void autoAdjustDate(DateTime *p){
 680   if( !p->rawS || p->validJD ){
 681     p->rawS = 0;
 682   }else if( p->s>=-21086676*(i64)10000        /* -4713-11-24 12:00:00 */
 683          && p->s<=(25340230*(i64)10000)+799   /*  9999-12-31 23:59:59 */
 684   ){
 685     double r = p->s*1000.0 + 210866760000000.0;
 686     clearYMD_HMS_TZ(p);
 687     p->iJD = (sqlite3_int64)(r + 0.5);
 688     p->validJD = 1;
 689     p->rawS = 0;
 690   }
 691 }
 692
 693 /*
 694 ** Process a modifier to a date-time stamp.  The modifiers are
 695 ** as follows:
 696 **
 697 **     NNN days
 698 **     NNN hours
 699 **     NNN minutes
 700 **     NNN.NNNN seconds
 701 **     NNN months
 702 **     NNN years
 703 **     +/-YYYY-MM-DD HH:MM:SS.SSS
 704 **     ceiling
 705 **     floor
 706 **     start of month
 707 **     start of year
 708 **     start of week
 709 **     start of day
 710 **     weekday N
 711 **     unixepoch
 712 **     auto
 713 **     localtime
 714 **     utc
 715 **     subsec
 716 **     subsecond
 717 **
 718 ** Return 0 on success and 1 if there is any kind of error. If the error
 719 ** is in a system call (i.e. localtime()), then an error message is written
 720 ** to context pCtx. If the error is an unrecognized modifier, no error is
 721 ** written to pCtx.
 722 */
 723 static int parseModifier(
 724   sqlite3_context *pCtx,      /* Function context */
 725   const char *z,              /* The text of the modifier */
 726   int n,                      /* Length of zMod in bytes */
 727   DateTime *p,                /* The date/time value to be modified */
 728   int idx                     /* Parameter index of the modifier */
 729 ){
 730   int rc = 1;
 731   double r;
 732   switch(sqlite3UpperToLower[(u8)z[0]] ){
 733     case 'a': {
 734       /*
 735       **    auto
 736       **
 737       ** If rawS is available, then interpret as a julian day number, or
 738       ** a unix timestamp, depending on its magnitude.
 739       */
 740       if( sqlite3_stricmp(z, "auto")==0 ){
 741         if( idx>1 ) return 1; /* IMP: R-33611-57934 */
 742         autoAdjustDate(p);
 743         rc = 0;
 744       }
 745       break;
 746     }
 747     case 'c': {
 748       /*
 749       **    ceiling
 750       **
 751       ** Resolve day-of-month overflow by rolling forward into the next
 752       ** month.  As this is the default action, this modifier is really
 753       ** a no-op that is only included for symmetry.  See "floor".
 754       */
 755       if( sqlite3_stricmp(z, "ceiling")==0 ){
 756         computeJD(p);
 757         clearYMD_HMS_TZ(p);
 758         rc = 0;
 759         p->nFloor = 0;
 760       }
 761       break;
 762     }
 763     case 'f': {
 764       /*
 765       **    floor
 766       **
 767       ** Resolve day-of-month overflow by rolling back to the end of the
 768       ** previous month.
 769       */
 770       if( sqlite3_stricmp(z, "floor")==0 ){
 771         computeJD(p);
 772         p->iJD -= p->nFloor*86400000;
 773         clearYMD_HMS_TZ(p);
 774         rc = 0;
 775       }
 776       break;
 777     }
 778     case 'j': {
 779       /*
 780       **    julianday
 781       **
 782       ** Always interpret the prior number as a julian-day value.  If this
 783       ** is not the first modifier, or if the prior argument is not a numeric
 784       ** value in the allowed range of julian day numbers understood by
 785       ** SQLite (0..5373484.5) then the result will be NULL.
 786       */
 787       if( sqlite3_stricmp(z, "julianday")==0 ){
 788         if( idx>1 ) return 1;  /* IMP: R-31176-64601 */
 789         if( p->validJD && p->rawS ){
 790           rc = 0;
 791           p->rawS = 0;
 792         }
 793       }
 794       break;
 795     }
 796 #ifndef SQLITE_OMIT_LOCALTIME
 797     case 'l': {
 798       /*    localtime
 799       **
 800       ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
 801       ** show local time.
 802       */
 803       if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){
 804         rc = p->isLocal ? SQLITE_OK : toLocaltime(p, pCtx);
 805         p->isUtc = 0;
 806         p->isLocal = 1;
 807       }
 808       break;
 809     }
 810 #endif
 811     case 'u': {
 812       /*
 813       **    unixepoch
 814       **
 815       ** Treat the current value of p->s as the number of
 816       ** seconds since 1970.  Convert to a real julian day number.
 817       */
 818       if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){
 819         if( idx>1 ) return 1;  /* IMP: R-49255-55373 */
 820         r = p->s*1000.0 + 210866760000000.0;
 821         if( r>=0.0 && r<464269060800000.0 ){
 822           clearYMD_HMS_TZ(p);
 823           p->iJD = (sqlite3_int64)(r + 0.5);
 824           p->validJD = 1;
 825           p->rawS = 0;
 826           rc = 0;
 827         }
 828       }
 829 #ifndef SQLITE_OMIT_LOCALTIME
 830       else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){
 831         if( p->isUtc==0 ){
 832           i64 iOrigJD;              /* Original localtime */
 833           i64 iGuess;               /* Guess at the corresponding utc time */
 834           int cnt = 0;              /* Safety to prevent infinite loop */
 835           i64 iErr;                 /* Guess is off by this much */
 836
 837           computeJD(p);
 838           iGuess = iOrigJD = p->iJD;
 839           iErr = 0;
 840           do{
 841             DateTime new;
 842             memset(&new, 0, sizeof(new));
 843             iGuess -= iErr;
 844             new.iJD = iGuess;
 845             new.validJD = 1;
 846             rc = toLocaltime(&new, pCtx);
 847             if( rc ) return rc;
 848             computeJD(&new);
 849             iErr = new.iJD - iOrigJD;
 850           }while( iErr && cnt++<3 );
 851           memset(p, 0, sizeof(*p));
 852           p->iJD = iGuess;
 853           p->validJD = 1;
 854           p->isUtc = 1;
 855           p->isLocal = 0;
 856         }
 857         rc = SQLITE_OK;
 858       }
 859 #endif
 860       break;
 861     }
 862     case 'w': {
 863       /*
 864       **    weekday N
 865       **
 866       ** Move the date to the same time on the next occurrence of
 867       ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
 868       ** date is already on the appropriate weekday, this is a no-op.
 869       */
 870       if( sqlite3_strnicmp(z, "weekday ", 8)==0
 871                && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0
 872                && r>=0.0 && r<7.0 && (n=(int)r)==r ){
 873         sqlite3_int64 Z;
 874         computeYMD_HMS(p);
 875         p->tz = 0;
 876         p->validJD = 0;
 877         computeJD(p);
 878         Z = ((p->iJD + 129600000)/86400000) % 7;
 879         if( Z>n ) Z -= 7;
 880         p->iJD += (n - Z)*86400000;
 881         clearYMD_HMS_TZ(p);
 882         rc = 0;
 883       }
 884       break;
 885     }
 886     case 's': {
 887       /*
 888       **    start of TTTTT
 889       **
 890       ** Move the date backwards to the beginning of the current day,
 891       ** or month or year.
 892       **
 893       **    subsecond
 894       **    subsec
 895       **
 896       ** Show subsecond precision in the output of datetime() and
 897       ** unixepoch() and strftime('%s').
 898       */
 899       if( sqlite3_strnicmp(z, "start of ", 9)!=0 ){
 900         if( sqlite3_stricmp(z, "subsec")==0
 901          || sqlite3_stricmp(z, "subsecond")==0
 902         ){
 903           p->useSubsec = 1;
 904           rc = 0;
 905         }
 906         break;
 907       }
 908       if( !p->validJD && !p->validYMD && !p->validHMS ) break;
 909       z += 9;
 910       computeYMD(p);
 911       p->validHMS = 1;
 912       p->h = p->m = 0;
 913       p->s = 0.0;
 914       p->rawS = 0;
 915       p->tz = 0;
 916       p->validJD = 0;
 917       if( sqlite3_stricmp(z,"month")==0 ){
 918         p->D = 1;
 919         rc = 0;
 920       }else if( sqlite3_stricmp(z,"year")==0 ){
 921         p->M = 1;
 922         p->D = 1;
 923         rc = 0;
 924       }else if( sqlite3_stricmp(z,"day")==0 ){
 925         rc = 0;
 926       }
 927       break;
 928     }
 929     case '+':
 930     case '-':
 931     case '0':
 932     case '1':
 933     case '2':
 934     case '3':
 935     case '4':
 936     case '5':
 937     case '6':
 938     case '7':
 939     case '8':
 940     case '9': {
 941       double rRounder;
 942       int i;
 943       int Y,M,D,h,m,x;
 944       const char *z2 = z;
 945       char z0 = z[0];
 946       for(n=1; z[n]; n++){
 947         if( z[n]==':' ) break;
 948         if( sqlite3Isspace(z[n]) ) break;
 949         if( z[n]=='-' ){
 950           if( n==5 && getDigits(&z[1], "40f", &Y)==1 ) break;
 951           if( n==6 && getDigits(&z[1], "50f", &Y)==1 ) break;
 952         }
 953       }
 954       if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){
 955         assert( rc==1 );
 956         break;
 957       }
 958       if( z[n]=='-' ){
 959         /* A modifier of the form (+|-)YYYY-MM-DD adds or subtracts the
 960         ** specified number of years, months, and days.  MM is limited to
 961         ** the range 0-11 and DD is limited to 0-30.
 962         */
 963         if( z0!='+' && z0!='-' ) break;  /* Must start with +/- */
 964         if( n==5 ){
 965           if( getDigits(&z[1], "40f-20a-20d", &Y, &M, &D)!=3 ) break;
 966         }else{
 967           assert( n==6 );
 968           if( getDigits(&z[1], "50f-20a-20d", &Y, &M, &D)!=3 ) break;
 969           z++;
 970         }
 971         if( M>=12 ) break;                   /* M range 0..11 */
 972         if( D>=31 ) break;                   /* D range 0..30 */
 973         computeYMD_HMS(p);
 974         p->validJD = 0;
 975         if( z0=='-' ){
 976           p->Y -= Y;
 977           p->M -= M;
 978           D = -D;
 979         }else{
 980           p->Y += Y;
 981           p->M += M;
 982         }
 983         x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
 984         p->Y += x;
 985         p->M -= x*12;
 986         computeFloor(p);
 987         computeJD(p);
 988         p->validHMS = 0;
 989         p->validYMD = 0;
 990         p->iJD += (i64)D*86400000;
 991         if( z[11]==0 ){
 992           rc = 0;
 993           break;
 994         }
 995         if( sqlite3Isspace(z[11])
 996          && getDigits(&z[12], "20c:20e", &h, &m)==2
 997         ){
 998           z2 = &z[12];
 999           n = 2;
1000         }else{
1001           break;
1002         }
1003       }
1004       if( z2[n]==':' ){
1005         /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
1006         ** specified number of hours, minutes, seconds, and fractional seconds
1007         ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
1008         ** omitted.
1009         */
1010
1011         DateTime tx;
1012         sqlite3_int64 day;
1013         if( !sqlite3Isdigit(*z2) ) z2++;
1014         memset(&tx, 0, sizeof(tx));
1015         if( parseHhMmSs(z2, &tx) ) break;
1016         computeJD(&tx);
1017         tx.iJD -= 43200000;
1018         day = tx.iJD/86400000;
1019         tx.iJD -= day*86400000;
1020         if( z0=='-' ) tx.iJD = -tx.iJD;
1021         computeJD(p);
1022         clearYMD_HMS_TZ(p);
1023         p->iJD += tx.iJD;
1024         rc = 0;
1025         break;
1026       }
1027
1028       /* If control reaches this point, it means the transformation is
1029       ** one of the forms like "+NNN days".  */
1030       z += n;
1031       while( sqlite3Isspace(*z) ) z++;
1032       n = sqlite3Strlen30(z);
1033       if( n<3 || n>10 ) break;
1034       if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
1035       computeJD(p);
1036       assert( rc==1 );
1037       rRounder = r<0 ? -0.5 : +0.5;
1038       p->nFloor = 0;
1039       for(i=0; i<ArraySize(aXformType); i++){
1040         if( aXformType[i].nName==n
1041          && sqlite3_strnicmp(aXformType[i].zName, z, n)==0
1042          && r>-aXformType[i].rLimit && r<aXformType[i].rLimit
1043         ){
1044           switch( i ){
1045             case 4: { /* Special processing to add months */
1046               assert( strcmp(aXformType[4].zName,"month")==0 );
1047               computeYMD_HMS(p);
1048               p->M += (int)r;
1049               x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
1050               p->Y += x;
1051               p->M -= x*12;
1052               computeFloor(p);
1053               p->validJD = 0;
1054               r -= (int)r;
1055               break;
1056             }
1057             case 5: { /* Special processing to add years */
1058               int y = (int)r;
1059               assert( strcmp(aXformType[5].zName,"year")==0 );
1060               computeYMD_HMS(p);
1061               assert( p->M>=0 && p->M<=12 );
1062               p->Y += y;
1063               computeFloor(p);
1064               p->validJD = 0;
1065               r -= (int)r;
1066               break;
1067             }
1068           }
1069           computeJD(p);
1070           p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder);
1071           rc = 0;
1072           break;
1073         }
1074       }
1075       clearYMD_HMS_TZ(p);
1076       break;
1077     }
1078     default: {
1079       break;
1080     }
1081   }
1082   return rc;
1083 }
1084
1085 /*
1086 ** Process time function arguments.  argv[0] is a date-time stamp.
1087 ** argv[1] and following are modifiers.  Parse them all and write
1088 ** the resulting time into the DateTime structure p.  Return 0
1089 ** on success and 1 if there are any errors.
1090 **
1091 ** If there are zero parameters (if even argv[0] is undefined)
1092 ** then assume a default value of "now" for argv[0].
1093 */
1094 static int isDate(
1095   sqlite3_context *context,
1096   int argc,
1097   sqlite3_value **argv,
1098   DateTime *p
1099 ){
1100   int i, n;
1101   const unsigned char *z;
1102   int eType;
1103   memset(p, 0, sizeof(*p));
1104   if( argc==0 ){
1105     if( !sqlite3NotPureFunc(context) ) return 1;
1106     return setDateTimeToCurrent(context, p);
1107   }
1108   if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
1109                    || eType==SQLITE_INTEGER ){
1110     setRawDateNumber(p, sqlite3_value_double(argv[0]));
1111   }else{
1112     z = sqlite3_value_text(argv[0]);
1113     if( !z || parseDateOrTime(context, (char*)z, p) ){
1114       return 1;
1115     }
1116   }
1117   for(i=1; i<argc; i++){
1118     z = sqlite3_value_text(argv[i]);
1119     n = sqlite3_value_bytes(argv[i]);
1120     if( z==0 || parseModifier(context, (char*)z, n, p, i) ) return 1;
1121   }
1122   computeJD(p);
1123   if( p->isError || !validJulianDay(p->iJD) ) return 1;
1124   if( argc==1 && p->validYMD && p->D>28 ){
1125     /* Make sure a YYYY-MM-DD is normalized.
1126     ** Example: 2023-02-31 -> 2023-03-03 */
1127     assert( p->validJD );
1128     p->validYMD = 0;
1129   }
1130   return 0;
1131 }
1132
1133
1134 /*
1135 ** The following routines implement the various date and time functions
1136 ** of SQLite.
1137 */
1138
1139 /*
1140 **    julianday( TIMESTRING, MOD, MOD, ...)
1141 **
1142 ** Return the julian day number of the date specified in the arguments
1143 */
1144 static void juliandayFunc(
1145   sqlite3_context *context,
1146   int argc,
1147   sqlite3_value **argv
1148 ){
1149   DateTime x;
1150   if( isDate(context, argc, argv, &x)==0 ){
1151     computeJD(&x);
1152     sqlite3_result_double(context, x.iJD/86400000.0);
1153   }
1154 }
1155
1156 /*
1157 **    unixepoch( TIMESTRING, MOD, MOD, ...)
1158 **
1159 ** Return the number of seconds (including fractional seconds) since
1160 ** the unix epoch of 1970-01-01 00:00:00 GMT.
1161 */
1162 static void unixepochFunc(
1163   sqlite3_context *context,
1164   int argc,
1165   sqlite3_value **argv
1166 ){
1167   DateTime x;
1168   if( isDate(context, argc, argv, &x)==0 ){
1169     computeJD(&x);
1170     if( x.useSubsec ){
1171       sqlite3_result_double(context, (x.iJD - 21086676*(i64)10000000)/1000.0);
1172     }else{
1173       sqlite3_result_int64(context, x.iJD/1000 - 21086676*(i64)10000);
1174     }
1175   }
1176 }
1177
1178 /*
1179 **    datetime( TIMESTRING, MOD, MOD, ...)
1180 **
1181 ** Return YYYY-MM-DD HH:MM:SS
1182 */
1183 static void datetimeFunc(
1184   sqlite3_context *context,
1185   int argc,
1186   sqlite3_value **argv
1187 ){
1188   DateTime x;
1189   if( isDate(context, argc, argv, &x)==0 ){
1190     int Y, s, n;
1191     char zBuf[32];
1192     computeYMD_HMS(&x);
1193     Y = x.Y;
1194     if( Y<0 ) Y = -Y;
1195     zBuf[1] = '0' + (Y/1000)%10;
1196     zBuf[2] = '0' + (Y/100)%10;
1197     zBuf[3] = '0' + (Y/10)%10;
1198     zBuf[4] = '0' + (Y)%10;
1199     zBuf[5] = '-';
1200     zBuf[6] = '0' + (x.M/10)%10;
1201     zBuf[7] = '0' + (x.M)%10;
1202     zBuf[8] = '-';
1203     zBuf[9] = '0' + (x.D/10)%10;
1204     zBuf[10] = '0' + (x.D)%10;
1205     zBuf[11] = ' ';
1206     zBuf[12] = '0' + (x.h/10)%10;
1207     zBuf[13] = '0' + (x.h)%10;
1208     zBuf[14] = ':';
1209     zBuf[15] = '0' + (x.m/10)%10;
1210     zBuf[16] = '0' + (x.m)%10;
1211     zBuf[17] = ':';
1212     if( x.useSubsec ){
1213       s = (int)(1000.0*x.s + 0.5);
1214       zBuf[18] = '0' + (s/10000)%10;
1215       zBuf[19] = '0' + (s/1000)%10;
1216       zBuf[20] = '.';
1217       zBuf[21] = '0' + (s/100)%10;
1218       zBuf[22] = '0' + (s/10)%10;
1219       zBuf[23] = '0' + (s)%10;
1220       zBuf[24] = 0;
1221       n = 24;
1222     }else{
1223       s = (int)x.s;
1224       zBuf[18] = '0' + (s/10)%10;
1225       zBuf[19] = '0' + (s)%10;
1226       zBuf[20] = 0;
1227       n = 20;
1228     }
1229     if( x.Y<0 ){
1230       zBuf[0] = '-';
1231       sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT);
1232     }else{
1233       sqlite3_result_text(context, &zBuf[1], n-1, SQLITE_TRANSIENT);
1234     }
1235   }
1236 }
1237
1238 /*
1239 **    time( TIMESTRING, MOD, MOD, ...)
1240 **
1241 ** Return HH:MM:SS
1242 */
1243 static void timeFunc(
1244   sqlite3_context *context,
1245   int argc,
1246   sqlite3_value **argv
1247 ){
1248   DateTime x;
1249   if( isDate(context, argc, argv, &x)==0 ){
1250     int s, n;
1251     char zBuf[16];
1252     computeHMS(&x);
1253     zBuf[0] = '0' + (x.h/10)%10;
1254     zBuf[1] = '0' + (x.h)%10;
1255     zBuf[2] = ':';
1256     zBuf[3] = '0' + (x.m/10)%10;
1257     zBuf[4] = '0' + (x.m)%10;
1258     zBuf[5] = ':';
1259     if( x.useSubsec ){
1260       s = (int)(1000.0*x.s + 0.5);
1261       zBuf[6] = '0' + (s/10000)%10;
1262       zBuf[7] = '0' + (s/1000)%10;
1263       zBuf[8] = '.';
1264       zBuf[9] = '0' + (s/100)%10;
1265       zBuf[10] = '0' + (s/10)%10;
1266       zBuf[11] = '0' + (s)%10;
1267       zBuf[12] = 0;
1268       n = 12;
1269     }else{
1270       s = (int)x.s;
1271       zBuf[6] = '0' + (s/10)%10;
1272       zBuf[7] = '0' + (s)%10;
1273       zBuf[8] = 0;
1274       n = 8;
1275     }
1276     sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT);
1277   }
1278 }
1279
1280 /*
1281 **    date( TIMESTRING, MOD, MOD, ...)
1282 **
1283 ** Return YYYY-MM-DD
1284 */
1285 static void dateFunc(
1286   sqlite3_context *context,
1287   int argc,
1288   sqlite3_value **argv
1289 ){
1290   DateTime x;
1291   if( isDate(context, argc, argv, &x)==0 ){
1292     int Y;
1293     char zBuf[16];
1294     computeYMD(&x);
1295     Y = x.Y;
1296     if( Y<0 ) Y = -Y;
1297     zBuf[1] = '0' + (Y/1000)%10;
1298     zBuf[2] = '0' + (Y/100)%10;
1299     zBuf[3] = '0' + (Y/10)%10;
1300     zBuf[4] = '0' + (Y)%10;
1301     zBuf[5] = '-';
1302     zBuf[6] = '0' + (x.M/10)%10;
1303     zBuf[7] = '0' + (x.M)%10;
1304     zBuf[8] = '-';
1305     zBuf[9] = '0' + (x.D/10)%10;
1306     zBuf[10] = '0' + (x.D)%10;
1307     zBuf[11] = 0;
1308     if( x.Y<0 ){
1309       zBuf[0] = '-';
1310       sqlite3_result_text(context, zBuf, 11, SQLITE_TRANSIENT);
1311     }else{
1312       sqlite3_result_text(context, &zBuf[1], 10, SQLITE_TRANSIENT);
1313     }
1314   }
1315 }
1316
1317 /*
1318 ** Compute the number of days after the most recent January 1.
1319 **
1320 ** In other words, compute the zero-based day number for the
1321 ** current year:
1322 **
1323 **   Jan01 = 0,  Jan02 = 1, ..., Jan31 = 30, Feb01 = 31, ...
1324 **   Dec31 = 364 or 365.
1325 */
1326 static int daysAfterJan01(DateTime *pDate){
1327   DateTime jan01 = *pDate;
1328   assert( jan01.validYMD );
1329   assert( jan01.validHMS );
1330   assert( pDate->validJD );
1331   jan01.validJD = 0;
1332   jan01.M = 1;
1333   jan01.D = 1;
1334   computeJD(&jan01);
1335   return (int)((pDate->iJD-jan01.iJD+43200000)/86400000);
1336 }
1337
1338 /*
1339 ** Return the number of days after the most recent Monday.
1340 **
1341 ** In other words, return the day of the week according
1342 ** to this code:
1343 **
1344 **   0=Monday, 1=Tuesday, 2=Wednesday, ..., 6=Sunday.
1345 */
1346 static int daysAfterMonday(DateTime *pDate){
1347   assert( pDate->validJD );
1348   return (int)((pDate->iJD+43200000)/86400000) % 7;
1349 }
1350
1351 /*
1352 ** Return the number of days after the most recent Sunday.
1353 **
1354 ** In other words, return the day of the week according
1355 ** to this code:
1356 **
1357 **   0=Sunday, 1=Monday, 2=Tues, ..., 6=Saturday
1358 */
1359 static int daysAfterSunday(DateTime *pDate){
1360   assert( pDate->validJD );
1361   return (int)((pDate->iJD+129600000)/86400000) % 7;
1362 }
1363
1364 /*
1365 **    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
1366 **
1367 ** Return a string described by FORMAT.  Conversions as follows:
1368 **
1369 **   %d  day of month  01-31
1370 **   %e  day of month  1-31
1371 **   %f  ** fractional seconds  SS.SSS
1372 **   %F  ISO date.  YYYY-MM-DD
1373 **   %G  ISO year corresponding to %V 0000-9999.
1374 **   %g  2-digit ISO year corresponding to %V 00-99
1375 **   %H  hour 00-24
1376 **   %k  hour  0-24  (leading zero converted to space)
1377 **   %I  hour 01-12
1378 **   %j  day of year 001-366
1379 **   %J  ** julian day number
1380 **   %l  hour  1-12  (leading zero converted to space)
1381 **   %m  month 01-12
1382 **   %M  minute 00-59
1383 **   %p  "am" or "pm"
1384 **   %P  "AM" or "PM"
1385 **   %R  time as HH:MM
1386 **   %s  seconds since 1970-01-01
1387 **   %S  seconds 00-59
1388 **   %T  time as HH:MM:SS
1389 **   %u  day of week 1-7  Monday==1, Sunday==7
1390 **   %w  day of week 0-6  Sunday==0, Monday==1
1391 **   %U  week of year 00-53  (First Sunday is start of week 01)
1392 **   %V  week of year 01-53  (First week containing Thursday is week 01)
1393 **   %W  week of year 00-53  (First Monday is start of week 01)
1394 **   %Y  year 0000-9999
1395 **   %%  %
1396 */
1397 static void strftimeFunc(
1398   sqlite3_context *context,
1399   int argc,
1400   sqlite3_value **argv
1401 ){
1402   DateTime x;
1403   size_t i,j;
1404   sqlite3 *db;
1405   const char *zFmt;
1406   sqlite3_str sRes;
1407
1408
1409   if( argc==0 ) return;
1410   zFmt = (const char*)sqlite3_value_text(argv[0]);
1411   if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
1412   db = sqlite3_context_db_handle(context);
1413   sqlite3StrAccumInit(&sRes, 0, 0, 0, db->aLimit[SQLITE_LIMIT_LENGTH]);
1414
1415   computeJD(&x);
1416   computeYMD_HMS(&x);
1417   for(i=j=0; zFmt[i]; i++){
1418     char cf;
1419     if( zFmt[i]!='%' ) continue;
1420     if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j));
1421     i++;
1422     j = i + 1;
1423     cf = zFmt[i];
1424     switch( cf ){
1425       case 'd':  /* Fall thru */
1426       case 'e': {
1427         sqlite3_str_appendf(&sRes, cf=='d' ? "%02d" : "%2d", x.D);
1428         break;
1429       }
1430       case 'f': {  /* Fractional seconds.  (Non-standard) */
1431         double s = x.s;
1432         if( s>59.999 ) s = 59.999;
1433         sqlite3_str_appendf(&sRes, "%06.3f", s);
1434         break;
1435       }
1436       case 'F': {
1437         sqlite3_str_appendf(&sRes, "%04d-%02d-%02d", x.Y, x.M, x.D);
1438         break;
1439       }
1440       case 'G': /* Fall thru */
1441       case 'g': {
1442         DateTime y = x;
1443         assert( y.validJD );
1444         /* Move y so that it is the Thursday in the same week as x */
1445         y.iJD += (3 - daysAfterMonday(&x))*86400000;
1446         y.validYMD = 0;
1447         computeYMD(&y);
1448         if( cf=='g' ){
1449           sqlite3_str_appendf(&sRes, "%02d", y.Y%100);
1450         }else{
1451           sqlite3_str_appendf(&sRes, "%04d", y.Y);
1452         }
1453         break;
1454       }
1455       case 'H':
1456       case 'k': {
1457         sqlite3_str_appendf(&sRes, cf=='H' ? "%02d" : "%2d", x.h);
1458         break;
1459       }
1460       case 'I': /* Fall thru */
1461       case 'l': {
1462         int h = x.h;
1463         if( h>12 ) h -= 12;
1464         if( h==0 ) h = 12;
1465         sqlite3_str_appendf(&sRes, cf=='I' ? "%02d" : "%2d", h);
1466         break;
1467       }
1468       case 'j': {  /* Day of year.  Jan01==1, Jan02==2, and so forth */
1469         sqlite3_str_appendf(&sRes,"%03d",daysAfterJan01(&x)+1);
1470         break;
1471       }
1472       case 'J': {  /* Julian day number.  (Non-standard) */
1473         sqlite3_str_appendf(&sRes,"%.16g",x.iJD/86400000.0);
1474         break;
1475       }
1476       case 'm': {
1477         sqlite3_str_appendf(&sRes,"%02d",x.M);
1478         break;
1479       }
1480       case 'M': {
1481         sqlite3_str_appendf(&sRes,"%02d",x.m);
1482         break;
1483       }
1484       case 'p': /* Fall thru */
1485       case 'P': {
1486         if( x.h>=12 ){
1487           sqlite3_str_append(&sRes, cf=='p' ? "PM" : "pm", 2);
1488         }else{
1489           sqlite3_str_append(&sRes, cf=='p' ? "AM" : "am", 2);
1490         }
1491         break;
1492       }
1493       case 'R': {
1494         sqlite3_str_appendf(&sRes, "%02d:%02d", x.h, x.m);
1495         break;
1496       }
1497       case 's': {
1498         if( x.useSubsec ){
1499           sqlite3_str_appendf(&sRes,"%.3f",
1500                 (x.iJD - 21086676*(i64)10000000)/1000.0);
1501         }else{
1502           i64 iS = (i64)(x.iJD/1000 - 21086676*(i64)10000);
1503           sqlite3_str_appendf(&sRes,"%lld",iS);
1504         }
1505         break;
1506       }
1507       case 'S': {
1508         sqlite3_str_appendf(&sRes,"%02d",(int)x.s);
1509         break;
1510       }
1511       case 'T': {
1512         sqlite3_str_appendf(&sRes,"%02d:%02d:%02d", x.h, x.m, (int)x.s);
1513         break;
1514       }
1515       case 'u':    /* Day of week.  1 to 7.  Monday==1, Sunday==7 */
1516       case 'w': {  /* Day of week.  0 to 6.  Sunday==0, Monday==1 */
1517         char c = (char)daysAfterSunday(&x) + '0';
1518         if( c=='0' && cf=='u' ) c = '7';
1519         sqlite3_str_appendchar(&sRes, 1, c);
1520         break;
1521       }
1522       case 'U': {  /* Week num. 00-53. First Sun of the year is week 01 */
1523         sqlite3_str_appendf(&sRes,"%02d",
1524               (daysAfterJan01(&x)-daysAfterSunday(&x)+7)/7);
1525         break;
1526       }
1527       case 'V': {  /* Week num. 01-53. First week with a Thur is week 01 */
1528         DateTime y = x;
1529         /* Adjust y so that is the Thursday in the same week as x */
1530         assert( y.validJD );
1531         y.iJD += (3 - daysAfterMonday(&x))*86400000;
1532         y.validYMD = 0;
1533         computeYMD(&y);
1534         sqlite3_str_appendf(&sRes,"%02d", daysAfterJan01(&y)/7+1);
1535         break;
1536       }
1537       case 'W': {  /* Week num. 00-53. First Mon of the year is week 01 */
1538         sqlite3_str_appendf(&sRes,"%02d",
1539            (daysAfterJan01(&x)-daysAfterMonday(&x)+7)/7);
1540         break;
1541       }
1542       case 'Y': {
1543         sqlite3_str_appendf(&sRes,"%04d",x.Y);
1544         break;
1545       }
1546       case '%': {
1547         sqlite3_str_appendchar(&sRes, 1, '%');
1548         break;
1549       }
1550       default: {
1551         sqlite3_str_reset(&sRes);
1552         return;
1553       }
1554     }
1555   }
1556   if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j));
1557   sqlite3ResultStrAccum(context, &sRes);
1558 }
1559
1560 /*
1561 ** current_time()
1562 **
1563 ** This function returns the same value as time('now').
1564 */
1565 static void ctimeFunc(
1566   sqlite3_context *context,
1567   int NotUsed,
1568   sqlite3_value **NotUsed2
1569 ){
1570   UNUSED_PARAMETER2(NotUsed, NotUsed2);
1571   timeFunc(context, 0, 0);
1572 }
1573
1574 /*
1575 ** current_date()
1576 **
1577 ** This function returns the same value as date('now').
1578 */
1579 static void cdateFunc(
1580   sqlite3_context *context,
1581   int NotUsed,
1582   sqlite3_value **NotUsed2
1583 ){
1584   UNUSED_PARAMETER2(NotUsed, NotUsed2);
1585   dateFunc(context, 0, 0);
1586 }
1587
1588 /*
1589 ** timediff(DATE1, DATE2)
1590 **
1591 ** Return the amount of time that must be added to DATE2 in order to
1592 ** convert it into DATE2.  The time difference format is:
1593 **
1594 **     +YYYY-MM-DD HH:MM:SS.SSS
1595 **
1596 ** The initial "+" becomes "-" if DATE1 occurs before DATE2.  For
1597 ** date/time values A and B, the following invariant should hold:
1598 **
1599 **     datetime(A) == (datetime(B, timediff(A,B))
1600 **
1601 ** Both DATE arguments must be either a julian day number, or an
1602 ** ISO-8601 string.  The unix timestamps are not supported by this
1603 ** routine.
1604 */
1605 static void timediffFunc(
1606   sqlite3_context *context,
1607   int NotUsed1,
1608   sqlite3_value **argv
1609 ){
1610   char sign;
1611   int Y, M;
1612   DateTime d1, d2;
1613   sqlite3_str sRes;
1614   UNUSED_PARAMETER(NotUsed1);
1615   if( isDate(context, 1, &argv[0], &d1) ) return;
1616   if( isDate(context, 1, &argv[1], &d2) ) return;
1617   computeYMD_HMS(&d1);
1618   computeYMD_HMS(&d2);
1619   if( d1.iJD>=d2.iJD ){
1620     sign = '+';
1621     Y = d1.Y - d2.Y;
1622     if( Y ){
1623       d2.Y = d1.Y;
1624       d2.validJD = 0;
1625       computeJD(&d2);
1626     }
1627     M = d1.M - d2.M;
1628     if( M<0 ){
1629       Y--;
1630       M += 12;
1631     }
1632     if( M!=0 ){
1633       d2.M = d1.M;
1634       d2.validJD = 0;
1635       computeJD(&d2);
1636     }
1637     while( d1.iJD<d2.iJD ){
1638       M--;
1639       if( M<0 ){
1640         M = 11;
1641         Y--;
1642       }
1643       d2.M--;
1644       if( d2.M<1 ){
1645         d2.M = 12;
1646         d2.Y--;
1647       }
1648       d2.validJD = 0;
1649       computeJD(&d2);
1650     }
1651     d1.iJD -= d2.iJD;
1652     d1.iJD += (u64)1486995408 * (u64)100000;
1653   }else /* d1<d2 */{
1654     sign = '-';
1655     Y = d2.Y - d1.Y;
1656     if( Y ){
1657       d2.Y = d1.Y;
1658       d2.validJD = 0;
1659       computeJD(&d2);
1660     }
1661     M = d2.M - d1.M;
1662     if( M<0 ){
1663       Y--;
1664       M += 12;
1665     }
1666     if( M!=0 ){
1667       d2.M = d1.M;
1668       d2.validJD = 0;
1669       computeJD(&d2);
1670     }
1671     while( d1.iJD>d2.iJD ){
1672       M--;
1673       if( M<0 ){
1674         M = 11;
1675         Y--;
1676       }
1677       d2.M++;
1678       if( d2.M>12 ){
1679         d2.M = 1;
1680         d2.Y++;
1681       }
1682       d2.validJD = 0;
1683       computeJD(&d2);
1684     }
1685     d1.iJD = d2.iJD - d1.iJD;
1686     d1.iJD += (u64)1486995408 * (u64)100000;
1687   }
1688   clearYMD_HMS_TZ(&d1);
1689   computeYMD_HMS(&d1);
1690   sqlite3StrAccumInit(&sRes, 0, 0, 0, 100);
1691   sqlite3_str_appendf(&sRes, "%c%04d-%02d-%02d %02d:%02d:%06.3f",
1692        sign, Y, M, d1.D-1, d1.h, d1.m, d1.s);
1693   sqlite3ResultStrAccum(context, &sRes);
1694 }
1695
1696
1697 /*
1698 ** current_timestamp()
1699 **
1700 ** This function returns the same value as datetime('now').
1701 */
1702 static void ctimestampFunc(
1703   sqlite3_context *context,
1704   int NotUsed,
1705   sqlite3_value **NotUsed2
1706 ){
1707   UNUSED_PARAMETER2(NotUsed, NotUsed2);
1708   datetimeFunc(context, 0, 0);
1709 }
1710 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
1711
1712 #ifdef SQLITE_OMIT_DATETIME_FUNCS
1713 /*
1714 ** If the library is compiled to omit the full-scale date and time
1715 ** handling (to get a smaller binary), the following minimal version
1716 ** of the functions current_time(), current_date() and current_timestamp()
1717 ** are included instead. This is to support column declarations that
1718 ** include "DEFAULT CURRENT_TIME" etc.
1719 **
1720 ** This function uses the C-library functions time(), gmtime()
1721 ** and strftime(). The format string to pass to strftime() is supplied
1722 ** as the user-data for the function.
1723 */
1724 static void currentTimeFunc(
1725   sqlite3_context *context,
1726   int argc,
1727   sqlite3_value **argv
1728 ){
1729   time_t t;
1730   char *zFormat = (char *)sqlite3_user_data(context);
1731   sqlite3_int64 iT;
1732   struct tm *pTm;
1733   struct tm sNow;
1734   char zBuf[20];
1735
1736   UNUSED_PARAMETER(argc);
1737   UNUSED_PARAMETER(argv);
1738
1739   iT = sqlite3StmtCurrentTime(context);
1740   if( iT<=0 ) return;
1741   t = iT/1000 - 10000*(sqlite3_int64)21086676;
1742 #if HAVE_GMTIME_R
1743   pTm = gmtime_r(&t, &sNow);
1744 #else
1745   sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN));
1746   pTm = gmtime(&t);
1747   if( pTm ) memcpy(&sNow, pTm, sizeof(sNow));
1748   sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN));
1749 #endif
1750   if( pTm ){
1751     strftime(zBuf, 20, zFormat, &sNow);
1752     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
1753   }
1754 }
1755 #endif
1756
1757 #if !defined(SQLITE_OMIT_DATETIME_FUNCS) && defined(SQLITE_DEBUG)
1758 /*
1759 **   datedebug(...)
1760 **
1761 ** This routine returns JSON that describes the internal DateTime object.
1762 ** Used for debugging and testing only.  Subject to change.
1763 */
1764 static void datedebugFunc(
1765   sqlite3_context *context,
1766   int argc,
1767   sqlite3_value **argv
1768 ){
1769   DateTime x;
1770   if( isDate(context, argc, argv, &x)==0 ){
1771     char *zJson;
1772     zJson = sqlite3_mprintf(
1773       "{iJD:%lld,Y:%d,M:%d,D:%d,h:%d,m:%d,tz:%d,"
1774       "s:%.3f,validJD:%d,validYMS:%d,validHMS:%d,"
1775       "nFloor:%d,rawS:%d,isError:%d,useSubsec:%d,"
1776       "isUtc:%d,isLocal:%d}",
1777       x.iJD, x.Y, x.M, x.D, x.h, x.m, x.tz,
1778       x.s, x.validJD, x.validYMD, x.validHMS,
1779       x.nFloor, x.rawS, x.isError, x.useSubsec,
1780       x.isUtc, x.isLocal);
1781     sqlite3_result_text(context, zJson, -1, sqlite3_free);
1782   }
1783 }
1784 #endif /* !SQLITE_OMIT_DATETIME_FUNCS && SQLITE_DEBUG */
1785
1786
1787 /*
1788 ** This function registered all of the above C functions as SQL
1789 ** functions.  This should be the only routine in this file with
1790 ** external linkage.
1791 */
1792 void sqlite3RegisterDateTimeFunctions(void){
1793   static FuncDef aDateTimeFuncs[] = {
1794 #ifndef SQLITE_OMIT_DATETIME_FUNCS
1795     PURE_DATE(julianday,        -1, 0, 0, juliandayFunc ),
1796     PURE_DATE(unixepoch,        -1, 0, 0, unixepochFunc ),
1797     PURE_DATE(date,             -1, 0, 0, dateFunc      ),
1798     PURE_DATE(time,             -1, 0, 0, timeFunc      ),
1799     PURE_DATE(datetime,         -1, 0, 0, datetimeFunc  ),
1800     PURE_DATE(strftime,         -1, 0, 0, strftimeFunc  ),
1801     PURE_DATE(timediff,          2, 0, 0, timediffFunc  ),
1802 #ifdef SQLITE_DEBUG
1803     PURE_DATE(datedebug,        -1, 0, 0, datedebugFunc ),
1804 #endif
1805     DFUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
1806     DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
1807     DFUNCTION(current_date,      0, 0, 0, cdateFunc     ),
1808 #else
1809     STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
1810     STR_FUNCTION(current_date,      0, "%Y-%m-%d",          0, currentTimeFunc),
1811     STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
1812 #endif
1813   };
1814   sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
1815 }