/*** 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.90 2008/09/03 17:11:16 drh Exp $**** 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 <ctype.h>#include <stdlib.h>#include <assert.h>#include <time.h>#ifndef SQLITE_OMIT_DATETIME_FUNCS/*** On recent Windows platforms, the localtime_s() function is available** as part of the "Secure CRT". It is essentially equivalent to ** localtime_r() available under most POSIX platforms, except that the ** order of the parameters is reversed.**** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.**** If the user has not indicated to use localtime_r() or localtime_s()** already, check for an MSVC build environment that provides ** localtime_s().*/#if !defined(HAVE_LOCALTIME_R) && !defined(HAVE_LOCALTIME_S) && \     defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)#define HAVE_LOCALTIME_S 1#endif/*** A structure for holding a single date and time.*/typedef struct DateTime DateTime;struct DateTime {  sqlite3_int64 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 */  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 iJD 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**** Or the "zulu" notation:****        Z**** If the parse is successful, write the number of minutes** of change in p->tz and return 0.  If a parser error occurs,** return non-zero.**** A missing specifier is not considered an error.*/static int parseTimezone(const char *zDate, DateTime *p){  int sgn = 0;  int nHr, nMn;  int c;  while( isspace(*(u8*)zDate) ){ zDate++; }  p->tz = 0;  c = *zDate;  if( c=='-' ){    sgn = -1;  }else if( c=='+' ){    sgn = +1;  }else if( c=='Z' || c=='z' ){    zDate++;    goto zulu_time;  }else{    return c!=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);zulu_time:  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->iJD = (X1 + X2 + D + B - 1524.5)*86400000;  p->validJD = 1;  if( p->validHMS ){    p->iJD += p->h*3600000 + p->m*60000 + p->s*1000;    if( p->validTZ ){      p->iJD -= p->tz*60000;      p->validYMD = 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;}/*** Set the time to the current time reported by the VFS*/static void setDateTimeToCurrent(sqlite3_context *context, DateTime *p){  double r;  sqlite3 *db = sqlite3_context_db_handle(context);  sqlite3OsCurrentTime(db->pVfs, &r);  p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);  p->validJD = 1;}/*** 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(  sqlite3_context *context,   const char *zDate,   DateTime *p){  if( parseYyyyMmDd(zDate,p)==0 ){    return 0;  }else if( parseHhMmSs(zDate, p)==0 ){    return 0;  }else if( sqlite3StrICmp(zDate,"now")==0){    setDateTimeToCurrent(context, p);    return 0;  }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){    double r;    getValue(zDate, &r);    p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);    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->iJD + 43200000)/86400000;    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 s;  if( p->validHMS ) return;  computeJD(p);  s = (p->iJD + 43200000) % 86400000;  p->s = s/1000.0;  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;}#ifndef SQLITE_OMIT_LOCALTIME/*** Compute the difference (in milliseconds)** between localtime and UTC (a.k.a. GMT)** for the time value p where p is in UTC.*/static int localtimeOffset(DateTime *p){  DateTime x, y;  time_t t;  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.iJD/1000 - 2440587.5*86400.0;#ifdef HAVE_LOCALTIME_R  {    struct tm sLocal;    localtime_r(&t, &sLocal);    y.Y = sLocal.tm_year + 1900;    y.M = sLocal.tm_mon + 1;    y.D = sLocal.tm_mday;    y.h = sLocal.tm_hour;    y.m = sLocal.tm_min;    y.s = sLocal.tm_sec;  }#elif defined(HAVE_LOCALTIME_S)  {    struct tm sLocal;    localtime_s(&sLocal, &t);    y.Y = sLocal.tm_year + 1900;    y.M = sLocal.tm_mon + 1;    y.D = sLocal.tm_mday;    y.h = sLocal.tm_hour;    y.m = sLocal.tm_min;    y.s = sLocal.tm_sec;  }#else  {    struct tm *pTm;    sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));    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;    sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));  }#endif  y.validYMD = 1;  y.validHMS = 1;  y.validJD = 0;  y.validTZ = 0;  computeJD(&y);  return y.iJD - x.iJD;}#endif /* SQLITE_OMIT_LOCALTIME *//*** 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] ){#ifndef SQLITE_OMIT_LOCALTIME    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->iJD += localtimeOffset(p);        clearYMD_HMS_TZ(p);        rc = 0;