SQLite4  Check-in [fa869b0982]

/*
** A structure for holding a single date and time.
*/
typedef struct DateTime DateTime;
struct DateTime {
  sqlite4_uint64 iJD;   /* The julian day number times 86400000 */
  int Y, M, D;          /* Year, month, and day */
  int h, m;             /* Hour and minutes */
  int tz;               /* Timezone offset in minutes */
  sqlite4_num s;        /* Seconds */
  char validYMD;        /* True (1) if Y,M,D are valid */
  char validHMS;        /* True (1) if h,m,s are valid */
  char validJD;         /* True (1) if iJD is valid */
  char validTZ;         /* True (1) if tz is valid */
};


/*
** Convert zDate into one or more integers.  Additional arguments
** come in groups of 5 as follows:
**

** The HH, MM, and SS must each be exactly 2 digits.  The
** fractional seconds FFFF can be one or more digits.
**
** Return 1 if there is a parsing error and 0 on success.
*/
static int parseHhMmSs(const char *zDate, DateTime *p){
  int h, m, s;
  sqlite4_num ms = {0,0,0,0};
  if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){
    return 1;
  }
  zDate += 5;
  if( *zDate==':' ){
    zDate++;
    if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
      return 1;
    }
    zDate += 2;

    if( *zDate=='.' ){
      int iMs = 0;
      int nDigit = 1;

      while( sqlite4Isdigit(zDate[nDigit]) ){
        iMs = iMs * 10 + (zDate[nDigit] - '0');
        nDigit++;
      }
      if( nDigit>1 ){
        ms = sqlite4_num_from_int64(iMs);
        assert( ms.e==0 );
        ms.e += (1-nDigit);
        zDate += nDigit;
      }

    }
  }else{
    s = 0;
  }
  p->validJD = 0;
  p->validHMS = 1;
  p->h = h;
  p->m = m;
  p->s = sqlite4_num_add(sqlite4_num_from_int64(s), ms);
  if( parseTimezone(zDate, p) ) return 1;
  p->validTZ = (p->tz!=0)?1:0;
  return 0;
}

/*
** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume

  A = Y/100;
  B = 2 - A + (A/4);
  X1 = 36525*(Y+4716)/100;
  X2 = 306001*(M+1)/10000;
  p->iJD = (sqlite4_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
  p->validJD = 1;
  if( p->validHMS ){
    p->iJD += p->h*3600000 + p->m*60000;
    p->iJD += sqlite4_num_to_int64(
        sqlite4_num_mul(p->s, sqlite4_num_from_int64(1000)), 0
    );
    if( p->validTZ ){
      p->iJD -= p->tz*60000;
      p->validYMD = 0;
      p->validHMS = 0;
      p->validTZ = 0;
    }
  }

** as there is a year and date.
*/
static int parseDateOrTime(
  sqlite4_context *context, 
  const char *zDate, 
  DateTime *p
){

  if( parseYyyyMmDd(zDate,p)==0 ){
    return 0;
  }else if( parseHhMmSs(zDate, p)==0 ){
    return 0;
  }else if( sqlite4_stricmp(zDate,"now")==0){
    return setDateTimeToCurrent(context, p);
  }else{
    sqlite4_num num;
    num = sqlite4_num_from_text(zDate, -1, SQLITE4_IGNORE_WHITESPACE, 0);
    if( sqlite4_num_isnan(num)==0 ){
      static const sqlite4_num one_half = {0, 0, -1, 5};

      num = sqlite4_num_mul(num, sqlite4_num_from_int64(86400000));
      num = sqlite4_num_add(num, one_half);
      p->iJD = sqlite4_num_to_int64(num, 0);
      p->validJD = 1;
      return 0;
    }
  }
  return 1;
}

/*
** Compute the Year, Month, and Day from the julian day number.
*/

** Compute the Hour, Minute, and Seconds from the julian day number.
*/
static void computeHMS(DateTime *p){
  int s;
  if( p->validHMS ) return;
  computeJD(p);
  s = (int)((p->iJD + 43200000) % 86400000);
  p->s = sqlite4_num_div(
      sqlite4_num_from_int64(s), sqlite4_num_from_int64(1000)
  );
  s = (int)sqlite4_num_to_int64(p->s, 0);
  p->s = sqlite4_num_sub(p->s, sqlite4_num_from_int64(s));
  p->h = s/3600;
  s -= p->h*3600;
  p->m = s/60;
  p->s = sqlite4_num_add(p->s, sqlite4_num_from_int64(s - p->m*60));
  p->validHMS = 1;
}

