1 /*

     2 ** 2003 October 31

     3 **

     4 ** The author disclaims copyright to this source code.  In place of

     5 ** a legal notice, here is a blessing:

     6 **

     7 **    May you do good and not evil.

     8 **    May you find forgiveness for yourself and forgive others.

     9 **    May you share freely, never taking more than you give.

    10 **

    11 *************************************************************************

    12 ** This file contains the C functions that implement date and time

    13 ** functions for SQLite.  

    14 **

    15 ** There is only one exported symbol in this file - the function

    16 ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.

    17 ** All other code has file scope.

    18 **

    19 ** $Id: date.c,v 1.92 2008/10/13 15:35:09 drh Exp $

    20 **

    21 ** SQLite processes all times and dates as Julian Day numbers.  The

    22 ** dates and times are stored as the number of days since noon

    23 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian

    24 ** calendar system. 

    25 **

    26 ** 1970-01-01 00:00:00 is JD 2440587.5

    27 ** 2000-01-01 00:00:00 is JD 2451544.5

    28 **

    29 ** This implemention requires years to be expressed as a 4-digit number

    30 ** which means that only dates between 0000-01-01 and 9999-12-31 can

    31 ** be represented, even though julian day numbers allow a much wider

    32 ** range of dates.

    33 **

    34 ** The Gregorian calendar system is used for all dates and times,

    35 ** even those that predate the Gregorian calendar.  Historians usually

    36 ** use the Julian calendar for dates prior to 1582-10-15 and for some

    37 ** dates afterwards, depending on locale.  Beware of this difference.

    38 **

    39 ** The conversion algorithms are implemented based on descriptions

    40 ** in the following text:

    41 **

    42 **      Jean Meeus

    43 **      Astronomical Algorithms, 2nd Edition, 1998

    44 **      ISBM 0-943396-61-1

    45 **      Willmann-Bell, Inc

    46 **      Richmond, Virginia (USA)

    47 */

    48 #include "sqliteInt.h"

    49 #include <ctype.h>

    50 #include <stdlib.h>

    51 #include <assert.h>

    52 #include <time.h>

    53 

    54 #ifndef SQLITE_OMIT_DATETIME_FUNCS

    55 

    56 /*

    57 ** On recent Windows platforms, the localtime_s() function is available

    58 ** as part of the "Secure CRT". It is essentially equivalent to 

    59 ** localtime_r() available under most POSIX platforms, except that the 

    60 ** order of the parameters is reversed.

    61 **

    62 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.

    63 **

    64 ** If the user has not indicated to use localtime_r() or localtime_s()

    65 ** already, check for an MSVC build environment that provides 

    66 ** localtime_s().

    67 */

    68 #if !defined(HAVE_LOCALTIME_R) && !defined(HAVE_LOCALTIME_S) && \

    69      defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)

    70 #define HAVE_LOCALTIME_S 1

    71 #endif

    72 

    73 /*

    74 ** A structure for holding a single date and time.

    75 */

    76 typedef struct DateTime DateTime;

    77 struct DateTime {

    78   sqlite3_int64 iJD; /* The julian day number times 86400000 */

    79   int Y, M, D;       /* Year, month, and day */

    80   int h, m;          /* Hour and minutes */

    81   int tz;            /* Timezone offset in minutes */

    82   double s;          /* Seconds */

    83   char validYMD;     /* True if Y,M,D are valid */

    84   char validHMS;     /* True if h,m,s are valid */

    85   char validJD;      /* True if iJD is valid */

    86   char validTZ;      /* True if tz is valid */

    87 };

    88 

    89 

    90 /*

    91 ** Convert zDate into one or more integers.  Additional arguments

    92 ** come in groups of 5 as follows:

    93 **

    94 **       N       number of digits in the integer

    95 **       min     minimum allowed value of the integer

    96 **       max     maximum allowed value of the integer

    97 **       nextC   first character after the integer

    98 **       pVal    where to write the integers value.

    99 **

   100 ** Conversions continue until one with nextC==0 is encountered.

   101 ** The function returns the number of successful conversions.

   102 */

   103 static int getDigits(const char *zDate, ...){

   104   va_list ap;

   105   int val;

   106   int N;

   107   int min;

   108   int max;

   109   int nextC;

   110   int *pVal;

   111   int cnt = 0;

   112   va_start(ap, zDate);

   113   do{

   114     N = va_arg(ap, int);

   115     min = va_arg(ap, int);

   116     max = va_arg(ap, int);

   117     nextC = va_arg(ap, int);

   118     pVal = va_arg(ap, int*);

   119     val = 0;

   120     while( N-- ){

   121       if( !isdigit(*(u8*)zDate) ){

   122         goto end_getDigits;

   123       }

   124       val = val*10 + *zDate - '0';

   125       zDate++;

   126     }

   127     if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){

   128       goto end_getDigits;

   129     }

   130     *pVal = val;

   131     zDate++;

   132     cnt++;

   133   }while( nextC );

   134 end_getDigits:

   135   va_end(ap);

   136   return cnt;

   137 }

   138 

   139 /*

   140 ** Read text from z[] and convert into a floating point number.  Return

   141 ** the number of digits converted.

   142 */

   143 #define getValue sqlite3AtoF

   144 

   145 /*

   146 ** Parse a timezone extension on the end of a date-time.

   147 ** The extension is of the form:

   148 **

   149 **        (+/-)HH:MM

   150 **

   151 ** Or the "zulu" notation:

   152 **

   153 **        Z

   154 **

   155 ** If the parse is successful, write the number of minutes

   156 ** of change in p->tz and return 0.  If a parser error occurs,

   157 ** return non-zero.

   158 **

   159 ** A missing specifier is not considered an error.

   160 */

   161 static int parseTimezone(const char *zDate, DateTime *p){

   162   int sgn = 0;

   163   int nHr, nMn;

   164   int c;

   165   while( isspace(*(u8*)zDate) ){ zDate++; }

   166   p->tz = 0;

   167   c = *zDate;

   168   if( c=='-' ){

   169     sgn = -1;

   170   }else if( c=='+' ){

   171     sgn = +1;

   172   }else if( c=='Z' || c=='z' ){

   173     zDate++;

   174     goto zulu_time;

   175   }else{

   176     return c!=0;

   177   }

   178   zDate++;

   179   if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){

   180     return 1;

   181   }

   182   zDate += 5;

   183   p->tz = sgn*(nMn + nHr*60);

   184 zulu_time:

   185   while( isspace(*(u8*)zDate) ){ zDate++; }

   186   return *zDate!=0;

   187 }

   188 

   189 /*

   190 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.

   191 ** The HH, MM, and SS must each be exactly 2 digits.  The

   192 ** fractional seconds FFFF can be one or more digits.

   193 **

   194 ** Return 1 if there is a parsing error and 0 on success.

   195 */

   196 static int parseHhMmSs(const char *zDate, DateTime *p){

   197   int h, m, s;

   198   double ms = 0.0;

   199   if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){

   200     return 1;

   201   }

   202   zDate += 5;

   203   if( *zDate==':' ){

   204     zDate++;

   205     if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){

   206       return 1;

   207     }

   208     zDate += 2;

   209     if( *zDate=='.' && isdigit((u8)zDate[1]) ){

   210       double rScale = 1.0;

   211       zDate++;

   212       while( isdigit(*(u8*)zDate) ){

   213         ms = ms*10.0 + *zDate - '0';

   214         rScale *= 10.0;

   215         zDate++;

   216       }

   217       ms /= rScale;

   218     }

   219   }else{

   220     s = 0;

   221   }

   222   p->validJD = 0;

   223   p->validHMS = 1;

   224   p->h = h;

   225   p->m = m;

   226   p->s = s + ms;

   227   if( parseTimezone(zDate, p) ) return 1;

   228   p->validTZ = p->tz!=0;

   229   return 0;

   230 }

   231 

   232 /*

   233 ** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume

   234 ** that the YYYY-MM-DD is according to the Gregorian calendar.

   235 **

   236 ** Reference:  Meeus page 61

   237 */

   238 static void computeJD(DateTime *p){

   239   int Y, M, D, A, B, X1, X2;

   240 

   241   if( p->validJD ) return;

   242   if( p->validYMD ){

   243     Y = p->Y;

   244     M = p->M;

   245     D = p->D;

   246   }else{

   247     Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */

   248     M = 1;

   249     D = 1;

   250   }

   251   if( M<=2 ){

   252     Y--;

   253     M += 12;

   254   }

   255   A = Y/100;

   256   B = 2 - A + (A/4);

   257   X1 = 365.25*(Y+4716);

   258   X2 = 30.6001*(M+1);

   259   p->iJD = (X1 + X2 + D + B - 1524.5)*86400000;

   260   p->validJD = 1;

   261   if( p->validHMS ){

   262     p->iJD += p->h*3600000 + p->m*60000 + p->s*1000;

   263     if( p->validTZ ){

   264       p->iJD -= p->tz*60000;

   265       p->validYMD = 0;

   266       p->validHMS = 0;

   267       p->validTZ = 0;

   268     }

   269   }

   270 }

   271 

   272 /*

   273 ** Parse dates of the form

   274 **

   275 **     YYYY-MM-DD HH:MM:SS.FFF

   276 **     YYYY-MM-DD HH:MM:SS

   277 **     YYYY-MM-DD HH:MM

   278 **     YYYY-MM-DD

   279 **

   280 ** Write the result into the DateTime structure and return 0

   281 ** on success and 1 if the input string is not a well-formed

   282 ** date.

   283 */

   284 static int parseYyyyMmDd(const char *zDate, DateTime *p){

   285   int Y, M, D, neg;

   286 

   287   if( zDate[0]=='-' ){

   288     zDate++;

   289     neg = 1;

   290   }else{

   291     neg = 0;

   292   }

   293   if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){

   294     return 1;

   295   }

   296   zDate += 10;

   297   while( isspace(*(u8*)zDate) || 'T'==*(u8*)zDate ){ zDate++; }

   298   if( parseHhMmSs(zDate, p)==0 ){

   299     /* We got the time */

   300   }else if( *zDate==0 ){

   301     p->validHMS = 0;

   302   }else{

   303     return 1;

   304   }

   305   p->validJD = 0;

   306   p->validYMD = 1;

   307   p->Y = neg ? -Y : Y;

   308   p->M = M;

   309   p->D = D;

   310   if( p->validTZ ){

   311     computeJD(p);

   312   }

   313   return 0;

   314 }

   315 

   316 /*

   317 ** Set the time to the current time reported by the VFS

   318 */

   319 static void setDateTimeToCurrent(sqlite3_context *context, DateTime *p){

   320   double r;

   321   sqlite3 *db = sqlite3_context_db_handle(context);

   322   sqlite3OsCurrentTime(db->pVfs, &r);

   323   p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);

   324   p->validJD = 1;

   325 }

   326 

   327 /*

   328 ** Attempt to parse the given string into a Julian Day Number.  Return

   329 ** the number of errors.

   330 **

   331 ** The following are acceptable forms for the input string:

   332 **

   333 **      YYYY-MM-DD HH:MM:SS.FFF  +/-HH:MM

   334 **      DDDD.DD 

   335 **      now

   336 **

   337 ** In the first form, the +/-HH:MM is always optional.  The fractional

   338 ** seconds extension (the ".FFF") is optional.  The seconds portion

   339 ** (":SS.FFF") is option.  The year and date can be omitted as long

   340 ** as there is a time string.  The time string can be omitted as long

   341 ** as there is a year and date.

   342 */

   343 static int parseDateOrTime(

   344   sqlite3_context *context, 

   345   const char *zDate, 

   346   DateTime *p

   347 ){

   348   if( parseYyyyMmDd(zDate,p)==0 ){

   349     return 0;

   350   }else if( parseHhMmSs(zDate, p)==0 ){

   351     return 0;

   352   }else if( sqlite3StrICmp(zDate,"now")==0){

   353     setDateTimeToCurrent(context, p);

   354     return 0;

   355   }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){

   356     double r;

   357     getValue(zDate, &r);

   358     p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);

   359     p->validJD = 1;

   360     return 0;

   361   }

   362   return 1;

   363 }

   364 

   365 /*

   366 ** Compute the Year, Month, and Day from the julian day number.

   367 */

   368 static void computeYMD(DateTime *p){

   369   int Z, A, B, C, D, E, X1;

   370   if( p->validYMD ) return;

   371   if( !p->validJD ){

   372     p->Y = 2000;

   373     p->M = 1;

   374     p->D = 1;

   375   }else{

   376     Z = (p->iJD + 43200000)/86400000;

   377     A = (Z - 1867216.25)/36524.25;

   378     A = Z + 1 + A - (A/4);

   379     B = A + 1524;

   380     C = (B - 122.1)/365.25;

   381     D = 365.25*C;

   382     E = (B-D)/30.6001;

   383     X1 = 30.6001*E;

   384     p->D = B - D - X1;

   385     p->M = E<14 ? E-1 : E-13;

   386     p->Y = p->M>2 ? C - 4716 : C - 4715;

   387   }

   388   p->validYMD = 1;

   389 }

   390 

   391 /*

   392 ** Compute the Hour, Minute, and Seconds from the julian day number.

   393 */

   394 static void computeHMS(DateTime *p){

   395   int s;

   396   if( p->validHMS ) return;

   397   computeJD(p);

   398   s = (p->iJD + 43200000) % 86400000;

   399   p->s = s/1000.0;

   400   s = p->s;

   401   p->s -= s;

   402   p->h = s/3600;

   403   s -= p->h*3600;

   404   p->m = s/60;

   405   p->s += s - p->m*60;

   406   p->validHMS = 1;

   407 }

   408 

   409 /*

   410 ** Compute both YMD and HMS

   411 */

   412 static void computeYMD_HMS(DateTime *p){

   413   computeYMD(p);

   414   computeHMS(p);

   415 }

   416 

   417 /*

   418 ** Clear the YMD and HMS and the TZ

   419 */

   420 static void clearYMD_HMS_TZ(DateTime *p){

   421   p->validYMD = 0;

   422   p->validHMS = 0;

   423   p->validTZ = 0;

   424 }

   425 

   426 #ifndef SQLITE_OMIT_LOCALTIME

   427 /*

   428 ** Compute the difference (in milliseconds)

   429 ** between localtime and UTC (a.k.a. GMT)

   430 ** for the time value p where p is in UTC.

   431 */

   432 static int localtimeOffset(DateTime *p){

   433   DateTime x, y;

   434   time_t t;

   435   x = *p;

   436   computeYMD_HMS(&x);

   437   if( x.Y<1971 || x.Y>=2038 ){

   438     x.Y = 2000;

   439     x.M = 1;

   440     x.D = 1;

   441     x.h = 0;

   442     x.m = 0;

   443     x.s = 0.0;

   444   } else {

   445     int s = x.s + 0.5;

   446     x.s = s;

   447   }

   448   x.tz = 0;

   449   x.validJD = 0;

   450   computeJD(&x);

   451   t = x.iJD/1000 - 2440587.5*86400.0;

   452 #ifdef HAVE_LOCALTIME_R

   453   {

   454     struct tm sLocal;

   455     localtime_r(&t, &sLocal);

   456     y.Y = sLocal.tm_year + 1900;

   457     y.M = sLocal.tm_mon + 1;

   458     y.D = sLocal.tm_mday;

   459     y.h = sLocal.tm_hour;

   460     y.m = sLocal.tm_min;

   461     y.s = sLocal.tm_sec;

   462   }

   463 #elif defined(HAVE_LOCALTIME_S)

   464   {

   465     struct tm sLocal;

   466     localtime_s(&sLocal, &t);

   467     y.Y = sLocal.tm_year + 1900;

   468     y.M = sLocal.tm_mon + 1;

   469     y.D = sLocal.tm_mday;

   470     y.h = sLocal.tm_hour;

   471     y.m = sLocal.tm_min;

   472     y.s = sLocal.tm_sec;

   473   }

   474 #else

   475   {

   476     struct tm *pTm;

   477     sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));

   478     pTm = localtime(&t);

   479     y.Y = pTm->tm_year + 1900;

   480     y.M = pTm->tm_mon + 1;

   481     y.D = pTm->tm_mday;

   482     y.h = pTm->tm_hour;

   483     y.m = pTm->tm_min;

   484     y.s = pTm->tm_sec;

   485     sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));

   486   }

   487 #endif

   488   y.validYMD = 1;

   489   y.validHMS = 1;

   490   y.validJD = 0;

   491   y.validTZ = 0;

   492   computeJD(&y);

   493   return y.iJD - x.iJD;

   494 }

   495 #endif /* SQLITE_OMIT_LOCALTIME */

   496 

   497 /*

   498 ** Process a modifier to a date-time stamp.  The modifiers are

   499 ** as follows:

   500 **

   501 **     NNN days

   502 **     NNN hours

   503 **     NNN minutes

   504 **     NNN.NNNN seconds

   505 **     NNN months

   506 **     NNN years

   507 **     start of month

   508 **     start of year

   509 **     start of week

   510 **     start of day

   511 **     weekday N

   512 **     unixepoch

   513 **     localtime

   514 **     utc

   515 **

   516 ** Return 0 on success and 1 if there is any kind of error.

   517 */

   518 static int parseModifier(const char *zMod, DateTime *p){

   519   int rc = 1;

   520   int n;

   521   double r;

   522   char *z, zBuf[30];

   523   z = zBuf;

   524   for(n=0; n<sizeof(zBuf)-1 && zMod[n]; n++){

   525     z[n] = tolower(zMod[n]);

   526   }

   527   z[n] = 0;

   528   switch( z[0] ){

   529 #ifndef SQLITE_OMIT_LOCALTIME

   530     case 'l': {

   531       /*    localtime

   532       **

   533       ** Assuming the current time value is UTC (a.k.a. GMT), shift it to

   534       ** show local time.

   535       */

   536       if( strcmp(z, "localtime")==0 ){

   537         computeJD(p);

   538         p->iJD += localtimeOffset(p);

   539         clearYMD_HMS_TZ(p);

   540         rc = 0;

   541       }

   542       break;

   543     }

   544 #endif

   545     case 'u': {

   546       /*

   547       **    unixepoch

   548       **

   549       ** Treat the current value of p->iJD as the number of

   550       ** seconds since 1970.  Convert to a real julian day number.

   551       */

   552       if( strcmp(z, "unixepoch")==0 && p->validJD ){

   553         p->iJD = p->iJD/86400.0 + 2440587.5*86400000.0;

   554         clearYMD_HMS_TZ(p);

   555         rc = 0;

   556       }

   557 #ifndef SQLITE_OMIT_LOCALTIME

   558       else if( strcmp(z, "utc")==0 ){

   559         int c1;

   560         computeJD(p);

   561         c1 = localtimeOffset(p);

   562         p->iJD -= c1;

   563         clearYMD_HMS_TZ(p);

   564         p->iJD += c1 - localtimeOffset(p);

   565         rc = 0;

   566       }

   567 #endif

   568       break;

   569     }

   570     case 'w': {

   571       /*

   572       **    weekday N

   573       **

   574       ** Move the date to the same time on the next occurrence of

   575       ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the

   576       ** date is already on the appropriate weekday, this is a no-op.

   577       */

   578       if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0

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

   580         sqlite3_int64 Z;

   581         computeYMD_HMS(p);

   582         p->validTZ = 0;

   583         p->validJD = 0;

   584         computeJD(p);

   585         Z = ((p->iJD + 129600000)/86400000) % 7;

   586         if( Z>n ) Z -= 7;

   587         p->iJD += (n - Z)*86400000;

   588         clearYMD_HMS_TZ(p);

   589         rc = 0;

   590       }

   591       break;

   592     }

   593     case 's': {

   594       /*

   595       **    start of TTTTT

   596       **

   597       ** Move the date backwards to the beginning of the current day,

   598       ** or month or year.

   599       */

   600       if( strncmp(z, "start of ", 9)!=0 ) break;

   601       z += 9;

   602       computeYMD(p);

   603       p->validHMS = 1;

   604       p->h = p->m = 0;

   605       p->s = 0.0;

   606       p->validTZ = 0;

   607       p->validJD = 0;

   608       if( strcmp(z,"month")==0 ){

   609         p->D = 1;

   610         rc = 0;

   611       }else if( strcmp(z,"year")==0 ){

   612         computeYMD(p);

   613         p->M = 1;

   614         p->D = 1;

   615         rc = 0;

   616       }else if( strcmp(z,"day")==0 ){

   617         rc = 0;

   618       }

   619       break;

   620     }

   621     case '+':

   622     case '-':

   623     case '0':

   624     case '1':

   625     case '2':

   626     case '3':

   627     case '4':

   628     case '5':

   629     case '6':

   630     case '7':

   631     case '8':

   632     case '9': {

   633       n = getValue(z, &r);

   634       assert( n>=1 );

   635       if( z[n]==':' ){

   636         /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the

   637         ** specified number of hours, minutes, seconds, and fractional seconds

   638         ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be

   639         ** omitted.

   640         */

   641         const char *z2 = z;

   642         DateTime tx;

   643         sqlite3_int64 day;

   644         if( !isdigit(*(u8*)z2) ) z2++;

   645         memset(&tx, 0, sizeof(tx));

   646         if( parseHhMmSs(z2, &tx) ) break;

   647         computeJD(&tx);

   648         tx.iJD -= 43200000;

   649         day = tx.iJD/86400000;

   650         tx.iJD -= day*86400000;

   651         if( z[0]=='-' ) tx.iJD = -tx.iJD;

   652         computeJD(p);

   653         clearYMD_HMS_TZ(p);

   654         p->iJD += tx.iJD;

   655         rc = 0;

   656         break;

   657       }

   658       z += n;

   659       while( isspace(*(u8*)z) ) z++;

   660       n = strlen(z);

   661       if( n>10 || n<3 ) break;

   662       if( z[n-1]=='s' ){ z[n-1] = 0; n--; }

   663       computeJD(p);

   664       rc = 0;

   665       if( n==3 && strcmp(z,"day")==0 ){

   666         p->iJD += r*86400000.0 + 0.5;

   667       }else if( n==4 && strcmp(z,"hour")==0 ){

   668         p->iJD += r*(86400000.0/24.0) + 0.5;

   669       }else if( n==6 && strcmp(z,"minute")==0 ){

   670         p->iJD += r*(86400000.0/(24.0*60.0)) + 0.5;

   671       }else if( n==6 && strcmp(z,"second")==0 ){

   672         p->iJD += r*(86400000.0/(24.0*60.0*60.0)) + 0.5;

   673       }else if( n==5 && strcmp(z,"month")==0 ){

   674         int x, y;

   675         computeYMD_HMS(p);

   676         p->M += r;

   677         x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;

   678         p->Y += x;

   679         p->M -= x*12;

   680         p->validJD = 0;

   681         computeJD(p);

   682         y = r;

   683         if( y!=r ){

   684           p->iJD += (r - y)*30.0*86400000.0 + 0.5;

   685         }

   686       }else if( n==4 && strcmp(z,"year")==0 ){

   687         computeYMD_HMS(p);

   688         p->Y += r;

   689         p->validJD = 0;

   690         computeJD(p);

   691       }else{

   692         rc = 1;

   693       }

   694       clearYMD_HMS_TZ(p);

   695       break;

   696     }

   697     default: {

   698       break;

   699     }

   700   }

   701   return rc;

   702 }

   703 

   704 /*

   705 ** Process time function arguments.  argv[0] is a date-time stamp.

   706 ** argv[1] and following are modifiers.  Parse them all and write

   707 ** the resulting time into the DateTime structure p.  Return 0

   708 ** on success and 1 if there are any errors.

   709 **

   710 ** If there are zero parameters (if even argv[0] is undefined)

   711 ** then assume a default value of "now" for argv[0].

   712 */

   713 static int isDate(

   714   sqlite3_context *context, 

   715   int argc, 

   716   sqlite3_value **argv, 

   717   DateTime *p

   718 ){

   719   int i;

   720   const unsigned char *z;

   721   int eType;

   722   memset(p, 0, sizeof(*p));

   723   if( argc==0 ){

   724     setDateTimeToCurrent(context, p);

   725   }else if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT

   726                    || eType==SQLITE_INTEGER ){

   727     p->iJD = sqlite3_value_double(argv[0])*86400000.0 + 0.5;

   728     p->validJD = 1;

   729   }else{

   730     z = sqlite3_value_text(argv[0]);

   731     if( !z || parseDateOrTime(context, (char*)z, p) ){

   732       return 1;

   733     }

   734   }

   735   for(i=1; i<argc; i++){

   736     if( (z = sqlite3_value_text(argv[i]))==0 || parseModifier((char*)z, p) ){

   737       return 1;

   738     }

   739   }

   740   return 0;

   741 }

   742 

   743 

   744 /*

   745 ** The following routines implement the various date and time functions

   746 ** of SQLite.

   747 */

   748 

   749 /*

   750 **    julianday( TIMESTRING, MOD, MOD, ...)

   751 **

   752 ** Return the julian day number of the date specified in the arguments

   753 */

   754 static void juliandayFunc(

   755   sqlite3_context *context,

   756   int argc,

   757   sqlite3_value **argv

   758 ){

   759   DateTime x;

   760   if( isDate(context, argc, argv, &x)==0 ){

   761     computeJD(&x);

   762     sqlite3_result_double(context, x.iJD/86400000.0);

   763   }

   764 }

   765 

   766 /*

   767 **    datetime( TIMESTRING, MOD, MOD, ...)

   768 **

   769 ** Return YYYY-MM-DD HH:MM:SS

   770 */

   771 static void datetimeFunc(

   772   sqlite3_context *context,

   773   int argc,

   774   sqlite3_value **argv

   775 ){

   776   DateTime x;

   777   if( isDate(context, argc, argv, &x)==0 ){

   778     char zBuf[100];

   779     computeYMD_HMS(&x);

   780     sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d",

   781                      x.Y, x.M, x.D, x.h, x.m, (int)(x.s));

   782     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);

   783   }

   784 }

   785 

   786 /*

   787 **    time( TIMESTRING, MOD, MOD, ...)

   788 **

   789 ** Return HH:MM:SS

   790 */

   791 static void timeFunc(

   792   sqlite3_context *context,

   793   int argc,

   794   sqlite3_value **argv

   795 ){

   796   DateTime x;

   797   if( isDate(context, argc, argv, &x)==0 ){

   798     char zBuf[100];

   799     computeHMS(&x);

   800     sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);

   801     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);

   802   }

   803 }

   804 

   805 /*

   806 **    date( TIMESTRING, MOD, MOD, ...)

   807 **

   808 ** Return YYYY-MM-DD

   809 */

   810 static void dateFunc(

   811   sqlite3_context *context,

   812   int argc,

   813   sqlite3_value **argv

   814 ){

   815   DateTime x;

   816   if( isDate(context, argc, argv, &x)==0 ){

   817     char zBuf[100];

   818     computeYMD(&x);

   819     sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);

   820     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);

   821   }

   822 }

   823 

   824 /*

   825 **    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)

   826 **

   827 ** Return a string described by FORMAT.  Conversions as follows:

   828 **

   829 **   %d  day of month

   830 **   %f  ** fractional seconds  SS.SSS

   831 **   %H  hour 00-24

   832 **   %j  day of year 000-366

   833 **   %J  ** Julian day number

   834 **   %m  month 01-12

   835 **   %M  minute 00-59

   836 **   %s  seconds since 1970-01-01

   837 **   %S  seconds 00-59

   838 **   %w  day of week 0-6  sunday==0

   839 **   %W  week of year 00-53

   840 **   %Y  year 0000-9999

   841 **   %%  %

   842 */

   843 static void strftimeFunc(

   844   sqlite3_context *context,

   845   int argc,

   846   sqlite3_value **argv

   847 ){

   848   DateTime x;

   849   u64 n;

   850   int i, j;

   851   char *z;

   852   sqlite3 *db;

   853   const char *zFmt = (const char*)sqlite3_value_text(argv[0]);

   854   char zBuf[100];

   855   if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;

   856   db = sqlite3_context_db_handle(context);

   857   for(i=0, n=1; zFmt[i]; i++, n++){

   858     if( zFmt[i]=='%' ){

   859       switch( zFmt[i+1] ){

   860         case 'd':

   861         case 'H':

   862         case 'm':

   863         case 'M':

   864         case 'S':

   865         case 'W':

   866           n++;

   867           /* fall thru */

   868         case 'w':

   869         case '%':

   870           break;

   871         case 'f':

   872           n += 8;

   873           break;

   874         case 'j':

   875           n += 3;

   876           break;

   877         case 'Y':

   878           n += 8;

   879           break;

   880         case 's':

   881         case 'J':

   882           n += 50;

   883           break;

   884         default:

   885           return;  /* ERROR.  return a NULL */

   886       }

   887       i++;

   888     }

   889   }

   890   if( n<sizeof(zBuf) ){

   891     z = zBuf;

   892   }else if( n>db->aLimit[SQLITE_LIMIT_LENGTH] ){

   893     sqlite3_result_error_toobig(context);

   894     return;

   895   }else{

   896     z = sqlite3DbMallocRaw(db, n);

   897     if( z==0 ){

   898       sqlite3_result_error_nomem(context);

   899       return;

   900     }

   901   }

   902   computeJD(&x);

   903   computeYMD_HMS(&x);

   904   for(i=j=0; zFmt[i]; i++){

   905     if( zFmt[i]!='%' ){

   906       z[j++] = zFmt[i];

   907     }else{

   908       i++;

   909       switch( zFmt[i] ){

   910         case 'd':  sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break;

   911         case 'f': {

   912           double s = x.s;

   913           if( s>59.999 ) s = 59.999;

   914           sqlite3_snprintf(7, &z[j],"%06.3f", s);

   915           j += strlen(&z[j]);

   916           break;

   917         }

   918         case 'H':  sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break;

   919         case 'W': /* Fall thru */

   920         case 'j': {

   921           int nDay;             /* Number of days since 1st day of year */

   922           DateTime y = x;

   923           y.validJD = 0;

   924           y.M = 1;

   925           y.D = 1;

   926           computeJD(&y);

   927           nDay = (x.iJD - y.iJD)/86400000.0 + 0.5;

   928           if( zFmt[i]=='W' ){

   929             int wd;   /* 0=Monday, 1=Tuesday, ... 6=Sunday */

   930             wd = ((x.iJD+43200000)/86400000) % 7;

   931             sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);

   932             j += 2;

   933           }else{

   934             sqlite3_snprintf(4, &z[j],"%03d",nDay+1);

   935             j += 3;

   936           }

   937           break;

   938         }

   939         case 'J': {

   940           sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0);

   941           j+=strlen(&z[j]);

   942           break;

   943         }

   944         case 'm':  sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;

   945         case 'M':  sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;

   946         case 's': {

   947           sqlite3_snprintf(30,&z[j],"%d",

   948                            (int)(x.iJD/1000.0 - 210866760000.0));

   949           j += strlen(&z[j]);

   950           break;

   951         }

   952         case 'S':  sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;

   953         case 'w':  z[j++] = (((x.iJD+129600000)/86400000) % 7) + '0'; break;

   954         case 'Y':  sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=strlen(&z[j]);break;

   955         default:   z[j++] = '%'; break;

   956       }

   957     }

   958   }

   959   z[j] = 0;

   960   sqlite3_result_text(context, z, -1,

   961                       z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC);

   962 }

   963 

   964 /*

   965 ** current_time()

   966 **

   967 ** This function returns the same value as time('now').

   968 */

   969 static void ctimeFunc(

   970   sqlite3_context *context,

   971   int argc,

   972   sqlite3_value **argv

   973 ){

   974   timeFunc(context, 0, 0);

   975 }

   976 

   977 /*

   978 ** current_date()

   979 **

   980 ** This function returns the same value as date('now').

   981 */

   982 static void cdateFunc(

   983   sqlite3_context *context,

   984   int argc,

   985   sqlite3_value **argv

   986 ){

   987   dateFunc(context, 0, 0);

   988 }

   989 

   990 /*

   991 ** current_timestamp()

   992 **

   993 ** This function returns the same value as datetime('now').

   994 */

   995 static void ctimestampFunc(

   996   sqlite3_context *context,

   997   int argc,

   998   sqlite3_value **argv

   999 ){

  1000   datetimeFunc(context, 0, 0);

  1001 }

  1002 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */

  1003 

  1004 #ifdef SQLITE_OMIT_DATETIME_FUNCS

  1005 /*

  1006 ** If the library is compiled to omit the full-scale date and time

  1007 ** handling (to get a smaller binary), the following minimal version

  1008 ** of the functions current_time(), current_date() and current_timestamp()

  1009 ** are included instead. This is to support column declarations that

  1010 ** include "DEFAULT CURRENT_TIME" etc.

  1011 **

  1012 ** This function uses the C-library functions time(), gmtime()

  1013 ** and strftime(). The format string to pass to strftime() is supplied

  1014 ** as the user-data for the function.

  1015 */

  1016 static void currentTimeFunc(

  1017   sqlite3_context *context,

  1018   int argc,

  1019   sqlite3_value **argv

  1020 ){

  1021   time_t t;

  1022   char *zFormat = (char *)sqlite3_user_data(context);

  1023   sqlite3 *db;

  1024   double rT;

  1025   char zBuf[20];

  1026 

  1027   db = sqlite3_context_db_handle(context);

  1028   sqlite3OsCurrentTime(db->pVfs, &rT);

  1029   t = 86400.0*(rT - 2440587.5) + 0.5;

  1030 #ifdef HAVE_GMTIME_R

  1031   {

  1032     struct tm sNow;

  1033     gmtime_r(&t, &sNow);

  1034     strftime(zBuf, 20, zFormat, &sNow);

  1035   }

  1036 #else

  1037   {

  1038     struct tm *pTm;

  1039     sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));

  1040     pTm = gmtime(&t);

  1041     strftime(zBuf, 20, zFormat, pTm);

  1042     sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));

  1043   }

  1044 #endif

  1045 

  1046   sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);

  1047 }

  1048 #endif

  1049 

  1050 /*

  1051 ** This function registered all of the above C functions as SQL

  1052 ** functions.  This should be the only routine in this file with

  1053 ** external linkage.

  1054 */

  1055 void sqlite3RegisterDateTimeFunctions(void){

  1056   static SQLITE_WSD FuncDef aDateTimeFuncs[] = {

  1057 #ifndef SQLITE_OMIT_DATETIME_FUNCS

  1058     FUNCTION(julianday,        -1, 0, 0, juliandayFunc ),

  1059     FUNCTION(date,             -1, 0, 0, dateFunc      ),

  1060     FUNCTION(time,             -1, 0, 0, timeFunc      ),

  1061     FUNCTION(datetime,         -1, 0, 0, datetimeFunc  ),

  1062     FUNCTION(strftime,         -1, 0, 0, strftimeFunc  ),

  1063     FUNCTION(current_time,      0, 0, 0, ctimeFunc     ),

  1064     FUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),

  1065     FUNCTION(current_date,      0, 0, 0, cdateFunc     ),

  1066 #else

  1067     STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),

  1068     STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d",          0, currentTimeFunc),

  1069     STR_FUNCTION(current_date,      0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),

  1070 #endif

  1071   };

  1072   int i;

  1073   FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions);

  1074   FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aDateTimeFuncs);

  1075 

  1076   for(i=0; i<ArraySize(aDateTimeFuncs); i++){

  1077     sqlite3FuncDefInsert(pHash, &aFunc[i]);

  1078   }

  1079 }