/*** 2003 October 31**** The author disclaims copyright to this source code.  In place of** a legal notice, here is a blessing:****    May you do good and not evil.**    May you find forgiveness for yourself and forgive others.**    May you share freely, never taking more than you give.***************************************************************************** This file contains the C functions that implement date and time** functions for SQLite.  **** There is only one exported symbol in this file - the function** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.** All other code has file scope.**** $Id: date.c,v 1.54 2006/01/31 20:49:13 drh Exp $**** NOTES:**** SQLite processes all times and dates as Julian Day numbers.  The** dates and times are stored as the number of days since noon** in Greenwich on November 24, 4714 B.C. according to the Gregorian** calendar system.**** 1970-01-01 00:00:00 is JD 2440587.5** 2000-01-01 00:00:00 is JD 2451544.5**** This implemention requires years to be expressed as a 4-digit number** which means that only dates between 0000-01-01 and 9999-12-31 can** be represented, even though julian day numbers allow a much wider** range of dates.**** The Gregorian calendar system is used for all dates and times,** even those that predate the Gregorian calendar.  Historians usually** use the Julian calendar for dates prior to 1582-10-15 and for some** dates afterwards, depending on locale.  Beware of this difference.**** The conversion algorithms are implemented based on descriptions** in the following text:****      Jean Meeus**      Astronomical Algorithms, 2nd Edition, 1998**      ISBM 0-943396-61-1**      Willmann-Bell, Inc**      Richmond, Virginia (USA)*/#include "sqliteInt.h"#include "os.h"#include <ctype.h>#include <stdlib.h>#include <assert.h>#include <time.h>#ifndef SQLITE_OMIT_DATETIME_FUNCS/*** A structure for holding a single date and time.*/typedef struct DateTime DateTime;struct DateTime {  double rJD;      /* The julian day number */  int Y, M, D;     /* Year, month, and day */  int h, m;        /* Hour and minutes */  int tz;          /* Timezone offset in minutes */  double s;        /* Seconds */  char validYMD;   /* True if Y,M,D are valid */  char validHMS;   /* True if h,m,s are valid */  char validJD;    /* True if rJD is valid */  char validTZ;    /* True if tz is valid */};/*** Convert zDate into one or more integers.  Additional arguments** come in groups of 5 as follows:****       N       number of digits in the integer**       min     minimum allowed value of the integer**       max     maximum allowed value of the integer**       nextC   first character after the integer**       pVal    where to write the integers value.**** Conversions continue until one with nextC==0 is encountered.** The function returns the number of successful conversions.*/static int getDigits(const char *zDate, ...){  va_list ap;  int val;  int N;  int min;  int max;  int nextC;  int *pVal;  int cnt = 0;  va_start(ap, zDate);  do{    N = va_arg(ap, int);    min = va_arg(ap, int);    max = va_arg(ap, int);    nextC = va_arg(ap, int);    pVal = va_arg(ap, int*);    val = 0;    while( N-- ){      if( !isdigit(*(u8*)zDate) ){        goto end_getDigits;      }      val = val*10 + *zDate - '0';      zDate++;    }    if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){      goto end_getDigits;    }    *pVal = val;    zDate++;    cnt++;  }while( nextC );end_getDigits:  va_end(ap);  return cnt;}/*** Read text from z[] and convert into a floating point number.  Return** the number of digits converted.*/#define getValue sqlite3AtoF/*** Parse a timezone extension on the end of a date-time.** The extension is of the form:****        (+/-)HH:MM**** If the parse is successful, write the number of minutes** of change in *pnMin and return 0.  If a parser error occurs,** return 0.**** A missing specifier is not considered an error.*/static int parseTimezone(const char *zDate, DateTime *p){  int sgn = 0;  int nHr, nMn;  while( isspace(*(u8*)zDate) ){ zDate++; }  p->tz = 0;  if( *zDate=='-' ){    sgn = -1;  }else if( *zDate=='+' ){    sgn = +1;  }else{    return *zDate!=0;  }  zDate++;  if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){    return 1;  }  zDate += 5;  p->tz = sgn*(nMn + nHr*60);  while( isspace(*(u8*)zDate) ){ zDate++; }  return *zDate!=0;}/*** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.** 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;  double ms = 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=='.' && isdigit((u8)zDate[1]) ){      double rScale = 1.0;      zDate++;      while( isdigit(*(u8*)zDate) ){        ms = ms*10.0 + *zDate - '0';        rScale *= 10.0;        zDate++;      }      ms /= rScale;    }  }else{    s = 0;  }  p->validJD = 0;  p->validHMS = 1;  p->h = h;  p->m = m;  p->s = s + ms;  if( parseTimezone(zDate, p) ) return 1;  p->validTZ = p->tz!=0;  return 0;}/*** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume** that the YYYY-MM-DD is according to the Gregorian calendar.**** Reference:  Meeus page 61*/static void computeJD(DateTime *p){  int Y, M, D, A, B, X1, X2;  if( p->validJD ) return;  if( p->validYMD ){    Y = p->Y;    M = p->M;    D = p->D;  }else{    Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */    M = 1;    D = 1;  }  if( M<=2 ){    Y--;    M += 12;  }  A = Y/100;  B = 2 - A + (A/4);  X1 = 365.25*(Y+4716);  X2 = 30.6001*(M+1);  p->rJD = X1 + X2 + D + B - 1524.5;  p->validJD = 1;  p->validYMD = 0;  if( p->validHMS ){    p->rJD += (p->h*3600.0 + p->m*60.0 + p->s)/86400.0;    if( p->validTZ ){      p->rJD -= p->tz*60/86400.0;      p->validHMS = 0;      p->validTZ = 0;    }  }}/*** Parse dates of the form****     YYYY-MM-DD HH:MM:SS.FFF**     YYYY-MM-DD HH:MM:SS**     YYYY-MM-DD HH:MM**     YYYY-MM-DD**** Write the result into the DateTime structure and return 0** on success and 1 if the input string is not a well-formed** date.*/static int parseYyyyMmDd(const char *zDate, DateTime *p){  int Y, M, D, neg;  if( zDate[0]=='-' ){    zDate++;    neg = 1;  }else{    neg = 0;  }  if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){    return 1;  }  zDate += 10;  while( isspace(*(u8*)zDate) || 'T'==*(u8*)zDate ){ zDate++; }  if( parseHhMmSs(zDate, p)==0 ){    /* We got the time */  }else if( *zDate==0 ){    p->validHMS = 0;  }else{    return 1;  }  p->validJD = 0;  p->validYMD = 1;  p->Y = neg ? -Y : Y;  p->M = M;  p->D = D;  if( p->validTZ ){    computeJD(p);  }  return 0;}/*** Attempt to parse the given string into a Julian Day Number.  Return** the number of errors.**** The following are acceptable forms for the input string:****      YYYY-MM-DD HH:MM:SS.FFF  +/-HH:MM**      DDDD.DD **      now**** In the first form, the +/-HH:MM is always optional.  The fractional** seconds extension (the ".FFF") is optional.  The seconds portion** (":SS.FFF") is option.  The year and date can be omitted as long** as there is a time string.  The time string can be omitted as long** as there is a year and date.*/static int parseDateOrTime(const char *zDate, DateTime *p){  memset(p, 0, sizeof(*p));  if( parseYyyyMmDd(zDate,p)==0 ){    return 0;  }else if( parseHhMmSs(zDate, p)==0 ){    return 0;  }else if( sqlite3StrICmp(zDate,"now")==0){    double r;    sqlite3OsCurrentTime(&r);    p->rJD = r;    p->validJD = 1;    return 0;  }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){    getValue(zDate, &p->rJD);    p->validJD = 1;    return 0;  }  return 1;}/*** Compute the Year, Month, and Day from the julian day number.*/static void computeYMD(DateTime *p){  int Z, A, B, C, D, E, X1;  if( p->validYMD ) return;  if( !p->validJD ){    p->Y = 2000;    p->M = 1;    p->D = 1;  }else{    Z = p->rJD + 0.5;    A = (Z - 1867216.25)/36524.25;    A = Z + 1 + A - (A/4);    B = A + 1524;    C = (B - 122.1)/365.25;    D = 365.25*C;    E = (B-D)/30.6001;    X1 = 30.6001*E;    p->D = B - D - X1;    p->M = E<14 ? E-1 : E-13;    p->Y = p->M>2 ? C - 4716 : C - 4715;  }  p->validYMD = 1;}/*** Compute the Hour, Minute, and Seconds from the julian day number.*/static void computeHMS(DateTime *p){  int Z, s;  if( p->validHMS ) return;  Z = p->rJD + 0.5;  s = (p->rJD + 0.5 - Z)*86400000.0 + 0.5;  p->s = 0.001*s;  s = p->s;  p->s -= s;  p->h = s/3600;  s -= p->h*3600;  p->m = s/60;  p->s += s - p->m*60;  p->validHMS = 1;}/*** Compute both YMD and HMS*/static void computeYMD_HMS(DateTime *p){  computeYMD(p);  computeHMS(p);}/*** Clear the YMD and HMS and the TZ*/static void clearYMD_HMS_TZ(DateTime *p){  p->validYMD = 0;  p->validHMS = 0;  p->validTZ = 0;}/*** Compute the difference (in days) between localtime and UTC (a.k.a. GMT)** for the time value p where p is in UTC.*/static double localtimeOffset(DateTime *p){  DateTime x, y;  time_t t;  struct tm *pTm;  x = *p;  computeYMD_HMS(&x);  if( x.Y<1971 || x.Y>=2038 ){    x.Y = 2000;    x.M = 1;    x.D = 1;    x.h = 0;    x.m = 0;    x.s = 0.0;  } else {    int s = x.s + 0.5;    x.s = s;  }  x.tz = 0;  x.validJD = 0;  computeJD(&x);  t = (x.rJD-2440587.5)*86400.0 + 0.5;  sqlite3OsEnterMutex();  pTm = localtime(&t);  y.Y = pTm->tm_year + 1900;  y.M = pTm->tm_mon + 1;  y.D = pTm->tm_mday;  y.h = pTm->tm_hour;  y.m = pTm->tm_min;  y.s = pTm->tm_sec;  sqlite3OsLeaveMutex();  y.validYMD = 1;  y.validHMS = 1;  y.validJD = 0;  y.validTZ = 0;  computeJD(&y);  return y.rJD - x.rJD;}/*** Process a modifier to a date-time stamp.  The modifiers are** as follows:****     NNN days**     NNN hours**     NNN minutes**     NNN.NNNN seconds**     NNN months**     NNN years**     start of month**     start of year**     start of week**     start of day**     weekday N**     unixepoch**     localtime**     utc**** Return 0 on success and 1 if there is any kind of error.*/static int parseModifier(const char *zMod, DateTime *p){  int rc = 1;  int n;  double r;  char *z, zBuf[30];  z = zBuf;  for(n=0; n<sizeof(zBuf)-1 && zMod[n]; n++){    z[n] = tolower(zMod[n]);  }  z[n] = 0;  switch( z[0] ){    case 'l': {      /*    localtime      **      ** Assuming the current time value is UTC (a.k.a. GMT), shift it to      ** show local time.      */      if( strcmp(z, "localtime")==0 ){        computeJD(p);        p->rJD += localtimeOffset(p);        clearYMD_HMS_TZ(p);        rc = 0;      }      break;    }    case 'u': {      /*      **    unixepoch      **      ** Treat the current value of p->rJD as the number of      ** seconds since 1970.  Convert to a real julian day number.      */      if( strcmp(z, "unixepoch")==0 && p->validJD ){        p->rJD = p->rJD/86400.0 + 2440587.5;        clearYMD_HMS_TZ(p);        rc = 0;      }else if( strcmp(z, "utc")==0 ){        double c1;        computeJD(p);        c1 = localtimeOffset(p);        p->rJD -= c1;        clearYMD_HMS_TZ(p);        p->rJD += c1 - localtimeOffset(p);        rc = 0;      }      break;    }    case 'w': {      /*