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, 14712 };
 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 day_ms, day_min; /* milliseconds, minutes into the day */
 456   if( p->validHMS ) return;
 457   computeJD(p);
 458   day_ms = (int)((p->iJD + 43200000) % 86400000);
 459   p->s = (day_ms % 60000)/1000.0;
 460   day_min = day_ms/60000;
 461   p->m = day_min % 60;
 462   p->h = day_min / 60;
 463   p->rawS = 0;
 464   p->validHMS = 1;
 465 }
 466
 467 /*
 468 ** Compute both YMD and HMS
 469 */
 470 static void computeYMD_HMS(DateTime *p){
 471   computeYMD(p);
 472   computeHMS(p);
 473 }
 474
 475 /*
 476 ** Clear the YMD and HMS and the TZ
 477 */
 478 static void clearYMD_HMS_TZ(DateTime *p){
 479   p->validYMD = 0;
 480   p->validHMS = 0;
 481   p->validTZ = 0;
 482 }
 483
 484 #ifndef SQLITE_OMIT_LOCALTIME
 485 /*
 486 ** On recent Windows platforms, the localtime_s() function is available
 487 ** as part of the "Secure CRT". It is essentially equivalent to
 488 ** localtime_r() available under most POSIX platforms, except that the
 489 ** order of the parameters is reversed.
 490 **
 491 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
 492 **
 493 ** If the user has not indicated to use localtime_r() or localtime_s()
 494 ** already, check for an MSVC build environment that provides
 495 ** localtime_s().
 496 */
 497 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \
 498     && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
 499 #undef  HAVE_LOCALTIME_S
 500 #define HAVE_LOCALTIME_S 1
 501 #endif
 502
 503 /*
 504 ** The following routine implements the rough equivalent of localtime_r()
 505 ** using whatever operating-system specific localtime facility that
 506 ** is available.  This routine returns 0 on success and
 507 ** non-zero on any kind of error.
 508 **
 509 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is non-zero then this
 510 ** routine will always fail.  If bLocaltimeFault is nonzero and
 511 ** sqlite3GlobalConfig.xAltLocaltime is not NULL, then xAltLocaltime() is
 512 ** invoked in place of the OS-defined localtime() function.
 513 **
 514 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
 515 ** library function localtime_r() is used to assist in the calculation of
 516 ** local time.
 517 */
 518 static int osLocaltime(time_t *t, struct tm *pTm){
 519   int rc;
 520 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S
 521   struct tm *pX;
 522 #if SQLITE_THREADSAFE>0
 523   sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN);
 524 #endif
 525   sqlite3_mutex_enter(mutex);
 526   pX = localtime(t);
 527 #ifndef SQLITE_UNTESTABLE
 528   if( sqlite3GlobalConfig.bLocaltimeFault ){
 529     if( sqlite3GlobalConfig.xAltLocaltime!=0
 530      && 0==sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm)
 531     ){
 532       pX = pTm;
 533     }else{
 534       pX = 0;
 535     }
 536   }
 537 #endif
 538   if( pX ) *pTm = *pX;
 539 #if SQLITE_THREADSAFE>0
 540   sqlite3_mutex_leave(mutex);
 541 #endif
 542   rc = pX==0;
 543 #else
 544 #ifndef SQLITE_UNTESTABLE
 545   if( sqlite3GlobalConfig.bLocaltimeFault ){
 546     if( sqlite3GlobalConfig.xAltLocaltime!=0 ){
 547       return sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm);
 548     }else{
 549       return 1;
 550     }
 551   }
 552 #endif
 553 #if HAVE_LOCALTIME_R
 554   rc = localtime_r(t, pTm)==0;
 555 #else
 556   rc = localtime_s(pTm, t);
 557 #endif /* HAVE_LOCALTIME_R */
 558 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
 559   return rc;
 560 }
 561 #endif /* SQLITE_OMIT_LOCALTIME */
 562
 563
 564 #ifndef SQLITE_OMIT_LOCALTIME
 565 /*
 566 ** Assuming the input DateTime is UTC, move it to its localtime equivalent.
 567 */
 568 static int toLocaltime(
 569   DateTime *p,                   /* Date at which to calculate offset */
 570   sqlite3_context *pCtx          /* Write error here if one occurs */
 571 ){
 572   time_t t;
 573   struct tm sLocal;
 574   int iYearDiff;
 575
 576   /* Initialize the contents of sLocal to avoid a compiler warning. */
 577   memset(&sLocal, 0, sizeof(sLocal));
 578
 579   computeJD(p);
 580   if( p->iJD<2108667600*(i64)100000 /* 1970-01-01 */
 581    || p->iJD>2130141456*(i64)100000 /* 2038-01-18 */
 582   ){
 583     /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
 584     ** works for years between 1970 and 2037. For dates outside this range,
 585     ** SQLite attempts to map the year into an equivalent year within this
 586     ** range, do the calculation, then map the year back.
 587     */
 588     DateTime x = *p;
 589     computeYMD_HMS(&x);
 590     iYearDiff = (2000 + x.Y%4) - x.Y;
 591     x.Y += iYearDiff;
 592     x.validJD = 0;
 593     computeJD(&x);
 594     t = (time_t)(x.iJD/1000 -  21086676*(i64)10000);
 595   }else{
 596     iYearDiff = 0;
 597     t = (time_t)(p->iJD/1000 -  21086676*(i64)10000);
 598   }
 599   if( osLocaltime(&t, &sLocal) ){
 600     sqlite3_result_error(pCtx, "local time unavailable", -1);
 601     return SQLITE_ERROR;
 602   }
 603   p->Y = sLocal.tm_year + 1900 - iYearDiff;
 604   p->M = sLocal.tm_mon + 1;
 605   p->D = sLocal.tm_mday;
 606   p->h = sLocal.tm_hour;
 607   p->m = sLocal.tm_min;
 608   p->s = sLocal.tm_sec + (p->iJD%1000)*0.001;
 609   p->validYMD = 1;
 610   p->validHMS = 1;
 611   p->validJD = 0;
 612   p->rawS = 0;
 613   p->validTZ = 0;
 614   p->isError = 0;
 615   return SQLITE_OK;
 616 }
 617 #endif /* SQLITE_OMIT_LOCALTIME */
 618
 619 /*
 620 ** The following table defines various date transformations of the form
 621 **
 622 **            'NNN days'
 623 **
 624 ** Where NNN is an arbitrary floating-point number and "days" can be one
 625 ** of several units of time.
 626 */
 627 static const struct {
 628   u8 nName;           /* Length of the name */
 629   char zName[7];      /* Name of the transformation */
 630   float rLimit;       /* Maximum NNN value for this transform */
 631   float rXform;       /* Constant used for this transform */
 632 } aXformType[] = {
 633   { 6, "second", 4.6427e+14,       1.0  },
 634   { 6, "minute", 7.7379e+12,      60.0  },
 635   { 4, "hour",   1.2897e+11,    3600.0  },
 636   { 3, "day",    5373485.0,    86400.0  },
 637   { 5, "month",  176546.0,   2592000.0  },
 638   { 4, "year",   14713.0,   31536000.0  },
 639 };
 640
 641 /*
 642 ** If the DateTime p is raw number, try to figure out if it is
 643 ** a julian day number of a unix timestamp.  Set the p value
 644 ** appropriately.
 645 */
 646 static void autoAdjustDate(DateTime *p){
 647   if( !p->rawS || p->validJD ){
 648     p->rawS = 0;
 649   }else if( p->s>=-21086676*(i64)10000        /* -4713-11-24 12:00:00 */
 650          && p->s<=(25340230*(i64)10000)+799   /*  9999-12-31 23:59:59 */
 651   ){
 652     double r = p->s*1000.0 + 210866760000000.0;
 653     clearYMD_HMS_TZ(p);
 654     p->iJD = (sqlite3_int64)(r + 0.5);
 655     p->validJD = 1;
 656     p->rawS = 0;
 657   }
 658 }
 659
 660 /*
 661 ** Process a modifier to a date-time stamp.  The modifiers are
 662 ** as follows:
 663 **
 664 **     NNN days
 665 **     NNN hours
 666 **     NNN minutes
 667 **     NNN.NNNN seconds
 668 **     NNN months
 669 **     NNN years
 670 **     start of month
 671 **     start of year
 672 **     start of week
 673 **     start of day
 674 **     weekday N
 675 **     unixepoch
 676 **     localtime
 677 **     utc
 678 **
 679 ** Return 0 on success and 1 if there is any kind of error. If the error
 680 ** is in a system call (i.e. localtime()), then an error message is written
 681 ** to context pCtx. If the error is an unrecognized modifier, no error is
 682 ** written to pCtx.
 683 */
 684 static int parseModifier(
 685   sqlite3_context *pCtx,      /* Function context */
 686   const char *z,              /* The text of the modifier */
 687   int n,                      /* Length of zMod in bytes */
 688   DateTime *p,                /* The date/time value to be modified */
 689   int idx                     /* Parameter index of the modifier */
 690 ){
 691   int rc = 1;
 692   double r;
 693   switch(sqlite3UpperToLower[(u8)z[0]] ){
 694     case 'a': {
 695       /*
 696       **    auto
 697       **
 698       ** If rawS is available, then interpret as a julian day number, or
 699       ** a unix timestamp, depending on its magnitude.
 700       */
 701       if( sqlite3_stricmp(z, "auto")==0 ){
 702         if( idx>1 ) return 1; /* IMP: R-33611-57934 */
 703         autoAdjustDate(p);
 704         rc = 0;
 705       }
 706       break;
 707     }
 708     case 'j': {
 709       /*
 710       **    julianday
 711       **
 712       ** Always interpret the prior number as a julian-day value.  If this
 713       ** is not the first modifier, or if the prior argument is not a numeric
 714       ** value in the allowed range of julian day numbers understood by
 715       ** SQLite (0..5373484.5) then the result will be NULL.
 716       */
 717       if( sqlite3_stricmp(z, "julianday")==0 ){
 718         if( idx>1 ) return 1;  /* IMP: R-31176-64601 */
 719         if( p->validJD && p->rawS ){
 720           rc = 0;
 721           p->rawS = 0;
 722         }
 723       }
 724       break;
 725     }
 726 #ifndef SQLITE_OMIT_LOCALTIME
 727     case 'l': {
 728       /*    localtime
 729       **
 730       ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
 731       ** show local time.
 732       */
 733       if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){
 734         rc = toLocaltime(p, pCtx);
 735       }
 736       break;
 737     }
 738 #endif
 739     case 'u': {
 740       /*
 741       **    unixepoch
 742       **
 743       ** Treat the current value of p->s as the number of
 744       ** seconds since 1970.  Convert to a real julian day number.
 745       */
 746       if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){
 747         if( idx>1 ) return 1;  /* IMP: R-49255-55373 */
 748         r = p->s*1000.0 + 210866760000000.0;
 749         if( r>=0.0 && r<464269060800000.0 ){
 750           clearYMD_HMS_TZ(p);
 751           p->iJD = (sqlite3_int64)(r + 0.5);
 752           p->validJD = 1;
 753           p->rawS = 0;
 754           rc = 0;
 755         }
 756       }
 757 #ifndef SQLITE_OMIT_LOCALTIME
 758       else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){
 759         if( p->tzSet==0 ){
 760           i64 iOrigJD;              /* Original localtime */
 761           i64 iGuess;               /* Guess at the corresponding utc time */
 762           int cnt = 0;              /* Safety to prevent infinite loop */
 763           i64 iErr;                 /* Guess is off by this much */
 764
 765           computeJD(p);
 766           iGuess = iOrigJD = p->iJD;
 767           iErr = 0;
 768           do{
 769             DateTime new;
 770             memset(&new, 0, sizeof(new));
 771             iGuess -= iErr;
 772             new.iJD = iGuess;
 773             new.validJD = 1;
 774             rc = toLocaltime(&new, pCtx);
 775             if( rc ) return rc;
 776             computeJD(&new);
 777             iErr = new.iJD - iOrigJD;
 778           }while( iErr && cnt++<3 );
 779           memset(p, 0, sizeof(*p));
 780           p->iJD = iGuess;
 781           p->validJD = 1;
 782           p->tzSet = 1;
 783         }
 784         rc = SQLITE_OK;
 785       }
 786 #endif
 787       break;
 788     }
 789     case 'w': {
 790       /*
 791       **    weekday N
 792       **
 793       ** Move the date to the same time on the next occurrence of
 794       ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
 795       ** date is already on the appropriate weekday, this is a no-op.
 796       */
 797       if( sqlite3_strnicmp(z, "weekday ", 8)==0
 798                && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0
 799                && r>=0.0 && r<7.0 && (n=(int)r)==r ){
 800         sqlite3_int64 Z;
 801         computeYMD_HMS(p);
 802         p->validTZ = 0;
 803         p->validJD = 0;
 804         computeJD(p);
 805         Z = ((p->iJD + 129600000)/86400000) % 7;
 806         if( Z>n ) Z -= 7;
 807         p->iJD += (n - Z)*86400000;
 808         clearYMD_HMS_TZ(p);
 809         rc = 0;
 810       }
 811       break;
 812     }
 813     case 's': {
 814       /*
 815       **    start of TTTTT
 816       **
 817       ** Move the date backwards to the beginning of the current day,
 818       ** or month or year.
 819       **
 820       **    subsecond
 821       **    subsec
 822       **
 823       ** Show subsecond precision in the output of datetime() and
 824       ** unixepoch() and strftime('%s').
 825       */
 826       if( sqlite3_strnicmp(z, "start of ", 9)!=0 ){
 827         if( sqlite3_stricmp(z, "subsec")==0
 828          || sqlite3_stricmp(z, "subsecond")==0
 829         ){
 830           p->useSubsec = 1;
 831           rc = 0;
 832         }
 833         break;
 834       }
 835       if( !p->validJD && !p->validYMD && !p->validHMS ) break;
 836       z += 9;
 837       computeYMD(p);
 838       p->validHMS = 1;
 839       p->h = p->m = 0;
 840       p->s = 0.0;
 841       p->rawS = 0;
 842       p->validTZ = 0;
 843       p->validJD = 0;
 844       if( sqlite3_stricmp(z,"month")==0 ){
 845         p->D = 1;
 846         rc = 0;
 847       }else if( sqlite3_stricmp(z,"year")==0 ){
 848         p->M = 1;
 849         p->D = 1;
 850         rc = 0;
 851       }else if( sqlite3_stricmp(z,"day")==0 ){
 852         rc = 0;
 853       }
 854       break;
 855     }
 856     case '+':
 857     case '-':
 858     case '0':
 859     case '1':
 860     case '2':
 861     case '3':
 862     case '4':
 863     case '5':
 864     case '6':
 865     case '7':
 866     case '8':
 867     case '9': {
 868       double rRounder;
 869       int i;
 870       int Y,M,D,h,m,x;
 871       const char *z2 = z;
 872       char z0 = z[0];
 873       for(n=1; z[n]; n++){
 874         if( z[n]==':' ) break;
 875         if( sqlite3Isspace(z[n]) ) break;
 876         if( z[n]=='-' ){
 877           if( n==5 && getDigits(&z[1], "40f", &Y)==1 ) break;
 878           if( n==6 && getDigits(&z[1], "50f", &Y)==1 ) break;
 879         }
 880       }
 881       if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){
 882         assert( rc==1 );
 883         break;
 884       }
 885       if( z[n]=='-' ){
 886         /* A modifier of the form (+|-)YYYY-MM-DD adds or subtracts the
 887         ** specified number of years, months, and days.  MM is limited to
 888         ** the range 0-11 and DD is limited to 0-30.
 889         */
 890         if( z0!='+' && z0!='-' ) break;  /* Must start with +/- */
 891         if( n==5 ){
 892           if( getDigits(&z[1], "40f-20a-20d", &Y, &M, &D)!=3 ) break;
 893         }else{
 894           assert( n==6 );
 895           if( getDigits(&z[1], "50f-20a-20d", &Y, &M, &D)!=3 ) break;
 896           z++;
 897         }
 898         if( M>=12 ) break;                   /* M range 0..11 */
 899         if( D>=31 ) break;                   /* D range 0..30 */
 900         computeYMD_HMS(p);
 901         p->validJD = 0;
 902         if( z0=='-' ){
 903           p->Y -= Y;
 904           p->M -= M;
 905           D = -D;
 906         }else{
 907           p->Y += Y;
 908           p->M += M;
 909         }
 910         x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
 911         p->Y += x;
 912         p->M -= x*12;
 913         computeJD(p);
 914         p->validHMS = 0;
 915         p->validYMD = 0;
 916         p->iJD += (i64)D*86400000;
 917         if( z[11]==0 ){
 918           rc = 0;
 919           break;
 920         }
 921         if( sqlite3Isspace(z[11])
 922          && getDigits(&z[12], "20c:20e", &h, &m)==2
 923         ){
 924           z2 = &z[12];
 925           n = 2;
 926         }else{
 927           break;
 928         }
 929       }
 930       if( z2[n]==':' ){
 931         /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
 932         ** specified number of hours, minutes, seconds, and fractional seconds
 933         ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
 934         ** omitted.
 935         */
 936
 937         DateTime tx;
 938         sqlite3_int64 day;
 939         if( !sqlite3Isdigit(*z2) ) z2++;
 940         memset(&tx, 0, sizeof(tx));
 941         if( parseHhMmSs(z2, &tx) ) break;
 942         computeJD(&tx);
 943         tx.iJD -= 43200000;
 944         day = tx.iJD/86400000;
 945         tx.iJD -= day*86400000;
 946         if( z0=='-' ) tx.iJD = -tx.iJD;
 947         computeJD(p);
 948         clearYMD_HMS_TZ(p);
 949         p->iJD += tx.iJD;
 950         rc = 0;
 951         break;
 952       }
 953
 954       /* If control reaches this point, it means the transformation is
 955       ** one of the forms like "+NNN days".  */
 956       z += n;
 957       while( sqlite3Isspace(*z) ) z++;
 958       n = sqlite3Strlen30(z);
 959       if( n>10 || n<3 ) break;
 960       if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
 961       computeJD(p);
 962       assert( rc==1 );
 963       rRounder = r<0 ? -0.5 : +0.5;
 964       for(i=0; i<ArraySize(aXformType); i++){
 965         if( aXformType[i].nName==n
 966          && sqlite3_strnicmp(aXformType[i].zName, z, n)==0
 967          && r>-aXformType[i].rLimit && r<aXformType[i].rLimit
 968         ){
 969           switch( i ){
 970             case 4: { /* Special processing to add months */
 971               assert( strcmp(aXformType[i].zName,"month")==0 );
 972               computeYMD_HMS(p);
 973               p->M += (int)r;
 974               x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
 975               p->Y += x;
 976               p->M -= x*12;
 977               p->validJD = 0;
 978               r -= (int)r;
 979               break;
 980             }
 981             case 5: { /* Special processing to add years */
 982               int y = (int)r;
 983               assert( strcmp(aXformType[i].zName,"year")==0 );
 984               computeYMD_HMS(p);
 985               p->Y += y;
 986               p->validJD = 0;
 987               r -= (int)r;
 988               break;
 989             }
 990           }
 991           computeJD(p);
 992           p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder);
 993           rc = 0;
 994           break;
 995         }
 996       }
 997       clearYMD_HMS_TZ(p);
 998       break;
 999     }
