Back to index

lightning-sunbird  0.9+nobinonly
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 ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
00017 ** All other code has file scope.
00018 **
00019 ** $Id: date.c,v 1.54 2006/01/31 20:49:13 drh Exp $
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 "sqliteInt.h"
00051 #include "os.h"
00052 #include <ctype.h>
00053 #include <stdlib.h>
00054 #include <assert.h>
00055 #include <time.h>
00056 
00057 #ifndef SQLITE_OMIT_DATETIME_FUNCS
00058 
00059 /*
00060 ** A structure for holding a single date and time.
00061 */
00062 typedef struct DateTime DateTime;
00063 struct DateTime {
00064   double rJD;      /* The julian day number */
00065   int Y, M, D;     /* Year, month, and day */
00066   int h, m;        /* Hour and minutes */
00067   int tz;          /* Timezone offset in minutes */
00068   double s;        /* Seconds */
00069   char validYMD;   /* True if Y,M,D are valid */
00070   char validHMS;   /* True if h,m,s are valid */
00071   char validJD;    /* True if rJD is valid */
00072   char validTZ;    /* True if tz is valid */
00073 };
00074 
00075 
00076 /*
00077 ** Convert zDate into one or more integers.  Additional arguments
00078 ** come in groups of 5 as follows:
00079 **
00080 **       N       number of digits in the integer
00081 **       min     minimum allowed value of the integer
00082 **       max     maximum allowed value of the integer
00083 **       nextC   first character after the integer
00084 **       pVal    where to write the integers value.
00085 **
00086 ** Conversions continue until one with nextC==0 is encountered.
00087 ** The function returns the number of successful conversions.
00088 */
00089 static int getDigits(const char *zDate, ...){
00090   va_list ap;
00091   int val;
00092   int N;
00093   int min;
00094   int max;
00095   int nextC;
00096   int *pVal;
00097   int cnt = 0;
00098   va_start(ap, zDate);
00099   do{
00100     N = va_arg(ap, int);
00101     min = va_arg(ap, int);
00102     max = va_arg(ap, int);
00103     nextC = va_arg(ap, int);
00104     pVal = va_arg(ap, int*);
00105     val = 0;
00106     while( N-- ){
00107       if( !isdigit(*(u8*)zDate) ){
00108         goto end_getDigits;
00109       }
00110       val = val*10 + *zDate - '0';
00111       zDate++;
00112     }
00113     if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){
00114       goto end_getDigits;
00115     }
00116     *pVal = val;
00117     zDate++;
00118     cnt++;
00119   }while( nextC );
00120 end_getDigits:
00121   va_end(ap);
00122   return cnt;
00123 }
00124 
00125 /*
00126 ** Read text from z[] and convert into a floating point number.  Return
00127 ** the number of digits converted.
00128 */
00129 #define getValue sqlite3AtoF
00130 
00131 /*
00132 ** Parse a timezone extension on the end of a date-time.
00133 ** The extension is of the form:
00134 **
00135 **        (+/-)HH:MM
00136 **
00137 ** If the parse is successful, write the number of minutes
00138 ** of change in *pnMin and return 0.  If a parser error occurs,
00139 ** return 0.
00140 **
00141 ** A missing specifier is not considered an error.
00142 */
00143 static int parseTimezone(const char *zDate, DateTime *p){
00144   int sgn = 0;
00145   int nHr, nMn;
00146   while( isspace(*(u8*)zDate) ){ zDate++; }
00147   p->tz = 0;
00148   if( *zDate=='-' ){
00149     sgn = -1;
00150   }else if( *zDate=='+' ){
00151     sgn = +1;
00152   }else{
00153     return *zDate!=0;
00154   }
00155   zDate++;
00156   if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){
00157     return 1;
00158   }
00159   zDate += 5;
00160   p->tz = sgn*(nMn + nHr*60);
00161   while( isspace(*(u8*)zDate) ){ zDate++; }
00162   return *zDate!=0;
00163 }
00164 
00165 /*
00166 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
00167 ** The HH, MM, and SS must each be exactly 2 digits.  The
00168 ** fractional seconds FFFF can be one or more digits.
00169 **
00170 ** Return 1 if there is a parsing error and 0 on success.
00171 */
00172 static int parseHhMmSs(const char *zDate, DateTime *p){
00173   int h, m, s;
00174   double ms = 0.0;
00175   if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){
00176     return 1;
00177   }
00178   zDate += 5;
00179   if( *zDate==':' ){
00180     zDate++;
00181     if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
00182       return 1;
00183     }
00184     zDate += 2;
00185     if( *zDate=='.' && isdigit((u8)zDate[1]) ){
00186       double rScale = 1.0;
00187       zDate++;
00188       while( isdigit(*(u8*)zDate) ){
00189         ms = ms*10.0 + *zDate - '0';
00190         rScale *= 10.0;
00191         zDate++;
00192       }
00193       ms /= rScale;
00194     }
00195   }else{
00196     s = 0;
00197   }
00198   p->validJD = 0;
00199   p->validHMS = 1;
00200   p->h = h;
00201   p->m = m;
00202   p->s = s + ms;
00203   if( parseTimezone(zDate, p) ) return 1;
00204   p->validTZ = p->tz!=0;
00205   return 0;
00206 }
00207 
00208 /*
00209 ** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume
00210 ** that the YYYY-MM-DD is according to the Gregorian calendar.
00211 **
00212 ** Reference:  Meeus page 61
00213 */
00214 static void computeJD(DateTime *p){
00215   int Y, M, D, A, B, X1, X2;
00216 
00217   if( p->validJD ) return;
00218   if( p->validYMD ){
00219     Y = p->Y;
00220     M = p->M;
00221     D = p->D;
00222   }else{
00223     Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */
00224     M = 1;
00225     D = 1;
00226   }
00227   if( M<=2 ){
00228     Y--;
00229     M += 12;
00230   }
00231   A = Y/100;
00232   B = 2 - A + (A/4);
00233   X1 = 365.25*(Y+4716);
00234   X2 = 30.6001*(M+1);
00235   p->rJD = X1 + X2 + D + B - 1524.5;
00236   p->validJD = 1;
00237   p->validYMD = 0;
00238   if( p->validHMS ){
00239     p->rJD += (p->h*3600.0 + p->m*60.0 + p->s)/86400.0;
00240     if( p->validTZ ){
00241       p->rJD -= p->tz*60/86400.0;
00242       p->validHMS = 0;
00243       p->validTZ = 0;
00244     }
00245   }
00246 }
00247 
00248 /*
00249 ** Parse dates of the form
00250 **
00251 **     YYYY-MM-DD HH:MM:SS.FFF
00252 **     YYYY-MM-DD HH:MM:SS
00253 **     YYYY-MM-DD HH:MM
00254 **     YYYY-MM-DD
00255 **
00256 ** Write the result into the DateTime structure and return 0
00257 ** on success and 1 if the input string is not a well-formed
00258 ** date.
00259 */
00260 static int parseYyyyMmDd(const char *zDate, DateTime *p){
00261   int Y, M, D, neg;
00262 
00263   if( zDate[0]=='-' ){
00264     zDate++;
00265     neg = 1;
00266   }else{
00267     neg = 0;
00268   }
00269   if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){
00270     return 1;
00271   }
00272   zDate += 10;
00273   while( isspace(*(u8*)zDate) || 'T'==*(u8*)zDate ){ zDate++; }
00274   if( parseHhMmSs(zDate, p)==0 ){
00275     /* We got the time */
00276   }else if( *zDate==0 ){
00277     p->validHMS = 0;
00278   }else{
00279     return 1;
00280   }
00281   p->validJD = 0;
00282   p->validYMD = 1;
00283   p->Y = neg ? -Y : Y;
00284   p->M = M;
00285   p->D = D;
00286   if( p->validTZ ){
00287     computeJD(p);
00288   }
00289   return 0;
00290 }
00291 
00292 /*
00293 ** Attempt to parse the given string into a Julian Day Number.  Return
00294 ** the number of errors.
00295 **
00296 ** The following are acceptable forms for the input string:
00297 **
00298 **      YYYY-MM-DD HH:MM:SS.FFF  +/-HH:MM
00299 **      DDDD.DD 
00300 **      now
00301 **
00302 ** In the first form, the +/-HH:MM is always optional.  The fractional
00303 ** seconds extension (the ".FFF") is optional.  The seconds portion
00304 ** (":SS.FFF") is option.  The year and date can be omitted as long
00305 ** as there is a time string.  The time string can be omitted as long
00306 ** as there is a year and date.
00307 */
00308 static int parseDateOrTime(const char *zDate, DateTime *p){
00309   memset(p, 0, sizeof(*p));
00310   if( parseYyyyMmDd(zDate,p)==0 ){
00311     return 0;
00312   }else if( parseHhMmSs(zDate, p)==0 ){
00313     return 0;
00314   }else if( sqlite3StrICmp(zDate,"now")==0){
00315     double r;
00316     sqlite3OsCurrentTime(&r);
00317     p->rJD = r;
00318     p->validJD = 1;
00319     return 0;
00320   }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){
00321     getValue(zDate, &p->rJD);
00322     p->validJD = 1;
00323     return 0;
00324   }
00325   return 1;
00326 }
00327 
00328 /*
00329 ** Compute the Year, Month, and Day from the julian day number.
00330 */
00331 static void computeYMD(DateTime *p){
00332   int Z, A, B, C, D, E, X1;
00333   if( p->validYMD ) return;
00334   if( !p->validJD ){
00335     p->Y = 2000;
00336     p->M = 1;
00337     p->D = 1;
00338   }else{
00339     Z = p->rJD + 0.5;
00340     A = (Z - 1867216.25)/36524.25;
00341     A = Z + 1 + A - (A/4);
00342     B = A + 1524;
00343     C = (B - 122.1)/365.25;
00344     D = 365.25*C;
00345     E = (B-D)/30.6001;
00346     X1 = 30.6001*E;
00347     p->D = B - D - X1;
00348     p->M = E<14 ? E-1 : E-13;
00349     p->Y = p->M>2 ? C - 4716 : C - 4715;
00350   }
00351   p->validYMD = 1;
00352 }
00353 
00354 /*
00355 ** Compute the Hour, Minute, and Seconds from the julian day number.
00356 */
00357 static void computeHMS(DateTime *p){
00358   int Z, s;
00359   if( p->validHMS ) return;
00360   Z = p->rJD + 0.5;
00361   s = (p->rJD + 0.5 - Z)*86400000.0 + 0.5;
00362   p->s = 0.001*s;
00363   s = p->s;
00364   p->s -= s;
00365   p->h = s/3600;
00366   s -= p->h*3600;
00367   p->m = s/60;
00368   p->s += s - p->m*60;
00369   p->validHMS = 1;
00370 }
00371 
00372 /*
00373 ** Compute both YMD and HMS
00374 */
00375 static void computeYMD_HMS(DateTime *p){
00376   computeYMD(p);
00377   computeHMS(p);
00378 }
00379 
00380 /*
00381 ** Clear the YMD and HMS and the TZ
00382 */
00383 static void clearYMD_HMS_TZ(DateTime *p){
00384   p->validYMD = 0;
00385   p->validHMS = 0;
00386   p->validTZ = 0;
00387 }
00388 
00389 /*
00390 ** Compute the difference (in days) between localtime and UTC (a.k.a. GMT)
00391 ** for the time value p where p is in UTC.
00392 */
00393 static double localtimeOffset(DateTime *p){
00394   DateTime x, y;
00395   time_t t;
00396   struct tm *pTm;
00397   x = *p;
00398   computeYMD_HMS(&x);
00399   if( x.Y<1971 || x.Y>=2038 ){
00400     x.Y = 2000;
00401     x.M = 1;
00402     x.D = 1;
00403     x.h = 0;
00404     x.m = 0;
00405     x.s = 0.0;
00406   } else {
00407     int s = x.s + 0.5;
00408     x.s = s;
00409   }
00410   x.tz = 0;
00411   x.validJD = 0;
00412   computeJD(&x);
00413   t = (x.rJD-2440587.5)*86400.0 + 0.5;
00414   sqlite3OsEnterMutex();
00415   pTm = localtime(&t);
00416   y.Y = pTm->tm_year + 1900;
00417   y.M = pTm->tm_mon + 1;
00418   y.D = pTm->tm_mday;
00419   y.h = pTm->tm_hour;
00420   y.m = pTm->tm_min;
00421   y.s = pTm->tm_sec;
00422   sqlite3OsLeaveMutex();
00423   y.validYMD = 1;
00424   y.validHMS = 1;
00425   y.validJD = 0;
00426   y.validTZ = 0;
00427   computeJD(&y);
00428   return y.rJD - x.rJD;
00429 }
00430 
00431 /*
00432 ** Process a modifier to a date-time stamp.  The modifiers are
00433 ** as follows:
00434 **
00435 **     NNN days
00436 **     NNN hours
00437 **     NNN minutes
00438 **     NNN.NNNN seconds
00439 **     NNN months
00440 **     NNN years
00441 **     start of month
00442 **     start of year
00443 **     start of week
00444 **     start of day
00445 **     weekday N
00446 **     unixepoch
00447 **     localtime
00448 **     utc
00449 **
00450 ** Return 0 on success and 1 if there is any kind of error.
00451 */
00452 static int parseModifier(const char *zMod, DateTime *p){
00453   int rc = 1;
00454   int n;
00455   double r;
00456   char *z, zBuf[30];
00457   z = zBuf;
00458   for(n=0; n<sizeof(zBuf)-1 && zMod[n]; n++){
00459     z[n] = tolower(zMod[n]);
00460   }
00461   z[n] = 0;
00462   switch( z[0] ){
00463     case 'l': {
00464       /*    localtime
00465       **
00466       ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
00467       ** show local time.
00468       */
00469       if( strcmp(z, "localtime")==0 ){
00470         computeJD(p);
00471         p->rJD += localtimeOffset(p);
00472         clearYMD_HMS_TZ(p);
00473         rc = 0;
00474       }
00475       break;
00476     }
00477     case 'u': {
00478       /*
00479       **    unixepoch
00480       **
00481       ** Treat the current value of p->rJD as the number of
00482       ** seconds since 1970.  Convert to a real julian day number.
00483       */
00484       if( strcmp(z, "unixepoch")==0 && p->validJD ){
00485         p->rJD = p->rJD/86400.0 + 2440587.5;
00486         clearYMD_HMS_TZ(p);
00487         rc = 0;
00488       }else if( strcmp(z, "utc")==0 ){
00489         double c1;
00490         computeJD(p);
00491         c1 = localtimeOffset(p);
00492         p->rJD -= c1;
00493         clearYMD_HMS_TZ(p);
00494         p->rJD += c1 - localtimeOffset(p);
00495         rc = 0;
00496       }
00497       break;
00498     }
00499     case 'w': {
00500       /*
00501       **    weekday N
00502       **
00503       ** Move the date to the same time on the next occurrence of
00504       ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
00505       ** date is already on the appropriate weekday, this is a no-op.
00506       */
00507       if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0
00508                  && (n=r)==r && n>=0 && r<7 ){
00509         int Z;
00510         computeYMD_HMS(p);
00511         p->validTZ = 0;
00512         p->validJD = 0;
00513         computeJD(p);
00514         Z = p->rJD + 1.5;
00515         Z %= 7;
00516         if( Z>n ) Z -= 7;
00517         p->rJD += n - Z;
00518         clearYMD_HMS_TZ(p);
00519         rc = 0;
00520       }
00521       break;
00522     }
00523     case 's': {
00524       /*
00525       **    start of TTTTT
00526       **
00527       ** Move the date backwards to the beginning of the current day,
00528       ** or month or year.
00529       */
00530       if( strncmp(z, "start of ", 9)!=0 ) break;
00531       z += 9;
00532       computeYMD(p);
00533       p->validHMS = 1;
00534       p->h = p->m = 0;
00535       p->s = 0.0;
00536       p->validTZ = 0;
00537       p->validJD = 0;
00538       if( strcmp(z,"month")==0 ){
00539         p->D = 1;
00540         rc = 0;
00541       }else if( strcmp(z,"year")==0 ){
00542         computeYMD(p);
00543         p->M = 1;
00544         p->D = 1;
00545         rc = 0;
00546       }else if( strcmp(z,"day")==0 ){
00547         rc = 0;
00548       }
00549       break;
00550     }
00551     case '+':
00552     case '-':
00553     case '0':
00554     case '1':
00555     case '2':
00556     case '3':
00557     case '4':
00558     case '5':
00559     case '6':
00560     case '7':
00561     case '8':
00562     case '9': {
00563       n = getValue(z, &r);
00564       if( n<=0 ) break;
00565       if( z[n]==':' ){
00566         /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
00567         ** specified number of hours, minutes, seconds, and fractional seconds
00568         ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
00569         ** omitted.
00570         */
00571         const char *z2 = z;
00572         DateTime tx;
00573         int day;
00574         if( !isdigit(*(u8*)z2) ) z2++;
00575         memset(&tx, 0, sizeof(tx));
00576         if( parseHhMmSs(z2, &tx) ) break;
00577         computeJD(&tx);
00578         tx.rJD -= 0.5;
00579         day = (int)tx.rJD;
00580         tx.rJD -= day;
00581         if( z[0]=='-' ) tx.rJD = -tx.rJD;
00582         computeJD(p);
00583         clearYMD_HMS_TZ(p);
00584        p->rJD += tx.rJD;
00585         rc = 0;
00586         break;
00587       }
00588       z += n;
00589       while( isspace(*(u8*)z) ) z++;
00590       n = strlen(z);
00591       if( n>10 || n<3 ) break;
00592       if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
00593       computeJD(p);
00594       rc = 0;
00595       if( n==3 && strcmp(z,"day")==0 ){
00596         p->rJD += r;
00597       }else if( n==4 && strcmp(z,"hour")==0 ){
00598         p->rJD += r/24.0;
00599       }else if( n==6 && strcmp(z,"minute")==0 ){
00600         p->rJD += r/(24.0*60.0);
00601       }else if( n==6 && strcmp(z,"second")==0 ){
00602         p->rJD += r/(24.0*60.0*60.0);
00603       }else if( n==5 && strcmp(z,"month")==0 ){
00604         int x, y;
00605         computeYMD_HMS(p);
00606         p->M += r;
00607         x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
00608         p->Y += x;
00609         p->M -= x*12;
00610         p->validJD = 0;
00611         computeJD(p);
00612         y = r;
00613         if( y!=r ){
00614           p->rJD += (r - y)*30.0;
00615         }
00616       }else if( n==4 && strcmp(z,"year")==0 ){
00617         computeYMD_HMS(p);
00618         p->Y += r;
00619         p->validJD = 0;
00620         computeJD(p);
00621       }else{
00622         rc = 1;
00623       }
00624       clearYMD_HMS_TZ(p);
00625       break;
00626     }
00627     default: {
00628       break;
00629     }
00630   }
00631   return rc;
00632 }
00633 
00634 /*
00635 ** Process time function arguments.  argv[0] is a date-time stamp.
00636 ** argv[1] and following are modifiers.  Parse them all and write
00637 ** the resulting time into the DateTime structure p.  Return 0
00638 ** on success and 1 if there are any errors.
00639 */
00640 static int isDate(int argc, sqlite3_value **argv, DateTime *p){
00641   int i;
00642   if( argc==0 ) return 1;
00643   if( SQLITE_NULL==sqlite3_value_type(argv[0]) || 
00644       parseDateOrTime((char*)sqlite3_value_text(argv[0]), p) ) return 1;
00645   for(i=1; i<argc; i++){
00646     if( SQLITE_NULL==sqlite3_value_type(argv[i]) || 
00647         parseModifier((char*)sqlite3_value_text(argv[i]), p) ) return 1;
00648   }
00649   return 0;
00650 }
00651 
00652 
00653 /*
00654 ** The following routines implement the various date and time functions
00655 ** of SQLite.
00656 */
00657 
00658 /*
00659 **    julianday( TIMESTRING, MOD, MOD, ...)
00660 **
00661 ** Return the julian day number of the date specified in the arguments
00662 */
00663 static void juliandayFunc(
00664   sqlite3_context *context,
00665   int argc,
00666   sqlite3_value **argv
00667 ){
00668   DateTime x;
00669   if( isDate(argc, argv, &x)==0 ){
00670     computeJD(&x);
00671     sqlite3_result_double(context, x.rJD);
00672   }
00673 }
00674 
00675 /*
00676 **    datetime( TIMESTRING, MOD, MOD, ...)
00677 **
00678 ** Return YYYY-MM-DD HH:MM:SS
00679 */
00680 static void datetimeFunc(
00681   sqlite3_context *context,
00682   int argc,
00683   sqlite3_value **argv
00684 ){
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     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
00692   }
00693 }
00694 
00695 /*
00696 **    time( TIMESTRING, MOD, MOD, ...)
00697 **
00698 ** Return HH:MM:SS
00699 */
00700 static void timeFunc(
00701   sqlite3_context *context,
00702   int argc,
00703   sqlite3_value **argv
00704 ){
00705   DateTime x;
00706   if( isDate(argc, argv, &x)==0 ){
00707     char zBuf[100];
00708     computeHMS(&x);
00709     sprintf(zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
00710     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
00711   }
00712 }
00713 
00714 /*
00715 **    date( TIMESTRING, MOD, MOD, ...)
00716 **
00717 ** Return YYYY-MM-DD
00718 */
00719 static void dateFunc(
00720   sqlite3_context *context,
00721   int argc,
00722   sqlite3_value **argv
00723 ){
00724   DateTime x;
00725   if( isDate(argc, argv, &x)==0 ){
00726     char zBuf[100];
00727     computeYMD(&x);
00728     sprintf(zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
00729     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
00730   }
00731 }
00732 
00733 /*
00734 **    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
00735 **
00736 ** Return a string described by FORMAT.  Conversions as follows:
00737 **
00738 **   %d  day of month
00739 **   %f  ** fractional seconds  SS.SSS
00740 **   %H  hour 00-24
00741 **   %j  day of year 000-366
00742 **   %J  ** Julian day number
00743 **   %m  month 01-12
00744 **   %M  minute 00-59
00745 **   %s  seconds since 1970-01-01
00746 **   %S  seconds 00-59
00747 **   %w  day of week 0-6  sunday==0
00748 **   %W  week of year 00-53
00749 **   %Y  year 0000-9999
00750 **   %%  %
00751 */
00752 static void strftimeFunc(
00753   sqlite3_context *context,
00754   int argc,
00755   sqlite3_value **argv
00756 ){
00757   DateTime x;
00758   int n, i, j;
00759   char *z;
00760   const char *zFmt = (const char*)sqlite3_value_text(argv[0]);
00761   char zBuf[100];
00762   if( zFmt==0 || isDate(argc-1, argv+1, &x) ) return;
00763   for(i=0, n=1; zFmt[i]; i++, n++){
00764     if( zFmt[i]=='%' ){
00765       switch( zFmt[i+1] ){
00766         case 'd':
00767         case 'H':
00768         case 'm':
00769         case 'M':
00770         case 'S':
00771         case 'W':
00772           n++;
00773           /* fall thru */
00774         case 'w':
00775         case '%':
00776           break;
00777         case 'f':
00778           n += 8;
00779           break;
00780         case 'j':
00781           n += 3;
00782           break;
00783         case 'Y':
00784           n += 8;
00785           break;
00786         case 's':
00787         case 'J':
00788           n += 50;
00789           break;
00790         default:
00791           return;  /* ERROR.  return a NULL */
00792       }
00793       i++;
00794     }
00795   }
00796   if( n<sizeof(zBuf) ){
00797     z = zBuf;
00798   }else{
00799     z = sqliteMalloc( n );
00800     if( z==0 ) return;
00801   }
00802   computeJD(&x);
00803   computeYMD_HMS(&x);
00804   for(i=j=0; zFmt[i]; i++){
00805     if( zFmt[i]!='%' ){
00806       z[j++] = zFmt[i];
00807     }else{
00808       i++;
00809       switch( zFmt[i] ){
00810         case 'd':  sprintf(&z[j],"%02d",x.D); j+=2; break;
00811         case 'f': {
00812           int s = x.s;
00813           int ms = (x.s - s)*1000.0;
00814           sprintf(&z[j],"%02d.%03d",s,ms);
00815           j += strlen(&z[j]);
00816           break;
00817         }
00818         case 'H':  sprintf(&z[j],"%02d",x.h); j+=2; break;
00819         case 'W': /* Fall thru */
00820         case 'j': {
00821           int nDay;             /* Number of days since 1st day of year */
00822           DateTime y = x;
00823           y.validJD = 0;
00824           y.M = 1;
00825           y.D = 1;
00826           computeJD(&y);
00827           nDay = x.rJD - y.rJD;
00828           if( zFmt[i]=='W' ){
00829             int wd;   /* 0=Monday, 1=Tuesday, ... 6=Sunday */
00830             wd = ((int)(x.rJD+0.5)) % 7;
00831             sprintf(&z[j],"%02d",(nDay+7-wd)/7);
00832             j += 2;
00833           }else{
00834             sprintf(&z[j],"%03d",nDay+1);
00835             j += 3;
00836           }
00837           break;
00838         }
00839         case 'J':  sprintf(&z[j],"%.16g",x.rJD); j+=strlen(&z[j]); break;
00840         case 'm':  sprintf(&z[j],"%02d",x.M); j+=2; break;
00841         case 'M':  sprintf(&z[j],"%02d",x.m); j+=2; break;
00842         case 's': {
00843           sprintf(&z[j],"%d",(int)((x.rJD-2440587.5)*86400.0 + 0.5));
00844           j += strlen(&z[j]);
00845           break;
00846         }
00847         case 'S':  sprintf(&z[j],"%02d",(int)(x.s+0.5)); j+=2; break;
00848         case 'w':  z[j++] = (((int)(x.rJD+1.5)) % 7) + '0'; break;
00849         case 'Y':  sprintf(&z[j],"%04d",x.Y); j+=strlen(&z[j]); break;
00850         case '%':  z[j++] = '%'; break;
00851       }
00852     }
00853   }
00854   z[j] = 0;
00855   sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT);
00856   if( z!=zBuf ){
00857     sqliteFree(z);
00858   }
00859 }
00860 
00861 /*
00862 ** current_time()
00863 **
00864 ** This function returns the same value as time('now').
00865 */
00866 static void ctimeFunc(
00867   sqlite3_context *context,
00868   int argc,
00869   sqlite3_value **argv
00870 ){
00871   sqlite3_value *pVal = sqlite3ValueNew();
00872   if( pVal ){
00873     sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC);
00874     timeFunc(context, 1, &pVal);
00875     sqlite3ValueFree(pVal);
00876   }
00877 }
00878 
00879 /*
00880 ** current_date()
00881 **
00882 ** This function returns the same value as date('now').
00883 */
00884 static void cdateFunc(
00885   sqlite3_context *context,
00886   int argc,
00887   sqlite3_value **argv
00888 ){
00889   sqlite3_value *pVal = sqlite3ValueNew();
00890   if( pVal ){
00891     sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC);
00892     dateFunc(context, 1, &pVal);
00893     sqlite3ValueFree(pVal);
00894   }
00895 }
00896 
00897 /*
00898 ** current_timestamp()
00899 **
00900 ** This function returns the same value as datetime('now').
00901 */
00902 static void ctimestampFunc(
00903   sqlite3_context *context,
00904   int argc,
00905   sqlite3_value **argv
00906 ){
00907   sqlite3_value *pVal = sqlite3ValueNew();
00908   if( pVal ){
00909     sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC);
00910     datetimeFunc(context, 1, &pVal);
00911     sqlite3ValueFree(pVal);
00912   }
00913 }
00914 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
00915 
00916 #ifdef SQLITE_OMIT_DATETIME_FUNCS
00917 /*
00918 ** If the library is compiled to omit the full-scale date and time
00919 ** handling (to get a smaller binary), the following minimal version
00920 ** of the functions current_time(), current_date() and current_timestamp()
00921 ** are included instead. This is to support column declarations that
00922 ** include "DEFAULT CURRENT_TIME" etc.
00923 **
00924 ** This function uses the C-library functions time(), gmtime()
00925 ** and strftime(). The format string to pass to strftime() is supplied
00926 ** as the user-data for the function.
00927 */
00928 static void currentTimeFunc(
00929   sqlite3_context *context,
00930   int argc,
00931   sqlite3_value **argv
00932 ){
00933   time_t t;
00934   char *zFormat = (char *)sqlite3_user_data(context);
00935   char zBuf[20];
00936 
00937   time(&t);
00938 #ifdef SQLITE_TEST
00939   {
00940     extern int sqlite3_current_time;  /* See os_XXX.c */
00941     if( sqlite3_current_time ){
00942       t = sqlite3_current_time;
00943     }
00944   }
00945 #endif
00946 
00947   sqlite3OsEnterMutex();
00948   strftime(zBuf, 20, zFormat, gmtime(&t));
00949   sqlite3OsLeaveMutex();
00950 
00951   sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
00952 }
00953 #endif
00954 
00955 /*
00956 ** This function registered all of the above C functions as SQL
00957 ** functions.  This should be the only routine in this file with
00958 ** external linkage.
00959 */
00960 void sqlite3RegisterDateTimeFunctions(sqlite3 *db){
00961 #ifndef SQLITE_OMIT_DATETIME_FUNCS
00962   static const struct {
00963      char *zName;
00964      int nArg;
00965      void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
00966   } aFuncs[] = {
00967     { "julianday", -1, juliandayFunc   },
00968     { "date",      -1, dateFunc        },
00969     { "time",      -1, timeFunc        },
00970     { "datetime",  -1, datetimeFunc    },
00971     { "strftime",  -1, strftimeFunc    },
00972     { "current_time",       0, ctimeFunc      },
00973     { "current_timestamp",  0, ctimestampFunc },
00974     { "current_date",       0, cdateFunc      },
00975   };
00976   int i;
00977 
00978   for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
00979     sqlite3CreateFunc(db, aFuncs[i].zName, aFuncs[i].nArg,
00980         SQLITE_UTF8, 0, aFuncs[i].xFunc, 0, 0);
00981   }
00982 #else
00983   static const struct {
00984      char *zName;
00985      char *zFormat;
00986   } aFuncs[] = {
00987     { "current_time", "%H:%M:%S" },
00988     { "current_date", "%Y-%m-%d" },
00989     { "current_timestamp", "%Y-%m-%d %H:%M:%S" }
00990   };
00991   int i;
00992 
00993   for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
00994     sqlite3CreateFunc(db, aFuncs[i].zName, 0, SQLITE_UTF8, 
00995         aFuncs[i].zFormat, currentTimeFunc, 0, 0);
00996   }
00997 #endif
00998 }