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  **      ISBM 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  };
000081  
000082  
000083  /*
000084  ** Convert zDate into one or more integers according to the conversion
000085  ** specifier zFormat.
000086  **
000087  ** zFormat[] contains 4 characters for each integer converted, except for
000088  ** the last integer which is specified by three characters.  The meaning
000089  ** of a four-character format specifiers ABCD is:
000090  **
000091  **    A:   number of digits to convert.  Always "2" or "4".
000092  **    B:   minimum value.  Always "0" or "1".
000093  **    C:   maximum value, decoded as:
000094  **           a:  12
000095  **           b:  14
000096  **           c:  24
000097  **           d:  31
000098  **           e:  59
000099  **           f:  9999
000100  **    D:   the separator character, or \000 to indicate this is the
000101  **         last number to convert.
000102  **
000103  ** Example:  To translate an ISO-8601 date YYYY-MM-DD, the format would
000104  ** be "40f-21a-20c".  The "40f-" indicates the 4-digit year followed by "-".
000105  ** The "21a-" indicates the 2-digit month followed by "-".  The "20c" indicates
000106  ** the 2-digit day which is the last integer in the set.
000107  **
000108  ** The function returns the number of successful conversions.
000109  */
000110  static int getDigits(const char *zDate, const char *zFormat, ...){
000111    /* The aMx[] array translates the 3rd character of each format
000112    ** spec into a max size:    a   b   c   d   e     f */
000113    static const u16 aMx[] = { 12, 14, 24, 31, 59, 9999 };
000114    va_list ap;
000115    int cnt = 0;
000116    char nextC;
000117    va_start(ap, zFormat);
000118    do{
000119      char N = zFormat[0] - '0';
000120      char min = zFormat[1] - '0';
000121      int val = 0;
000122      u16 max;
000123  
000124      assert( zFormat[2]>='a' && zFormat[2]<='f' );
000125      max = aMx[zFormat[2] - 'a'];
000126      nextC = zFormat[3];
000127      val = 0;
000128      while( N-- ){
000129        if( !sqlite3Isdigit(*zDate) ){
000130          goto end_getDigits;
000131        }
000132        val = val*10 + *zDate - '0';
000133        zDate++;
000134      }
000135      if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){
000136        goto end_getDigits;
000137      }
000138      *va_arg(ap,int*) = val;
000139      zDate++;
000140      cnt++;
000141      zFormat += 4;
000142    }while( nextC );
000143  end_getDigits:
000144    va_end(ap);
000145    return cnt;
000146  }
000147  
000148  /*
000149  ** Parse a timezone extension on the end of a date-time.
000150  ** The extension is of the form:
000151  **
000152  **        (+/-)HH:MM
000153  **
000154  ** Or the "zulu" notation:
000155  **
000156  **        Z
000157  **
000158  ** If the parse is successful, write the number of minutes
000159  ** of change in p->tz and return 0.  If a parser error occurs,
000160  ** return non-zero.
000161  **
000162  ** A missing specifier is not considered an error.
000163  */
000164  static int parseTimezone(const char *zDate, DateTime *p){
000165    int sgn = 0;
000166    int nHr, nMn;
000167    int c;
000168    while( sqlite3Isspace(*zDate) ){ zDate++; }
000169    p->tz = 0;
000170    c = *zDate;
000171    if( c=='-' ){
000172      sgn = -1;
000173    }else if( c=='+' ){
000174      sgn = +1;
000175    }else if( c=='Z' || c=='z' ){
000176      zDate++;
000177      goto zulu_time;
000178    }else{
000179      return c!=0;
000180    }
000181    zDate++;
000182    if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){
000183      return 1;
000184    }
000185    zDate += 5;
000186    p->tz = sgn*(nMn + nHr*60);
000187  zulu_time:
000188    while( sqlite3Isspace(*zDate) ){ zDate++; }
000189    p->tzSet = 1;
000190    return *zDate!=0;
000191  }
000192  
000193  /*
000194  ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
000195  ** The HH, MM, and SS must each be exactly 2 digits.  The
000196  ** fractional seconds FFFF can be one or more digits.
000197  **
000198  ** Return 1 if there is a parsing error and 0 on success.
000199  */
000200  static int parseHhMmSs(const char *zDate, DateTime *p){
000201    int h, m, s;
000202    double ms = 0.0;
000203    if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){
000204      return 1;
000205    }
000206    zDate += 5;
000207    if( *zDate==':' ){
000208      zDate++;
000209      if( getDigits(zDate, "20e", &s)!=1 ){
000210        return 1;
000211      }
000212      zDate += 2;
000213      if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){
000214        double rScale = 1.0;
000215        zDate++;
000216        while( sqlite3Isdigit(*zDate) ){
000217          ms = ms*10.0 + *zDate - '0';
000218          rScale *= 10.0;
000219          zDate++;
000220        }
000221        ms /= rScale;
000222      }
000223    }else{
000224      s = 0;
000225    }
000226    p->validJD = 0;
000227    p->rawS = 0;
000228    p->validHMS = 1;
000229    p->h = h;
000230    p->m = m;
000231    p->s = s + ms;
000232    if( parseTimezone(zDate, p) ) return 1;
000233    p->validTZ = (p->tz!=0)?1:0;
000234    return 0;
000235  }
000236  
000237  /*
000238  ** Put the DateTime object into its error state.
000239  */
000240  static void datetimeError(DateTime *p){
000241    memset(p, 0, sizeof(*p));
000242    p->isError = 1;
000243  }
000244  
000245  /*
000246  ** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume
000247  ** that the YYYY-MM-DD is according to the Gregorian calendar.
000248  **
000249  ** Reference:  Meeus page 61
000250  */
000251  static void computeJD(DateTime *p){
000252    int Y, M, D, A, B, X1, X2;
000253  
000254    if( p->validJD ) return;
000255    if( p->validYMD ){
000256      Y = p->Y;
000257      M = p->M;
000258      D = p->D;
000259    }else{
000260      Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */
000261      M = 1;
000262      D = 1;
000263    }
000264    if( Y<-4713 || Y>9999 || p->rawS ){
000265      datetimeError(p);
000266      return;
000267    }
000268    if( M<=2 ){
000269      Y--;
000270      M += 12;
000271    }
000272    A = Y/100;
000273    B = 2 - A + (A/4);
000274    X1 = 36525*(Y+4716)/100;
000275    X2 = 306001*(M+1)/10000;
000276    p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
000277    p->validJD = 1;
000278    if( p->validHMS ){
000279      p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000);
000280      if( p->validTZ ){
000281        p->iJD -= p->tz*60000;
000282        p->validYMD = 0;
000283        p->validHMS = 0;
000284        p->validTZ = 0;
000285      }
000286    }
000287  }
000288  
000289  /*
000290  ** Parse dates of the form
000291  **
000292  **     YYYY-MM-DD HH:MM:SS.FFF
000293  **     YYYY-MM-DD HH:MM:SS
000294  **     YYYY-MM-DD HH:MM
000295  **     YYYY-MM-DD
000296  **
000297  ** Write the result into the DateTime structure and return 0
000298  ** on success and 1 if the input string is not a well-formed
000299  ** date.
000300  */
000301  static int parseYyyyMmDd(const char *zDate, DateTime *p){
000302    int Y, M, D, neg;
000303  
000304    if( zDate[0]=='-' ){
000305      zDate++;
000306      neg = 1;
000307    }else{
000308      neg = 0;
000309    }
000310    if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){
000311      return 1;
000312    }
000313    zDate += 10;
000314    while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }
000315    if( parseHhMmSs(zDate, p)==0 ){
000316      /* We got the time */
000317    }else if( *zDate==0 ){
000318      p->validHMS = 0;
000319    }else{
000320      return 1;
000321    }
000322    p->validJD = 0;
000323    p->validYMD = 1;
000324    p->Y = neg ? -Y : Y;
000325    p->M = M;
000326    p->D = D;
000327    if( p->validTZ ){
000328      computeJD(p);
000329    }
000330    return 0;
000331  }
000332  
000333  /*
000334  ** Set the time to the current time reported by the VFS.
000335  **
000336  ** Return the number of errors.
000337  */
000338  static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
000339    p->iJD = sqlite3StmtCurrentTime(context);
000340    if( p->iJD>0 ){
000341      p->validJD = 1;
000342      return 0;
000343    }else{
000344      return 1;
000345    }
000346  }
000347  
000348  /*
000349  ** Input "r" is a numeric quantity which might be a julian day number,
000350  ** or the number of seconds since 1970.  If the value if r is within
000351  ** range of a julian day number, install it as such and set validJD.
000352  ** If the value is a valid unix timestamp, put it in p->s and set p->rawS.
000353  */
000354  static void setRawDateNumber(DateTime *p, double r){
000355    p->s = r;
000356    p->rawS = 1;
000357    if( r>=0.0 && r<5373484.5 ){
000358      p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
000359      p->validJD = 1;
000360    }
000361  }
000362  
000363  /*
000364  ** Attempt to parse the given string into a julian day number.  Return
000365  ** the number of errors.
000366  **
000367  ** The following are acceptable forms for the input string:
000368  **
000369  **      YYYY-MM-DD HH:MM:SS.FFF  +/-HH:MM
000370  **      DDDD.DD 
000371  **      now
000372  **
000373  ** In the first form, the +/-HH:MM is always optional.  The fractional
000374  ** seconds extension (the ".FFF") is optional.  The seconds portion
000375  ** (":SS.FFF") is option.  The year and date can be omitted as long
000376  ** as there is a time string.  The time string can be omitted as long
000377  ** as there is a year and date.
000378  */
000379  static int parseDateOrTime(
000380    sqlite3_context *context, 
000381    const char *zDate, 
000382    DateTime *p
000383  ){
000384    double r;
000385    if( parseYyyyMmDd(zDate,p)==0 ){
000386      return 0;
000387    }else if( parseHhMmSs(zDate, p)==0 ){
000388      return 0;
000389    }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
000390      return setDateTimeToCurrent(context, p);
000391    }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){
000392      setRawDateNumber(p, r);
000393      return 0;
000394    }
000395    return 1;
000396  }
000397  
000398  /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999.
000399  ** Multiplying this by 86400000 gives 464269060799999 as the maximum value
000400  ** for DateTime.iJD.
000401  **
000402  ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with 
000403  ** such a large integer literal, so we have to encode it.
000404  */
000405  #define INT_464269060799999  ((((i64)0x1a640)<<32)|0x1072fdff)
000406  
000407  /*
000408  ** Return TRUE if the given julian day number is within range.
000409  **
000410  ** The input is the JulianDay times 86400000.
000411  */
000412  static int validJulianDay(sqlite3_int64 iJD){
000413    return iJD>=0 && iJD<=INT_464269060799999;
000414  }
000415  
000416  /*
000417  ** Compute the Year, Month, and Day from the julian day number.
000418  */
000419  static void computeYMD(DateTime *p){
000420    int Z, A, B, C, D, E, X1;
000421    if( p->validYMD ) return;
000422    if( !p->validJD ){
000423      p->Y = 2000;
000424      p->M = 1;
000425      p->D = 1;
000426    }else if( !validJulianDay(p->iJD) ){
000427      datetimeError(p);
000428      return;
000429    }else{
000430      Z = (int)((p->iJD + 43200000)/86400000);
000431      A = (int)((Z - 1867216.25)/36524.25);
000432      A = Z + 1 + A - (A/4);
000433      B = A + 1524;
000434      C = (int)((B - 122.1)/365.25);
000435      D = (36525*(C&32767))/100;
000436      E = (int)((B-D)/30.6001);
000437      X1 = (int)(30.6001*E);
000438      p->D = B - D - X1;
000439      p->M = E<14 ? E-1 : E-13;
000440      p->Y = p->M>2 ? C - 4716 : C - 4715;
000441    }
000442    p->validYMD = 1;
000443  }
000444  
000445  /*
000446  ** Compute the Hour, Minute, and Seconds from the julian day number.
000447  */
000448  static void computeHMS(DateTime *p){
000449    int s;
000450    if( p->validHMS ) return;
000451    computeJD(p);
000452    s = (int)((p->iJD + 43200000) % 86400000);
000453    p->s = s/1000.0;
000454    s = (int)p->s;
000455    p->s -= s;
000456    p->h = s/3600;
000457    s -= p->h*3600;
000458    p->m = s/60;
000459    p->s += s - p->m*60;
000460    p->rawS = 0;
000461    p->validHMS = 1;
000462  }
000463  
000464  /*
000465  ** Compute both YMD and HMS
000466  */
000467  static void computeYMD_HMS(DateTime *p){
000468    computeYMD(p);
000469    computeHMS(p);
000470  }
000471  
000472  /*
000473  ** Clear the YMD and HMS and the TZ
000474  */
000475  static void clearYMD_HMS_TZ(DateTime *p){
000476    p->validYMD = 0;
000477    p->validHMS = 0;
000478    p->validTZ = 0;
000479  }
000480  
000481  #ifndef SQLITE_OMIT_LOCALTIME
000482  /*
000483  ** On recent Windows platforms, the localtime_s() function is available
000484  ** as part of the "Secure CRT". It is essentially equivalent to 
000485  ** localtime_r() available under most POSIX platforms, except that the 
000486  ** order of the parameters is reversed.
000487  **
000488  ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
000489  **
000490  ** If the user has not indicated to use localtime_r() or localtime_s()
000491  ** already, check for an MSVC build environment that provides 
000492  ** localtime_s().
000493  */
000494  #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \
000495      && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
000496  #undef  HAVE_LOCALTIME_S
000497  #define HAVE_LOCALTIME_S 1
000498  #endif
000499  
000500  /*
000501  ** The following routine implements the rough equivalent of localtime_r()
000502  ** using whatever operating-system specific localtime facility that
000503  ** is available.  This routine returns 0 on success and
000504  ** non-zero on any kind of error.
000505  **
000506  ** If the sqlite3GlobalConfig.bLocaltimeFault variable is true then this
000507  ** routine will always fail.
000508  **
000509  ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
000510  ** library function localtime_r() is used to assist in the calculation of
000511  ** local time.
000512  */
000513  static int osLocaltime(time_t *t, struct tm *pTm){
000514    int rc;
000515  #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S
000516    struct tm *pX;
000517  #if SQLITE_THREADSAFE>0
000518    sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER);
000519  #endif
000520    sqlite3_mutex_enter(mutex);
000521    pX = localtime(t);
000522  #ifndef SQLITE_UNTESTABLE
000523    if( sqlite3GlobalConfig.bLocaltimeFault ) pX = 0;
000524  #endif
000525    if( pX ) *pTm = *pX;
000526    sqlite3_mutex_leave(mutex);
000527    rc = pX==0;
000528  #else
000529  #ifndef SQLITE_UNTESTABLE
000530    if( sqlite3GlobalConfig.bLocaltimeFault ) return 1;
000531  #endif
000532  #if HAVE_LOCALTIME_R
000533    rc = localtime_r(t, pTm)==0;
000534  #else
000535    rc = localtime_s(pTm, t);
000536  #endif /* HAVE_LOCALTIME_R */
000537  #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
000538    return rc;
000539  }
000540  #endif /* SQLITE_OMIT_LOCALTIME */
000541  
000542  
000543  #ifndef SQLITE_OMIT_LOCALTIME
000544  /*
000545  ** Compute the difference (in milliseconds) between localtime and UTC
000546  ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs,
000547  ** return this value and set *pRc to SQLITE_OK. 
000548  **
000549  ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value
000550  ** is undefined in this case.
000551  */
000552  static sqlite3_int64 localtimeOffset(
000553    DateTime *p,                    /* Date at which to calculate offset */
000554    sqlite3_context *pCtx,          /* Write error here if one occurs */
000555    int *pRc                        /* OUT: Error code. SQLITE_OK or ERROR */
000556  ){
000557    DateTime x, y;
000558    time_t t;
000559    struct tm sLocal;
000560  
000561    /* Initialize the contents of sLocal to avoid a compiler warning. */
000562    memset(&sLocal, 0, sizeof(sLocal));
000563  
000564    x = *p;
000565    computeYMD_HMS(&x);
000566    if( x.Y<1971 || x.Y>=2038 ){
000567      /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
000568      ** works for years between 1970 and 2037. For dates outside this range,
000569      ** SQLite attempts to map the year into an equivalent year within this
000570      ** range, do the calculation, then map the year back.
000571      */
000572      x.Y = 2000;
000573      x.M = 1;
000574      x.D = 1;
000575      x.h = 0;
000576      x.m = 0;
000577      x.s = 0.0;
000578    } else {
000579      int s = (int)(x.s + 0.5);
000580      x.s = s;
000581    }
000582    x.tz = 0;
000583    x.validJD = 0;
000584    computeJD(&x);
000585    t = (time_t)(x.iJD/1000 - 21086676*(i64)10000);
000586    if( osLocaltime(&t, &sLocal) ){
000587      sqlite3_result_error(pCtx, "local time unavailable", -1);
000588      *pRc = SQLITE_ERROR;
000589      return 0;
000590    }
000591    y.Y = sLocal.tm_year + 1900;
000592    y.M = sLocal.tm_mon + 1;
000593    y.D = sLocal.tm_mday;
000594    y.h = sLocal.tm_hour;
000595    y.m = sLocal.tm_min;
000596    y.s = sLocal.tm_sec;
000597    y.validYMD = 1;
000598    y.validHMS = 1;
000599    y.validJD = 0;
000600    y.rawS = 0;
000601    y.validTZ = 0;
000602    y.isError = 0;
000603    computeJD(&y);
000604    *pRc = SQLITE_OK;
000605    return y.iJD - x.iJD;
000606  }
000607  #endif /* SQLITE_OMIT_LOCALTIME */
000608  
000609  /*
000610  ** The following table defines various date transformations of the form
000611  **
000612  **            'NNN days'
000613  **
000614  ** Where NNN is an arbitrary floating-point number and "days" can be one
000615  ** of several units of time.
000616  */
000617  static const struct {
000618    u8 eType;           /* Transformation type code */
000619    u8 nName;           /* Length of th name */
000620    char *zName;        /* Name of the transformation */
000621    double rLimit;      /* Maximum NNN value for this transform */
000622    double rXform;      /* Constant used for this transform */
000623  } aXformType[] = {
000624    { 0, 6, "second", 464269060800.0, 86400000.0/(24.0*60.0*60.0) },
000625    { 0, 6, "minute", 7737817680.0,   86400000.0/(24.0*60.0)      },
000626    { 0, 4, "hour",   128963628.0,    86400000.0/24.0             },
000627    { 0, 3, "day",    5373485.0,      86400000.0                  },
000628    { 1, 5, "month",  176546.0,       30.0*86400000.0             },
000629    { 2, 4, "year",   14713.0,        365.0*86400000.0            },
000630  };
000631  
000632  /*
000633  ** Process a modifier to a date-time stamp.  The modifiers are
000634  ** as follows:
000635  **
000636  **     NNN days
000637  **     NNN hours
000638  **     NNN minutes
000639  **     NNN.NNNN seconds
000640  **     NNN months
000641  **     NNN years
000642  **     start of month
000643  **     start of year
000644  **     start of week
000645  **     start of day
000646  **     weekday N
000647  **     unixepoch
000648  **     localtime
000649  **     utc
000650  **
000651  ** Return 0 on success and 1 if there is any kind of error. If the error
000652  ** is in a system call (i.e. localtime()), then an error message is written
000653  ** to context pCtx. If the error is an unrecognized modifier, no error is
000654  ** written to pCtx.
000655  */
000656  static int parseModifier(
000657    sqlite3_context *pCtx,      /* Function context */
000658    const char *z,              /* The text of the modifier */
000659    int n,                      /* Length of zMod in bytes */
000660    DateTime *p                 /* The date/time value to be modified */
000661  ){
000662    int rc = 1;
000663    double r;
000664    switch(sqlite3UpperToLower[(u8)z[0]] ){
000665  #ifndef SQLITE_OMIT_LOCALTIME
000666      case 'l': {
000667        /*    localtime
000668        **
000669        ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
000670        ** show local time.
000671        */
000672        if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){
000673          computeJD(p);
000674          p->iJD += localtimeOffset(p, pCtx, &rc);
000675          clearYMD_HMS_TZ(p);
000676        }
000677        break;
000678      }
000679  #endif
000680      case 'u': {
000681        /*
000682        **    unixepoch
000683        **
000684        ** Treat the current value of p->s as the number of
000685        ** seconds since 1970.  Convert to a real julian day number.
000686        */
000687        if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){
000688          r = p->s*1000.0 + 210866760000000.0;
000689          if( r>=0.0 && r<464269060800000.0 ){
000690            clearYMD_HMS_TZ(p);
000691            p->iJD = (sqlite3_int64)r;
000692            p->validJD = 1;
000693            p->rawS = 0;
000694            rc = 0;
000695          }
000696        }
000697  #ifndef SQLITE_OMIT_LOCALTIME
000698        else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){
000699          if( p->tzSet==0 ){
000700            sqlite3_int64 c1;
000701            computeJD(p);
000702            c1 = localtimeOffset(p, pCtx, &rc);
000703            if( rc==SQLITE_OK ){
000704              p->iJD -= c1;
000705              clearYMD_HMS_TZ(p);
000706              p->iJD += c1 - localtimeOffset(p, pCtx, &rc);
000707            }
000708            p->tzSet = 1;
000709          }else{
000710            rc = SQLITE_OK;
000711          }
000712        }
000713  #endif
000714        break;
000715      }
000716      case 'w': {
000717        /*
000718        **    weekday N
000719        **
000720        ** Move the date to the same time on the next occurrence of
000721        ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
000722        ** date is already on the appropriate weekday, this is a no-op.
000723        */
000724        if( sqlite3_strnicmp(z, "weekday ", 8)==0
000725                 && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)
000726                 && (n=(int)r)==r && n>=0 && r<7 ){
000727          sqlite3_int64 Z;
000728          computeYMD_HMS(p);
000729          p->validTZ = 0;
000730          p->validJD = 0;
000731          computeJD(p);
000732          Z = ((p->iJD + 129600000)/86400000) % 7;
000733          if( Z>n ) Z -= 7;
000734          p->iJD += (n - Z)*86400000;
000735          clearYMD_HMS_TZ(p);
000736          rc = 0;
000737        }
000738        break;
000739      }
000740      case 's': {
000741        /*
000742        **    start of TTTTT
000743        **
000744        ** Move the date backwards to the beginning of the current day,
000745        ** or month or year.
000746        */
000747        if( sqlite3_strnicmp(z, "start of ", 9)!=0 ) break;
000748        if( !p->validJD && !p->validYMD && !p->validHMS ) break;
000749        z += 9;
000750        computeYMD(p);
000751        p->validHMS = 1;
000752        p->h = p->m = 0;
000753        p->s = 0.0;
000754        p->rawS = 0;
000755        p->validTZ = 0;
000756        p->validJD = 0;
000757        if( sqlite3_stricmp(z,"month")==0 ){
000758          p->D = 1;
000759          rc = 0;
000760        }else if( sqlite3_stricmp(z,"year")==0 ){
000761          p->M = 1;
000762          p->D = 1;
000763          rc = 0;
000764        }else if( sqlite3_stricmp(z,"day")==0 ){
000765          rc = 0;
000766        }
000767        break;
000768      }
000769      case '+':
000770      case '-':
000771      case '0':
000772      case '1':
000773      case '2':
000774      case '3':
000775      case '4':
000776      case '5':
000777      case '6':
000778      case '7':
000779      case '8':
000780      case '9': {
000781        double rRounder;
000782        int i;
000783        for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){}
000784        if( !sqlite3AtoF(z, &r, n, SQLITE_UTF8) ){
000785          rc = 1;
000786          break;
000787        }
000788        if( z[n]==':' ){
000789          /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
000790          ** specified number of hours, minutes, seconds, and fractional seconds
000791          ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
000792          ** omitted.
000793          */
000794          const char *z2 = z;
000795          DateTime tx;
000796          sqlite3_int64 day;
000797          if( !sqlite3Isdigit(*z2) ) z2++;
000798          memset(&tx, 0, sizeof(tx));
000799          if( parseHhMmSs(z2, &tx) ) break;
000800          computeJD(&tx);
000801          tx.iJD -= 43200000;
000802          day = tx.iJD/86400000;
000803          tx.iJD -= day*86400000;
000804          if( z[0]=='-' ) tx.iJD = -tx.iJD;
000805          computeJD(p);
000806          clearYMD_HMS_TZ(p);
000807          p->iJD += tx.iJD;
000808          rc = 0;
000809          break;
000810        }
000811  
000812        /* If control reaches this point, it means the transformation is
000813        ** one of the forms like "+NNN days".  */
000814        z += n;
000815        while( sqlite3Isspace(*z) ) z++;
000816        n = sqlite3Strlen30(z);
000817        if( n>10 || n<3 ) break;
000818        if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
000819        computeJD(p);
000820        rc = 1;
000821        rRounder = r<0 ? -0.5 : +0.5;
000822        for(i=0; i<ArraySize(aXformType); i++){
000823          if( aXformType[i].nName==n
000824           && sqlite3_strnicmp(aXformType[i].zName, z, n)==0
000825           && r>-aXformType[i].rLimit && r<aXformType[i].rLimit
000826          ){
000827            switch( aXformType[i].eType ){
000828              case 1: { /* Special processing to add months */
000829                int x;
000830                computeYMD_HMS(p);
000831                p->M += (int)r;
000832                x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
000833                p->Y += x;
000834                p->M -= x*12;
000835                p->validJD = 0;
000836                r -= (int)r;
000837                break;
000838              }
000839              case 2: { /* Special processing to add years */
000840                int y = (int)r;
000841                computeYMD_HMS(p);
000842                p->Y += y;
000843                p->validJD = 0;
000844                r -= (int)r;
000845                break;
000846              }
000847            }
000848            computeJD(p);
000849            p->iJD += (sqlite3_int64)(r*aXformType[i].rXform + rRounder);
000850            rc = 0;
000851            break;
000852          }
000853        }
000854        clearYMD_HMS_TZ(p);
000855        break;
000856      }
000857      default: {
000858        break;
000859      }
000860    }
000861    return rc;
000862  }
000863  
000864  /*
000865  ** Process time function arguments.  argv[0] is a date-time stamp.
000866  ** argv[1] and following are modifiers.  Parse them all and write
000867  ** the resulting time into the DateTime structure p.  Return 0
000868  ** on success and 1 if there are any errors.
000869  **
000870  ** If there are zero parameters (if even argv[0] is undefined)
000871  ** then assume a default value of "now" for argv[0].
000872  */
000873  static int isDate(
000874    sqlite3_context *context, 
000875    int argc, 
000876    sqlite3_value **argv, 
000877    DateTime *p
000878  ){
000879    int i, n;
000880    const unsigned char *z;
000881    int eType;
000882    memset(p, 0, sizeof(*p));
000883    if( argc==0 ){
000884      return setDateTimeToCurrent(context, p);
000885    }
000886    if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
000887                     || eType==SQLITE_INTEGER ){
000888      setRawDateNumber(p, sqlite3_value_double(argv[0]));
000889    }else{
000890      z = sqlite3_value_text(argv[0]);
000891      if( !z || parseDateOrTime(context, (char*)z, p) ){
000892        return 1;
000893      }
000894    }
000895    for(i=1; i<argc; i++){
000896      z = sqlite3_value_text(argv[i]);
000897      n = sqlite3_value_bytes(argv[i]);
000898      if( z==0 || parseModifier(context, (char*)z, n, p) ) return 1;
000899    }
000900    computeJD(p);
000901    if( p->isError || !validJulianDay(p->iJD) ) return 1;
000902    return 0;
000903  }
000904  
000905  
000906  /*
000907  ** The following routines implement the various date and time functions
000908  ** of SQLite.
000909  */
000910  
000911  /*
000912  **    julianday( TIMESTRING, MOD, MOD, ...)
000913  **
000914  ** Return the julian day number of the date specified in the arguments
000915  */
000916  static void juliandayFunc(
000917    sqlite3_context *context,
000918    int argc,
000919    sqlite3_value **argv
000920  ){
000921    DateTime x;
000922    if( isDate(context, argc, argv, &x)==0 ){
000923      computeJD(&x);
000924      sqlite3_result_double(context, x.iJD/86400000.0);
000925    }
000926  }
000927  
000928  /*
000929  **    datetime( TIMESTRING, MOD, MOD, ...)
000930  **
000931  ** Return YYYY-MM-DD HH:MM:SS
000932  */
000933  static void datetimeFunc(
000934    sqlite3_context *context,
000935    int argc,
000936    sqlite3_value **argv
000937  ){
000938    DateTime x;
000939    if( isDate(context, argc, argv, &x)==0 ){
000940      char zBuf[100];
000941      computeYMD_HMS(&x);
000942      sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d",
000943                       x.Y, x.M, x.D, x.h, x.m, (int)(x.s));
000944      sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
000945    }
000946  }
000947  
000948  /*
000949  **    time( TIMESTRING, MOD, MOD, ...)
000950  **
000951  ** Return HH:MM:SS
000952  */
000953  static void timeFunc(
000954    sqlite3_context *context,
000955    int argc,
000956    sqlite3_value **argv
000957  ){
000958    DateTime x;
000959    if( isDate(context, argc, argv, &x)==0 ){
000960      char zBuf[100];
000961      computeHMS(&x);
000962      sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
000963      sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
000964    }
000965  }
000966  
000967  /*
000968  **    date( TIMESTRING, MOD, MOD, ...)
000969  **
000970  ** Return YYYY-MM-DD
000971  */
000972  static void dateFunc(
000973    sqlite3_context *context,
000974    int argc,
000975    sqlite3_value **argv
000976  ){
000977    DateTime x;
000978    if( isDate(context, argc, argv, &x)==0 ){
000979      char zBuf[100];
000980      computeYMD(&x);
000981      sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
000982      sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
000983    }
000984  }
000985  
000986  /*
000987  **    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
000988  **
000989  ** Return a string described by FORMAT.  Conversions as follows:
000990  **
000991  **   %d  day of month
000992  **   %f  ** fractional seconds  SS.SSS
000993  **   %H  hour 00-24
000994  **   %j  day of year 000-366
000995  **   %J  ** julian day number
000996  **   %m  month 01-12
000997  **   %M  minute 00-59
000998  **   %s  seconds since 1970-01-01
000999  **   %S  seconds 00-59
001000  **   %w  day of week 0-6  sunday==0
001001  **   %W  week of year 00-53
001002  **   %Y  year 0000-9999
001003  **   %%  %
001004  */
001005  static void strftimeFunc(
001006    sqlite3_context *context,
001007    int argc,
001008    sqlite3_value **argv
001009  ){
001010    DateTime x;
001011    u64 n;
001012    size_t i,j;
001013    char *z;
001014    sqlite3 *db;
001015    const char *zFmt;
001016    char zBuf[100];
001017    if( argc==0 ) return;
001018    zFmt = (const char*)sqlite3_value_text(argv[0]);
001019    if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
001020    db = sqlite3_context_db_handle(context);
001021    for(i=0, n=1; zFmt[i]; i++, n++){
001022      if( zFmt[i]=='%' ){
001023        switch( zFmt[i+1] ){
001024          case 'd':
001025          case 'H':
001026          case 'm':
001027          case 'M':
001028          case 'S':
001029          case 'W':
001030            n++;
001031            /* fall thru */
001032          case 'w':
001033          case '%':
001034            break;
001035          case 'f':
001036            n += 8;
001037            break;
001038          case 'j':
001039            n += 3;
001040            break;
001041          case 'Y':
001042            n += 8;
001043            break;
001044          case 's':
001045          case 'J':
001046            n += 50;
001047            break;
001048          default:
001049            return;  /* ERROR.  return a NULL */
001050        }
001051        i++;
001052      }
001053    }
001054    testcase( n==sizeof(zBuf)-1 );
001055    testcase( n==sizeof(zBuf) );
001056    testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH]+1 );
001057    testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH] );
001058    if( n<sizeof(zBuf) ){
001059      z = zBuf;
001060    }else if( n>(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ){
001061      sqlite3_result_error_toobig(context);
001062      return;
001063    }else{
001064      z = sqlite3DbMallocRawNN(db, (int)n);
001065      if( z==0 ){
001066        sqlite3_result_error_nomem(context);
001067        return;
001068      }
001069    }
001070    computeJD(&x);
001071    computeYMD_HMS(&x);
001072    for(i=j=0; zFmt[i]; i++){
001073      if( zFmt[i]!='%' ){
001074        z[j++] = zFmt[i];
001075      }else{
001076        i++;
001077        switch( zFmt[i] ){
001078          case 'd':  sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break;
001079          case 'f': {
001080            double s = x.s;
001081            if( s>59.999 ) s = 59.999;
001082            sqlite3_snprintf(7, &z[j],"%06.3f", s);
001083            j += sqlite3Strlen30(&z[j]);
001084            break;
001085          }
001086          case 'H':  sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break;
001087          case 'W': /* Fall thru */
001088          case 'j': {
001089            int nDay;             /* Number of days since 1st day of year */
001090            DateTime y = x;
001091            y.validJD = 0;
001092            y.M = 1;
001093            y.D = 1;
001094            computeJD(&y);
001095            nDay = (int)((x.iJD-y.iJD+43200000)/86400000);
001096            if( zFmt[i]=='W' ){
001097              int wd;   /* 0=Monday, 1=Tuesday, ... 6=Sunday */
001098              wd = (int)(((x.iJD+43200000)/86400000)%7);
001099              sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);
001100              j += 2;
001101            }else{
001102              sqlite3_snprintf(4, &z[j],"%03d",nDay+1);
001103              j += 3;
001104            }
001105            break;
001106          }
001107          case 'J': {
001108            sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0);
001109            j+=sqlite3Strlen30(&z[j]);
001110            break;
001111          }
001112          case 'm':  sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;
001113          case 'M':  sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;
001114          case 's': {
001115            sqlite3_snprintf(30,&z[j],"%lld",
001116                             (i64)(x.iJD/1000 - 21086676*(i64)10000));
001117            j += sqlite3Strlen30(&z[j]);
001118            break;
001119          }
001120          case 'S':  sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
001121          case 'w': {
001122            z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
001123            break;
001124          }
001125          case 'Y': {
001126            sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
001127            break;
001128          }
001129          default:   z[j++] = '%'; break;
001130        }
001131      }
001132    }
001133    z[j] = 0;
001134    sqlite3_result_text(context, z, -1,
001135                        z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC);
001136  }
001137  
001138  /*
001139  ** current_time()
001140  **
001141  ** This function returns the same value as time('now').
001142  */
001143  static void ctimeFunc(
001144    sqlite3_context *context,
001145    int NotUsed,
001146    sqlite3_value **NotUsed2
001147  ){
001148    UNUSED_PARAMETER2(NotUsed, NotUsed2);
001149    timeFunc(context, 0, 0);
001150  }
001151  
001152  /*
001153  ** current_date()
001154  **
001155  ** This function returns the same value as date('now').
001156  */
001157  static void cdateFunc(
001158    sqlite3_context *context,
001159    int NotUsed,
001160    sqlite3_value **NotUsed2
001161  ){
001162    UNUSED_PARAMETER2(NotUsed, NotUsed2);
001163    dateFunc(context, 0, 0);
001164  }
001165  
001166  /*
001167  ** current_timestamp()
001168  **
001169  ** This function returns the same value as datetime('now').
001170  */
001171  static void ctimestampFunc(
001172    sqlite3_context *context,
001173    int NotUsed,
001174    sqlite3_value **NotUsed2
001175  ){
001176    UNUSED_PARAMETER2(NotUsed, NotUsed2);
001177    datetimeFunc(context, 0, 0);
001178  }
001179  #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
001180  
001181  #ifdef SQLITE_OMIT_DATETIME_FUNCS
001182  /*
001183  ** If the library is compiled to omit the full-scale date and time
001184  ** handling (to get a smaller binary), the following minimal version
001185  ** of the functions current_time(), current_date() and current_timestamp()
001186  ** are included instead. This is to support column declarations that
001187  ** include "DEFAULT CURRENT_TIME" etc.
001188  **
001189  ** This function uses the C-library functions time(), gmtime()
001190  ** and strftime(). The format string to pass to strftime() is supplied
001191  ** as the user-data for the function.
001192  */
001193  static void currentTimeFunc(
001194    sqlite3_context *context,
001195    int argc,
001196    sqlite3_value **argv
001197  ){
001198    time_t t;
001199    char *zFormat = (char *)sqlite3_user_data(context);
001200    sqlite3_int64 iT;
001201    struct tm *pTm;
001202    struct tm sNow;
001203    char zBuf[20];
001204  
001205    UNUSED_PARAMETER(argc);
001206    UNUSED_PARAMETER(argv);
001207  
001208    iT = sqlite3StmtCurrentTime(context);
001209    if( iT<=0 ) return;
001210    t = iT/1000 - 10000*(sqlite3_int64)21086676;
001211  #if HAVE_GMTIME_R
001212    pTm = gmtime_r(&t, &sNow);
001213  #else
001214    sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
001215    pTm = gmtime(&t);
001216    if( pTm ) memcpy(&sNow, pTm, sizeof(sNow));
001217    sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
001218  #endif
001219    if( pTm ){
001220      strftime(zBuf, 20, zFormat, &sNow);
001221      sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
001222    }
001223  }
001224  #endif
001225  
001226  /*
001227  ** This function registered all of the above C functions as SQL
001228  ** functions.  This should be the only routine in this file with
001229  ** external linkage.
001230  */
001231  void sqlite3RegisterDateTimeFunctions(void){
001232    static FuncDef aDateTimeFuncs[] = {
001233  #ifndef SQLITE_OMIT_DATETIME_FUNCS
001234      PURE_DATE(julianday,        -1, 0, 0, juliandayFunc ),
001235      PURE_DATE(date,             -1, 0, 0, dateFunc      ),
001236      PURE_DATE(time,             -1, 0, 0, timeFunc      ),
001237      PURE_DATE(datetime,         -1, 0, 0, datetimeFunc  ),
001238      PURE_DATE(strftime,         -1, 0, 0, strftimeFunc  ),
001239      DFUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
001240      DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
001241      DFUNCTION(current_date,      0, 0, 0, cdateFunc     ),
001242  #else
001243      STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
001244      STR_FUNCTION(current_date,      0, "%Y-%m-%d",          0, currentTimeFunc),
001245      STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
001246  #endif
001247    };
001248    sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
001249  }