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.cpp 1282 2008-11-13 09:31:33Z LarsPson $

    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 ** A structure for holding a single date and time.

    58 */

    59 typedef struct DateTime DateTime;

    60 struct DateTime {

    61   double rJD;      /* The julian day number */

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

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

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

    65   double s;        /* Seconds */

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

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

    68   char validJD;    /* True if rJD is valid */

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

    70 };

    71 

    72 

    73 /*

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

    75 ** come in groups of 5 as follows:

    76 **

    77 **       N       number of digits in the integer

    78 **       min     minimum allowed value of the integer

    79 **       max     maximum allowed value of the integer

    80 **       nextC   first character after the integer

    81 **       pVal    where to write the integers value.

    82 **

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

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

    85 */

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

    87   va_list ap;

    88   int val;

    89   int N;

    90   int min;

    91   int max;

    92   int nextC;

    93   int *pVal;

    94   int cnt = 0;

    95   va_start(ap, zDate);

    96   do{

    97     N = va_arg(ap, int);

    98     min = va_arg(ap, int);

    99     max = va_arg(ap, int);

   100     nextC = va_arg(ap, int);

   101     pVal = va_arg(ap, int*);

   102     val = 0;

   103     while( N-- ){

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

   105         goto end_getDigits;

   106       }

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

   108       zDate++;

   109     }

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

   111       goto end_getDigits;

   112     }

   113     *pVal = val;

   114     zDate++;

   115     cnt++;

   116   }while( nextC );

   117 end_getDigits:

   118   va_end(ap);

   119   return cnt;

   120 }

   121 

   122 /*

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

   124 ** the number of digits converted.

   125 */

   126 #define getValue sqlite3AtoF

   127 

   128 /*

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

   130 ** The extension is of the form:

   131 **

   132 **        (+/-)HH:MM

   133 **

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

   135 ** of change in *pnMin and return 0.  If a parser error occurs,

   136 ** return 0.

   137 **

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

   139 */

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

   141   int sgn = 0;

   142   int nHr, nMn;

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

   144   p->tz = 0;

   145   if( *zDate=='-' ){

   146     sgn = -1;

   147   }else if( *zDate=='+' ){

   148     sgn = +1;

   149   }else{

   150     return *zDate!=0;

   151   }

   152   zDate++;

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

   154     return 1;

   155   }

   156   zDate += 5;

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

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

   159   return *zDate!=0;

   160 }

   161 

   162 /*

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

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

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

   166 **

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

   168 */

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

   170   int h, m, s;

   171   double ms = 0.0;

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

   173     return 1;

   174   }

   175   zDate += 5;

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

   177     zDate++;

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

   179       return 1;

   180     }

   181     zDate += 2;

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

   183       double rScale = 1.0;

   184       zDate++;

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

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

   187         rScale *= 10.0;

   188         zDate++;

   189       }

   190       ms /= rScale;

   191     }

   192   }else{

   193     s = 0;

   194   }

   195   p->validJD = 0;

   196   p->validHMS = 1;

   197   p->h = h;

   198   p->m = m;

   199   p->s = s + ms;

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

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

   202   return 0;

   203 }

   204 

   205 /*

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

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

   208 **

   209 ** Reference:  Meeus page 61

   210 */

   211 static void computeJD(DateTime *p){

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

   213 

   214   if( p->validJD ) return;

   215   if( p->validYMD ){

   216     Y = p->Y;

   217     M = p->M;

   218     D = p->D;

   219   }else{

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

   221     M = 1;

   222     D = 1;

   223   }

   224   if( M<=2 ){

   225     Y--;

   226     M += 12;

   227   }

   228   A = Y/100;

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

   230   X1 = 365.25*(Y+4716);

   231   X2 = 30.6001*(M+1);

   232   p->rJD = X1 + X2 + D + B - 1524.5;

   233   p->validJD = 1;

   234   if( p->validHMS ){

   235     p->rJD += (p->h*3600.0 + p->m*60.0 + p->s)/86400.0;

   236     if( p->validTZ ){

   237       p->rJD -= p->tz*60/86400.0;

   238       p->validYMD = 0;

   239       p->validHMS = 0;

   240       p->validTZ = 0;

   241     }

   242   }

   243 }

   244 

   245 /*

   246 ** Parse dates of the form

   247 **

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

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

   250 **     YYYY-MM-DD HH:MM

   251 **     YYYY-MM-DD

   252 **

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

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

   255 ** date.

   256 */

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

   258   int Y, M, D, neg;

   259 

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

   261     zDate++;

   262     neg = 1;

   263   }else{

   264     neg = 0;

   265   }

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

   267     return 1;

   268   }

   269   zDate += 10;

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

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

   272     /* We got the time */

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

   274     p->validHMS = 0;

   275   }else{

   276     return 1;

   277   }

   278   p->validJD = 0;

   279   p->validYMD = 1;

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

   281   p->M = M;

   282   p->D = D;

   283   if( p->validTZ ){

   284     computeJD(p);

   285   }

   286   return 0;

   287 }

   288 

   289 /*

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

   291 ** the number of errors.

   292 **

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

   294 **

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

   296 **      DDDD.DD 

   297 **      now

   298 **

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

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

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

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

   303 ** as there is a year and date.

   304 */

   305 static int parseDateOrTime(

   306   sqlite3_context *context, 

   307   const char *zDate, 

   308   DateTime *p

   309 ){

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

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

   312     return 0;

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

   314     return 0;

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

   316     double r;

   317     sqlite3OsCurrentTime((sqlite3_vfs *)sqlite3_user_data(context), &r);

   318     p->rJD = r;

   319     p->validJD = 1;

   320     return 0;

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

   322     getValue(zDate, &p->rJD);

   323     p->validJD = 1;

   324     return 0;

   325   }

   326   return 1;

   327 }

   328 

   329 /*

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

   331 */

   332 static void computeYMD(DateTime *p){

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

   334   if( p->validYMD ) return;

   335   if( !p->validJD ){

   336     p->Y = 2000;

   337     p->M = 1;

   338     p->D = 1;

   339   }else{

   340     Z = p->rJD + 0.5;

   341     A = (Z - 1867216.25)/36524.25;

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

   343     B = A + 1524;

   344     C = (B - 122.1)/365.25;

   345     D = 365.25*C;

   346     E = (B-D)/30.6001;

   347     X1 = 30.6001*E;

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

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

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

   351   }

   352   p->validYMD = 1;

   353 }

   354 

   355 /*

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

   357 */

   358 static void computeHMS(DateTime *p){

   359   int Z, s;

   360   if( p->validHMS ) return;

   361   computeJD(p);

   362   Z = p->rJD + 0.5;

   363   s = (p->rJD + 0.5 - Z)*86400000.0 + 0.5;

   364   p->s = 0.001*s;

   365   s = p->s;

   366   p->s -= s;

   367   p->h = s/3600;

   368   s -= p->h*3600;

   369   p->m = s/60;

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

   371   p->validHMS = 1;

   372 }

   373 

   374 /*

   375 ** Compute both YMD and HMS

   376 */

   377 static void computeYMD_HMS(DateTime *p){

   378   computeYMD(p);

   379   computeHMS(p);

   380 }

   381 

   382 /*

   383 ** Clear the YMD and HMS and the TZ

   384 */

   385 static void clearYMD_HMS_TZ(DateTime *p){

   386   p->validYMD = 0;

   387   p->validHMS = 0;

   388   p->validTZ = 0;

   389 }

   390 

   391 /*

   392 ** Compute the difference (in days) between localtime and UTC (a.k.a. GMT)

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

   394 */

   395 static double localtimeOffset(DateTime *p){

   396   DateTime x, y;

   397   time_t t;

   398   x = *p;

   399   computeYMD_HMS(&x);

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

   401     x.Y = 2000;

   402     x.M = 1;

   403     x.D = 1;

   404     x.h = 0;

   405     x.m = 0;

   406     x.s = 0.0;

   407   } else {

   408     int s = x.s + 0.5;

   409     x.s = s;

   410   }

   411   x.tz = 0;

   412   x.validJD = 0;

   413   computeJD(&x);

   414   t = (x.rJD-2440587.5)*86400.0 + 0.5;

   415 #ifdef HAVE_LOCALTIME_R

   416   {

   417     struct tm sLocal;

   418     localtime_r(&t, &sLocal);

   419     y.Y = sLocal.tm_year + 1900;

   420     y.M = sLocal.tm_mon + 1;

   421     y.D = sLocal.tm_mday;

   422     y.h = sLocal.tm_hour;

   423     y.m = sLocal.tm_min;

   424     y.s = sLocal.tm_sec;

   425   }

   426 #else

   427   {

   428     struct tm *pTm;

   429     sqlite3_mutex_enter(sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER));

   430     pTm = localtime(&t);

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

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

   433     y.D = pTm->tm_mday;

   434     y.h = pTm->tm_hour;

   435     y.m = pTm->tm_min;

   436     y.s = pTm->tm_sec;

   437     sqlite3_mutex_leave(sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER));

   438   }

   439 #endif

   440   y.validYMD = 1;

   441   y.validHMS = 1;

   442   y.validJD = 0;

   443   y.validTZ = 0;

   444   computeJD(&y);

   445   return y.rJD - x.rJD;

   446 }

   447 

   448 /*

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

   450 ** as follows:

   451 **

   452 **     NNN days

   453 **     NNN hours

   454 **     NNN minutes

   455 **     NNN.NNNN seconds

   456 **     NNN months

   457 **     NNN years

   458 **     start of month

   459 **     start of year

   460 **     start of week

   461 **     start of day

   462 **     weekday N

   463 **     unixepoch

   464 **     localtime

   465 **     utc

   466 **

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

   468 */

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

   470   int rc = 1;

   471   int n;

   472   double r;

   473   char *z, zBuf[30];

   474   z = zBuf;

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

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

   477   }

   478   z[n] = 0;

   479   switch( z[0] ){

   480     case 'l': {

   481       /*    localtime

   482       **

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

   484       ** show local time.

   485       */

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

   487         computeJD(p);

   488         p->rJD += localtimeOffset(p);

   489         clearYMD_HMS_TZ(p);

   490         rc = 0;

   491       }

   492       break;

   493     }

   494     case 'u': {

   495       /*

   496       **    unixepoch

   497       **

   498       ** Treat the current value of p->rJD as the number of

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

   500       */

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

   502         p->rJD = p->rJD/86400.0 + 2440587.5;

   503         clearYMD_HMS_TZ(p);

   504         rc = 0;

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

   506         double c1;

   507         computeJD(p);

   508         c1 = localtimeOffset(p);

   509         p->rJD -= c1;

   510         clearYMD_HMS_TZ(p);

   511         p->rJD += c1 - localtimeOffset(p);

   512         rc = 0;

   513       }

   514       break;

   515     }

   516     case 'w': {

   517       /*

   518       **    weekday N

   519       **

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

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

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

   523       */

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

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

   526         int Z;

   527         computeYMD_HMS(p);

   528         p->validTZ = 0;

   529         p->validJD = 0;

   530         computeJD(p);

   531         Z = p->rJD + 1.5;

   532         Z %= 7;

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

   534         p->rJD += n - Z;

   535         clearYMD_HMS_TZ(p);

   536         rc = 0;

   537       }

   538       break;

   539     }

   540     case 's': {

   541       /*

   542       **    start of TTTTT

   543       **

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

   545       ** or month or year.

   546       */

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

   548       z += 9;

   549       computeYMD(p);

   550       p->validHMS = 1;

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

   552       p->s = 0.0;

   553       p->validTZ = 0;

   554       p->validJD = 0;

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

   556         p->D = 1;

   557         rc = 0;

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

   559         computeYMD(p);

   560         p->M = 1;

   561         p->D = 1;

   562         rc = 0;

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

   564         rc = 0;

   565       }

   566       break;

   567     }

   568     case '+':

   569     case '-':

   570     case '0':

   571     case '1':

   572     case '2':

   573     case '3':

   574     case '4':

   575     case '5':

   576     case '6':

   577     case '7':

   578     case '8':

   579     case '9': {

   580       n = getValue(z, &r);

   581       assert( n>=1 );

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

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

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

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

   586         ** omitted.

   587         */

   588         const char *z2 = z;

   589         DateTime tx;

   590         int day;

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

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

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

   594         computeJD(&tx);

   595         tx.rJD -= 0.5;

   596         day = (int)tx.rJD;

   597         tx.rJD -= day;

   598         if( z[0]=='-' ) tx.rJD = -tx.rJD;

   599         computeJD(p);

   600         clearYMD_HMS_TZ(p);

   601         p->rJD += tx.rJD;

   602         rc = 0;

   603         break;

   604       }

   605       z += n;

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

   607       n = strlen(z);

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

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

   610       computeJD(p);

   611       rc = 0;

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

   613         p->rJD += r;

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

   615         p->rJD += r/24.0;

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

   617         p->rJD += r/(24.0*60.0);

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

   619         p->rJD += r/(24.0*60.0*60.0);

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

   621         int x, y;

   622         computeYMD_HMS(p);

   623         p->M += r;

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

   625         p->Y += x;

   626         p->M -= x*12;

   627         p->validJD = 0;

   628         computeJD(p);

   629         y = r;

   630         if( y!=r ){

   631           p->rJD += (r - y)*30.0;

   632         }

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

   634         computeYMD_HMS(p);

   635         p->Y += r;

   636         p->validJD = 0;

   637         computeJD(p);

   638       }else{

   639         rc = 1;

   640       }

   641       clearYMD_HMS_TZ(p);

   642       break;

   643     }

   644     default: {

   645       break;

   646     }

   647   }

   648   return rc;

   649 }

   650 

   651 /*

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

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

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

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

   656 */

   657 static int isDate(

   658   sqlite3_context *context, 

   659   int argc, 

   660   sqlite3_value **argv, 

   661   DateTime *p

   662 ){

   663   int i;

   664   const unsigned char *z;

   665   if( argc==0 ) return 1;

   666   z = sqlite3_value_text(argv[0]);

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

   668     return 1;

   669   }

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

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

   672       return 1;

   673     }

   674   }

   675   return 0;

   676 }

   677 

   678 

   679 /*

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

   681 ** of SQLite.

   682 */

   683 

   684 /*

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

   686 **

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

   688 */

   689 static void juliandayFunc(

   690   sqlite3_context *context,

   691   int argc,

   692   sqlite3_value **argv

   693 ){

   694   DateTime x;

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

   696     computeJD(&x);

   697     sqlite3_result_double(context, x.rJD);

   698   }

   699 }

   700 

   701 /*

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

   703 **

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

   705 */

   706 static void datetimeFunc(

   707   sqlite3_context *context,

   708   int argc,

   709   sqlite3_value **argv

   710 ){

   711   DateTime x;

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

   713     char zBuf[100];

   714     computeYMD_HMS(&x);

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

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

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

   718   }

   719 }

   720 

   721 /*

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

   723 **

   724 ** Return HH:MM:SS

   725 */

   726 static void timeFunc(

   727   sqlite3_context *context,

   728   int argc,

   729   sqlite3_value **argv

   730 ){

   731   DateTime x;

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

   733     char zBuf[100];

   734     computeHMS(&x);

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

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

   737   }

   738 }

   739 

   740 /*

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

   742 **

   743 ** Return YYYY-MM-DD

   744 */

   745 static void dateFunc(

   746   sqlite3_context *context,

   747   int argc,

   748   sqlite3_value **argv

   749 ){

   750   DateTime x;

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

   752     char zBuf[100];

   753     computeYMD(&x);

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

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

   756   }

   757 }

   758 

   759 /*

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

   761 **

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

   763 **

   764 **   %d  day of month

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

   766 **   %H  hour 00-24

   767 **   %j  day of year 000-366

   768 **   %J  ** Julian day number

   769 **   %m  month 01-12

   770 **   %M  minute 00-59

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

   772 **   %S  seconds 00-59

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

   774 **   %W  week of year 00-53

   775 **   %Y  year 0000-9999

   776 **   %%  %

   777 */

   778 static void strftimeFunc(

   779   sqlite3_context *context,

   780   int argc,

   781   sqlite3_value **argv

   782 ){

   783   DateTime x;

   784   u64 n;

   785   int i, j;

   786   char *z;

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

   788   char zBuf[100];

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

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

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

   792       switch( zFmt[i+1] ){

   793         case 'd':

   794         case 'H':

   795         case 'm':

   796         case 'M':

   797         case 'S':

   798         case 'W':

   799           n++;

   800           /* fall thru */

   801         case 'w':

   802         case '%':

   803           break;

   804         case 'f':

   805           n += 8;

   806           break;

   807         case 'j':

   808           n += 3;

   809           break;

   810         case 'Y':

   811           n += 8;

   812           break;

   813         case 's':

   814         case 'J':

   815           n += 50;

   816           break;

   817         default:

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

   819       }

   820       i++;

   821     }

   822   }

   823   if( n<sizeof(zBuf) ){

   824     z = zBuf;

   825   }else if( n>SQLITE_MAX_LENGTH ){

   826     sqlite3_result_error_toobig(context);

   827     return;

   828   }else{

   829     z = (char*)sqlite3_malloc( n );

   830     if( z==0 ) return;

   831   }

   832   computeJD(&x);

   833   computeYMD_HMS(&x);

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

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

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

   837     }else{

   838       i++;

   839       switch( zFmt[i] ){

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

   841         case 'f': {

   842           double s = x.s;

   843           if( s>59.999 ) s = 59.999;

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

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

   846           break;

   847         }

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

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

   850         case 'j': {

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

   852           DateTime y = x;

   853           y.validJD = 0;

   854           y.M = 1;

   855           y.D = 1;

   856           computeJD(&y);

   857           nDay = x.rJD - y.rJD + 0.5;

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

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

   860             wd = ((int)(x.rJD+0.5)) % 7;

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

   862             j += 2;

   863           }else{

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

   865             j += 3;

   866           }

   867           break;

   868         }

   869         case 'J': {

   870           sqlite3_snprintf(20, &z[j],"%.16g",x.rJD);

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

   872           break;

   873         }

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

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

   876         case 's': {

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

   878                            (int)((x.rJD-2440587.5)*86400.0 + 0.5));

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

   880           break;

   881         }

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

   883         case 'w':  z[j++] = (((int)(x.rJD+1.5)) % 7) + '0'; break;

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

   885         case '%':  z[j++] = '%'; break;

   886       }

   887     }

   888   }

   889   z[j] = 0;

   890   sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT);

   891   if( z!=zBuf ){

   892     sqlite3_free(z);

   893   }

   894 }

   895 

   896 /*

   897 ** current_time()

   898 **

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

   900 */

   901 static void ctimeFunc(

   902   sqlite3_context *context,

   903   int argc,

   904   sqlite3_value **argv

   905 ){

   906   sqlite3_value *pVal = sqlite3ValueNew(0);

   907   if( pVal ){

   908     sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC);

   909     timeFunc(context, 1, &pVal);

   910     sqlite3ValueFree(pVal);

   911   }

   912 }

   913 

   914 /*

   915 ** current_date()

   916 **

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

   918 */

   919 static void cdateFunc(

   920   sqlite3_context *context,

   921   int argc,

   922   sqlite3_value **argv

   923 ){

   924   sqlite3_value *pVal = sqlite3ValueNew(0);

   925   if( pVal ){

   926     sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC);

   927     dateFunc(context, 1, &pVal);

   928     sqlite3ValueFree(pVal);

   929   }

   930 }

   931 

   932 /*

   933 ** current_timestamp()

   934 **

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

   936 */

   937 static void ctimestampFunc(

   938   sqlite3_context *context,

   939   int argc,

   940   sqlite3_value **argv

   941 ){

   942   sqlite3_value *pVal = sqlite3ValueNew(0);

   943   if( pVal ){

   944     sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC);

   945     datetimeFunc(context, 1, &pVal);

   946     sqlite3ValueFree(pVal);

   947   }

   948 }

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

   950 

   951 #ifdef SQLITE_OMIT_DATETIME_FUNCS

   952 /*

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

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

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

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

   957 ** include "DEFAULT CURRENT_TIME" etc.

   958 **

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

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

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

   962 */

   963 static void currentTimeFunc(

   964   sqlite3_context *context,

   965   int argc,

   966   sqlite3_value **argv

   967 ){

   968   time_t t;

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

   970   char zBuf[20];

   971 

   972   time(&t);

   973 #ifdef SQLITE_TEST

   974   {

   975     extern int sqlite3_current_time;  /* See os_XXX.c */

   976     if( sqlite3_current_time ){

   977       t = sqlite3_current_time;

   978     }

   979   }

   980 #endif

   981 

   982 #ifdef HAVE_GMTIME_R

   983   {

   984     struct tm sNow;

   985     gmtime_r(&t, &sNow);

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

   987   }

   988 #else

   989   {

   990     struct tm *pTm;

   991     sqlite3_mutex_enter(sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER));

   992     pTm = gmtime(&t);

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

   994     sqlite3_mutex_leave(sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER));

   995   }

   996 #endif

   997 

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

   999 }

  1000 #endif

  1001 

  1002 /*

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

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

  1005 ** external linkage.

  1006 */

  1007 void sqlite3RegisterDateTimeFunctions(sqlite3 *db){

  1008 #ifndef SQLITE_OMIT_DATETIME_FUNCS

  1009   static const struct {

  1010      char *zName;

  1011      int nArg;

  1012      void (*xFunc)(sqlite3_context*,int,sqlite3_value**);

  1013   } aFuncs[] = {

  1014     { "julianday", -1, juliandayFunc   },

  1015     { "date",      -1, dateFunc        },

  1016     { "time",      -1, timeFunc        },

  1017     { "datetime",  -1, datetimeFunc    },

  1018     { "strftime",  -1, strftimeFunc    },

  1019     { "current_time",       0, ctimeFunc      },

  1020     { "current_timestamp",  0, ctimestampFunc },

  1021     { "current_date",       0, cdateFunc      },

  1022   };

  1023   int i;

  1024 

  1025   for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){

  1026     sqlite3CreateFunc(db, aFuncs[i].zName, aFuncs[i].nArg,

  1027         SQLITE_UTF8, (void *)(db->pVfs), aFuncs[i].xFunc, 0, 0);

  1028   }

  1029 #else

  1030   static const struct {

  1031      char *zName;

  1032      char *zFormat;

  1033   } aFuncs[] = {

  1034     { "current_time", "%H:%M:%S" },

  1035     { "current_date", "%Y-%m-%d" },

  1036     { "current_timestamp", "%Y-%m-%d %H:%M:%S" }

  1037   };

  1038   int i;

  1039 

  1040   for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){

  1041     sqlite3CreateFunc(db, aFuncs[i].zName, 0, SQLITE_UTF8, 

  1042         aFuncs[i].zFormat, currentTimeFunc, 0, 0);

  1043   }

  1044 #endif

  1045 }