/ Artifact Content
Login

Artifact e1d8ac7102f3f283e63e13867acb0efa33861cf34f0faf4cdbaf9fa7a1eb7041:


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