/*
** Compute both YMD and HMS
*/
static void computeYMD_HMS(DateTime *p){

  y.validJD = 0;
  y.validTZ = 0;
  computeJD(&y);
  *pRc = SQLITE4_OK;
  return y.iJD - x.iJD;
}
#endif /* SQLITE4_OMIT_LOCALTIME */

static sqlite4_int64 multiplyAndRound(sqlite4_num a, i64 b){
  static const sqlite4_num aRnd[2] = { {0, 0, -1, 5}, {1, 0, -1, 5} };
  sqlite4_num res;

  res = sqlite4_num_mul(a, sqlite4_num_from_int64(b));
  assert( res.sign==0 || res.sign==1 );
  res = sqlite4_num_add(res, aRnd[res.sign]);
  return sqlite4_num_to_int64(res, 0);
}

/*
** Process a modifier to a date-time stamp.  The modifiers are
** as follows:
**
**     NNN days
**     NNN hours

**     utc
**
** Return 0 on success and 1 if there is any kind of error. If the error
** is in a system call (i.e. localtime()), then an error message is written
** to context pCtx. If the error is an unrecognized modifier, no error is
** written to pCtx.
*/
static int parseModifier(
  sqlite4_context *pCtx,          /* Leave error message here */
  const char *zMod,               /* date-time modifier */
  DateTime *p                     /* Update the value this points to */
){
  int rc = 1;
  int n;

  char *z, zBuf[30];
  z = zBuf;
  for(n=0; n<ArraySize(zBuf)-1 && zMod[n]; n++){
    z[n] = (char)sqlite4UpperToLower[(u8)zMod[n]];
  }
  z[n] = 0;
  switch( z[0] ){

      /*
      **    weekday N
      **
      ** Move the date to the same time on the next occurrence of
      ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
      ** date is already on the appropriate weekday, this is a no-op.
      */
      if( strncmp(z, "weekday ", 8)==0 ){
        int bLossy;
        int iWeekday;
        sqlite4_int64 Z;
        sqlite4_num w;
        int n;

        for(n=8; sqlite4Isspace(z[n]); n++);
        if( z[n]==0 ) break;
        w = sqlite4_num_from_text(&z[8], -1, SQLITE4_IGNORE_WHITESPACE, 0);
        if( sqlite4_num_isnan(w) ) break;
        iWeekday = (int)sqlite4_num_to_int64(w, &bLossy);
        if( bLossy || iWeekday<0 || iWeekday>6 ) break;

        computeYMD_HMS(p);
        p->validTZ = 0;
        p->validJD = 0;
        computeJD(p);
        Z = ((p->iJD + 129600000)/86400000) % 7;
        if( Z>iWeekday ) Z -= 7;
        p->iJD += (iWeekday - Z)*86400000;
        clearYMD_HMS_TZ(p);
        rc = 0;
      }
      break;
    }
    case 's': {
      /*
      **    start of TTTTT
      **
      ** Move the date backwards to the beginning of the current day,
      ** or month or year.
      */
      if( strncmp(z, "start of ", 9)!=0 ) break;
      z += 9;
      computeYMD(p);
      p->validHMS = 1;
      p->h = p->m = 0;
      memset(&p->s, 0, sizeof(sqlite4_num));
      p->validTZ = 0;
      p->validJD = 0;
      if( strcmp(z,"month")==0 ){
        p->D = 1;
        rc = 0;
      }else if( strcmp(z,"year")==0 ){
        computeYMD(p);

    case '3':
    case '4':
    case '5':
    case '6':
    case '7':
    case '8':
    case '9': {
      sqlite4_num num;

      for(n=1; z[n] && z[n]!=':' && !sqlite4Isspace(z[n]); n++){}
      num = sqlite4_num_from_text(z, n, SQLITE4_IGNORE_WHITESPACE, 0);
      if( sqlite4_num_isnan(num) ){
        rc = 1;
        break;
      }

      if( z[n]==':' ){
        /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
        ** specified number of hours, minutes, seconds, and fractional seconds
        ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
        ** omitted.
        */
        const char *z2 = z;

        if( z[0]=='-' ) tx.iJD = -tx.iJD;
        computeJD(p);
        clearYMD_HMS_TZ(p);
        p->iJD += tx.iJD;
        rc = 0;
        break;
      }

      z += n;
      while( sqlite4Isspace(*z) ) z++;
      n = sqlite4Strlen30(z);
      if( n>10 || n<3 ) break;
      if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
      computeJD(p);
      rc = 0;

      if( n==3 && strcmp(z,"day")==0 ){
        p->iJD += multiplyAndRound(num, 86400000);
      }else if( n==4 && strcmp(z,"hour")==0 ){
        p->iJD += multiplyAndRound(num, 86400000 / 24);
      }else if( n==6 && strcmp(z,"minute")==0 ){
        p->iJD += multiplyAndRound(num, 86400000 / (24 * 60));
      }else if( n==6 && strcmp(z,"second")==0 ){
        p->iJD += multiplyAndRound(num, 86400000 / (24 * 60 * 60));
      }else if( n==5 && strcmp(z,"month")==0 ){
        int bLossy;
        int nMonth;
        int x;
        nMonth = sqlite4_num_to_int64(num, &bLossy);
        computeYMD_HMS(p);
        p->M += nMonth;
        x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
        p->Y += x;
        p->M -= x*12;
        p->validJD = 0;
        computeJD(p);

        if( bLossy ){
          num = sqlite4_num_sub(num, sqlite4_num_from_int64(nMonth));
          p->iJD += multiplyAndRound(num, (i64)30*86400000);
        }
      }else if( n==4 && strcmp(z,"year")==0 ){
        int bLossy;
        int nYear;
        nYear = sqlite4_num_to_int64(num, &bLossy);
        computeYMD_HMS(p);
        p->Y += nYear;
        p->validJD = 0;
        computeJD(p);
        if( bLossy ){
          num = sqlite4_num_sub(num, sqlite4_num_from_int64(nYear));
          p->iJD += multiplyAndRound(num, (i64)365*86400000);
        }
      }else{
        rc = 1;
      }
      clearYMD_HMS_TZ(p);
      break;
    }

*/
static int isDate(
  sqlite4_context *context, 
  int argc, 
  sqlite4_value **argv, 
  DateTime *p
){
  static const sqlite4_num ms_per_day = {0, 0, 0, 86400000};

  int i;
  const char *z;
  int eType;
  memset(p, 0, sizeof(*p));
  if( argc==0 ){
    return setDateTimeToCurrent(context, p);
  }
  eType = sqlite4_value_type(argv[0]);
  if( eType==SQLITE4_FLOAT || eType==SQLITE4_INTEGER ){
    sqlite4_num jd = sqlite4_num_mul(sqlite4_value_num(argv[0]), ms_per_day);
    p->iJD = sqlite4_num_to_int64(sqlite4_num_round(jd, 0), 0);
    p->validJD = 1;
  }else{
    z = sqlite4_value_text(argv[0], 0);
    if( !z || parseDateOrTime(context, z, p) ){
      return 1;
    }
  }

** Return the julian day number of the date specified in the arguments
*/
static void juliandayFunc(
  sqlite4_context *context,
  int argc,
  sqlite4_value **argv
){
  static const sqlite4_num ms_per_day = {0, 0, 0, 86400000};
  DateTime x;

  if( isDate(context, argc, argv, &x)==0 ){
    computeJD(&x);
    sqlite4_result_num(context, 
        sqlite4_num_div(sqlite4_num_from_int64(x.iJD), ms_per_day)
    );
  }
}

/*
**    datetime( TIMESTRING, MOD, MOD, ...)
**
** Return YYYY-MM-DD HH:MM:SS
*/
static void datetimeFunc(
  sqlite4_context *context,
  int argc,
  sqlite4_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    char zBuf[100];
    computeYMD_HMS(&x);
    sqlite4_snprintf(zBuf,sizeof(zBuf), "%04d-%02d-%02d %02d:%02d:%02d",
        x.Y, x.M, x.D, x.h, x.m, (int)(sqlite4_num_to_int64(x.s,0))
    );
    sqlite4_result_text(context, zBuf, -1, SQLITE4_TRANSIENT, 0);
  }
}

/*
**    time( TIMESTRING, MOD, MOD, ...)
**
** Return HH:MM:SS
*/
static void timeFunc(
  sqlite4_context *context,
  int argc,
  sqlite4_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    char zBuf[100];
    computeHMS(&x);
    sqlite4_snprintf(zBuf,sizeof(zBuf), "%02d:%02d:%02d", x.h, x.m, 
        (int)(sqlite4_num_to_int64(x.s,0))
    );
    sqlite4_result_text(context, zBuf, -1, SQLITE4_TRANSIENT, 0);
  }
}

/*
**    date( TIMESTRING, MOD, MOD, ...)
**

    if( zFmt[i]!='%' ){
      z[j++] = zFmt[i];
    }else{
      i++;
      switch( zFmt[i] ){
        case 'd':  sqlite4_snprintf(&z[j],3,"%02d",x.D); j+=2; break;
        case 'f': {
          sqlite4_num rnd = x.s;
          int i1;
          rnd.e += 3;
          i1 = sqlite4_num_to_int64(rnd, 0);
          j += sqlite4_snprintf(&z[j], 7, "%02d.%03d", i1 / 1000, i1 % 1000);
          break;
        }
        case 'H':  sqlite4_snprintf(&z[j],3,"%02d",x.h); j+=2; break;
        case 'W': /* Fall thru */
        case 'j': {
          int nDay;             /* Number of days since 1st day of year */
          DateTime y = x;

        case 'm':  sqlite4_snprintf(&z[j],3,"%02d",x.M); j+=2; break;
        case 'M':  sqlite4_snprintf(&z[j],3,"%02d",x.m); j+=2; break;
        case 's': {
          j += sqlite4_snprintf(&z[j],30,"%lld",
                                (i64)(x.iJD/1000 - 21086676*(i64)10000));
          break;
        }
        case 'S':  
          sqlite4_snprintf(&z[j], 3, "%02d", (int)sqlite4_num_to_int64(x.s, 0));
          j+=2; 
          break;
        case 'w': {
          z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
          break;
        }
        case 'Y': {
          sqlite4_snprintf(&z[j],5,"%04d",x.Y); j+=sqlite4Strlen30(&z[j]);
          break;

    if( SQLITE4_NULL==sqlite4_value_type(argv[1]) ) return;
    n = sqlite4_value_int(argv[1]);
    if( n>30 ) n = 30;
    if( n<0 ) n = 0;
  }
  if( sqlite4_value_type(argv[0])==SQLITE4_NULL ) return;












  sqlite4_result_num(context, sqlite4_num_round(sqlite4_value_num(argv[0]), n));
}

/*
** Allocate nByte bytes of space using sqlite4_malloc(). If the
** allocation fails, call sqlite4_result_error_nomem() to notify
** the database handle that malloc() has failed and return NULL.
** If nByte is larger than the maximum string or blob length, then

/*
** The following variable, if set to a non-zero value, is interpreted as
** the number of seconds since 1970 and is used to set the result of
** sqlite4OsCurrentTime() during testing.
*/
unsigned int sqlite4_current_time = 0; /* Fake system time */
int sqlite4OsCurrentTime(sqlite4_env *pEnv, sqlite4_uint64 *pTimeOut){
  static const sqlite4_uint64 unixEpoch = 24405875*(sqlite4_int64)8640000;
  int rc = SQLITE4_OK;
  if( sqlite4_current_time ){
    *pTimeOut = unixEpoch + (sqlite4_uint64)sqlite4_current_time * 1000;
    return SQLITE4_OK;
  }
#if SQLITE4_OS_UNIX

  struct timeval sNow;
  if( gettimeofday(&sNow, 0)==0 ){
    *pTimeOut = unixEpoch + 1000*(sqlite4_int64)sNow.tv_sec + sNow.tv_usec/1000;
  }else{
    rc = SQLITE4_ERROR;
  }
  UNUSED_PARAMETER(pEnv);

datetest 1.23b julianday('12345.6') 12345.6
datetest 1.24 {julianday('2001-01-01 12:00:00 bogus')} NULL
datetest 1.25 {julianday('2001-01-01 bogus')} NULL
datetest 1.26 {julianday('2001-01-01 12:60:00')} NULL
datetest 1.27 {julianday('2001-01-01 12:59:60')} NULL
datetest 1.28 {julianday('2001-00-01')} NULL
datetest 1.29 {julianday('2001-01-00')} NULL

datetest 2.1 datetime(0,'unixepoch') {1970-01-01 00:00:00}
datetest 2.1b datetime(0,'unixepoc') NULL
datetest 2.1c datetime(0,'unixepochx') NULL
datetest 2.1d datetime('2003-10-22','unixepoch') NULL
datetest 2.2 datetime(946684800,'unixepoch') {2000-01-01 00:00:00}
datetest 2.2b datetime('946684800','unixepoch') {2000-01-01 00:00:00}
for {set i 0} {$i<1000} {incr i} {

datetest 13.30 {date('2000-01-01','+1.5 years')} {2001-07-02}
datetest 13.31 {date('2001-01-01','+1.5 years')} {2002-07-02}
datetest 13.32 {date('2002-01-01','+1.5 years')} {2003-07-02}
datetest 13.33 {date('2002-01-01','-1.5 years')} {2000-07-02}
datetest 13.34 {date('2001-01-01','-1.5 years')} {1999-07-02}



































finish_test

  coalesce.test 
  collate1.test collate2.test collate3.test collate4.test collate5.test
  collate6.test collate7.test collate8.test collate9.test collateA.test
  conflict.test 
  count.test
  cse.test
  ctime.test
  date.test
  delete.test delete2.test
  distinct.test distinctagg.test
  exists.test
  e_droptrigger.test e_dropview.test
  e_resolve.test e_dropview.test
  e_select2.test
  enc4.test