sl@0: /*
sl@0: ** 2003 October 31
sl@0: **
sl@0: ** The author disclaims copyright to this source code.  In place of
sl@0: ** a legal notice, here is a blessing:
sl@0: **
sl@0: **    May you do good and not evil.
sl@0: **    May you find forgiveness for yourself and forgive others.
sl@0: **    May you share freely, never taking more than you give.
sl@0: **
sl@0: *************************************************************************
sl@0: ** This file contains the C functions that implement date and time
sl@0: ** functions for SQLite.  
sl@0: **
sl@0: ** There is only one exported symbol in this file - the function
sl@0: ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
sl@0: ** All other code has file scope.
sl@0: **
sl@0: ** $Id: date.c,v 1.92 2008/10/13 15:35:09 drh Exp $
sl@0: **
sl@0: ** SQLite processes all times and dates as Julian Day numbers.  The
sl@0: ** dates and times are stored as the number of days since noon
sl@0: ** in Greenwich on November 24, 4714 B.C. according to the Gregorian
sl@0: ** calendar system. 
sl@0: **
sl@0: ** 1970-01-01 00:00:00 is JD 2440587.5
sl@0: ** 2000-01-01 00:00:00 is JD 2451544.5
sl@0: **
sl@0: ** This implemention requires years to be expressed as a 4-digit number
sl@0: ** which means that only dates between 0000-01-01 and 9999-12-31 can
sl@0: ** be represented, even though julian day numbers allow a much wider
sl@0: ** range of dates.
sl@0: **
sl@0: ** The Gregorian calendar system is used for all dates and times,
sl@0: ** even those that predate the Gregorian calendar.  Historians usually
sl@0: ** use the Julian calendar for dates prior to 1582-10-15 and for some
sl@0: ** dates afterwards, depending on locale.  Beware of this difference.
sl@0: **
sl@0: ** The conversion algorithms are implemented based on descriptions
sl@0: ** in the following text:
sl@0: **
sl@0: **      Jean Meeus
sl@0: **      Astronomical Algorithms, 2nd Edition, 1998
sl@0: **      ISBM 0-943396-61-1
sl@0: **      Willmann-Bell, Inc
sl@0: **      Richmond, Virginia (USA)
sl@0: */
sl@0: #include "sqliteInt.h"
sl@0: #include <ctype.h>
sl@0: #include <stdlib.h>
sl@0: #include <assert.h>
sl@0: #include <time.h>
sl@0: 
sl@0: #ifndef SQLITE_OMIT_DATETIME_FUNCS
sl@0: 
sl@0: /*
sl@0: ** On recent Windows platforms, the localtime_s() function is available
sl@0: ** as part of the "Secure CRT". It is essentially equivalent to 
sl@0: ** localtime_r() available under most POSIX platforms, except that the 
sl@0: ** order of the parameters is reversed.
sl@0: **
sl@0: ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
sl@0: **
sl@0: ** If the user has not indicated to use localtime_r() or localtime_s()
sl@0: ** already, check for an MSVC build environment that provides 
sl@0: ** localtime_s().
sl@0: */
sl@0: #if !defined(HAVE_LOCALTIME_R) && !defined(HAVE_LOCALTIME_S) && \
sl@0:      defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
sl@0: #define HAVE_LOCALTIME_S 1
sl@0: #endif
sl@0: 
sl@0: /*
sl@0: ** A structure for holding a single date and time.
sl@0: */
sl@0: typedef struct DateTime DateTime;
sl@0: struct DateTime {
sl@0:   sqlite3_int64 iJD; /* The julian day number times 86400000 */
sl@0:   int Y, M, D;       /* Year, month, and day */
sl@0:   int h, m;          /* Hour and minutes */
sl@0:   int tz;            /* Timezone offset in minutes */
sl@0:   double s;          /* Seconds */
sl@0:   char validYMD;     /* True if Y,M,D are valid */
sl@0:   char validHMS;     /* True if h,m,s are valid */
sl@0:   char validJD;      /* True if iJD is valid */
sl@0:   char validTZ;      /* True if tz is valid */
sl@0: };
sl@0: 
sl@0: 
sl@0: /*
sl@0: ** Convert zDate into one or more integers.  Additional arguments
sl@0: ** come in groups of 5 as follows:
sl@0: **
sl@0: **       N       number of digits in the integer
sl@0: **       min     minimum allowed value of the integer
sl@0: **       max     maximum allowed value of the integer
sl@0: **       nextC   first character after the integer
sl@0: **       pVal    where to write the integers value.
sl@0: **
sl@0: ** Conversions continue until one with nextC==0 is encountered.
sl@0: ** The function returns the number of successful conversions.
sl@0: */
sl@0: static int getDigits(const char *zDate, ...){
sl@0:   va_list ap;
sl@0:   int val;
sl@0:   int N;
sl@0:   int min;
sl@0:   int max;
sl@0:   int nextC;
sl@0:   int *pVal;
sl@0:   int cnt = 0;
sl@0:   va_start(ap, zDate);
sl@0:   do{
sl@0:     N = va_arg(ap, int);
sl@0:     min = va_arg(ap, int);
sl@0:     max = va_arg(ap, int);
sl@0:     nextC = va_arg(ap, int);
sl@0:     pVal = va_arg(ap, int*);
sl@0:     val = 0;
sl@0:     while( N-- ){
sl@0:       if( !isdigit(*(u8*)zDate) ){
sl@0:         goto end_getDigits;
sl@0:       }
sl@0:       val = val*10 + *zDate - '0';
sl@0:       zDate++;
sl@0:     }
sl@0:     if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){
sl@0:       goto end_getDigits;
sl@0:     }
sl@0:     *pVal = val;
sl@0:     zDate++;
sl@0:     cnt++;
sl@0:   }while( nextC );
sl@0: end_getDigits:
sl@0:   va_end(ap);
sl@0:   return cnt;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Read text from z[] and convert into a floating point number.  Return
sl@0: ** the number of digits converted.
sl@0: */
sl@0: #define getValue sqlite3AtoF
sl@0: 
sl@0: /*
sl@0: ** Parse a timezone extension on the end of a date-time.
sl@0: ** The extension is of the form:
sl@0: **
sl@0: **        (+/-)HH:MM
sl@0: **
sl@0: ** Or the "zulu" notation:
sl@0: **
sl@0: **        Z
sl@0: **
sl@0: ** If the parse is successful, write the number of minutes
sl@0: ** of change in p->tz and return 0.  If a parser error occurs,
sl@0: ** return non-zero.
sl@0: **
sl@0: ** A missing specifier is not considered an error.
sl@0: */
sl@0: static int parseTimezone(const char *zDate, DateTime *p){
sl@0:   int sgn = 0;
sl@0:   int nHr, nMn;
sl@0:   int c;
sl@0:   while( isspace(*(u8*)zDate) ){ zDate++; }
sl@0:   p->tz = 0;
sl@0:   c = *zDate;
sl@0:   if( c=='-' ){
sl@0:     sgn = -1;
sl@0:   }else if( c=='+' ){
sl@0:     sgn = +1;
sl@0:   }else if( c=='Z' || c=='z' ){
sl@0:     zDate++;
sl@0:     goto zulu_time;
sl@0:   }else{
sl@0:     return c!=0;
sl@0:   }
sl@0:   zDate++;
sl@0:   if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){
sl@0:     return 1;
sl@0:   }
sl@0:   zDate += 5;
sl@0:   p->tz = sgn*(nMn + nHr*60);
sl@0: zulu_time:
sl@0:   while( isspace(*(u8*)zDate) ){ zDate++; }
sl@0:   return *zDate!=0;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
sl@0: ** The HH, MM, and SS must each be exactly 2 digits.  The
sl@0: ** fractional seconds FFFF can be one or more digits.
sl@0: **
sl@0: ** Return 1 if there is a parsing error and 0 on success.
sl@0: */
sl@0: static int parseHhMmSs(const char *zDate, DateTime *p){
sl@0:   int h, m, s;
sl@0:   double ms = 0.0;
sl@0:   if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){
sl@0:     return 1;
sl@0:   }
sl@0:   zDate += 5;
sl@0:   if( *zDate==':' ){
sl@0:     zDate++;
sl@0:     if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
sl@0:       return 1;
sl@0:     }
sl@0:     zDate += 2;
sl@0:     if( *zDate=='.' && isdigit((u8)zDate[1]) ){
sl@0:       double rScale = 1.0;
sl@0:       zDate++;
sl@0:       while( isdigit(*(u8*)zDate) ){
sl@0:         ms = ms*10.0 + *zDate - '0';
sl@0:         rScale *= 10.0;
sl@0:         zDate++;
sl@0:       }
sl@0:       ms /= rScale;
sl@0:     }
sl@0:   }else{
sl@0:     s = 0;
sl@0:   }
sl@0:   p->validJD = 0;
sl@0:   p->validHMS = 1;
sl@0:   p->h = h;
sl@0:   p->m = m;
sl@0:   p->s = s + ms;
sl@0:   if( parseTimezone(zDate, p) ) return 1;
sl@0:   p->validTZ = p->tz!=0;
sl@0:   return 0;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume
sl@0: ** that the YYYY-MM-DD is according to the Gregorian calendar.
sl@0: **
sl@0: ** Reference:  Meeus page 61
sl@0: */
sl@0: static void computeJD(DateTime *p){
sl@0:   int Y, M, D, A, B, X1, X2;
sl@0: 
sl@0:   if( p->validJD ) return;
sl@0:   if( p->validYMD ){
sl@0:     Y = p->Y;
sl@0:     M = p->M;
sl@0:     D = p->D;
sl@0:   }else{
sl@0:     Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */
sl@0:     M = 1;
sl@0:     D = 1;
sl@0:   }
sl@0:   if( M<=2 ){
sl@0:     Y--;
sl@0:     M += 12;
sl@0:   }
sl@0:   A = Y/100;
sl@0:   B = 2 - A + (A/4);
sl@0:   X1 = 365.25*(Y+4716);
sl@0:   X2 = 30.6001*(M+1);
sl@0:   p->iJD = (X1 + X2 + D + B - 1524.5)*86400000;
sl@0:   p->validJD = 1;
sl@0:   if( p->validHMS ){
sl@0:     p->iJD += p->h*3600000 + p->m*60000 + p->s*1000;
sl@0:     if( p->validTZ ){
sl@0:       p->iJD -= p->tz*60000;
sl@0:       p->validYMD = 0;
sl@0:       p->validHMS = 0;
sl@0:       p->validTZ = 0;
sl@0:     }
sl@0:   }
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Parse dates of the form
sl@0: **
sl@0: **     YYYY-MM-DD HH:MM:SS.FFF
sl@0: **     YYYY-MM-DD HH:MM:SS
sl@0: **     YYYY-MM-DD HH:MM
sl@0: **     YYYY-MM-DD
sl@0: **
sl@0: ** Write the result into the DateTime structure and return 0
sl@0: ** on success and 1 if the input string is not a well-formed
sl@0: ** date.
sl@0: */
sl@0: static int parseYyyyMmDd(const char *zDate, DateTime *p){
sl@0:   int Y, M, D, neg;
sl@0: 
sl@0:   if( zDate[0]=='-' ){
sl@0:     zDate++;
sl@0:     neg = 1;
sl@0:   }else{
sl@0:     neg = 0;
sl@0:   }
sl@0:   if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){
sl@0:     return 1;
sl@0:   }
sl@0:   zDate += 10;
sl@0:   while( isspace(*(u8*)zDate) || 'T'==*(u8*)zDate ){ zDate++; }
sl@0:   if( parseHhMmSs(zDate, p)==0 ){
sl@0:     /* We got the time */
sl@0:   }else if( *zDate==0 ){
sl@0:     p->validHMS = 0;
sl@0:   }else{
sl@0:     return 1;
sl@0:   }
sl@0:   p->validJD = 0;
sl@0:   p->validYMD = 1;
sl@0:   p->Y = neg ? -Y : Y;
sl@0:   p->M = M;
sl@0:   p->D = D;
sl@0:   if( p->validTZ ){
sl@0:     computeJD(p);
sl@0:   }
sl@0:   return 0;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Set the time to the current time reported by the VFS
sl@0: */
sl@0: static void setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
sl@0:   double r;
sl@0:   sqlite3 *db = sqlite3_context_db_handle(context);
sl@0:   sqlite3OsCurrentTime(db->pVfs, &r);
sl@0:   p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
sl@0:   p->validJD = 1;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Attempt to parse the given string into a Julian Day Number.  Return
sl@0: ** the number of errors.
sl@0: **
sl@0: ** The following are acceptable forms for the input string:
sl@0: **
sl@0: **      YYYY-MM-DD HH:MM:SS.FFF  +/-HH:MM
sl@0: **      DDDD.DD 
sl@0: **      now
sl@0: **
sl@0: ** In the first form, the +/-HH:MM is always optional.  The fractional
sl@0: ** seconds extension (the ".FFF") is optional.  The seconds portion
sl@0: ** (":SS.FFF") is option.  The year and date can be omitted as long
sl@0: ** as there is a time string.  The time string can be omitted as long
sl@0: ** as there is a year and date.
sl@0: */
sl@0: static int parseDateOrTime(
sl@0:   sqlite3_context *context, 
sl@0:   const char *zDate, 
sl@0:   DateTime *p
sl@0: ){
sl@0:   if( parseYyyyMmDd(zDate,p)==0 ){
sl@0:     return 0;
sl@0:   }else if( parseHhMmSs(zDate, p)==0 ){
sl@0:     return 0;
sl@0:   }else if( sqlite3StrICmp(zDate,"now")==0){
sl@0:     setDateTimeToCurrent(context, p);
sl@0:     return 0;
sl@0:   }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){
sl@0:     double r;
sl@0:     getValue(zDate, &r);
sl@0:     p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
sl@0:     p->validJD = 1;
sl@0:     return 0;
sl@0:   }
sl@0:   return 1;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Compute the Year, Month, and Day from the julian day number.
sl@0: */
sl@0: static void computeYMD(DateTime *p){
sl@0:   int Z, A, B, C, D, E, X1;
sl@0:   if( p->validYMD ) return;
sl@0:   if( !p->validJD ){
sl@0:     p->Y = 2000;
sl@0:     p->M = 1;
sl@0:     p->D = 1;
sl@0:   }else{
sl@0:     Z = (p->iJD + 43200000)/86400000;
sl@0:     A = (Z - 1867216.25)/36524.25;
sl@0:     A = Z + 1 + A - (A/4);
sl@0:     B = A + 1524;
sl@0:     C = (B - 122.1)/365.25;
sl@0:     D = 365.25*C;
sl@0:     E = (B-D)/30.6001;
sl@0:     X1 = 30.6001*E;
sl@0:     p->D = B - D - X1;
sl@0:     p->M = E<14 ? E-1 : E-13;
sl@0:     p->Y = p->M>2 ? C - 4716 : C - 4715;
sl@0:   }
sl@0:   p->validYMD = 1;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Compute the Hour, Minute, and Seconds from the julian day number.
sl@0: */
sl@0: static void computeHMS(DateTime *p){
sl@0:   int s;
sl@0:   if( p->validHMS ) return;
sl@0:   computeJD(p);
sl@0:   s = (p->iJD + 43200000) % 86400000;
sl@0:   p->s = s/1000.0;
sl@0:   s = p->s;
sl@0:   p->s -= s;
sl@0:   p->h = s/3600;
sl@0:   s -= p->h*3600;
sl@0:   p->m = s/60;
sl@0:   p->s += s - p->m*60;
sl@0:   p->validHMS = 1;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Compute both YMD and HMS
sl@0: */
sl@0: static void computeYMD_HMS(DateTime *p){
sl@0:   computeYMD(p);
sl@0:   computeHMS(p);
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Clear the YMD and HMS and the TZ
sl@0: */
sl@0: static void clearYMD_HMS_TZ(DateTime *p){
sl@0:   p->validYMD = 0;
sl@0:   p->validHMS = 0;
sl@0:   p->validTZ = 0;
sl@0: }
sl@0: 
sl@0: #ifndef SQLITE_OMIT_LOCALTIME
sl@0: /*
sl@0: ** Compute the difference (in milliseconds)
sl@0: ** between localtime and UTC (a.k.a. GMT)
sl@0: ** for the time value p where p is in UTC.
sl@0: */
sl@0: static int localtimeOffset(DateTime *p){
sl@0:   DateTime x, y;
sl@0:   time_t t;
sl@0:   x = *p;
sl@0:   computeYMD_HMS(&x);
sl@0:   if( x.Y<1971 || x.Y>=2038 ){
sl@0:     x.Y = 2000;
sl@0:     x.M = 1;
sl@0:     x.D = 1;
sl@0:     x.h = 0;
sl@0:     x.m = 0;
sl@0:     x.s = 0.0;
sl@0:   } else {
sl@0:     int s = x.s + 0.5;
sl@0:     x.s = s;
sl@0:   }
sl@0:   x.tz = 0;
sl@0:   x.validJD = 0;
sl@0:   computeJD(&x);
sl@0:   t = x.iJD/1000 - 2440587.5*86400.0;
sl@0: #ifdef HAVE_LOCALTIME_R
sl@0:   {
sl@0:     struct tm sLocal;
sl@0:     localtime_r(&t, &sLocal);
sl@0:     y.Y = sLocal.tm_year + 1900;
sl@0:     y.M = sLocal.tm_mon + 1;
sl@0:     y.D = sLocal.tm_mday;
sl@0:     y.h = sLocal.tm_hour;
sl@0:     y.m = sLocal.tm_min;
sl@0:     y.s = sLocal.tm_sec;
sl@0:   }
sl@0: #elif defined(HAVE_LOCALTIME_S)
sl@0:   {
sl@0:     struct tm sLocal;
sl@0:     localtime_s(&sLocal, &t);
sl@0:     y.Y = sLocal.tm_year + 1900;
sl@0:     y.M = sLocal.tm_mon + 1;
sl@0:     y.D = sLocal.tm_mday;
sl@0:     y.h = sLocal.tm_hour;
sl@0:     y.m = sLocal.tm_min;
sl@0:     y.s = sLocal.tm_sec;
sl@0:   }
sl@0: #else
sl@0:   {
sl@0:     struct tm *pTm;
sl@0:     sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
sl@0:     pTm = localtime(&t);
sl@0:     y.Y = pTm->tm_year + 1900;
sl@0:     y.M = pTm->tm_mon + 1;
sl@0:     y.D = pTm->tm_mday;
sl@0:     y.h = pTm->tm_hour;
sl@0:     y.m = pTm->tm_min;
sl@0:     y.s = pTm->tm_sec;
sl@0:     sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
sl@0:   }
sl@0: #endif
sl@0:   y.validYMD = 1;
sl@0:   y.validHMS = 1;
sl@0:   y.validJD = 0;
sl@0:   y.validTZ = 0;
sl@0:   computeJD(&y);
sl@0:   return y.iJD - x.iJD;
sl@0: }
sl@0: #endif /* SQLITE_OMIT_LOCALTIME */
sl@0: 
sl@0: /*
sl@0: ** Process a modifier to a date-time stamp.  The modifiers are
sl@0: ** as follows:
sl@0: **
sl@0: **     NNN days
sl@0: **     NNN hours
sl@0: **     NNN minutes
sl@0: **     NNN.NNNN seconds
sl@0: **     NNN months
sl@0: **     NNN years
sl@0: **     start of month
sl@0: **     start of year
sl@0: **     start of week
sl@0: **     start of day
sl@0: **     weekday N
sl@0: **     unixepoch
sl@0: **     localtime
sl@0: **     utc
sl@0: **
sl@0: ** Return 0 on success and 1 if there is any kind of error.
sl@0: */
sl@0: static int parseModifier(const char *zMod, DateTime *p){
sl@0:   int rc = 1;
sl@0:   int n;
sl@0:   double r;
sl@0:   char *z, zBuf[30];
sl@0:   z = zBuf;
sl@0:   for(n=0; n<sizeof(zBuf)-1 && zMod[n]; n++){
sl@0:     z[n] = tolower(zMod[n]);
sl@0:   }
sl@0:   z[n] = 0;
sl@0:   switch( z[0] ){
sl@0: #ifndef SQLITE_OMIT_LOCALTIME
sl@0:     case 'l': {
sl@0:       /*    localtime
sl@0:       **
sl@0:       ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
sl@0:       ** show local time.
sl@0:       */
sl@0:       if( strcmp(z, "localtime")==0 ){
sl@0:         computeJD(p);
sl@0:         p->iJD += localtimeOffset(p);
sl@0:         clearYMD_HMS_TZ(p);
sl@0:         rc = 0;
sl@0:       }
sl@0:       break;
sl@0:     }
sl@0: #endif
sl@0:     case 'u': {
sl@0:       /*
sl@0:       **    unixepoch
sl@0:       **
sl@0:       ** Treat the current value of p->iJD as the number of
sl@0:       ** seconds since 1970.  Convert to a real julian day number.
sl@0:       */
sl@0:       if( strcmp(z, "unixepoch")==0 && p->validJD ){
sl@0:         p->iJD = p->iJD/86400.0 + 2440587.5*86400000.0;
sl@0:         clearYMD_HMS_TZ(p);
sl@0:         rc = 0;
sl@0:       }
sl@0: #ifndef SQLITE_OMIT_LOCALTIME
sl@0:       else if( strcmp(z, "utc")==0 ){
sl@0:         int c1;
sl@0:         computeJD(p);
sl@0:         c1 = localtimeOffset(p);
sl@0:         p->iJD -= c1;
sl@0:         clearYMD_HMS_TZ(p);
sl@0:         p->iJD += c1 - localtimeOffset(p);
sl@0:         rc = 0;
sl@0:       }
sl@0: #endif
sl@0:       break;
sl@0:     }
sl@0:     case 'w': {
sl@0:       /*
sl@0:       **    weekday N
sl@0:       **
sl@0:       ** Move the date to the same time on the next occurrence of
sl@0:       ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
sl@0:       ** date is already on the appropriate weekday, this is a no-op.
sl@0:       */
sl@0:       if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0
sl@0:                  && (n=r)==r && n>=0 && r<7 ){
sl@0:         sqlite3_int64 Z;
sl@0:         computeYMD_HMS(p);
sl@0:         p->validTZ = 0;
sl@0:         p->validJD = 0;
sl@0:         computeJD(p);
sl@0:         Z = ((p->iJD + 129600000)/86400000) % 7;
sl@0:         if( Z>n ) Z -= 7;
sl@0:         p->iJD += (n - Z)*86400000;
sl@0:         clearYMD_HMS_TZ(p);
sl@0:         rc = 0;
sl@0:       }
sl@0:       break;
sl@0:     }
sl@0:     case 's': {
sl@0:       /*
sl@0:       **    start of TTTTT
sl@0:       **
sl@0:       ** Move the date backwards to the beginning of the current day,
sl@0:       ** or month or year.
sl@0:       */
sl@0:       if( strncmp(z, "start of ", 9)!=0 ) break;
sl@0:       z += 9;
sl@0:       computeYMD(p);
sl@0:       p->validHMS = 1;
sl@0:       p->h = p->m = 0;
sl@0:       p->s = 0.0;
sl@0:       p->validTZ = 0;
sl@0:       p->validJD = 0;
sl@0:       if( strcmp(z,"month")==0 ){
sl@0:         p->D = 1;
sl@0:         rc = 0;
sl@0:       }else if( strcmp(z,"year")==0 ){
sl@0:         computeYMD(p);
sl@0:         p->M = 1;
sl@0:         p->D = 1;
sl@0:         rc = 0;
sl@0:       }else if( strcmp(z,"day")==0 ){
sl@0:         rc = 0;
sl@0:       }
sl@0:       break;
sl@0:     }
sl@0:     case '+':
sl@0:     case '-':
sl@0:     case '0':
sl@0:     case '1':
sl@0:     case '2':
sl@0:     case '3':
sl@0:     case '4':
sl@0:     case '5':
sl@0:     case '6':
sl@0:     case '7':
sl@0:     case '8':
sl@0:     case '9': {
sl@0:       n = getValue(z, &r);
sl@0:       assert( n>=1 );
sl@0:       if( z[n]==':' ){
sl@0:         /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
sl@0:         ** specified number of hours, minutes, seconds, and fractional seconds
sl@0:         ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
sl@0:         ** omitted.
sl@0:         */
sl@0:         const char *z2 = z;
sl@0:         DateTime tx;
sl@0:         sqlite3_int64 day;
sl@0:         if( !isdigit(*(u8*)z2) ) z2++;
sl@0:         memset(&tx, 0, sizeof(tx));
sl@0:         if( parseHhMmSs(z2, &tx) ) break;
sl@0:         computeJD(&tx);
sl@0:         tx.iJD -= 43200000;
sl@0:         day = tx.iJD/86400000;
sl@0:         tx.iJD -= day*86400000;
sl@0:         if( z[0]=='-' ) tx.iJD = -tx.iJD;
sl@0:         computeJD(p);
sl@0:         clearYMD_HMS_TZ(p);
sl@0:         p->iJD += tx.iJD;
sl@0:         rc = 0;
sl@0:         break;
sl@0:       }
sl@0:       z += n;
sl@0:       while( isspace(*(u8*)z) ) z++;
sl@0:       n = strlen(z);
sl@0:       if( n>10 || n<3 ) break;
sl@0:       if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
sl@0:       computeJD(p);
sl@0:       rc = 0;
sl@0:       if( n==3 && strcmp(z,"day")==0 ){
sl@0:         p->iJD += r*86400000.0 + 0.5;
sl@0:       }else if( n==4 && strcmp(z,"hour")==0 ){
sl@0:         p->iJD += r*(86400000.0/24.0) + 0.5;
sl@0:       }else if( n==6 && strcmp(z,"minute")==0 ){
sl@0:         p->iJD += r*(86400000.0/(24.0*60.0)) + 0.5;
sl@0:       }else if( n==6 && strcmp(z,"second")==0 ){
sl@0:         p->iJD += r*(86400000.0/(24.0*60.0*60.0)) + 0.5;
sl@0:       }else if( n==5 && strcmp(z,"month")==0 ){
sl@0:         int x, y;
sl@0:         computeYMD_HMS(p);
sl@0:         p->M += r;
sl@0:         x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
sl@0:         p->Y += x;
sl@0:         p->M -= x*12;
sl@0:         p->validJD = 0;
sl@0:         computeJD(p);
sl@0:         y = r;
sl@0:         if( y!=r ){
sl@0:           p->iJD += (r - y)*30.0*86400000.0 + 0.5;
sl@0:         }
sl@0:       }else if( n==4 && strcmp(z,"year")==0 ){
sl@0:         computeYMD_HMS(p);
sl@0:         p->Y += r;
sl@0:         p->validJD = 0;
sl@0:         computeJD(p);
sl@0:       }else{
sl@0:         rc = 1;
sl@0:       }
sl@0:       clearYMD_HMS_TZ(p);
sl@0:       break;
sl@0:     }
sl@0:     default: {
sl@0:       break;
sl@0:     }
sl@0:   }
sl@0:   return rc;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Process time function arguments.  argv[0] is a date-time stamp.
sl@0: ** argv[1] and following are modifiers.  Parse them all and write
sl@0: ** the resulting time into the DateTime structure p.  Return 0
sl@0: ** on success and 1 if there are any errors.
sl@0: **
sl@0: ** If there are zero parameters (if even argv[0] is undefined)
sl@0: ** then assume a default value of "now" for argv[0].
sl@0: */
sl@0: static int isDate(
sl@0:   sqlite3_context *context, 
sl@0:   int argc, 
sl@0:   sqlite3_value **argv, 
sl@0:   DateTime *p
sl@0: ){
sl@0:   int i;
sl@0:   const unsigned char *z;
sl@0:   int eType;
sl@0:   memset(p, 0, sizeof(*p));
sl@0:   if( argc==0 ){
sl@0:     setDateTimeToCurrent(context, p);
sl@0:   }else if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
sl@0:                    || eType==SQLITE_INTEGER ){
sl@0:     p->iJD = sqlite3_value_double(argv[0])*86400000.0 + 0.5;
sl@0:     p->validJD = 1;
sl@0:   }else{
sl@0:     z = sqlite3_value_text(argv[0]);
sl@0:     if( !z || parseDateOrTime(context, (char*)z, p) ){
sl@0:       return 1;
sl@0:     }
sl@0:   }
sl@0:   for(i=1; i<argc; i++){
sl@0:     if( (z = sqlite3_value_text(argv[i]))==0 || parseModifier((char*)z, p) ){
sl@0:       return 1;
sl@0:     }
sl@0:   }
sl@0:   return 0;
sl@0: }
sl@0: 
sl@0: 
sl@0: /*
sl@0: ** The following routines implement the various date and time functions
sl@0: ** of SQLite.
sl@0: */
sl@0: 
sl@0: /*
sl@0: **    julianday( TIMESTRING, MOD, MOD, ...)
sl@0: **
sl@0: ** Return the julian day number of the date specified in the arguments
sl@0: */
sl@0: static void juliandayFunc(
sl@0:   sqlite3_context *context,
sl@0:   int argc,
sl@0:   sqlite3_value **argv
sl@0: ){
sl@0:   DateTime x;
sl@0:   if( isDate(context, argc, argv, &x)==0 ){
sl@0:     computeJD(&x);
sl@0:     sqlite3_result_double(context, x.iJD/86400000.0);
sl@0:   }
sl@0: }
sl@0: 
sl@0: /*
sl@0: **    datetime( TIMESTRING, MOD, MOD, ...)
sl@0: **
sl@0: ** Return YYYY-MM-DD HH:MM:SS
sl@0: */
sl@0: static void datetimeFunc(
sl@0:   sqlite3_context *context,
sl@0:   int argc,
sl@0:   sqlite3_value **argv
sl@0: ){
sl@0:   DateTime x;
sl@0:   if( isDate(context, argc, argv, &x)==0 ){
sl@0:     char zBuf[100];
sl@0:     computeYMD_HMS(&x);
sl@0:     sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d",
sl@0:                      x.Y, x.M, x.D, x.h, x.m, (int)(x.s));
sl@0:     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
sl@0:   }
sl@0: }
sl@0: 
sl@0: /*
sl@0: **    time( TIMESTRING, MOD, MOD, ...)
sl@0: **
sl@0: ** Return HH:MM:SS
sl@0: */
sl@0: static void timeFunc(
sl@0:   sqlite3_context *context,
sl@0:   int argc,
sl@0:   sqlite3_value **argv
sl@0: ){
sl@0:   DateTime x;
sl@0:   if( isDate(context, argc, argv, &x)==0 ){
sl@0:     char zBuf[100];
sl@0:     computeHMS(&x);
sl@0:     sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
sl@0:     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
sl@0:   }
sl@0: }
sl@0: 
sl@0: /*
sl@0: **    date( TIMESTRING, MOD, MOD, ...)
sl@0: **
sl@0: ** Return YYYY-MM-DD
sl@0: */
sl@0: static void dateFunc(
sl@0:   sqlite3_context *context,
sl@0:   int argc,
sl@0:   sqlite3_value **argv
sl@0: ){
sl@0:   DateTime x;
sl@0:   if( isDate(context, argc, argv, &x)==0 ){
sl@0:     char zBuf[100];
sl@0:     computeYMD(&x);
sl@0:     sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
sl@0:     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
sl@0:   }
sl@0: }
sl@0: 
sl@0: /*
sl@0: **    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
sl@0: **
sl@0: ** Return a string described by FORMAT.  Conversions as follows:
sl@0: **
sl@0: **   %d  day of month
sl@0: **   %f  ** fractional seconds  SS.SSS
sl@0: **   %H  hour 00-24
sl@0: **   %j  day of year 000-366
sl@0: **   %J  ** Julian day number
sl@0: **   %m  month 01-12
sl@0: **   %M  minute 00-59
sl@0: **   %s  seconds since 1970-01-01
sl@0: **   %S  seconds 00-59
sl@0: **   %w  day of week 0-6  sunday==0
sl@0: **   %W  week of year 00-53
sl@0: **   %Y  year 0000-9999
sl@0: **   %%  %
sl@0: */
sl@0: static void strftimeFunc(
sl@0:   sqlite3_context *context,
sl@0:   int argc,
sl@0:   sqlite3_value **argv
sl@0: ){
sl@0:   DateTime x;
sl@0:   u64 n;
sl@0:   int i, j;
sl@0:   char *z;
sl@0:   sqlite3 *db;
sl@0:   const char *zFmt = (const char*)sqlite3_value_text(argv[0]);
sl@0:   char zBuf[100];
sl@0:   if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
sl@0:   db = sqlite3_context_db_handle(context);
sl@0:   for(i=0, n=1; zFmt[i]; i++, n++){
sl@0:     if( zFmt[i]=='%' ){
sl@0:       switch( zFmt[i+1] ){
sl@0:         case 'd':
sl@0:         case 'H':
sl@0:         case 'm':
sl@0:         case 'M':
sl@0:         case 'S':
sl@0:         case 'W':
sl@0:           n++;
sl@0:           /* fall thru */
sl@0:         case 'w':
sl@0:         case '%':
sl@0:           break;
sl@0:         case 'f':
sl@0:           n += 8;
sl@0:           break;
sl@0:         case 'j':
sl@0:           n += 3;
sl@0:           break;
sl@0:         case 'Y':
sl@0:           n += 8;
sl@0:           break;
sl@0:         case 's':
sl@0:         case 'J':
sl@0:           n += 50;
sl@0:           break;
sl@0:         default:
sl@0:           return;  /* ERROR.  return a NULL */
sl@0:       }
sl@0:       i++;
sl@0:     }
sl@0:   }
sl@0:   if( n<sizeof(zBuf) ){
sl@0:     z = zBuf;
sl@0:   }else if( n>db->aLimit[SQLITE_LIMIT_LENGTH] ){
sl@0:     sqlite3_result_error_toobig(context);
sl@0:     return;
sl@0:   }else{
sl@0:     z = sqlite3DbMallocRaw(db, n);
sl@0:     if( z==0 ){
sl@0:       sqlite3_result_error_nomem(context);
sl@0:       return;
sl@0:     }
sl@0:   }
sl@0:   computeJD(&x);
sl@0:   computeYMD_HMS(&x);
sl@0:   for(i=j=0; zFmt[i]; i++){
sl@0:     if( zFmt[i]!='%' ){
sl@0:       z[j++] = zFmt[i];
sl@0:     }else{
sl@0:       i++;
sl@0:       switch( zFmt[i] ){
sl@0:         case 'd':  sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break;
sl@0:         case 'f': {
sl@0:           double s = x.s;
sl@0:           if( s>59.999 ) s = 59.999;
sl@0:           sqlite3_snprintf(7, &z[j],"%06.3f", s);
sl@0:           j += strlen(&z[j]);
sl@0:           break;
sl@0:         }
sl@0:         case 'H':  sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break;
sl@0:         case 'W': /* Fall thru */
sl@0:         case 'j': {
sl@0:           int nDay;             /* Number of days since 1st day of year */
sl@0:           DateTime y = x;
sl@0:           y.validJD = 0;
sl@0:           y.M = 1;
sl@0:           y.D = 1;
sl@0:           computeJD(&y);
sl@0:           nDay = (x.iJD - y.iJD)/86400000.0 + 0.5;
sl@0:           if( zFmt[i]=='W' ){
sl@0:             int wd;   /* 0=Monday, 1=Tuesday, ... 6=Sunday */
sl@0:             wd = ((x.iJD+43200000)/86400000) % 7;
sl@0:             sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);
sl@0:             j += 2;
sl@0:           }else{
sl@0:             sqlite3_snprintf(4, &z[j],"%03d",nDay+1);
sl@0:             j += 3;
sl@0:           }
sl@0:           break;
sl@0:         }
sl@0:         case 'J': {
sl@0:           sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0);
sl@0:           j+=strlen(&z[j]);
sl@0:           break;
sl@0:         }
sl@0:         case 'm':  sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;
sl@0:         case 'M':  sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;
sl@0:         case 's': {
sl@0:           sqlite3_snprintf(30,&z[j],"%d",
sl@0:                            (int)(x.iJD/1000.0 - 210866760000.0));
sl@0:           j += strlen(&z[j]);
sl@0:           break;
sl@0:         }
sl@0:         case 'S':  sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
sl@0:         case 'w':  z[j++] = (((x.iJD+129600000)/86400000) % 7) + '0'; break;
sl@0:         case 'Y':  sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=strlen(&z[j]);break;
sl@0:         default:   z[j++] = '%'; break;
sl@0:       }
sl@0:     }
sl@0:   }
sl@0:   z[j] = 0;
sl@0:   sqlite3_result_text(context, z, -1,
sl@0:                       z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC);
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** current_time()
sl@0: **
sl@0: ** This function returns the same value as time('now').
sl@0: */
sl@0: static void ctimeFunc(
sl@0:   sqlite3_context *context,
sl@0:   int argc,
sl@0:   sqlite3_value **argv
sl@0: ){
sl@0:   timeFunc(context, 0, 0);
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** current_date()
sl@0: **
sl@0: ** This function returns the same value as date('now').
sl@0: */
sl@0: static void cdateFunc(
sl@0:   sqlite3_context *context,
sl@0:   int argc,
sl@0:   sqlite3_value **argv
sl@0: ){
sl@0:   dateFunc(context, 0, 0);
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** current_timestamp()
sl@0: **
sl@0: ** This function returns the same value as datetime('now').
sl@0: */
sl@0: static void ctimestampFunc(
sl@0:   sqlite3_context *context,
sl@0:   int argc,
sl@0:   sqlite3_value **argv
sl@0: ){
sl@0:   datetimeFunc(context, 0, 0);
sl@0: }
sl@0: #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
sl@0: 
sl@0: #ifdef SQLITE_OMIT_DATETIME_FUNCS
sl@0: /*
sl@0: ** If the library is compiled to omit the full-scale date and time
sl@0: ** handling (to get a smaller binary), the following minimal version
sl@0: ** of the functions current_time(), current_date() and current_timestamp()
sl@0: ** are included instead. This is to support column declarations that
sl@0: ** include "DEFAULT CURRENT_TIME" etc.
sl@0: **
sl@0: ** This function uses the C-library functions time(), gmtime()
sl@0: ** and strftime(). The format string to pass to strftime() is supplied
sl@0: ** as the user-data for the function.
sl@0: */
sl@0: static void currentTimeFunc(
sl@0:   sqlite3_context *context,
sl@0:   int argc,
sl@0:   sqlite3_value **argv
sl@0: ){
sl@0:   time_t t;
sl@0:   char *zFormat = (char *)sqlite3_user_data(context);
sl@0:   sqlite3 *db;
sl@0:   double rT;
sl@0:   char zBuf[20];
sl@0: 
sl@0:   db = sqlite3_context_db_handle(context);
sl@0:   sqlite3OsCurrentTime(db->pVfs, &rT);
sl@0:   t = 86400.0*(rT - 2440587.5) + 0.5;
sl@0: #ifdef HAVE_GMTIME_R
sl@0:   {
sl@0:     struct tm sNow;
sl@0:     gmtime_r(&t, &sNow);
sl@0:     strftime(zBuf, 20, zFormat, &sNow);
sl@0:   }
sl@0: #else
sl@0:   {
sl@0:     struct tm *pTm;
sl@0:     sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
sl@0:     pTm = gmtime(&t);
sl@0:     strftime(zBuf, 20, zFormat, pTm);
sl@0:     sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
sl@0:   }
sl@0: #endif
sl@0: 
sl@0:   sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
sl@0: }
sl@0: #endif
sl@0: 
sl@0: /*
sl@0: ** This function registered all of the above C functions as SQL
sl@0: ** functions.  This should be the only routine in this file with
sl@0: ** external linkage.
sl@0: */
sl@0: void sqlite3RegisterDateTimeFunctions(void){
sl@0:   static SQLITE_WSD FuncDef aDateTimeFuncs[] = {
sl@0: #ifndef SQLITE_OMIT_DATETIME_FUNCS
sl@0:     FUNCTION(julianday,        -1, 0, 0, juliandayFunc ),
sl@0:     FUNCTION(date,             -1, 0, 0, dateFunc      ),
sl@0:     FUNCTION(time,             -1, 0, 0, timeFunc      ),
sl@0:     FUNCTION(datetime,         -1, 0, 0, datetimeFunc  ),
sl@0:     FUNCTION(strftime,         -1, 0, 0, strftimeFunc  ),
sl@0:     FUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
sl@0:     FUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
sl@0:     FUNCTION(current_date,      0, 0, 0, cdateFunc     ),
sl@0: #else
sl@0:     STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
sl@0:     STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d",          0, currentTimeFunc),
sl@0:     STR_FUNCTION(current_date,      0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
sl@0: #endif
sl@0:   };
sl@0:   int i;
sl@0:   FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions);
sl@0:   FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aDateTimeFuncs);
sl@0: 
sl@0:   for(i=0; i<ArraySize(aDateTimeFuncs); i++){
sl@0:     sqlite3FuncDefInsert(pHash, &aFunc[i]);
sl@0:   }
sl@0: }