Back to index

php5  5.3.10
date.c
Go to the documentation of this file.
00001 /*
00002 ** 2003 October 31
00003 **
00004 ** The author disclaims copyright to this source code.  In place of
00005 ** a legal notice, here is a blessing:
00006 **
00007 **    May you do good and not evil.
00008 **    May you find forgiveness for yourself and forgive others.
00009 **    May you share freely, never taking more than you give.
00010 **
00011 *************************************************************************
00012 ** This file contains the C functions that implement date and time
00013 ** functions for SQLite.  
00014 **
00015 ** There is only one exported symbol in this file - the function
00016 ** sqliteRegisterDateTimeFunctions() found at the bottom of the file.
00017 ** All other code has file scope.
00018 **
00019 ** $Id: date.c 278363 2009-04-07 11:45:13Z kalle $
00020 **
00021 ** NOTES:
00022 **
00023 ** SQLite processes all times and dates as Julian Day numbers.  The
00024 ** dates and times are stored as the number of days since noon
00025 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian
00026 ** calendar system.
00027 **
00028 ** 1970-01-01 00:00:00 is JD 2440587.5
00029 ** 2000-01-01 00:00:00 is JD 2451544.5
00030 **
00031 ** This implemention requires years to be expressed as a 4-digit number
00032 ** which means that only dates between 0000-01-01 and 9999-12-31 can
00033 ** be represented, even though julian day numbers allow a much wider
00034 ** range of dates.
00035 **
00036 ** The Gregorian calendar system is used for all dates and times,
00037 ** even those that predate the Gregorian calendar.  Historians usually
00038 ** use the Julian calendar for dates prior to 1582-10-15 and for some
00039 ** dates afterwards, depending on locale.  Beware of this difference.
00040 **
00041 ** The conversion algorithms are implemented based on descriptions
00042 ** in the following text:
00043 **
00044 **      Jean Meeus
00045 **      Astronomical Algorithms, 2nd Edition, 1998
00046 **      ISBM 0-943396-61-1
00047 **      Willmann-Bell, Inc
00048 **      Richmond, Virginia (USA)
00049 */
00050 #include "os.h"
00051 #include "sqliteInt.h"
00052 #include <ctype.h>
00053 #include <stdlib.h>
00054 #include <assert.h>
00055 #include <time.h>
00056 #ifndef PHP_WIN32
00057 #include "main/php_reentrancy.h"
00058 #endif
00059 
00060 #ifndef SQLITE_OMIT_DATETIME_FUNCS
00061 
00062 /*
00063 ** A structure for holding a single date and time.
00064 */
00065 typedef struct DateTime DateTime;
00066 struct DateTime {
00067   double rJD;      /* The julian day number */
00068   int Y, M, D;     /* Year, month, and day */
00069   int h, m;        /* Hour and minutes */
00070   int tz;          /* Timezone offset in minutes */
00071   double s;        /* Seconds */
00072   char validYMD;   /* True if Y,M,D are valid */
00073   char validHMS;   /* True if h,m,s are valid */
00074   char validJD;    /* True if rJD is valid */
00075   char validTZ;    /* True if tz is valid */
00076 };
00077 
00078 
00079 /*
00080 ** Convert zDate into one or more integers.  Additional arguments
00081 ** come in groups of 5 as follows:
00082 **
00083 **       N       number of digits in the integer
00084 **       min     minimum allowed value of the integer
00085 **       max     maximum allowed value of the integer
00086 **       nextC   first character after the integer
00087 **       pVal    where to write the integers value.
00088 **
00089 ** Conversions continue until one with nextC==0 is encountered.
00090 ** The function returns the number of successful conversions.
00091 */
00092 static int getDigits(const char *zDate, ...){
00093   va_list ap;
00094   int val;
00095   int N;
00096   int min;
00097   int max;
00098   int nextC;
00099   int *pVal;
00100   int cnt = 0;
00101   va_start(ap, zDate);
00102   do{
00103     N = va_arg(ap, int);
00104     min = va_arg(ap, int);
00105     max = va_arg(ap, int);
00106     nextC = va_arg(ap, int);
00107     pVal = va_arg(ap, int*);
00108     val = 0;
00109     while( N-- ){
00110       if( !isdigit(*zDate) ){
00111         return cnt;
00112       }
00113       val = val*10 + *zDate - '0';
00114       zDate++;
00115     }
00116     if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){
00117       return cnt;
00118     }
00119     *pVal = val;
00120     zDate++;
00121     cnt++;
00122   }while( nextC );
00123   return cnt;
00124 }
00125 
00126 /*
00127 ** Read text from z[] and convert into a floating point number.  Return
00128 ** the number of digits converted.
00129 */
00130 static int getValue(const char *z, double *pR){
00131   const char *zEnd;
00132   *pR = sqliteAtoF(z, &zEnd);
00133   return zEnd - z;
00134 }
00135 
00136 /*
00137 ** Parse a timezone extension on the end of a date-time.
00138 ** The extension is of the form:
00139 **
00140 **        (+/-)HH:MM
00141 **
00142 ** If the parse is successful, write the number of minutes
00143 ** of change in *pnMin and return 0.  If a parser error occurs,
00144 ** return 0.
00145 **
00146 ** A missing specifier is not considered an error.
00147 */
00148 static int parseTimezone(const char *zDate, DateTime *p){
00149   int sgn = 0;
00150   int nHr, nMn;
00151   while( isspace(*zDate) ){ zDate++; }
00152   p->tz = 0;
00153   if( *zDate=='-' ){
00154     sgn = -1;
00155   }else if( *zDate=='+' ){
00156     sgn = +1;
00157   }else{
00158     return *zDate!=0;
00159   }
00160   zDate++;
00161   if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){
00162     return 1;
00163   }
00164   zDate += 5;
00165   p->tz = sgn*(nMn + nHr*60);
00166   while( isspace(*zDate) ){ zDate++; }
00167   return *zDate!=0;
00168 }
00169 
00170 /*
00171 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
00172 ** The HH, MM, and SS must each be exactly 2 digits.  The
00173 ** fractional seconds FFFF can be one or more digits.
00174 **
00175 ** Return 1 if there is a parsing error and 0 on success.
00176 */
00177 static int parseHhMmSs(const char *zDate, DateTime *p){
00178   int h, m, s;
00179   double ms = 0.0;
00180   if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){
00181     return 1;
00182   }
00183   zDate += 5;
00184   if( *zDate==':' ){
00185     zDate++;
00186     if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
00187       return 1;
00188     }
00189     zDate += 2;
00190     if( *zDate=='.' && isdigit(zDate[1]) ){
00191       double rScale = 1.0;
00192       zDate++;
00193       while( isdigit(*zDate) ){
00194         ms = ms*10.0 + *zDate - '0';
00195         rScale *= 10.0;
00196         zDate++;
00197       }
00198       ms /= rScale;
00199     }
00200   }else{
00201     s = 0;
00202   }
00203   p->validJD = 0;
00204   p->validHMS = 1;
00205   p->h = h;
00206   p->m = m;
00207   p->s = s + ms;
00208   if( parseTimezone(zDate, p) ) return 1;
00209   p->validTZ = p->tz!=0;
00210   return 0;
00211 }
00212 
00213 /*
00214 ** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume
00215 ** that the YYYY-MM-DD is according to the Gregorian calendar.
00216 **
00217 ** Reference:  Meeus page 61
00218 */
00219 static void computeJD(DateTime *p){
00220   int Y, M, D, A, B, X1, X2;
00221 
00222   if( p->validJD ) return;
00223   if( p->validYMD ){
00224     Y = p->Y;
00225     M = p->M;
00226     D = p->D;
00227   }else{
00228     Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */
00229     M = 1;
00230     D = 1;
00231   }
00232   if( M<=2 ){
00233     Y--;
00234     M += 12;
00235   }
00236   A = Y/100;
00237   B = 2 - A + (A/4);
00238   X1 = 365.25*(Y+4716);
00239   X2 = 30.6001*(M+1);
00240   p->rJD = X1 + X2 + D + B - 1524.5;
00241   p->validJD = 1;
00242   p->validYMD = 0;
00243   if( p->validHMS ){
00244     p->rJD += (p->h*3600.0 + p->m*60.0 + p->s)/86400.0;
00245     if( p->validTZ ){
00246       p->rJD += p->tz*60/86400.0;
00247       p->validHMS = 0;
00248       p->validTZ = 0;
00249     }
00250   }
00251 }
00252 
00253 /*
00254 ** Parse dates of the form
00255 **
00256 **     YYYY-MM-DD HH:MM:SS.FFF
00257 **     YYYY-MM-DD HH:MM:SS
00258 **     YYYY-MM-DD HH:MM
00259 **     YYYY-MM-DD
00260 **
00261 ** Write the result into the DateTime structure and return 0
00262 ** on success and 1 if the input string is not a well-formed
00263 ** date.
00264 */
00265 static int parseYyyyMmDd(const char *zDate, DateTime *p){
00266   int Y, M, D, neg;
00267 
00268   if( zDate[0]=='-' ){
00269     zDate++;
00270     neg = 1;
00271   }else{
00272     neg = 0;
00273   }
00274   if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){
00275     return 1;
00276   }
00277   zDate += 10;
00278   while( isspace(*zDate) ){ zDate++; }
00279   if( parseHhMmSs(zDate, p)==0 ){
00280     /* We got the time */
00281   }else if( *zDate==0 ){
00282     p->validHMS = 0;
00283   }else{
00284     return 1;
00285   }
00286   p->validJD = 0;
00287   p->validYMD = 1;
00288   p->Y = neg ? -Y : Y;
00289   p->M = M;
00290   p->D = D;
00291   if( p->validTZ ){
00292     computeJD(p);
00293   }
00294   return 0;
00295 }
00296 
00297 /*
00298 ** Attempt to parse the given string into a Julian Day Number.  Return
00299 ** the number of errors.
00300 **
00301 ** The following are acceptable forms for the input string:
00302 **
00303 **      YYYY-MM-DD HH:MM:SS.FFF  +/-HH:MM
00304 **      DDDD.DD 
00305 **      now
00306 **
00307 ** In the first form, the +/-HH:MM is always optional.  The fractional
00308 ** seconds extension (the ".FFF") is optional.  The seconds portion
00309 ** (":SS.FFF") is option.  The year and date can be omitted as long
00310 ** as there is a time string.  The time string can be omitted as long
00311 ** as there is a year and date.
00312 */
00313 static int parseDateOrTime(const char *zDate, DateTime *p){
00314   memset(p, 0, sizeof(*p));
00315   if( parseYyyyMmDd(zDate,p)==0 ){
00316     return 0;
00317   }else if( parseHhMmSs(zDate, p)==0 ){
00318     return 0;
00319   }else if( sqliteStrICmp(zDate,"now")==0){
00320     double r;
00321     if( sqliteOsCurrentTime(&r)==0 ){
00322       p->rJD = r;
00323       p->validJD = 1;
00324       return 0;
00325     }
00326     return 1;
00327   }else if( sqliteIsNumber(zDate) ){
00328     p->rJD = sqliteAtoF(zDate, 0);
00329     p->validJD = 1;
00330     return 0;
00331   }
00332   return 1;
00333 }
00334 
00335 /*
00336 ** Compute the Year, Month, and Day from the julian day number.
00337 */
00338 static void computeYMD(DateTime *p){
00339   int Z, A, B, C, D, E, X1;
00340   if( p->validYMD ) return;
00341   if( !p->validJD ){
00342     p->Y = 2000;
00343     p->M = 1;
00344     p->D = 1;
00345   }else{
00346     Z = p->rJD + 0.5;
00347     A = (Z - 1867216.25)/36524.25;
00348     A = Z + 1 + A - (A/4);
00349     B = A + 1524;
00350     C = (B - 122.1)/365.25;
00351     D = 365.25*C;
00352     E = (B-D)/30.6001;
00353     X1 = 30.6001*E;
00354     p->D = B - D - X1;
00355     p->M = E<14 ? E-1 : E-13;
00356     p->Y = p->M>2 ? C - 4716 : C - 4715;
00357   }
00358   p->validYMD = 1;
00359 }
00360 
00361 /*
00362 ** Compute the Hour, Minute, and Seconds from the julian day number.
00363 */
00364 static void computeHMS(DateTime *p){
00365   int Z, s;
00366   if( p->validHMS ) return;
00367   Z = p->rJD + 0.5;
00368   s = (p->rJD + 0.5 - Z)*86400000.0 + 0.5;
00369   p->s = 0.001*s;
00370   s = p->s;
00371   p->s -= s;
00372   p->h = s/3600;
00373   s -= p->h*3600;
00374   p->m = s/60;
00375   p->s += s - p->m*60;
00376   p->validHMS = 1;
00377 }
00378 
00379 /*
00380 ** Compute both YMD and HMS
00381 */
00382 static void computeYMD_HMS(DateTime *p){
00383   computeYMD(p);
00384   computeHMS(p);
00385 }
00386 
00387 /*
00388 ** Clear the YMD and HMS and the TZ
00389 */
00390 static void clearYMD_HMS_TZ(DateTime *p){
00391   p->validYMD = 0;
00392   p->validHMS = 0;
00393   p->validTZ = 0;
00394 }
00395 
00396 /*
00397 ** Compute the difference (in days) between localtime and UTC (a.k.a. GMT)
00398 ** for the time value p where p is in UTC.
00399 */
00400 static double localtimeOffset(DateTime *p){
00401   DateTime x, y;
00402   time_t t;
00403   struct tm *pTm, tmbuf;
00404   x = *p;
00405   computeYMD_HMS(&x);
00406   if( x.Y<1971 || x.Y>=2038 ){
00407     x.Y = 2000;
00408     x.M = 1;
00409     x.D = 1;
00410     x.h = 0;
00411     x.m = 0;
00412     x.s = 0.0;
00413   } else {
00414     int s = x.s + 0.5;
00415     x.s = s;
00416   }
00417   x.tz = 0;
00418   x.validJD = 0;
00419   computeJD(&x);
00420   t = (x.rJD-2440587.5)*86400.0 + 0.5;
00421   sqliteOsEnterMutex();
00422   pTm = php_localtime_r(&t, &tmbuf);
00423   if (!pTm) {
00424          return 0;
00425   }
00426   y.Y = pTm->tm_year + 1900;
00427   y.M = pTm->tm_mon + 1;
00428   y.D = pTm->tm_mday;
00429   y.h = pTm->tm_hour;
00430   y.m = pTm->tm_min;
00431   y.s = pTm->tm_sec;
00432   sqliteOsLeaveMutex();
00433   y.validYMD = 1;
00434   y.validHMS = 1;
00435   y.validJD = 0;
00436   y.validTZ = 0;
00437   computeJD(&y);
00438   return y.rJD - x.rJD;
00439 }
00440 
00441 /*
00442 ** Process a modifier to a date-time stamp.  The modifiers are
00443 ** as follows:
00444 **
00445 **     NNN days
00446 **     NNN hours
00447 **     NNN minutes
00448 **     NNN.NNNN seconds
00449 **     NNN months
00450 **     NNN years
00451 **     start of month
00452 **     start of year
00453 **     start of week
00454 **     start of day
00455 **     weekday N
00456 **     unixepoch
00457 **     localtime
00458 **     utc
00459 **
00460 ** Return 0 on success and 1 if there is any kind of error.
00461 */
00462 static int parseModifier(const char *zMod, DateTime *p){
00463   int rc = 1;
00464   int n;
00465   double r;
00466   char *z, zBuf[30];
00467   z = zBuf;
00468   for(n=0; n<sizeof(zBuf)-1 && zMod[n]; n++){
00469     z[n] = tolower(zMod[n]);
00470   }
00471   z[n] = 0;
00472   switch( z[0] ){
00473     case 'l': {
00474       /*    localtime
00475       **
00476       ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
00477       ** show local time.
00478       */
00479       if( strcmp(z, "localtime")==0 ){
00480         computeJD(p);
00481         p->rJD += localtimeOffset(p);
00482         clearYMD_HMS_TZ(p);
00483         rc = 0;
00484       }
00485       break;
00486     }
00487     case 'u': {
00488       /*
00489       **    unixepoch
00490       **
00491       ** Treat the current value of p->rJD as the number of
00492       ** seconds since 1970.  Convert to a real julian day number.
00493       */
00494       if( strcmp(z, "unixepoch")==0 && p->validJD ){
00495         p->rJD = p->rJD/86400.0 + 2440587.5;
00496         clearYMD_HMS_TZ(p);
00497         rc = 0;
00498       }else if( strcmp(z, "utc")==0 ){
00499         double c1;
00500         computeJD(p);
00501         c1 = localtimeOffset(p);
00502         p->rJD -= c1;
00503         clearYMD_HMS_TZ(p);
00504         p->rJD += c1 - localtimeOffset(p);
00505         rc = 0;
00506       }
00507       break;
00508     }
00509     case 'w': {
00510       /*
00511       **    weekday N
00512       **
00513       ** Move the date to the same time on the next occurrance of
00514       ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
00515       ** date is already on the appropriate weekday, this is a no-op.
00516       */
00517       if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0
00518                  && (n=r)==r && n>=0 && r<7 ){
00519         int Z;
00520         computeYMD_HMS(p);
00521         p->validTZ = 0;
00522         p->validJD = 0;
00523         computeJD(p);
00524         Z = p->rJD + 1.5;
00525         Z %= 7;
00526         if( Z>n ) Z -= 7;
00527         p->rJD += n - Z;
00528         clearYMD_HMS_TZ(p);
00529         rc = 0;
00530       }
00531       break;
00532     }
00533     case 's': {
00534       /*
00535       **    start of TTTTT
00536       **
00537       ** Move the date backwards to the beginning of the current day,
00538       ** or month or year.
00539       */
00540       if( strncmp(z, "start of ", 9)!=0 ) break;
00541       z += 9;
00542       computeYMD(p);
00543       p->validHMS = 1;
00544       p->h = p->m = 0;
00545       p->s = 0.0;
00546       p->validTZ = 0;
00547       p->validJD = 0;
00548       if( strcmp(z,"month")==0 ){
00549         p->D = 1;
00550         rc = 0;
00551       }else if( strcmp(z,"year")==0 ){
00552         computeYMD(p);
00553         p->M = 1;
00554         p->D = 1;
00555         rc = 0;
00556       }else if( strcmp(z,"day")==0 ){
00557         rc = 0;
00558       }
00559       break;
00560     }
00561     case '+':
00562     case '-':
00563     case '0':
00564     case '1':
00565     case '2':
00566     case '3':
00567     case '4':
00568     case '5':
00569     case '6':
00570     case '7':
00571     case '8':
00572     case '9': {
00573       n = getValue(z, &r);
00574       if( n<=0 ) break;
00575       if( z[n]==':' ){
00576         /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
00577         ** specified number of hours, minutes, seconds, and fractional seconds
00578         ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
00579         ** omitted.
00580         */
00581         const char *z2 = z;
00582         DateTime tx;
00583         int day;
00584         if( !isdigit(*z2) ) z2++;
00585         memset(&tx, 0, sizeof(tx));
00586         if( parseHhMmSs(z2, &tx) ) break;
00587         computeJD(&tx);
00588         tx.rJD -= 0.5;
00589         day = (int)tx.rJD;
00590         tx.rJD -= day;
00591         if( z[0]=='-' ) tx.rJD = -tx.rJD;
00592         computeJD(p);
00593         clearYMD_HMS_TZ(p);
00594        p->rJD += tx.rJD;
00595         rc = 0;
00596         break;
00597       }
00598       z += n;
00599       while( isspace(z[0]) ) z++;
00600       n = strlen(z);
00601       if( n>10 || n<3 ) break;
00602       if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
00603       computeJD(p);
00604       rc = 0;
00605       if( n==3 && strcmp(z,"day")==0 ){
00606         p->rJD += r;
00607       }else if( n==4 && strcmp(z,"hour")==0 ){
00608         p->rJD += r/24.0;
00609       }else if( n==6 && strcmp(z,"minute")==0 ){
00610         p->rJD += r/(24.0*60.0);
00611       }else if( n==6 && strcmp(z,"second")==0 ){
00612         p->rJD += r/(24.0*60.0*60.0);
00613       }else if( n==5 && strcmp(z,"month")==0 ){
00614         int x, y;
00615         computeYMD_HMS(p);
00616         p->M += r;
00617         x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
00618         p->Y += x;
00619         p->M -= x*12;
00620         p->validJD = 0;
00621         computeJD(p);
00622         y = r;
00623         if( y!=r ){
00624           p->rJD += (r - y)*30.0;
00625         }
00626       }else if( n==4 && strcmp(z,"year")==0 ){
00627         computeYMD_HMS(p);
00628         p->Y += r;
00629         p->validJD = 0;
00630         computeJD(p);
00631       }else{
00632         rc = 1;
00633       }
00634       clearYMD_HMS_TZ(p);
00635       break;
00636     }
00637     default: {
00638       break;
00639     }
00640   }
00641   return rc;
00642 }
00643 
00644 /*
00645 ** Process time function arguments.  argv[0] is a date-time stamp.
00646 ** argv[1] and following are modifiers.  Parse them all and write
00647 ** the resulting time into the DateTime structure p.  Return 0
00648 ** on success and 1 if there are any errors.
00649 */
00650 static int isDate(int argc, const char **argv, DateTime *p){
00651   int i;
00652   if( argc==0 ) return 1;
00653   if( argv[0]==0 || parseDateOrTime(argv[0], p) ) return 1;
00654   for(i=1; i<argc; i++){
00655     if( argv[i]==0 || parseModifier(argv[i], p) ) return 1;
00656   }
00657   return 0;
00658 }
00659 
00660 
00661 /*
00662 ** The following routines implement the various date and time functions
00663 ** of SQLite.
00664 */
00665 
00666 /*
00667 **    julianday( TIMESTRING, MOD, MOD, ...)
00668 **
00669 ** Return the julian day number of the date specified in the arguments
00670 */
00671 static void juliandayFunc(sqlite_func *context, int argc, const char **argv){
00672   DateTime x;
00673   if( isDate(argc, argv, &x)==0 ){
00674     computeJD(&x);
00675     sqlite_set_result_double(context, x.rJD);
00676   }
00677 }
00678 
00679 /*
00680 **    datetime( TIMESTRING, MOD, MOD, ...)
00681 **
00682 ** Return YYYY-MM-DD HH:MM:SS
00683 */
00684 static void datetimeFunc(sqlite_func *context, int argc, const char **argv){
00685   DateTime x;
00686   if( isDate(argc, argv, &x)==0 ){
00687     char zBuf[100];
00688     computeYMD_HMS(&x);
00689     sprintf(zBuf, "%04d-%02d-%02d %02d:%02d:%02d",x.Y, x.M, x.D, x.h, x.m,
00690            (int)(x.s));
00691     sqlite_set_result_string(context, zBuf, -1);
00692   }
00693 }
00694 
00695 /*
00696 **    time( TIMESTRING, MOD, MOD, ...)
00697 **
00698 ** Return HH:MM:SS
00699 */
00700 static void timeFunc(sqlite_func *context, int argc, const char **argv){
00701   DateTime x;
00702   if( isDate(argc, argv, &x)==0 ){
00703     char zBuf[100];
00704     computeHMS(&x);
00705     sprintf(zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
00706     sqlite_set_result_string(context, zBuf, -1);
00707   }
00708 }
00709 
00710 /*
00711 **    date( TIMESTRING, MOD, MOD, ...)
00712 **
00713 ** Return YYYY-MM-DD
00714 */
00715 static void dateFunc(sqlite_func *context, int argc, const char **argv){
00716   DateTime x;
00717   if( isDate(argc, argv, &x)==0 ){
00718     char zBuf[100];
00719     computeYMD(&x);
00720     sprintf(zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
00721     sqlite_set_result_string(context, zBuf, -1);
00722   }
00723 }
00724 
00725 /*
00726 **    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
00727 **
00728 ** Return a string described by FORMAT.  Conversions as follows:
00729 **
00730 **   %d  day of month
00731 **   %f  ** fractional seconds  SS.SSS
00732 **   %H  hour 00-24
00733 **   %j  day of year 000-366
00734 **   %J  ** Julian day number
00735 **   %m  month 01-12
00736 **   %M  minute 00-59
00737 **   %s  seconds since 1970-01-01
00738 **   %S  seconds 00-59
00739 **   %w  day of week 0-6  sunday==0
00740 **   %W  week of year 00-53
00741 **   %Y  year 0000-9999
00742 **   %%  %
00743 */
00744 static void strftimeFunc(sqlite_func *context, int argc, const char **argv){
00745   DateTime x;
00746   int n, i, j;
00747   char *z;
00748   const char *zFmt = argv[0];
00749   char zBuf[100];
00750   if( argv[0]==0 || isDate(argc-1, argv+1, &x) ) return;
00751   for(i=0, n=1; zFmt[i]; i++, n++){
00752     if( zFmt[i]=='%' ){
00753       switch( zFmt[i+1] ){
00754         case 'd':
00755         case 'H':
00756         case 'm':
00757         case 'M':
00758         case 'S':
00759         case 'W':
00760           n++;
00761           /* fall thru */
00762         case 'w':
00763         case '%':
00764           break;
00765         case 'f':
00766           n += 8;
00767           break;
00768         case 'j':
00769           n += 3;
00770           break;
00771         case 'Y':
00772           n += 8;
00773           break;
00774         case 's':
00775         case 'J':
00776           n += 50;
00777           break;
00778         default:
00779           return;  /* ERROR.  return a NULL */
00780       }
00781       i++;
00782     }
00783   }
00784   if( n<sizeof(zBuf) ){
00785     z = zBuf;
00786   }else{
00787     z = sqliteMalloc( n );
00788     if( z==0 ) return;
00789   }
00790   computeJD(&x);
00791   computeYMD_HMS(&x);
00792   for(i=j=0; zFmt[i]; i++){
00793     if( zFmt[i]!='%' ){
00794       z[j++] = zFmt[i];
00795     }else{
00796       i++;
00797       switch( zFmt[i] ){
00798         case 'd':  sprintf(&z[j],"%02d",x.D); j+=2; break;
00799         case 'f': {
00800           int s = x.s;
00801           int ms = (x.s - s)*1000.0;
00802           sprintf(&z[j],"%02d.%03d",s,ms);
00803           j += strlen(&z[j]);
00804           break;
00805         }
00806         case 'H':  sprintf(&z[j],"%02d",x.h); j+=2; break;
00807         case 'W': /* Fall thru */
00808         case 'j': {
00809           int n;             /* Number of days since 1st day of year */
00810           DateTime y = x;
00811           y.validJD = 0;
00812           y.M = 1;
00813           y.D = 1;
00814           computeJD(&y);
00815           n = x.rJD - y.rJD;
00816           if( zFmt[i]=='W' ){
00817             int wd;   /* 0=Monday, 1=Tuesday, ... 6=Sunday */
00818             wd = ((int)(x.rJD+0.5)) % 7;
00819             sprintf(&z[j],"%02d",(n+7-wd)/7);
00820             j += 2;
00821           }else{
00822             sprintf(&z[j],"%03d",n+1);
00823             j += 3;
00824           }
00825           break;
00826         }
00827         case 'J':  sprintf(&z[j],"%.16g",x.rJD); j+=strlen(&z[j]); break;
00828         case 'm':  sprintf(&z[j],"%02d",x.M); j+=2; break;
00829         case 'M':  sprintf(&z[j],"%02d",x.m); j+=2; break;
00830         case 's': {
00831           sprintf(&z[j],"%d",(int)((x.rJD-2440587.5)*86400.0 + 0.5));
00832           j += strlen(&z[j]);
00833           break;
00834         }
00835         case 'S':  sprintf(&z[j],"%02d",(int)(x.s+0.5)); j+=2; break;
00836         case 'w':  z[j++] = (((int)(x.rJD+1.5)) % 7) + '0'; break;
00837         case 'Y':  sprintf(&z[j],"%04d",x.Y); j+=strlen(&z[j]); break;
00838         case '%':  z[j++] = '%'; break;
00839       }
00840     }
00841   }
00842   z[j] = 0;
00843   sqlite_set_result_string(context, z, -1);
00844   if( z!=zBuf ){
00845     sqliteFree(z);
00846   }
00847 }
00848 
00849 
00850 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
00851 
00852 /*
00853 ** This function registered all of the above C functions as SQL
00854 ** functions.  This should be the only routine in this file with
00855 ** external linkage.
00856 */
00857 void sqliteRegisterDateTimeFunctions(sqlite *db){
00858 #ifndef SQLITE_OMIT_DATETIME_FUNCS
00859   static struct {
00860      char *zName;
00861      int nArg;
00862      int dataType;
00863      void (*xFunc)(sqlite_func*,int,const char**);
00864   } aFuncs[] = {
00865     { "julianday", -1, SQLITE_NUMERIC, juliandayFunc   },
00866     { "date",      -1, SQLITE_TEXT,    dateFunc        },
00867     { "time",      -1, SQLITE_TEXT,    timeFunc        },
00868     { "datetime",  -1, SQLITE_TEXT,    datetimeFunc    },
00869     { "strftime",  -1, SQLITE_TEXT,    strftimeFunc    },
00870   };
00871   int i;
00872 
00873   for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
00874     sqlite_create_function(db, aFuncs[i].zName,
00875            aFuncs[i].nArg, aFuncs[i].xFunc, 0);
00876     if( aFuncs[i].xFunc ){
00877       sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
00878     }
00879   }
00880 #endif
00881 }