000001  /*
000002  ** 2003 October 31
000003  **
000004  ** The author disclaims copyright to this source code.  In place of
000005  ** a legal notice, here is a blessing:
000006  **
000007  **    May you do good and not evil.
000008  **    May you find forgiveness for yourself and forgive others.
000009  **    May you share freely, never taking more than you give.
000010  **
000011  *************************************************************************
000012  ** This file contains the C functions that implement date and time
000013  ** functions for SQLite.  
000014  **
000015  ** There is only one exported symbol in this file - the function
000016  ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
000017  ** All other code has file scope.
000018  **
000019  ** SQLite processes all times and dates as julian day numbers.  The
000020  ** dates and times are stored as the number of days since noon
000021  ** in Greenwich on November 24, 4714 B.C. according to the Gregorian
000022  ** calendar system. 
000023  **
000024  ** 1970-01-01 00:00:00 is JD 2440587.5
000025  ** 2000-01-01 00:00:00 is JD 2451544.5
000026  **
000027  ** This implementation requires years to be expressed as a 4-digit number
000028  ** which means that only dates between 0000-01-01 and 9999-12-31 can
000029  ** be represented, even though julian day numbers allow a much wider
000030  ** range of dates.
000031  **
000032  ** The Gregorian calendar system is used for all dates and times,
000033  ** even those that predate the Gregorian calendar.  Historians usually
000034  ** use the julian calendar for dates prior to 1582-10-15 and for some
000035  ** dates afterwards, depending on locale.  Beware of this difference.
000036  **
000037  ** The conversion algorithms are implemented based on descriptions
000038  ** in the following text:
000039  **
000040  **      Jean Meeus
000041  **      Astronomical Algorithms, 2nd Edition, 1998
000042  **      ISBN 0-943396-61-1
000043  **      Willmann-Bell, Inc
000044  **      Richmond, Virginia (USA)
000045  */
000046  #include "sqliteInt.h"
000047  #include <stdlib.h>
000048  #include <assert.h>
000049  #include <time.h>
000050  
000051  #ifndef SQLITE_OMIT_DATETIME_FUNCS
000052  
000053  /*
000054  ** The MSVC CRT on Windows CE may not have a localtime() function.
000055  ** So declare a substitute.  The substitute function itself is
000056  ** defined in "os_win.c".
000057  */
000058  #if !defined(SQLITE_OMIT_LOCALTIME) && defined(_WIN32_WCE) && \
000059      (!defined(SQLITE_MSVC_LOCALTIME_API) || !SQLITE_MSVC_LOCALTIME_API)
000060  struct tm *__cdecl localtime(const time_t *);
000061  #endif
000062  
000063  /*
000064  ** A structure for holding a single date and time.
000065  */
000066  typedef struct DateTime DateTime;
000067  struct DateTime {
000068    sqlite3_int64 iJD;  /* The julian day number times 86400000 */
000069    int Y, M, D;        /* Year, month, and day */
000070    int h, m;           /* Hour and minutes */
000071    int tz;             /* Timezone offset in minutes */
000072    double s;           /* Seconds */
000073    char validJD;       /* True (1) if iJD is valid */
000074    char rawS;          /* Raw numeric value stored in s */
000075    char validYMD;      /* True (1) if Y,M,D are valid */
000076    char validHMS;      /* True (1) if h,m,s are valid */
000077    char validTZ;       /* True (1) if tz is valid */
000078    char tzSet;         /* Timezone was set explicitly */
000079    char isError;       /* An overflow has occurred */
000080    char useSubsec;     /* Display subsecond precision */
000081  };
000082  
000083  
000084  /*
000085  ** Convert zDate into one or more integers according to the conversion
000086  ** specifier zFormat.
000087  **
000088  ** zFormat[] contains 4 characters for each integer converted, except for
000089  ** the last integer which is specified by three characters.  The meaning
000090  ** of a four-character format specifiers ABCD is:
000091  **
000092  **    A:   number of digits to convert.  Always "2" or "4".
000093  **    B:   minimum value.  Always "0" or "1".
000094  **    C:   maximum value, decoded as:
000095  **           a:  12
000096  **           b:  14
000097  **           c:  24
000098  **           d:  31
000099  **           e:  59
000100  **           f:  9999
000101  **    D:   the separator character, or \000 to indicate this is the
000102  **         last number to convert.
000103  **
000104  ** Example:  To translate an ISO-8601 date YYYY-MM-DD, the format would
000105  ** be "40f-21a-20c".  The "40f-" indicates the 4-digit year followed by "-".
000106  ** The "21a-" indicates the 2-digit month followed by "-".  The "20c" indicates
000107  ** the 2-digit day which is the last integer in the set.
000108  **
000109  ** The function returns the number of successful conversions.
000110  */
000111  static int getDigits(const char *zDate, const char *zFormat, ...){
000112    /* The aMx[] array translates the 3rd character of each format
000113    ** spec into a max size:    a   b   c   d   e      f */
000114    static const u16 aMx[] = { 12, 14, 24, 31, 59, 14712 };
000115    va_list ap;
000116    int cnt = 0;
000117    char nextC;
000118    va_start(ap, zFormat);
000119    do{
000120      char N = zFormat[0] - '0';
000121      char min = zFormat[1] - '0';
000122      int val = 0;
000123      u16 max;
000124  
000125      assert( zFormat[2]>='a' && zFormat[2]<='f' );
000126      max = aMx[zFormat[2] - 'a'];
000127      nextC = zFormat[3];
000128      val = 0;
000129      while( N-- ){
000130        if( !sqlite3Isdigit(*zDate) ){
000131          goto end_getDigits;
000132        }
000133        val = val*10 + *zDate - '0';
000134        zDate++;
000135      }
000136      if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){
000137        goto end_getDigits;
000138      }
000139      *va_arg(ap,int*) = val;
000140      zDate++;
000141      cnt++;
000142      zFormat += 4;
000143    }while( nextC );
000144  end_getDigits:
000145    va_end(ap);
000146    return cnt;
000147  }
000148  
000149  /*
000150  ** Parse a timezone extension on the end of a date-time.
000151  ** The extension is of the form:
000152  **
000153  **        (+/-)HH:MM
000154  **
000155  ** Or the "zulu" notation:
000156  **
000157  **        Z
000158  **
000159  ** If the parse is successful, write the number of minutes
000160  ** of change in p->tz and return 0.  If a parser error occurs,
000161  ** return non-zero.
000162  **
000163  ** A missing specifier is not considered an error.
000164  */
000165  static int parseTimezone(const char *zDate, DateTime *p){
000166    int sgn = 0;
000167    int nHr, nMn;
000168    int c;
000169    while( sqlite3Isspace(*zDate) ){ zDate++; }
000170    p->tz = 0;
000171    c = *zDate;
000172    if( c=='-' ){
000173      sgn = -1;
000174    }else if( c=='+' ){
000175      sgn = +1;
000176    }else if( c=='Z' || c=='z' ){
000177      zDate++;
000178      goto zulu_time;
000179    }else{
000180      return c!=0;
000181    }
000182    zDate++;
000183    if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){
000184      return 1;
000185    }
000186    zDate += 5;
000187    p->tz = sgn*(nMn + nHr*60);
000188  zulu_time:
000189    while( sqlite3Isspace(*zDate) ){ zDate++; }
000190    p->tzSet = 1;
000191    return *zDate!=0;
000192  }
000193  
000194  /*
000195  ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
000196  ** The HH, MM, and SS must each be exactly 2 digits.  The
000197  ** fractional seconds FFFF can be one or more digits.
000198  **
000199  ** Return 1 if there is a parsing error and 0 on success.
000200  */
000201  static int parseHhMmSs(const char *zDate, DateTime *p){
000202    int h, m, s;
000203    double ms = 0.0;
000204    if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){
000205      return 1;
000206    }
000207    zDate += 5;
000208    if( *zDate==':' ){
000209      zDate++;
000210      if( getDigits(zDate, "20e", &s)!=1 ){
000211        return 1;
000212      }
000213      zDate += 2;
000214      if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){
000215        double rScale = 1.0;
000216        zDate++;
000217        while( sqlite3Isdigit(*zDate) ){
000218          ms = ms*10.0 + *zDate - '0';
000219          rScale *= 10.0;
000220          zDate++;
000221        }
000222        ms /= rScale;
000223      }
000224    }else{
000225      s = 0;
000226    }
000227    p->validJD = 0;
000228    p->rawS = 0;
000229    p->validHMS = 1;
000230    p->h = h;
000231    p->m = m;
000232    p->s = s + ms;
000233    if( parseTimezone(zDate, p) ) return 1;
000234    p->validTZ = (p->tz!=0)?1:0;
000235    return 0;
000236  }
000237  
000238  /*
000239  ** Put the DateTime object into its error state.
000240  */
000241  static void datetimeError(DateTime *p){
000242    memset(p, 0, sizeof(*p));
000243    p->isError = 1;
000244  }
000245  
000246  /*
000247  ** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume
000248  ** that the YYYY-MM-DD is according to the Gregorian calendar.
000249  **
000250  ** Reference:  Meeus page 61
000251  */
000252  static void computeJD(DateTime *p){
000253    int Y, M, D, A, B, X1, X2;
000254  
000255    if( p->validJD ) return;
000256    if( p->validYMD ){
000257      Y = p->Y;
000258      M = p->M;
000259      D = p->D;
000260    }else{
000261      Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */
000262      M = 1;
000263      D = 1;
000264    }
000265    if( Y<-4713 || Y>9999 || p->rawS ){
000266      datetimeError(p);
000267      return;
000268    }
000269    if( M<=2 ){
000270      Y--;
000271      M += 12;
000272    }
000273    A = Y/100;
000274    B = 2 - A + (A/4);
000275    X1 = 36525*(Y+4716)/100;
000276    X2 = 306001*(M+1)/10000;
000277    p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
000278    p->validJD = 1;
000279    if( p->validHMS ){
000280      p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000 + 0.5);
000281      if( p->validTZ ){
000282        p->iJD -= p->tz*60000;
000283        p->validYMD = 0;
000284        p->validHMS = 0;
000285        p->validTZ = 0;
000286      }
000287    }
000288  }
000289  
000290  /*
000291  ** Parse dates of the form
000292  **
000293  **     YYYY-MM-DD HH:MM:SS.FFF
000294  **     YYYY-MM-DD HH:MM:SS
000295  **     YYYY-MM-DD HH:MM
000296  **     YYYY-MM-DD
000297  **
000298  ** Write the result into the DateTime structure and return 0
000299  ** on success and 1 if the input string is not a well-formed
000300  ** date.
000301  */
000302  static int parseYyyyMmDd(const char *zDate, DateTime *p){
000303    int Y, M, D, neg;
000304  
000305    if( zDate[0]=='-' ){
000306      zDate++;
000307      neg = 1;
000308    }else{
000309      neg = 0;
000310    }
000311    if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){
000312      return 1;
000313    }
000314    zDate += 10;
000315    while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }
000316    if( parseHhMmSs(zDate, p)==0 ){
000317      /* We got the time */
000318    }else if( *zDate==0 ){
000319      p->validHMS = 0;
000320    }else{
000321      return 1;
000322    }
000323    p->validJD = 0;
000324    p->validYMD = 1;
000325    p->Y = neg ? -Y : Y;
000326    p->M = M;
000327    p->D = D;
000328    if( p->validTZ ){
000329      computeJD(p);
000330    }
000331    return 0;
000332  }
000333  
000334  /*
000335  ** Set the time to the current time reported by the VFS.
000336  **
000337  ** Return the number of errors.
000338  */
000339  static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
000340    p->iJD = sqlite3StmtCurrentTime(context);
000341    if( p->iJD>0 ){
000342      p->validJD = 1;
000343      return 0;
000344    }else{
000345      return 1;
000346    }
000347  }
000348  
000349  /*
000350  ** Input "r" is a numeric quantity which might be a julian day number,
000351  ** or the number of seconds since 1970.  If the value if r is within
000352  ** range of a julian day number, install it as such and set validJD.
000353  ** If the value is a valid unix timestamp, put it in p->s and set p->rawS.
000354  */
000355  static void setRawDateNumber(DateTime *p, double r){
000356    p->s = r;
000357    p->rawS = 1;
000358    if( r>=0.0 && r<5373484.5 ){
000359      p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
000360      p->validJD = 1;
000361    }
000362  }
000363  
000364  /*
000365  ** Attempt to parse the given string into a julian day number.  Return
000366  ** the number of errors.
000367  **
000368  ** The following are acceptable forms for the input string:
000369  **
000370  **      YYYY-MM-DD HH:MM:SS.FFF  +/-HH:MM
000371  **      DDDD.DD 
000372  **      now
000373  **
000374  ** In the first form, the +/-HH:MM is always optional.  The fractional
000375  ** seconds extension (the ".FFF") is optional.  The seconds portion
000376  ** (":SS.FFF") is option.  The year and date can be omitted as long
000377  ** as there is a time string.  The time string can be omitted as long
000378  ** as there is a year and date.
000379  */
000380  static int parseDateOrTime(
000381    sqlite3_context *context, 
000382    const char *zDate, 
000383    DateTime *p
000384  ){
000385    double r;
000386    if( parseYyyyMmDd(zDate,p)==0 ){
000387      return 0;
000388    }else if( parseHhMmSs(zDate, p)==0 ){
000389      return 0;
000390    }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
000391      return setDateTimeToCurrent(context, p);
000392    }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){
000393      setRawDateNumber(p, r);
000394      return 0;
000395    }else if( (sqlite3StrICmp(zDate,"subsec")==0
000396               || sqlite3StrICmp(zDate,"subsecond")==0)
000397             && sqlite3NotPureFunc(context) ){
000398      p->useSubsec = 1;
000399      return setDateTimeToCurrent(context, p);
000400    }
000401    return 1;
000402  }
000403  
000404  /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999.
000405  ** Multiplying this by 86400000 gives 464269060799999 as the maximum value
000406  ** for DateTime.iJD.
000407  **
000408  ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with 
000409  ** such a large integer literal, so we have to encode it.
000410  */
000411  #define INT_464269060799999  ((((i64)0x1a640)<<32)|0x1072fdff)
000412  
000413  /*
000414  ** Return TRUE if the given julian day number is within range.
000415  **
000416  ** The input is the JulianDay times 86400000.
000417  */
000418  static int validJulianDay(sqlite3_int64 iJD){
000419    return iJD>=0 && iJD<=INT_464269060799999;
000420  }
000421  
000422  /*
000423  ** Compute the Year, Month, and Day from the julian day number.
000424  */
000425  static void computeYMD(DateTime *p){
000426    int Z, A, B, C, D, E, X1;
000427    if( p->validYMD ) return;
000428    if( !p->validJD ){
000429      p->Y = 2000;
000430      p->M = 1;
000431      p->D = 1;
000432    }else if( !validJulianDay(p->iJD) ){
000433      datetimeError(p);
000434      return;
000435    }else{
000436      Z = (int)((p->iJD + 43200000)/86400000);
000437      A = (int)((Z - 1867216.25)/36524.25);
000438      A = Z + 1 + A - (A/4);
000439      B = A + 1524;
000440      C = (int)((B - 122.1)/365.25);
000441      D = (36525*(C&32767))/100;
000442      E = (int)((B-D)/30.6001);
000443      X1 = (int)(30.6001*E);
000444      p->D = B - D - X1;
000445      p->M = E<14 ? E-1 : E-13;
000446      p->Y = p->M>2 ? C - 4716 : C - 4715;
000447    }
000448    p->validYMD = 1;
000449  }
000450  
000451  /*
000452  ** Compute the Hour, Minute, and Seconds from the julian day number.
000453  */
000454  static void computeHMS(DateTime *p){
000455    int day_ms, day_min; /* milliseconds, minutes into the day */
000456    if( p->validHMS ) return;
000457    computeJD(p);
000458    day_ms = (int)((p->iJD + 43200000) % 86400000);
000459    p->s = (day_ms % 60000)/1000.0;
000460    day_min = day_ms/60000;
000461    p->m = day_min % 60;
000462    p->h = day_min / 60;
000463    p->rawS = 0;
000464    p->validHMS = 1;
000465  }
000466  
000467  /*
000468  ** Compute both YMD and HMS
000469  */
000470  static void computeYMD_HMS(DateTime *p){
000471    computeYMD(p);
000472    computeHMS(p);
000473  }
000474  
000475  /*
000476  ** Clear the YMD and HMS and the TZ
000477  */
000478  static void clearYMD_HMS_TZ(DateTime *p){
000479    p->validYMD = 0;
000480    p->validHMS = 0;
000481    p->validTZ = 0;
000482  }
000483  
000484  #ifndef SQLITE_OMIT_LOCALTIME
000485  /*
000486  ** On recent Windows platforms, the localtime_s() function is available
000487  ** as part of the "Secure CRT". It is essentially equivalent to 
000488  ** localtime_r() available under most POSIX platforms, except that the 
000489  ** order of the parameters is reversed.
000490  **
000491  ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
000492  **
000493  ** If the user has not indicated to use localtime_r() or localtime_s()
000494  ** already, check for an MSVC build environment that provides 
000495  ** localtime_s().
000496  */
000497  #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \
000498      && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
000499  #undef  HAVE_LOCALTIME_S
000500  #define HAVE_LOCALTIME_S 1
000501  #endif
000502  
000503  /*
000504  ** The following routine implements the rough equivalent of localtime_r()
000505  ** using whatever operating-system specific localtime facility that
000506  ** is available.  This routine returns 0 on success and
000507  ** non-zero on any kind of error.
000508  **
000509  ** If the sqlite3GlobalConfig.bLocaltimeFault variable is non-zero then this
000510  ** routine will always fail.  If bLocaltimeFault is nonzero and
000511  ** sqlite3GlobalConfig.xAltLocaltime is not NULL, then xAltLocaltime() is
000512  ** invoked in place of the OS-defined localtime() function.
000513  **
000514  ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
000515  ** library function localtime_r() is used to assist in the calculation of
000516  ** local time.
000517  */
000518  static int osLocaltime(time_t *t, struct tm *pTm){
000519    int rc;
000520  #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S
000521    struct tm *pX;
000522  #if SQLITE_THREADSAFE>0
000523    sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN);
000524  #endif
000525    sqlite3_mutex_enter(mutex);
000526    pX = localtime(t);
000527  #ifndef SQLITE_UNTESTABLE
000528    if( sqlite3GlobalConfig.bLocaltimeFault ){
000529      if( sqlite3GlobalConfig.xAltLocaltime!=0
000530       && 0==sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm)
000531      ){
000532        pX = pTm;
000533      }else{
000534        pX = 0;
000535      }
000536    }
000537  #endif
000538    if( pX ) *pTm = *pX;
000539  #if SQLITE_THREADSAFE>0
000540    sqlite3_mutex_leave(mutex);
000541  #endif
000542    rc = pX==0;
000543  #else
000544  #ifndef SQLITE_UNTESTABLE
000545    if( sqlite3GlobalConfig.bLocaltimeFault ){
000546      if( sqlite3GlobalConfig.xAltLocaltime!=0 ){
000547        return sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm);
000548      }else{
000549        return 1;
000550      }
000551    }
000552  #endif
000553  #if HAVE_LOCALTIME_R
000554    rc = localtime_r(t, pTm)==0;
000555  #else
000556    rc = localtime_s(pTm, t);
000557  #endif /* HAVE_LOCALTIME_R */
000558  #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
000559    return rc;
000560  }
000561  #endif /* SQLITE_OMIT_LOCALTIME */
000562  
000563  
000564  #ifndef SQLITE_OMIT_LOCALTIME
000565  /*
000566  ** Assuming the input DateTime is UTC, move it to its localtime equivalent.
000567  */
000568  static int toLocaltime(
000569    DateTime *p,                   /* Date at which to calculate offset */
000570    sqlite3_context *pCtx          /* Write error here if one occurs */
000571  ){
000572    time_t t;
000573    struct tm sLocal;
000574    int iYearDiff;
000575  
000576    /* Initialize the contents of sLocal to avoid a compiler warning. */
000577    memset(&sLocal, 0, sizeof(sLocal));
000578  
000579    computeJD(p);
000580    if( p->iJD<2108667600*(i64)100000 /* 1970-01-01 */
000581     || p->iJD>2130141456*(i64)100000 /* 2038-01-18 */
000582    ){
000583      /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
000584      ** works for years between 1970 and 2037. For dates outside this range,
000585      ** SQLite attempts to map the year into an equivalent year within this
000586      ** range, do the calculation, then map the year back.
000587      */
000588      DateTime x = *p;
000589      computeYMD_HMS(&x);
000590      iYearDiff = (2000 + x.Y%4) - x.Y;
000591      x.Y += iYearDiff;
000592      x.validJD = 0;
000593      computeJD(&x);
000594      t = (time_t)(x.iJD/1000 -  21086676*(i64)10000);
000595    }else{
000596      iYearDiff = 0;
000597      t = (time_t)(p->iJD/1000 -  21086676*(i64)10000);
000598    }
000599    if( osLocaltime(&t, &sLocal) ){
000600      sqlite3_result_error(pCtx, "local time unavailable", -1);
000601      return SQLITE_ERROR;
000602    }
000603    p->Y = sLocal.tm_year + 1900 - iYearDiff;
000604    p->M = sLocal.tm_mon + 1;
000605    p->D = sLocal.tm_mday;
000606    p->h = sLocal.tm_hour;
000607    p->m = sLocal.tm_min;
000608    p->s = sLocal.tm_sec + (p->iJD%1000)*0.001;
000609    p->validYMD = 1;
000610    p->validHMS = 1;
000611    p->validJD = 0;
000612    p->rawS = 0;
000613    p->validTZ = 0;
000614    p->isError = 0;
000615    return SQLITE_OK;
000616  }
000617  #endif /* SQLITE_OMIT_LOCALTIME */
000618  
000619  /*
000620  ** The following table defines various date transformations of the form
000621  **
000622  **            'NNN days'
000623  **
000624  ** Where NNN is an arbitrary floating-point number and "days" can be one
000625  ** of several units of time.
000626  */
000627  static const struct {
000628    u8 nName;           /* Length of the name */
000629    char zName[7];      /* Name of the transformation */
000630    float rLimit;       /* Maximum NNN value for this transform */
000631    float rXform;       /* Constant used for this transform */
000632  } aXformType[] = {
000633    { 6, "second", 4.6427e+14,       1.0  },
000634    { 6, "minute", 7.7379e+12,      60.0  },
000635    { 4, "hour",   1.2897e+11,    3600.0  },
000636    { 3, "day",    5373485.0,    86400.0  },
000637    { 5, "month",  176546.0,   2592000.0  },
000638    { 4, "year",   14713.0,   31536000.0  },
000639  };
000640  
000641  /*
000642  ** If the DateTime p is raw number, try to figure out if it is
000643  ** a julian day number of a unix timestamp.  Set the p value
000644  ** appropriately.
000645  */
000646  static void autoAdjustDate(DateTime *p){
000647    if( !p->rawS || p->validJD ){
000648      p->rawS = 0;
000649    }else if( p->s>=-21086676*(i64)10000        /* -4713-11-24 12:00:00 */
000650           && p->s<=(25340230*(i64)10000)+799   /*  9999-12-31 23:59:59 */
000651    ){
000652      double r = p->s*1000.0 + 210866760000000.0;
000653      clearYMD_HMS_TZ(p);
000654      p->iJD = (sqlite3_int64)(r + 0.5);
000655      p->validJD = 1;
000656      p->rawS = 0;
000657    }
000658  }
000659  
000660  /*
000661  ** Process a modifier to a date-time stamp.  The modifiers are
000662  ** as follows:
000663  **
000664  **     NNN days
000665  **     NNN hours
000666  **     NNN minutes
000667  **     NNN.NNNN seconds
000668  **     NNN months
000669  **     NNN years
000670  **     start of month
000671  **     start of year
000672  **     start of week
000673  **     start of day
000674  **     weekday N
000675  **     unixepoch
000676  **     localtime
000677  **     utc
000678  **
000679  ** Return 0 on success and 1 if there is any kind of error. If the error
000680  ** is in a system call (i.e. localtime()), then an error message is written
000681  ** to context pCtx. If the error is an unrecognized modifier, no error is
000682  ** written to pCtx.
000683  */
000684  static int parseModifier(
000685    sqlite3_context *pCtx,      /* Function context */
000686    const char *z,              /* The text of the modifier */
000687    int n,                      /* Length of zMod in bytes */
000688    DateTime *p,                /* The date/time value to be modified */
000689    int idx                     /* Parameter index of the modifier */
000690  ){
000691    int rc = 1;
000692    double r;
000693    switch(sqlite3UpperToLower[(u8)z[0]] ){
000694      case 'a': {
000695        /*
000696        **    auto
000697        **
000698        ** If rawS is available, then interpret as a julian day number, or
000699        ** a unix timestamp, depending on its magnitude.
000700        */
000701        if( sqlite3_stricmp(z, "auto")==0 ){
000702          if( idx>1 ) return 1; /* IMP: R-33611-57934 */
000703          autoAdjustDate(p);
000704          rc = 0;
000705        }
000706        break;
000707      }
000708      case 'j': {
000709        /*
000710        **    julianday
000711        **
000712        ** Always interpret the prior number as a julian-day value.  If this
000713        ** is not the first modifier, or if the prior argument is not a numeric
000714        ** value in the allowed range of julian day numbers understood by
000715        ** SQLite (0..5373484.5) then the result will be NULL.
000716        */
000717        if( sqlite3_stricmp(z, "julianday")==0 ){
000718          if( idx>1 ) return 1;  /* IMP: R-31176-64601 */
000719          if( p->validJD && p->rawS ){
000720            rc = 0;
000721            p->rawS = 0;
000722          }
000723        }
000724        break;
000725      }
000726  #ifndef SQLITE_OMIT_LOCALTIME
000727      case 'l': {
000728        /*    localtime
000729        **
000730        ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
000731        ** show local time.
000732        */
000733        if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){
000734          rc = toLocaltime(p, pCtx);
000735        }
000736        break;
000737      }
000738  #endif
000739      case 'u': {
000740        /*
000741        **    unixepoch
000742        **
000743        ** Treat the current value of p->s as the number of
000744        ** seconds since 1970.  Convert to a real julian day number.
000745        */
000746        if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){
000747          if( idx>1 ) return 1;  /* IMP: R-49255-55373 */
000748          r = p->s*1000.0 + 210866760000000.0;
000749          if( r>=0.0 && r<464269060800000.0 ){
000750            clearYMD_HMS_TZ(p);
000751            p->iJD = (sqlite3_int64)(r + 0.5);
000752            p->validJD = 1;
000753            p->rawS = 0;
000754            rc = 0;
000755          }
000756        }
000757  #ifndef SQLITE_OMIT_LOCALTIME
000758        else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){
000759          if( p->tzSet==0 ){
000760            i64 iOrigJD;              /* Original localtime */
000761            i64 iGuess;               /* Guess at the corresponding utc time */
000762            int cnt = 0;              /* Safety to prevent infinite loop */
000763            i64 iErr;                 /* Guess is off by this much */
000764  
000765            computeJD(p);
000766            iGuess = iOrigJD = p->iJD;
000767            iErr = 0;
000768            do{
000769              DateTime new;
000770              memset(&new, 0, sizeof(new));
000771              iGuess -= iErr;
000772              new.iJD = iGuess;
000773              new.validJD = 1;
000774              rc = toLocaltime(&new, pCtx);
000775              if( rc ) return rc;
000776              computeJD(&new);
000777              iErr = new.iJD - iOrigJD;
000778            }while( iErr && cnt++<3 );
000779            memset(p, 0, sizeof(*p));
000780            p->iJD = iGuess;
000781            p->validJD = 1;
000782            p->tzSet = 1;
000783          }
000784          rc = SQLITE_OK;
000785        }
000786  #endif
000787        break;
000788      }
000789      case 'w': {
000790        /*
000791        **    weekday N
000792        **
000793        ** Move the date to the same time on the next occurrence of
000794        ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
000795        ** date is already on the appropriate weekday, this is a no-op.
000796        */
000797        if( sqlite3_strnicmp(z, "weekday ", 8)==0
000798                 && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0
000799                 && r>=0.0 && r<7.0 && (n=(int)r)==r ){
000800          sqlite3_int64 Z;
000801          computeYMD_HMS(p);
000802          p->validTZ = 0;
000803          p->validJD = 0;
000804          computeJD(p);
000805          Z = ((p->iJD + 129600000)/86400000) % 7;
000806          if( Z>n ) Z -= 7;
000807          p->iJD += (n - Z)*86400000;
000808          clearYMD_HMS_TZ(p);
000809          rc = 0;
000810        }
000811        break;
000812      }
000813      case 's': {
000814        /*
000815        **    start of TTTTT
000816        **
000817        ** Move the date backwards to the beginning of the current day,
000818        ** or month or year.
000819        **
000820        **    subsecond
000821        **    subsec
000822        **
000823        ** Show subsecond precision in the output of datetime() and
000824        ** unixepoch() and strftime('%s').
000825        */
000826        if( sqlite3_strnicmp(z, "start of ", 9)!=0 ){
000827          if( sqlite3_stricmp(z, "subsec")==0
000828           || sqlite3_stricmp(z, "subsecond")==0
000829          ){
000830            p->useSubsec = 1;
000831            rc = 0;
000832          }
000833          break;
000834        }        
000835        if( !p->validJD && !p->validYMD && !p->validHMS ) break;
000836        z += 9;
000837        computeYMD(p);
000838        p->validHMS = 1;
000839        p->h = p->m = 0;
000840        p->s = 0.0;
000841        p->rawS = 0;
000842        p->validTZ = 0;
000843        p->validJD = 0;
000844        if( sqlite3_stricmp(z,"month")==0 ){
000845          p->D = 1;
000846          rc = 0;
000847        }else if( sqlite3_stricmp(z,"year")==0 ){
000848          p->M = 1;
000849          p->D = 1;
000850          rc = 0;
000851        }else if( sqlite3_stricmp(z,"day")==0 ){
000852          rc = 0;
000853        }
000854        break;
000855      }
000856      case '+':
000857      case '-':
000858      case '0':
000859      case '1':
000860      case '2':
000861      case '3':
000862      case '4':
000863      case '5':
000864      case '6':
000865      case '7':
000866      case '8':
000867      case '9': {
000868        double rRounder;
000869        int i;
000870        int Y,M,D,h,m,x;
000871        const char *z2 = z;
000872        char z0 = z[0];
000873        for(n=1; z[n]; n++){
000874          if( z[n]==':' ) break;
000875          if( sqlite3Isspace(z[n]) ) break;
000876          if( z[n]=='-' ){
000877            if( n==5 && getDigits(&z[1], "40f", &Y)==1 ) break;
000878            if( n==6 && getDigits(&z[1], "50f", &Y)==1 ) break;
000879          }
000880        }
000881        if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){
000882          assert( rc==1 );
000883          break;
000884        }
000885        if( z[n]=='-' ){
000886          /* A modifier of the form (+|-)YYYY-MM-DD adds or subtracts the
000887          ** specified number of years, months, and days.  MM is limited to
000888          ** the range 0-11 and DD is limited to 0-30.
000889          */
000890          if( z0!='+' && z0!='-' ) break;  /* Must start with +/- */
000891          if( n==5 ){
000892            if( getDigits(&z[1], "40f-20a-20d", &Y, &M, &D)!=3 ) break;
000893          }else{
000894            assert( n==6 );
000895            if( getDigits(&z[1], "50f-20a-20d", &Y, &M, &D)!=3 ) break;
000896            z++;
000897          }
000898          if( M>=12 ) break;                   /* M range 0..11 */
000899          if( D>=31 ) break;                   /* D range 0..30 */
000900          computeYMD_HMS(p);
000901          p->validJD = 0;
000902          if( z0=='-' ){
000903            p->Y -= Y;
000904            p->M -= M;
000905            D = -D;
000906          }else{
000907            p->Y += Y;
000908            p->M += M;
000909          }
000910          x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
000911          p->Y += x;
000912          p->M -= x*12;
000913          computeJD(p);
000914          p->validHMS = 0;
000915          p->validYMD = 0;
000916          p->iJD += (i64)D*86400000;
000917          if( z[11]==0 ){
000918            rc = 0;
000919            break;
000920          }
000921          if( sqlite3Isspace(z[11])
000922           && getDigits(&z[12], "20c:20e", &h, &m)==2
000923          ){
000924            z2 = &z[12];
000925            n = 2;
000926          }else{
000927            break;
000928          }
000929        }
000930        if( z2[n]==':' ){
000931          /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
000932          ** specified number of hours, minutes, seconds, and fractional seconds
000933          ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
000934          ** omitted.
000935          */
000936  
000937          DateTime tx;
000938          sqlite3_int64 day;
000939          if( !sqlite3Isdigit(*z2) ) z2++;
000940          memset(&tx, 0, sizeof(tx));
000941          if( parseHhMmSs(z2, &tx) ) break;
000942          computeJD(&tx);
000943          tx.iJD -= 43200000;
000944          day = tx.iJD/86400000;
000945          tx.iJD -= day*86400000;
000946          if( z0=='-' ) tx.iJD = -tx.iJD;
000947          computeJD(p);
000948          clearYMD_HMS_TZ(p);
000949          p->iJD += tx.iJD;
000950          rc = 0;
000951          break;
000952        }
000953  
000954        /* If control reaches this point, it means the transformation is
000955        ** one of the forms like "+NNN days".  */
000956        z += n;
000957        while( sqlite3Isspace(*z) ) z++;
000958        n = sqlite3Strlen30(z);
000959        if( n>10 || n<3 ) break;
000960        if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
000961        computeJD(p);
000962        assert( rc==1 );
000963        rRounder = r<0 ? -0.5 : +0.5;
000964        for(i=0; i<ArraySize(aXformType); i++){
000965          if( aXformType[i].nName==n
000966           && sqlite3_strnicmp(aXformType[i].zName, z, n)==0
000967           && r>-aXformType[i].rLimit && r<aXformType[i].rLimit
000968          ){
000969            switch( i ){
000970              case 4: { /* Special processing to add months */
000971                assert( strcmp(aXformType[i].zName,"month")==0 );
000972                computeYMD_HMS(p);
000973                p->M += (int)r;
000974                x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
000975                p->Y += x;
000976                p->M -= x*12;
000977                p->validJD = 0;
000978                r -= (int)r;
000979                break;
000980              }
000981              case 5: { /* Special processing to add years */
000982                int y = (int)r;
000983                assert( strcmp(aXformType[i].zName,"year")==0 );
000984                computeYMD_HMS(p);
000985                p->Y += y;
000986                p->validJD = 0;
000987                r -= (int)r;
000988                break;
000989              }
000990            }
000991            computeJD(p);
000992            p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder);
000993            rc = 0;
000994            break;
000995          }
000996        }
000997        clearYMD_HMS_TZ(p);
000998        break;
000999      }
001000      default: {
001001        break;
001002      }
001003    }
001004    return rc;
001005  }
001006  
001007  /*
001008  ** Process time function arguments.  argv[0] is a date-time stamp.
001009  ** argv[1] and following are modifiers.  Parse them all and write
001010  ** the resulting time into the DateTime structure p.  Return 0
001011  ** on success and 1 if there are any errors.
001012  **
001013  ** If there are zero parameters (if even argv[0] is undefined)
001014  ** then assume a default value of "now" for argv[0].
001015  */
001016  static int isDate(
001017    sqlite3_context *context, 
001018    int argc, 
001019    sqlite3_value **argv, 
001020    DateTime *p
001021  ){
001022    int i, n;
001023    const unsigned char *z;
001024    int eType;
001025    memset(p, 0, sizeof(*p));
001026    if( argc==0 ){
001027      if( !sqlite3NotPureFunc(context) ) return 1;
001028      return setDateTimeToCurrent(context, p);
001029    }
001030    if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
001031                     || eType==SQLITE_INTEGER ){
001032      setRawDateNumber(p, sqlite3_value_double(argv[0]));
001033    }else{
001034      z = sqlite3_value_text(argv[0]);
001035      if( !z || parseDateOrTime(context, (char*)z, p) ){
001036        return 1;
001037      }
001038    }
001039    for(i=1; i<argc; i++){
001040      z = sqlite3_value_text(argv[i]);
001041      n = sqlite3_value_bytes(argv[i]);
001042      if( z==0 || parseModifier(context, (char*)z, n, p, i) ) return 1;
001043    }
001044    computeJD(p);
001045    if( p->isError || !validJulianDay(p->iJD) ) return 1;
001046    if( argc==1 && p->validYMD && p->D>28 ){
001047      /* Make sure a YYYY-MM-DD is normalized.
001048      ** Example: 2023-02-31 -> 2023-03-03 */
001049      assert( p->validJD );
001050      p->validYMD = 0;  
001051    }
001052    return 0;
001053  }
001054  
001055  
001056  /*
001057  ** The following routines implement the various date and time functions
001058  ** of SQLite.
001059  */
001060  
001061  /*
001062  **    julianday( TIMESTRING, MOD, MOD, ...)
001063  **
001064  ** Return the julian day number of the date specified in the arguments
001065  */
001066  static void juliandayFunc(
001067    sqlite3_context *context,
001068    int argc,
001069    sqlite3_value **argv
001070  ){
001071    DateTime x;
001072    if( isDate(context, argc, argv, &x)==0 ){
001073      computeJD(&x);
001074      sqlite3_result_double(context, x.iJD/86400000.0);
001075    }
001076  }
001077  
001078  /*
001079  **    unixepoch( TIMESTRING, MOD, MOD, ...)
001080  **
001081  ** Return the number of seconds (including fractional seconds) since
001082  ** the unix epoch of 1970-01-01 00:00:00 GMT.
001083  */
001084  static void unixepochFunc(
001085    sqlite3_context *context,
001086    int argc,
001087    sqlite3_value **argv
001088  ){
001089    DateTime x;
001090    if( isDate(context, argc, argv, &x)==0 ){
001091      computeJD(&x);
001092      if( x.useSubsec ){
001093        sqlite3_result_double(context, (x.iJD - 21086676*(i64)10000000)/1000.0);
001094      }else{
001095        sqlite3_result_int64(context, x.iJD/1000 - 21086676*(i64)10000);
001096      }
001097    }
001098  }
001099  
001100  /*
001101  **    datetime( TIMESTRING, MOD, MOD, ...)
001102  **
001103  ** Return YYYY-MM-DD HH:MM:SS
001104  */
001105  static void datetimeFunc(
001106    sqlite3_context *context,
001107    int argc,
001108    sqlite3_value **argv
001109  ){
001110    DateTime x;
001111    if( isDate(context, argc, argv, &x)==0 ){
001112      int Y, s, n;
001113      char zBuf[32];
001114      computeYMD_HMS(&x);
001115      Y = x.Y;
001116      if( Y<0 ) Y = -Y;
001117      zBuf[1] = '0' + (Y/1000)%10;
001118      zBuf[2] = '0' + (Y/100)%10;
001119      zBuf[3] = '0' + (Y/10)%10;
001120      zBuf[4] = '0' + (Y)%10;
001121      zBuf[5] = '-';
001122      zBuf[6] = '0' + (x.M/10)%10;
001123      zBuf[7] = '0' + (x.M)%10;
001124      zBuf[8] = '-';
001125      zBuf[9] = '0' + (x.D/10)%10;
001126      zBuf[10] = '0' + (x.D)%10;
001127      zBuf[11] = ' ';
001128      zBuf[12] = '0' + (x.h/10)%10;
001129      zBuf[13] = '0' + (x.h)%10;
001130      zBuf[14] = ':';
001131      zBuf[15] = '0' + (x.m/10)%10;
001132      zBuf[16] = '0' + (x.m)%10;
001133      zBuf[17] = ':';
001134      if( x.useSubsec ){
001135        s = (int)(1000.0*x.s + 0.5);
001136        zBuf[18] = '0' + (s/10000)%10;
001137        zBuf[19] = '0' + (s/1000)%10;
001138        zBuf[20] = '.';
001139        zBuf[21] = '0' + (s/100)%10;
001140        zBuf[22] = '0' + (s/10)%10;
001141        zBuf[23] = '0' + (s)%10;
001142        zBuf[24] = 0;
001143        n = 24;
001144      }else{
001145        s = (int)x.s;
001146        zBuf[18] = '0' + (s/10)%10;
001147        zBuf[19] = '0' + (s)%10;
001148        zBuf[20] = 0;
001149        n = 20;
001150      }
001151      if( x.Y<0 ){
001152        zBuf[0] = '-';
001153        sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT);
001154      }else{
001155        sqlite3_result_text(context, &zBuf[1], n-1, SQLITE_TRANSIENT);
001156      }
001157    }
001158  }
001159  
001160  /*
001161  **    time( TIMESTRING, MOD, MOD, ...)
001162  **
001163  ** Return HH:MM:SS
001164  */
001165  static void timeFunc(
001166    sqlite3_context *context,
001167    int argc,
001168    sqlite3_value **argv
001169  ){
001170    DateTime x;
001171    if( isDate(context, argc, argv, &x)==0 ){
001172      int s, n;
001173      char zBuf[16];
001174      computeHMS(&x);
001175      zBuf[0] = '0' + (x.h/10)%10;
001176      zBuf[1] = '0' + (x.h)%10;
001177      zBuf[2] = ':';
001178      zBuf[3] = '0' + (x.m/10)%10;
001179      zBuf[4] = '0' + (x.m)%10;
001180      zBuf[5] = ':';
001181      if( x.useSubsec ){
001182        s = (int)(1000.0*x.s + 0.5);
001183        zBuf[6] = '0' + (s/10000)%10;
001184        zBuf[7] = '0' + (s/1000)%10;
001185        zBuf[8] = '.';
001186        zBuf[9] = '0' + (s/100)%10;
001187        zBuf[10] = '0' + (s/10)%10;
001188        zBuf[11] = '0' + (s)%10;
001189        zBuf[12] = 0;
001190        n = 12;
001191      }else{
001192        s = (int)x.s;
001193        zBuf[6] = '0' + (s/10)%10;
001194        zBuf[7] = '0' + (s)%10;
001195        zBuf[8] = 0;
001196        n = 8;
001197      }
001198      sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT);
001199    }
001200  }
001201  
001202  /*
001203  **    date( TIMESTRING, MOD, MOD, ...)
001204  **
001205  ** Return YYYY-MM-DD
001206  */
001207  static void dateFunc(
001208    sqlite3_context *context,
001209    int argc,
001210    sqlite3_value **argv
001211  ){
001212    DateTime x;
001213    if( isDate(context, argc, argv, &x)==0 ){
001214      int Y;
001215      char zBuf[16];
001216      computeYMD(&x);
001217      Y = x.Y;
001218      if( Y<0 ) Y = -Y;
001219      zBuf[1] = '0' + (Y/1000)%10;
001220      zBuf[2] = '0' + (Y/100)%10;
001221      zBuf[3] = '0' + (Y/10)%10;
001222      zBuf[4] = '0' + (Y)%10;
001223      zBuf[5] = '-';
001224      zBuf[6] = '0' + (x.M/10)%10;
001225      zBuf[7] = '0' + (x.M)%10;
001226      zBuf[8] = '-';
001227      zBuf[9] = '0' + (x.D/10)%10;
001228      zBuf[10] = '0' + (x.D)%10;
001229      zBuf[11] = 0;
001230      if( x.Y<0 ){
001231        zBuf[0] = '-';
001232        sqlite3_result_text(context, zBuf, 11, SQLITE_TRANSIENT);
001233      }else{
001234        sqlite3_result_text(context, &zBuf[1], 10, SQLITE_TRANSIENT);
001235      }
001236    }
001237  }
001238  
001239  /*
001240  **    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
001241  **
001242  ** Return a string described by FORMAT.  Conversions as follows:
001243  **
001244  **   %d  day of month
001245  **   %f  ** fractional seconds  SS.SSS
001246  **   %H  hour 00-24
001247  **   %j  day of year 000-366
001248  **   %J  ** julian day number
001249  **   %m  month 01-12
001250  **   %M  minute 00-59
001251  **   %s  seconds since 1970-01-01
001252  **   %S  seconds 00-59
001253  **   %w  day of week 0-6  Sunday==0
001254  **   %W  week of year 00-53
001255  **   %Y  year 0000-9999
001256  **   %%  %
001257  */
001258  static void strftimeFunc(
001259    sqlite3_context *context,
001260    int argc,
001261    sqlite3_value **argv
001262  ){
001263    DateTime x;
001264    size_t i,j;
001265    sqlite3 *db;
001266    const char *zFmt;
001267    sqlite3_str sRes;
001268  
001269  
001270    if( argc==0 ) return;
001271    zFmt = (const char*)sqlite3_value_text(argv[0]);
001272    if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
001273    db = sqlite3_context_db_handle(context);
001274    sqlite3StrAccumInit(&sRes, 0, 0, 0, db->aLimit[SQLITE_LIMIT_LENGTH]);
001275  
001276    computeJD(&x);
001277    computeYMD_HMS(&x);
001278    for(i=j=0; zFmt[i]; i++){
001279      char cf;
001280      if( zFmt[i]!='%' ) continue;
001281      if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j));
001282      i++;
001283      j = i + 1;
001284      cf = zFmt[i];
001285      switch( cf ){
001286        case 'd':  /* Fall thru */
001287        case 'e': {
001288          sqlite3_str_appendf(&sRes, cf=='d' ? "%02d" : "%2d", x.D);
001289          break;
001290        }
001291        case 'f': {
001292          double s = x.s;
001293          if( s>59.999 ) s = 59.999;
001294          sqlite3_str_appendf(&sRes, "%06.3f", s);
001295          break;
001296        }
001297        case 'F': {
001298          sqlite3_str_appendf(&sRes, "%04d-%02d-%02d", x.Y, x.M, x.D);
001299          break;
001300        }
001301        case 'H':
001302        case 'k': {
001303          sqlite3_str_appendf(&sRes, cf=='H' ? "%02d" : "%2d", x.h);
001304          break;
001305        }
001306        case 'I': /* Fall thru */
001307        case 'l': {
001308          int h = x.h;
001309          if( h>12 ) h -= 12;
001310          if( h==0 ) h = 12;
001311          sqlite3_str_appendf(&sRes, cf=='I' ? "%02d" : "%2d", h);
001312          break;
001313        }
001314        case 'W': /* Fall thru */
001315        case 'j': {
001316          int nDay;             /* Number of days since 1st day of year */
001317          DateTime y = x;
001318          y.validJD = 0;
001319          y.M = 1;
001320          y.D = 1;
001321          computeJD(&y);
001322          nDay = (int)((x.iJD-y.iJD+43200000)/86400000);
001323          if( cf=='W' ){
001324            int wd;   /* 0=Monday, 1=Tuesday, ... 6=Sunday */
001325            wd = (int)(((x.iJD+43200000)/86400000)%7);
001326            sqlite3_str_appendf(&sRes,"%02d",(nDay+7-wd)/7);
001327          }else{
001328            sqlite3_str_appendf(&sRes,"%03d",nDay+1);
001329          }
001330          break;
001331        }
001332        case 'J': {
001333          sqlite3_str_appendf(&sRes,"%.16g",x.iJD/86400000.0);
001334          break;
001335        }
001336        case 'm': {
001337          sqlite3_str_appendf(&sRes,"%02d",x.M);
001338          break;
001339        }
001340        case 'M': {
001341          sqlite3_str_appendf(&sRes,"%02d",x.m);
001342          break;
001343        }
001344        case 'p': /* Fall thru */
001345        case 'P': {
001346          if( x.h>=12 ){
001347            sqlite3_str_append(&sRes, cf=='p' ? "PM" : "pm", 2);
001348          }else{
001349            sqlite3_str_append(&sRes, cf=='p' ? "AM" : "am", 2);
001350          }
001351          break;
001352        }
001353        case 'R': {
001354          sqlite3_str_appendf(&sRes, "%02d:%02d", x.h, x.m);
001355          break;
001356        }
001357        case 's': {
001358          if( x.useSubsec ){
001359            sqlite3_str_appendf(&sRes,"%.3f",
001360                  (x.iJD - 21086676*(i64)10000000)/1000.0);
001361          }else{
001362            i64 iS = (i64)(x.iJD/1000 - 21086676*(i64)10000);
001363            sqlite3_str_appendf(&sRes,"%lld",iS);
001364          }
001365          break;
001366        }
001367        case 'S': {
001368          sqlite3_str_appendf(&sRes,"%02d",(int)x.s);
001369          break;
001370        }
001371        case 'T': {
001372          sqlite3_str_appendf(&sRes,"%02d:%02d:%02d", x.h, x.m, (int)x.s);
001373          break;
001374        }
001375        case 'u': /* Fall thru */
001376        case 'w': {
001377          char c = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
001378          if( c=='0' && cf=='u' ) c = '7';
001379          sqlite3_str_appendchar(&sRes, 1, c);
001380          break;
001381        }
001382        case 'Y': {
001383          sqlite3_str_appendf(&sRes,"%04d",x.Y);
001384          break;
001385        }
001386        case '%': {
001387          sqlite3_str_appendchar(&sRes, 1, '%');
001388          break;
001389        }
001390        default: {
001391          sqlite3_str_reset(&sRes);
001392          return;
001393        }
001394      }
001395    }
001396    if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j));
001397    sqlite3ResultStrAccum(context, &sRes);
001398  }
001399  
001400  /*
001401  ** current_time()
001402  **
001403  ** This function returns the same value as time('now').
001404  */
001405  static void ctimeFunc(
001406    sqlite3_context *context,
001407    int NotUsed,
001408    sqlite3_value **NotUsed2
001409  ){
001410    UNUSED_PARAMETER2(NotUsed, NotUsed2);
001411    timeFunc(context, 0, 0);
001412  }
001413  
001414  /*
001415  ** current_date()
001416  **
001417  ** This function returns the same value as date('now').
001418  */
001419  static void cdateFunc(
001420    sqlite3_context *context,
001421    int NotUsed,
001422    sqlite3_value **NotUsed2
001423  ){
001424    UNUSED_PARAMETER2(NotUsed, NotUsed2);
001425    dateFunc(context, 0, 0);
001426  }
001427  
001428  /*
001429  ** timediff(DATE1, DATE2)
001430  **
001431  ** Return the amount of time that must be added to DATE2 in order to
001432  ** convert it into DATE2.  The time difference format is:
001433  **
001434  **     +YYYY-MM-DD HH:MM:SS.SSS
001435  **
001436  ** The initial "+" becomes "-" if DATE1 occurs before DATE2.  For
001437  ** date/time values A and B, the following invariant should hold:
001438  **
001439  **     datetime(A) == (datetime(B, timediff(A,B))
001440  **
001441  ** Both DATE arguments must be either a julian day number, or an
001442  ** ISO-8601 string.  The unix timestamps are not supported by this
001443  ** routine.
001444  */
001445  static void timediffFunc(
001446    sqlite3_context *context,
001447    int NotUsed1,
001448    sqlite3_value **argv
001449  ){
001450    char sign;
001451    int Y, M;
001452    DateTime d1, d2;
001453    sqlite3_str sRes;
001454    UNUSED_PARAMETER(NotUsed1);
001455    if( isDate(context, 1, &argv[0], &d1) ) return;
001456    if( isDate(context, 1, &argv[1], &d2) ) return;
001457    computeYMD_HMS(&d1);
001458    computeYMD_HMS(&d2);
001459    if( d1.iJD>=d2.iJD ){
001460      sign = '+';
001461      Y = d1.Y - d2.Y;
001462      if( Y ){
001463        d2.Y = d1.Y;
001464        d2.validJD = 0;
001465        computeJD(&d2);
001466      }
001467      M = d1.M - d2.M;
001468      if( M<0 ){
001469        Y--;
001470        M += 12;
001471      }
001472      if( M!=0 ){
001473        d2.M = d1.M;
001474        d2.validJD = 0;
001475        computeJD(&d2);
001476      }
001477      while( d1.iJD<d2.iJD ){
001478        M--;
001479        if( M<0 ){
001480          M = 11;
001481          Y--;
001482        }
001483        d2.M--;
001484        if( d2.M<1 ){
001485          d2.M = 12;
001486          d2.Y--;
001487        }
001488        d2.validJD = 0;
001489        computeJD(&d2);
001490      }
001491      d1.iJD -= d2.iJD;
001492      d1.iJD += (u64)1486995408 * (u64)100000;
001493    }else /* d1<d2 */{
001494      sign = '-';
001495      Y = d2.Y - d1.Y;
001496      if( Y ){
001497        d2.Y = d1.Y;
001498        d2.validJD = 0;
001499        computeJD(&d2);
001500      }
001501      M = d2.M - d1.M;
001502      if( M<0 ){
001503        Y--;
001504        M += 12;
001505      }
001506      if( M!=0 ){
001507        d2.M = d1.M;
001508        d2.validJD = 0;
001509        computeJD(&d2);
001510      }
001511      while( d1.iJD>d2.iJD ){
001512        M--;
001513        if( M<0 ){
001514          M = 11;
001515          Y--;
001516        }
001517        d2.M++;
001518        if( d2.M>12 ){
001519          d2.M = 1;
001520          d2.Y++;
001521        }
001522        d2.validJD = 0;
001523        computeJD(&d2);
001524      }
001525      d1.iJD = d2.iJD - d1.iJD;
001526      d1.iJD += (u64)1486995408 * (u64)100000;
001527    }
001528    d1.validYMD = 0;
001529    d1.validHMS = 0;
001530    d1.validTZ = 0;
001531    computeYMD_HMS(&d1);
001532    sqlite3StrAccumInit(&sRes, 0, 0, 0, 100);
001533    sqlite3_str_appendf(&sRes, "%c%04d-%02d-%02d %02d:%02d:%06.3f",
001534         sign, Y, M, d1.D-1, d1.h, d1.m, d1.s);
001535    sqlite3ResultStrAccum(context, &sRes);
001536  }
001537  
001538  
001539  /*
001540  ** current_timestamp()
001541  **
001542  ** This function returns the same value as datetime('now').
001543  */
001544  static void ctimestampFunc(
001545    sqlite3_context *context,
001546    int NotUsed,
001547    sqlite3_value **NotUsed2
001548  ){
001549    UNUSED_PARAMETER2(NotUsed, NotUsed2);
001550    datetimeFunc(context, 0, 0);
001551  }
001552  #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
001553  
001554  #ifdef SQLITE_OMIT_DATETIME_FUNCS
001555  /*
001556  ** If the library is compiled to omit the full-scale date and time
001557  ** handling (to get a smaller binary), the following minimal version
001558  ** of the functions current_time(), current_date() and current_timestamp()
001559  ** are included instead. This is to support column declarations that
001560  ** include "DEFAULT CURRENT_TIME" etc.
001561  **
001562  ** This function uses the C-library functions time(), gmtime()
001563  ** and strftime(). The format string to pass to strftime() is supplied
001564  ** as the user-data for the function.
001565  */
001566  static void currentTimeFunc(
001567    sqlite3_context *context,
001568    int argc,
001569    sqlite3_value **argv
001570  ){
001571    time_t t;
001572    char *zFormat = (char *)sqlite3_user_data(context);
001573    sqlite3_int64 iT;
001574    struct tm *pTm;
001575    struct tm sNow;
001576    char zBuf[20];
001577  
001578    UNUSED_PARAMETER(argc);
001579    UNUSED_PARAMETER(argv);
001580  
001581    iT = sqlite3StmtCurrentTime(context);
001582    if( iT<=0 ) return;
001583    t = iT/1000 - 10000*(sqlite3_int64)21086676;
001584  #if HAVE_GMTIME_R
001585    pTm = gmtime_r(&t, &sNow);
001586  #else
001587    sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN));
001588    pTm = gmtime(&t);
001589    if( pTm ) memcpy(&sNow, pTm, sizeof(sNow));
001590    sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN));
001591  #endif
001592    if( pTm ){
001593      strftime(zBuf, 20, zFormat, &sNow);
001594      sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
001595    }
001596  }
001597  #endif
001598  
001599  /*
001600  ** This function registered all of the above C functions as SQL
001601  ** functions.  This should be the only routine in this file with
001602  ** external linkage.
001603  */
001604  void sqlite3RegisterDateTimeFunctions(void){
001605    static FuncDef aDateTimeFuncs[] = {
001606  #ifndef SQLITE_OMIT_DATETIME_FUNCS
001607      PURE_DATE(julianday,        -1, 0, 0, juliandayFunc ),
001608      PURE_DATE(unixepoch,        -1, 0, 0, unixepochFunc ),
001609      PURE_DATE(date,             -1, 0, 0, dateFunc      ),
001610      PURE_DATE(time,             -1, 0, 0, timeFunc      ),
001611      PURE_DATE(datetime,         -1, 0, 0, datetimeFunc  ),
001612      PURE_DATE(strftime,         -1, 0, 0, strftimeFunc  ),
001613      PURE_DATE(timediff,          2, 0, 0, timediffFunc  ),
001614      DFUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
001615      DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
001616      DFUNCTION(current_date,      0, 0, 0, cdateFunc     ),
001617  #else
001618      STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
001619      STR_FUNCTION(current_date,      0, "%Y-%m-%d",          0, currentTimeFunc),
001620      STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
001621  #endif
001622    };
001623    sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
001624  }