1000     default: {
1001       break;
1002     }
1003   }
1004   return rc;
1005 }
1006
1007 /*
1008 ** Process time function arguments.  argv[0] is a date-time stamp.
1009 ** argv[1] and following are modifiers.  Parse them all and write
1010 ** the resulting time into the DateTime structure p.  Return 0
1011 ** on success and 1 if there are any errors.
1012 **
1013 ** If there are zero parameters (if even argv[0] is undefined)
1014 ** then assume a default value of "now" for argv[0].
1015 */
1016 static int isDate(
1017   sqlite3_context *context,
1018   int argc,
1019   sqlite3_value **argv,
1020   DateTime *p
1021 ){
1022   int i, n;
1023   const unsigned char *z;
1024   int eType;
1025   memset(p, 0, sizeof(*p));
1026   if( argc==0 ){
1027     if( !sqlite3NotPureFunc(context) ) return 1;
1028     return setDateTimeToCurrent(context, p);
1029   }
1030   if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
1031                    || eType==SQLITE_INTEGER ){
1032     setRawDateNumber(p, sqlite3_value_double(argv[0]));
1033   }else{
1034     z = sqlite3_value_text(argv[0]);
1035     if( !z || parseDateOrTime(context, (char*)z, p) ){
1036       return 1;
1037     }
1038   }
1039   for(i=1; i<argc; i++){
1040     z = sqlite3_value_text(argv[i]);
1041     n = sqlite3_value_bytes(argv[i]);
1042     if( z==0 || parseModifier(context, (char*)z, n, p, i) ) return 1;
1043   }
1044   computeJD(p);
1045   if( p->isError || !validJulianDay(p->iJD) ) return 1;
1046   if( argc==1 && p->validYMD && p->D>28 ){
1047     /* Make sure a YYYY-MM-DD is normalized.
1048     ** Example: 2023-02-31 -> 2023-03-03 */
1049     assert( p->validJD );
1050     p->validYMD = 0;
1051   }
1052   return 0;
1053 }
1054
1055
1056 /*
1057 ** The following routines implement the various date and time functions
1058 ** of SQLite.
1059 */
1060
1061 /*
1062 **    julianday( TIMESTRING, MOD, MOD, ...)
1063 **
1064 ** Return the julian day number of the date specified in the arguments
1065 */
1066 static void juliandayFunc(
1067   sqlite3_context *context,
1068   int argc,
1069   sqlite3_value **argv
1070 ){
1071   DateTime x;
1072   if( isDate(context, argc, argv, &x)==0 ){
1073     computeJD(&x);
1074     sqlite3_result_double(context, x.iJD/86400000.0);
1075   }
1076 }
1077
1078 /*
1079 **    unixepoch( TIMESTRING, MOD, MOD, ...)
1080 **
1081 ** Return the number of seconds (including fractional seconds) since
1082 ** the unix epoch of 1970-01-01 00:00:00 GMT.
1083 */
1084 static void unixepochFunc(
1085   sqlite3_context *context,
1086   int argc,
1087   sqlite3_value **argv
1088 ){
1089   DateTime x;
1090   if( isDate(context, argc, argv, &x)==0 ){
1091     computeJD(&x);
1092     if( x.useSubsec ){
1093       sqlite3_result_double(context, (x.iJD - 21086676*(i64)10000000)/1000.0);
1094     }else{
1095       sqlite3_result_int64(context, x.iJD/1000 - 21086676*(i64)10000);
1096     }
1097   }
1098 }
1099
1100 /*
1101 **    datetime( TIMESTRING, MOD, MOD, ...)
1102 **
1103 ** Return YYYY-MM-DD HH:MM:SS
1104 */
1105 static void datetimeFunc(
1106   sqlite3_context *context,
1107   int argc,
1108   sqlite3_value **argv
1109 ){
1110   DateTime x;
1111   if( isDate(context, argc, argv, &x)==0 ){
1112     int Y, s, n;
1113     char zBuf[32];
1114     computeYMD_HMS(&x);
1115     Y = x.Y;
1116     if( Y<0 ) Y = -Y;
1117     zBuf[1] = '0' + (Y/1000)%10;
1118     zBuf[2] = '0' + (Y/100)%10;
1119     zBuf[3] = '0' + (Y/10)%10;
1120     zBuf[4] = '0' + (Y)%10;
1121     zBuf[5] = '-';
1122     zBuf[6] = '0' + (x.M/10)%10;
1123     zBuf[7] = '0' + (x.M)%10;
1124     zBuf[8] = '-';
1125     zBuf[9] = '0' + (x.D/10)%10;
1126     zBuf[10] = '0' + (x.D)%10;
1127     zBuf[11] = ' ';
1128     zBuf[12] = '0' + (x.h/10)%10;
1129     zBuf[13] = '0' + (x.h)%10;
1130     zBuf[14] = ':';
1131     zBuf[15] = '0' + (x.m/10)%10;
1132     zBuf[16] = '0' + (x.m)%10;
1133     zBuf[17] = ':';
1134     if( x.useSubsec ){
1135       s = (int)(1000.0*x.s + 0.5);
1136       zBuf[18] = '0' + (s/10000)%10;
1137       zBuf[19] = '0' + (s/1000)%10;
1138       zBuf[20] = '.';
1139       zBuf[21] = '0' + (s/100)%10;
1140       zBuf[22] = '0' + (s/10)%10;
1141       zBuf[23] = '0' + (s)%10;
1142       zBuf[24] = 0;
1143       n = 24;
1144     }else{
1145       s = (int)x.s;
1146       zBuf[18] = '0' + (s/10)%10;
1147       zBuf[19] = '0' + (s)%10;
1148       zBuf[20] = 0;
1149       n = 20;
1150     }
1151     if( x.Y<0 ){
1152       zBuf[0] = '-';
1153       sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT);
1154     }else{
1155       sqlite3_result_text(context, &zBuf[1], n-1, SQLITE_TRANSIENT);
1156     }
1157   }
1158 }
1159
1160 /*
1161 **    time( TIMESTRING, MOD, MOD, ...)
1162 **
1163 ** Return HH:MM:SS
1164 */
1165 static void timeFunc(
1166   sqlite3_context *context,
1167   int argc,
1168   sqlite3_value **argv
1169 ){
1170   DateTime x;
1171   if( isDate(context, argc, argv, &x)==0 ){
1172     int s, n;
1173     char zBuf[16];
1174     computeHMS(&x);
1175     zBuf[0] = '0' + (x.h/10)%10;
1176     zBuf[1] = '0' + (x.h)%10;
1177     zBuf[2] = ':';
1178     zBuf[3] = '0' + (x.m/10)%10;
1179     zBuf[4] = '0' + (x.m)%10;
1180     zBuf[5] = ':';
1181     if( x.useSubsec ){
1182       s = (int)(1000.0*x.s + 0.5);
1183       zBuf[6] = '0' + (s/10000)%10;
1184       zBuf[7] = '0' + (s/1000)%10;
1185       zBuf[8] = '.';
1186       zBuf[9] = '0' + (s/100)%10;
1187       zBuf[10] = '0' + (s/10)%10;
1188       zBuf[11] = '0' + (s)%10;
1189       zBuf[12] = 0;
1190       n = 12;
1191     }else{
1192       s = (int)x.s;
1193       zBuf[6] = '0' + (s/10)%10;
1194       zBuf[7] = '0' + (s)%10;
1195       zBuf[8] = 0;
1196       n = 8;
1197     }
1198     sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT);
1199   }
1200 }
1201
1202 /*
1203 **    date( TIMESTRING, MOD, MOD, ...)
1204 **
1205 ** Return YYYY-MM-DD
1206 */
1207 static void dateFunc(
1208   sqlite3_context *context,
1209   int argc,
1210   sqlite3_value **argv
1211 ){
1212   DateTime x;
1213   if( isDate(context, argc, argv, &x)==0 ){
1214     int Y;
1215     char zBuf[16];
1216     computeYMD(&x);
1217     Y = x.Y;
1218     if( Y<0 ) Y = -Y;
1219     zBuf[1] = '0' + (Y/1000)%10;
1220     zBuf[2] = '0' + (Y/100)%10;
1221     zBuf[3] = '0' + (Y/10)%10;
1222     zBuf[4] = '0' + (Y)%10;
1223     zBuf[5] = '-';
1224     zBuf[6] = '0' + (x.M/10)%10;
1225     zBuf[7] = '0' + (x.M)%10;
1226     zBuf[8] = '-';
1227     zBuf[9] = '0' + (x.D/10)%10;
1228     zBuf[10] = '0' + (x.D)%10;
1229     zBuf[11] = 0;
1230     if( x.Y<0 ){
1231       zBuf[0] = '-';
1232       sqlite3_result_text(context, zBuf, 11, SQLITE_TRANSIENT);
1233     }else{
1234       sqlite3_result_text(context, &zBuf[1], 10, SQLITE_TRANSIENT);
1235     }
1236   }
1237 }
1238
1239 /*
1240 ** Compute the number of days after the most recent January 1.
1241 **
1242 ** In other words, compute the zero-based day number for the
1243 ** current year:
1244 **
1245 **   Jan01 = 0,  Jan02 = 1, ..., Jan31 = 30, Feb01 = 31, ...
1246 **   Dec31 = 364 or 365.
1247 */
1248 static int daysAfterJan01(DateTime *pDate){
1249   DateTime jan01 = *pDate;
1250   assert( jan01.validYMD );
1251   assert( jan01.validHMS );
1252   assert( pDate->validJD );
1253   jan01.validJD = 0;
1254   jan01.M = 1;
1255   jan01.D = 1;
1256   computeJD(&jan01);
1257   return (int)((pDate->iJD-jan01.iJD+43200000)/86400000);
1258 }
1259
1260 /*
1261 ** Return the number of days after the most recent Monday.
1262 **
1263 ** In other words, return the day of the week according
1264 ** to this code:
1265 **
1266 **   0=Monday, 1=Tuesday, 2=Wednesday, ..., 6=Sunday.
1267 */
1268 static int daysAfterMonday(DateTime *pDate){
1269   assert( pDate->validJD );
1270   return (int)((pDate->iJD+43200000)/86400000) % 7;
1271 }
1272
1273 /*
1274 ** Return the number of days after the most recent Sunday.
1275 **
1276 ** In other words, return the day of the week according
1277 ** to this code:
1278 **
1279 **   0=Sunday, 1=Monday, 2=Tues, ..., 6=Saturday
1280 */
1281 static int daysAfterSunday(DateTime *pDate){
1282   assert( pDate->validJD );
1283   return (int)((pDate->iJD+129600000)/86400000) % 7;
1284 }
1285
1286 /*
1287 **    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
1288 **
1289 ** Return a string described by FORMAT.  Conversions as follows:
1290 **
1291 **   %d  day of month  01-31
1292 **   %e  day of month  1-31
1293 **   %f  ** fractional seconds  SS.SSS
1294 **   %F  ISO date.  YYYY-MM-DD
1295 **   %G  ISO year corresponding to %V 0000-9999.
1296 **   %g  2-digit ISO year corresponding to %V 00-99
1297 **   %H  hour 00-24
1298 **   %k  hour  0-24  (leading zero converted to space)
1299 **   %I  hour 01-12
1300 **   %j  day of year 001-366
1301 **   %J  ** julian day number
1302 **   %l  hour  1-12  (leading zero converted to space)
1303 **   %m  month 01-12
1304 **   %M  minute 00-59
1305 **   %p  "am" or "pm"
1306 **   %P  "AM" or "PM"
1307 **   %R  time as HH:MM
1308 **   %s  seconds since 1970-01-01
1309 **   %S  seconds 00-59
1310 **   %T  time as HH:MM:SS
1311 **   %u  day of week 1-7  Monday==1, Sunday==7
1312 **   %w  day of week 0-6  Sunday==0, Monday==1
1313 **   %U  week of year 00-53  (First Sunday is start of week 01)
1314 **   %V  week of year 01-53  (First week containing Thursday is week 01)
1315 **   %W  week of year 00-53  (First Monday is start of week 01)
1316 **   %Y  year 0000-9999
1317 **   %%  %
1318 */
1319 static void strftimeFunc(
1320   sqlite3_context *context,
1321   int argc,
1322   sqlite3_value **argv
1323 ){
1324   DateTime x;
1325   size_t i,j;
1326   sqlite3 *db;
1327   const char *zFmt;
1328   sqlite3_str sRes;
1329
1330
1331   if( argc==0 ) return;
1332   zFmt = (const char*)sqlite3_value_text(argv[0]);
1333   if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
1334   db = sqlite3_context_db_handle(context);
1335   sqlite3StrAccumInit(&sRes, 0, 0, 0, db->aLimit[SQLITE_LIMIT_LENGTH]);
1336
1337   computeJD(&x);
1338   computeYMD_HMS(&x);
1339   for(i=j=0; zFmt[i]; i++){
1340     char cf;
1341     if( zFmt[i]!='%' ) continue;
1342     if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j));
1343     i++;
1344     j = i + 1;
1345     cf = zFmt[i];
1346     switch( cf ){
1347       case 'd':  /* Fall thru */
1348       case 'e': {
1349         sqlite3_str_appendf(&sRes, cf=='d' ? "%02d" : "%2d", x.D);
1350         break;
1351       }
1352       case 'f': {  /* Fractional seconds.  (Non-standard) */
1353         double s = x.s;
1354         if( s>59.999 ) s = 59.999;
1355         sqlite3_str_appendf(&sRes, "%06.3f", s);
1356         break;
1357       }
1358       case 'F': {
1359         sqlite3_str_appendf(&sRes, "%04d-%02d-%02d", x.Y, x.M, x.D);
1360         break;
1361       }
1362       case 'G': /* Fall thru */
1363       case 'g': {
1364         DateTime y = x;
1365         assert( y.validJD );
1366         /* Move y so that it is the Thursday in the same week as x */
1367         y.iJD += (3 - daysAfterMonday(&x))*86400000;
1368         y.validYMD = 0;
1369         computeYMD(&y);
1370         if( cf=='g' ){
1371           sqlite3_str_appendf(&sRes, "%02d", y.Y%100);
1372         }else{
1373           sqlite3_str_appendf(&sRes, "%04d", y.Y);
1374         }
1375         break;
1376       }
1377       case 'H':
1378       case 'k': {
1379         sqlite3_str_appendf(&sRes, cf=='H' ? "%02d" : "%2d", x.h);
1380         break;
1381       }
1382       case 'I': /* Fall thru */
1383       case 'l': {
1384         int h = x.h;
1385         if( h>12 ) h -= 12;
1386         if( h==0 ) h = 12;
1387         sqlite3_str_appendf(&sRes, cf=='I' ? "%02d" : "%2d", h);
1388         break;
1389       }
1390       case 'j': {  /* Day of year.  Jan01==1, Jan02==2, and so forth */
1391         sqlite3_str_appendf(&sRes,"%03d",daysAfterJan01(&x)+1);
1392         break;
1393       }
1394       case 'J': {  /* Julian day number.  (Non-standard) */
1395         sqlite3_str_appendf(&sRes,"%.16g",x.iJD/86400000.0);
1396         break;
1397       }
1398       case 'm': {
1399         sqlite3_str_appendf(&sRes,"%02d",x.M);
1400         break;
1401       }
1402       case 'M': {
1403         sqlite3_str_appendf(&sRes,"%02d",x.m);
1404         break;
1405       }
1406       case 'p': /* Fall thru */
1407       case 'P': {
1408         if( x.h>=12 ){
1409           sqlite3_str_append(&sRes, cf=='p' ? "PM" : "pm", 2);
1410         }else{
1411           sqlite3_str_append(&sRes, cf=='p' ? "AM" : "am", 2);
1412         }
1413         break;
1414       }
1415       case 'R': {
1416         sqlite3_str_appendf(&sRes, "%02d:%02d", x.h, x.m);
1417         break;
1418       }
1419       case 's': {
1420         if( x.useSubsec ){
1421           sqlite3_str_appendf(&sRes,"%.3f",
1422                 (x.iJD - 21086676*(i64)10000000)/1000.0);
1423         }else{
1424           i64 iS = (i64)(x.iJD/1000 - 21086676*(i64)10000);
1425           sqlite3_str_appendf(&sRes,"%lld",iS);
1426         }
1427         break;
1428       }
1429       case 'S': {
1430         sqlite3_str_appendf(&sRes,"%02d",(int)x.s);
1431         break;
1432       }
1433       case 'T': {
1434         sqlite3_str_appendf(&sRes,"%02d:%02d:%02d", x.h, x.m, (int)x.s);
1435         break;
1436       }
1437       case 'u':    /* Day of week.  1 to 7.  Monday==1, Sunday==7 */
1438       case 'w': {  /* Day of week.  0 to 6.  Sunday==0, Monday==1 */
1439         char c = (char)daysAfterSunday(&x) + '0';
1440         if( c=='0' && cf=='u' ) c = '7';
1441         sqlite3_str_appendchar(&sRes, 1, c);
1442         break;
1443       }
1444       case 'U': {  /* Week num. 00-53. First Sun of the year is week 01 */
1445         sqlite3_str_appendf(&sRes,"%02d",
1446               (daysAfterJan01(&x)-daysAfterSunday(&x)+7)/7);
1447         break;
1448       }
1449       case 'V': {  /* Week num. 01-53. First week with a Thur is week 01 */
1450         DateTime y = x;
1451         /* Adjust y so that is the Thursday in the same week as x */
1452         assert( y.validJD );
1453         y.iJD += (3 - daysAfterMonday(&x))*86400000;
1454         y.validYMD = 0;
1455         computeYMD(&y);
1456         sqlite3_str_appendf(&sRes,"%02d", daysAfterJan01(&y)/7+1);
1457         break;
1458       }
1459       case 'W': {  /* Week num. 00-53. First Mon of the year is week 01 */
1460         sqlite3_str_appendf(&sRes,"%02d",
1461            (daysAfterJan01(&x)-daysAfterMonday(&x)+7)/7);
1462         break;
1463       }
1464       case 'Y': {
1465         sqlite3_str_appendf(&sRes,"%04d",x.Y);
1466         break;
1467       }
1468       case '%': {
1469         sqlite3_str_appendchar(&sRes, 1, '%');
1470         break;
1471       }
1472       default: {
1473         sqlite3_str_reset(&sRes);
1474         return;
1475       }
1476     }
1477   }
1478   if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j));
1479   sqlite3ResultStrAccum(context, &sRes);
1480 }
1481
1482 /*
1483 ** current_time()
1484 **
1485 ** This function returns the same value as time('now').
1486 */
1487 static void ctimeFunc(
1488   sqlite3_context *context,
1489   int NotUsed,
1490   sqlite3_value **NotUsed2
1491 ){
1492   UNUSED_PARAMETER2(NotUsed, NotUsed2);
1493   timeFunc(context, 0, 0);
1494 }
1495
1496 /*
1497 ** current_date()
1498 **
1499 ** This function returns the same value as date('now').
1500 */
1501 static void cdateFunc(
1502   sqlite3_context *context,
1503   int NotUsed,
1504   sqlite3_value **NotUsed2
1505 ){
1506   UNUSED_PARAMETER2(NotUsed, NotUsed2);
1507   dateFunc(context, 0, 0);
1508 }
1509
1510 /*
1511 ** timediff(DATE1, DATE2)
1512 **
1513 ** Return the amount of time that must be added to DATE2 in order to
1514 ** convert it into DATE2.  The time difference format is:
1515 **
1516 **     +YYYY-MM-DD HH:MM:SS.SSS
1517 **
1518 ** The initial "+" becomes "-" if DATE1 occurs before DATE2.  For
1519 ** date/time values A and B, the following invariant should hold:
1520 **
1521 **     datetime(A) == (datetime(B, timediff(A,B))
1522 **
1523 ** Both DATE arguments must be either a julian day number, or an
1524 ** ISO-8601 string.  The unix timestamps are not supported by this
1525 ** routine.
1526 */
1527 static void timediffFunc(
1528   sqlite3_context *context,
1529   int NotUsed1,
1530   sqlite3_value **argv
1531 ){
1532   char sign;
1533   int Y, M;
1534   DateTime d1, d2;
1535   sqlite3_str sRes;
1536   UNUSED_PARAMETER(NotUsed1);
1537   if( isDate(context, 1, &argv[0], &d1) ) return;
1538   if( isDate(context, 1, &argv[1], &d2) ) return;
1539   computeYMD_HMS(&d1);
1540   computeYMD_HMS(&d2);
1541   if( d1.iJD>=d2.iJD ){
1542     sign = '+';
1543     Y = d1.Y - d2.Y;
1544     if( Y ){
1545       d2.Y = d1.Y;
1546       d2.validJD = 0;
1547       computeJD(&d2);
1548     }
1549     M = d1.M - d2.M;
1550     if( M<0 ){
1551       Y--;
1552       M += 12;
1553     }
1554     if( M!=0 ){
1555       d2.M = d1.M;
1556       d2.validJD = 0;
1557       computeJD(&d2);
1558     }
1559     while( d1.iJD<d2.iJD ){
1560       M--;
1561       if( M<0 ){
1562         M = 11;
1563         Y--;
1564       }
1565       d2.M--;
1566       if( d2.M<1 ){
1567         d2.M = 12;
1568         d2.Y--;
1569       }
1570       d2.validJD = 0;
1571       computeJD(&d2);
1572     }
1573     d1.iJD -= d2.iJD;
1574     d1.iJD += (u64)1486995408 * (u64)100000;
1575   }else /* d1<d2 */{
1576     sign = '-';
1577     Y = d2.Y - d1.Y;
1578     if( Y ){
1579       d2.Y = d1.Y;
1580       d2.validJD = 0;
1581       computeJD(&d2);
1582     }
1583     M = d2.M - d1.M;
1584     if( M<0 ){
1585       Y--;
1586       M += 12;
1587     }
1588     if( M!=0 ){
1589       d2.M = d1.M;
1590       d2.validJD = 0;
1591       computeJD(&d2);
1592     }
1593     while( d1.iJD>d2.iJD ){
1594       M--;
1595       if( M<0 ){
1596         M = 11;
1597         Y--;
1598       }
1599       d2.M++;
1600       if( d2.M>12 ){
1601         d2.M = 1;
1602         d2.Y++;
1603       }
1604       d2.validJD = 0;
1605       computeJD(&d2);
1606     }
1607     d1.iJD = d2.iJD - d1.iJD;
1608     d1.iJD += (u64)1486995408 * (u64)100000;
1609   }
1610   d1.validYMD = 0;
1611   d1.validHMS = 0;
1612   d1.validTZ = 0;
1613   computeYMD_HMS(&d1);
1614   sqlite3StrAccumInit(&sRes, 0, 0, 0, 100);
1615   sqlite3_str_appendf(&sRes, "%c%04d-%02d-%02d %02d:%02d:%06.3f",
1616        sign, Y, M, d1.D-1, d1.h, d1.m, d1.s);
1617   sqlite3ResultStrAccum(context, &sRes);
1618 }
1619
1620
1621 /*
1622 ** current_timestamp()
1623 **
1624 ** This function returns the same value as datetime('now').
1625 */
1626 static void ctimestampFunc(
1627   sqlite3_context *context,
1628   int NotUsed,
1629   sqlite3_value **NotUsed2
1630 ){
1631   UNUSED_PARAMETER2(NotUsed, NotUsed2);
1632   datetimeFunc(context, 0, 0);
1633 }
1634 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
1635
1636 #ifdef SQLITE_OMIT_DATETIME_FUNCS
1637 /*
1638 ** If the library is compiled to omit the full-scale date and time
1639 ** handling (to get a smaller binary), the following minimal version
1640 ** of the functions current_time(), current_date() and current_timestamp()
1641 ** are included instead. This is to support column declarations that
1642 ** include "DEFAULT CURRENT_TIME" etc.
1643 **
1644 ** This function uses the C-library functions time(), gmtime()
1645 ** and strftime(). The format string to pass to strftime() is supplied
1646 ** as the user-data for the function.
1647 */
1648 static void currentTimeFunc(
1649   sqlite3_context *context,
1650   int argc,
1651   sqlite3_value **argv
1652 ){
1653   time_t t;
1654   char *zFormat = (char *)sqlite3_user_data(context);
1655   sqlite3_int64 iT;
1656   struct tm *pTm;
1657   struct tm sNow;
1658   char zBuf[20];
1659
1660   UNUSED_PARAMETER(argc);
1661   UNUSED_PARAMETER(argv);
1662
1663   iT = sqlite3StmtCurrentTime(context);
1664   if( iT<=0 ) return;
1665   t = iT/1000 - 10000*(sqlite3_int64)21086676;
1666 #if HAVE_GMTIME_R
1667   pTm = gmtime_r(&t, &sNow);
1668 #else
1669   sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN));
1670   pTm = gmtime(&t);
1671   if( pTm ) memcpy(&sNow, pTm, sizeof(sNow));
1672   sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN));
1673 #endif
1674   if( pTm ){
1675     strftime(zBuf, 20, zFormat, &sNow);
1676     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
1677   }
1678 }
1679 #endif
1680
1681 /*
1682 ** This function registered all of the above C functions as SQL
1683 ** functions.  This should be the only routine in this file with
1684 ** external linkage.
1685 */
1686 void sqlite3RegisterDateTimeFunctions(void){
1687   static FuncDef aDateTimeFuncs[] = {
1688 #ifndef SQLITE_OMIT_DATETIME_FUNCS
1689     PURE_DATE(julianday,        -1, 0, 0, juliandayFunc ),
1690     PURE_DATE(unixepoch,        -1, 0, 0, unixepochFunc ),
1691     PURE_DATE(date,             -1, 0, 0, dateFunc      ),
1692     PURE_DATE(time,             -1, 0, 0, timeFunc      ),
1693     PURE_DATE(datetime,         -1, 0, 0, datetimeFunc  ),
1694     PURE_DATE(strftime,         -1, 0, 0, strftimeFunc  ),
1695     PURE_DATE(timediff,          2, 0, 0, timediffFunc  ),
1696     DFUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
1697     DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
1698     DFUNCTION(current_date,      0, 0, 0, cdateFunc     ),
1699 #else
1700     STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
1701     STR_FUNCTION(current_date,      0, "%Y-%m-%d",          0, currentTimeFunc),
1702     STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
1703 #endif
1704   };
1705   sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
1706 }