/*

** 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': {

      /*

      **    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 && getValue(&z[8],&r)>0

                 && (n=r)==r && n>=0 && r<7 ){

        int Z;

        computeYMD_HMS(p);

        p->validTZ = 0;

        p->validJD = 0;

        computeJD(p);

        Z = p->rJD + 1.5;

        Z %= 7;

        if( Z>n ) Z -= 7;

        p->rJD += n - Z;

        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.

      */