/*

** 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.87 2008/07/28 19:34:53 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] ){