sqlite3/date.c

   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$
  20 **
  21 ** NOTES:
  22 **
  23 ** SQLite processes all times and dates as Julian Day numbers.  The
  24 ** dates and times are stored as the number of days since noon
  25 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian
  26 ** calendar system.
  27 **
  28 ** 1970-01-01 00:00:00 is JD 2440587.5
  29 ** 2000-01-01 00:00:00 is JD 2451544.5
  30 **
  31 ** This implemention requires years to be expressed as a 4-digit number
  32 ** which means that only dates between 0000-01-01 and 9999-12-31 can
  33 ** be represented, even though julian day numbers allow a much wider
  34 ** range of dates.
  35 **
  36 ** The Gregorian calendar system is used for all dates and times,
  37 ** even those that predate the Gregorian calendar.  Historians usually
  38 ** use the Julian calendar for dates prior to 1582-10-15 and for some
  39 ** dates afterwards, depending on locale.  Beware of this difference.
  40 **
  41 ** The conversion algorithms are implemented based on descriptions
  42 ** in the following text:
  43 **
  44 **      Jean Meeus
  45 **      Astronomical Algorithms, 2nd Edition, 1998
  46 **      ISBM 0-943396-61-1
  47 **      Willmann-Bell, Inc
  48 **      Richmond, Virginia (USA)
  49 */
  50 #include "sqliteInt.h"
  51 #include "os.h"
  52 #include <ctype.h>
  53 #include <stdlib.h>
  54 #include <assert.h>
  55 #include <time.h>
  56
  57 #ifndef SQLITE_OMIT_DATETIME_FUNCS
  58
  59 /*
  60 ** A structure for holding a single date and time.
  61 */
  62 typedef struct DateTime DateTime;
  63 struct DateTime {
  64   double rJD;      /* The julian day number */
  65   int Y, M, D;     /* Year, month, and day */
  66   int h, m;        /* Hour and minutes */
  67   int tz;          /* Timezone offset in minutes */
  68   double s;        /* Seconds */
  69   char validYMD;   /* True if Y,M,D are valid */
  70   char validHMS;   /* True if h,m,s are valid */
  71   char validJD;    /* True if rJD is valid */
  72   char validTZ;    /* True if tz is valid */
  73 };
  74
  75
  76 /*
  77 ** Convert zDate into one or more integers.  Additional arguments
  78 ** come in groups of 5 as follows:
  79 **
  80 **       N       number of digits in the integer
  81 **       min     minimum allowed value of the integer
  82 **       max     maximum allowed value of the integer
  83 **       nextC   first character after the integer
  84 **       pVal    where to write the integers value.
  85 **
  86 ** Conversions continue until one with nextC==0 is encountered.
  87 ** The function returns the number of successful conversions.
  88 */
  89 static int getDigits(const char *zDate, ...){
  90   va_list ap;
  91   int val;
  92   int N;
  93   int min;
  94   int max;
  95   int nextC;
  96   int *pVal;
  97   int cnt = 0;
  98   va_start(ap, zDate);
  99   do{
 100     N = va_arg(ap, int);
 101     min = va_arg(ap, int);
 102     max = va_arg(ap, int);
 103     nextC = va_arg(ap, int);
 104     pVal = va_arg(ap, int*);
 105     val = 0;
 106     while( N-- ){
 107       if( !isdigit(*(u8*)zDate) ){
 108         return cnt;
 109       }
 110       val = val*10 + *zDate - '0';
 111       zDate++;
 112     }
 113     if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){
 114       return cnt;
 115     }
 116     *pVal = val;
 117     zDate++;
 118     cnt++;
 119   }while( nextC );
 120   return cnt;
 121 }
 122
 123 /*
 124 ** Read text from z[] and convert into a floating point number.  Return
 125 ** the number of digits converted.
 126 */
 127 static int getValue(const char *z, double *pR){
 128   const char *zEnd;
 129   *pR = sqlite3AtoF(z, &zEnd);
 130   return zEnd - z;
 131 }
 132
 133 /*
 134 ** Parse a timezone extension on the end of a date-time.
 135 ** The extension is of the form:
 136 **
 137 **        (+/-)HH:MM
 138 **
 139 ** If the parse is successful, write the number of minutes
 140 ** of change in *pnMin and return 0.  If a parser error occurs,
 141 ** return 0.
 142 **
 143 ** A missing specifier is not considered an error.
 144 */
 145 static int parseTimezone(const char *zDate, DateTime *p){
 146   int sgn = 0;
 147   int nHr, nMn;
 148   while( isspace(*(u8*)zDate) ){ zDate++; }
 149   p->tz = 0;
 150   if( *zDate=='-' ){
 151     sgn = -1;
 152   }else if( *zDate=='+' ){
 153     sgn = +1;
 154   }else{
 155     return *zDate!=0;
 156   }
 157   zDate++;
 158   if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){
 159     return 1;
 160   }
 161   zDate += 5;
 162   p->tz = sgn*(nMn + nHr*60);
 163   while( isspace(*(u8*)zDate) ){ zDate++; }
 164   return *zDate!=0;
 165 }
 166
 167 /*
 168 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
 169 ** The HH, MM, and SS must each be exactly 2 digits.  The
 170 ** fractional seconds FFFF can be one or more digits.
 171 **
 172 ** Return 1 if there is a parsing error and 0 on success.
 173 */
 174 static int parseHhMmSs(const char *zDate, DateTime *p){
 175   int h, m, s;
 176   double ms = 0.0;
 177   if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){
 178     return 1;
 179   }
 180   zDate += 5;
 181   if( *zDate==':' ){
 182     zDate++;
 183     if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
 184       return 1;
 185     }
 186     zDate += 2;
 187     if( *zDate=='.' && isdigit((u8)zDate[1]) ){
 188       double rScale = 1.0;
 189       zDate++;
 190       while( isdigit(*(u8*)zDate) ){
 191         ms = ms*10.0 + *zDate - '0';
 192         rScale *= 10.0;
 193         zDate++;
 194       }
 195       ms /= rScale;
 196     }
 197   }else{
 198     s = 0;
 199   }
 200   p->validJD = 0;
 201   p->validHMS = 1;
 202   p->h = h;
 203   p->m = m;
 204   p->s = s + ms;
 205   if( parseTimezone(zDate, p) ) return 1;
 206   p->validTZ = p->tz!=0;
 207   return 0;
 208 }
 209
 210 /*
 211 ** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume
 212 ** that the YYYY-MM-DD is according to the Gregorian calendar.
 213 **
 214 ** Reference:  Meeus page 61
 215 */
 216 static void computeJD(DateTime *p){
 217   int Y, M, D, A, B, X1, X2;
 218
 219   if( p->validJD ) return;
 220   if( p->validYMD ){
 221     Y = p->Y;
 222     M = p->M;
 223     D = p->D;
 224   }else{
 225     Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */
 226     M = 1;
 227     D = 1;
 228   }
 229   if( M<=2 ){
 230     Y--;
 231     M += 12;
 232   }
 233   A = Y/100;
 234   B = 2 - A + (A/4);
 235   X1 = 365.25*(Y+4716);
 236   X2 = 30.6001*(M+1);
 237   p->rJD = X1 + X2 + D + B - 1524.5;
 238   p->validJD = 1;
 239   p->validYMD = 0;
 240   if( p->validHMS ){
 241     p->rJD += (p->h*3600.0 + p->m*60.0 + p->s)/86400.0;
 242     if( p->validTZ ){
 243       p->rJD += p->tz*60/86400.0;
 244       p->validHMS = 0;
 245       p->validTZ = 0;
 246     }
 247   }
 248 }
 249
 250 /*
 251 ** Parse dates of the form
 252 **
 253 **     YYYY-MM-DD HH:MM:SS.FFF
 254 **     YYYY-MM-DD HH:MM:SS
 255 **     YYYY-MM-DD HH:MM
 256 **     YYYY-MM-DD
 257 **
 258 ** Write the result into the DateTime structure and return 0
 259 ** on success and 1 if the input string is not a well-formed
 260 ** date.
 261 */
 262 static int parseYyyyMmDd(const char *zDate, DateTime *p){
 263   int Y, M, D, neg;
 264
 265   if( zDate[0]=='-' ){
 266     zDate++;
 267     neg = 1;
 268   }else{
 269     neg = 0;
 270   }
 271   if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){
 272     return 1;
 273   }
 274   zDate += 10;
 275   while( isspace(*(u8*)zDate) || 'T'==*(u8*)zDate ){ zDate++; }
 276   if( parseHhMmSs(zDate, p)==0 ){
 277     /* We got the time */
 278   }else if( *zDate==0 ){
 279     p->validHMS = 0;
 280   }else{
 281     return 1;
 282   }
 283   p->validJD = 0;
 284   p->validYMD = 1;
 285   p->Y = neg ? -Y : Y;
 286   p->M = M;
 287   p->D = D;
 288   if( p->validTZ ){
 289     computeJD(p);
 290   }
 291   return 0;
 292 }
 293
 294 /*
 295 ** Attempt to parse the given string into a Julian Day Number.  Return
 296 ** the number of errors.
 297 **
 298 ** The following are acceptable forms for the input string:
 299 **
 300 **      YYYY-MM-DD HH:MM:SS.FFF  +/-HH:MM
 301 **      DDDD.DD
 302 **      now
 303 **
 304 ** In the first form, the +/-HH:MM is always optional.  The fractional
 305 ** seconds extension (the ".FFF") is optional.  The seconds portion
 306 ** (":SS.FFF") is option.  The year and date can be omitted as long
 307 ** as there is a time string.  The time string can be omitted as long
 308 ** as there is a year and date.
 309 */
 310 static int parseDateOrTime(const char *zDate, DateTime *p){
 311   memset(p, 0, sizeof(*p));
 312   if( parseYyyyMmDd(zDate,p)==0 ){
 313     return 0;
 314   }else if( parseHhMmSs(zDate, p)==0 ){
 315     return 0;
 316   }else if( sqlite3StrICmp(zDate,"now")==0){
 317     double r;
 318     sqlite3OsCurrentTime(&r);
 319     p->rJD = r;
 320     p->validJD = 1;
 321     return 0;
 322   }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){
 323     p->rJD = sqlite3AtoF(zDate, 0);
 324     p->validJD = 1;
 325     return 0;
 326   }
 327   return 1;
 328 }
 329
 330 /*
 331 ** Compute the Year, Month, and Day from the julian day number.
 332 */
 333 static void computeYMD(DateTime *p){
 334   int Z, A, B, C, D, E, X1;
 335   if( p->validYMD ) return;
 336   if( !p->validJD ){
 337     p->Y = 2000;
 338     p->M = 1;
 339     p->D = 1;
 340   }else{
 341     Z = p->rJD + 0.5;
 342     A = (Z - 1867216.25)/36524.25;
 343     A = Z + 1 + A - (A/4);
 344     B = A + 1524;
 345     C = (B - 122.1)/365.25;
 346     D = 365.25*C;
 347     E = (B-D)/30.6001;
 348     X1 = 30.6001*E;
 349     p->D = B - D - X1;
 350     p->M = E<14 ? E-1 : E-13;
 351     p->Y = p->M>2 ? C - 4716 : C - 4715;
 352   }
 353   p->validYMD = 1;
 354 }
 355
 356 /*
 357 ** Compute the Hour, Minute, and Seconds from the julian day number.
 358 */
 359 static void computeHMS(DateTime *p){
 360   int Z, s;
 361   if( p->validHMS ) return;
 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   struct tm *pTm;
 399   x = *p;
 400   computeYMD_HMS(&x);
 401   if( x.Y<1971 || x.Y>=2038 ){
 402     x.Y = 2000;
 403     x.M = 1;
 404     x.D = 1;
 405     x.h = 0;
 406     x.m = 0;
 407     x.s = 0.0;
 408   } else {
 409     int s = x.s + 0.5;
 410     x.s = s;
 411   }
 412   x.tz = 0;
 413   x.validJD = 0;
 414   computeJD(&x);
 415   t = (x.rJD-2440587.5)*86400.0 + 0.5;
 416   sqlite3OsEnterMutex();
 417   pTm = localtime(&t);
 418   y.Y = pTm->tm_year + 1900;
 419   y.M = pTm->tm_mon + 1;
 420   y.D = pTm->tm_mday;
 421   y.h = pTm->tm_hour;
 422   y.m = pTm->tm_min;
 423   y.s = pTm->tm_sec;
 424   sqlite3OsLeaveMutex();
 425   y.validYMD = 1;
 426   y.validHMS = 1;
 427   y.validJD = 0;
 428   y.validTZ = 0;
 429   computeJD(&y);
 430   return y.rJD - x.rJD;
 431 }
 432
 433 /*
 434 ** Process a modifier to a date-time stamp.  The modifiers are
 435 ** as follows:
 436 **
 437 **     NNN days
 438 **     NNN hours
 439 **     NNN minutes
 440 **     NNN.NNNN seconds
 441 **     NNN months
 442 **     NNN years
 443 **     start of month
 444 **     start of year
 445 **     start of week
 446 **     start of day
 447 **     weekday N
 448 **     unixepoch
 449 **     localtime
 450 **     utc
 451 **
 452 ** Return 0 on success and 1 if there is any kind of error.
 453 */
 454 static int parseModifier(const char *zMod, DateTime *p){
 455   int rc = 1;
 456   int n;
 457   double r;
 458   char *z, zBuf[30];
 459   z = zBuf;
 460   for(n=0; n<sizeof(zBuf)-1 && zMod[n]; n++){
 461     z[n] = tolower(zMod[n]);
 462   }
 463   z[n] = 0;
 464   switch( z[0] ){
 465     case 'l': {
 466       /*    localtime
 467       **
 468       ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
 469       ** show local time.
 470       */
 471       if( strcmp(z, "localtime")==0 ){
 472         computeJD(p);
 473         p->rJD += localtimeOffset(p);
 474         clearYMD_HMS_TZ(p);
 475         rc = 0;
 476       }
 477       break;
 478     }
 479     case 'u': {
 480       /*
 481       **    unixepoch
 482       **
 483       ** Treat the current value of p->rJD as the number of
 484       ** seconds since 1970.  Convert to a real julian day number.
 485       */
 486       if( strcmp(z, "unixepoch")==0 && p->validJD ){
 487         p->rJD = p->rJD/86400.0 + 2440587.5;
 488         clearYMD_HMS_TZ(p);
 489         rc = 0;
 490       }else if( strcmp(z, "utc")==0 ){
 491         double c1;
 492         computeJD(p);
 493         c1 = localtimeOffset(p);
 494         p->rJD -= c1;
 495         clearYMD_HMS_TZ(p);
 496         p->rJD += c1 - localtimeOffset(p);
 497         rc = 0;
 498       }
 499       break;
 500     }
 501     case 'w': {
 502       /*
 503       **    weekday N
 504       **
 505       ** Move the date to the same time on the next occurrence of
 506       ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
 507       ** date is already on the appropriate weekday, this is a no-op.
 508       */
 509       if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0
 510                  && (n=r)==r && n>=0 && r<7 ){
 511         int Z;
 512         computeYMD_HMS(p);
 513         p->validTZ = 0;
 514         p->validJD = 0;
 515         computeJD(p);
 516         Z = p->rJD + 1.5;
 517         Z %= 7;
 518         if( Z>n ) Z -= 7;
 519         p->rJD += n - Z;
 520         clearYMD_HMS_TZ(p);
 521         rc = 0;
 522       }
 523       break;
 524     }
 525     case 's': {
 526       /*
 527       **    start of TTTTT
 528       **
 529       ** Move the date backwards to the beginning of the current day,
 530       ** or month or year.
 531       */
 532       if( strncmp(z, "start of ", 9)!=0 ) break;
 533       z += 9;
 534       computeYMD(p);
 535       p->validHMS = 1;
 536       p->h = p->m = 0;
 537       p->s = 0.0;
 538       p->validTZ = 0;
 539       p->validJD = 0;
 540       if( strcmp(z,"month")==0 ){
 541         p->D = 1;
 542         rc = 0;
 543       }else if( strcmp(z,"year")==0 ){
 544         computeYMD(p);
 545         p->M = 1;
 546         p->D = 1;
 547         rc = 0;
 548       }else if( strcmp(z,"day")==0 ){
 549         rc = 0;
 550       }
 551       break;
 552     }
 553     case '+':
 554     case '-':
 555     case '0':
 556     case '1':
 557     case '2':
 558     case '3':
 559     case '4':
 560     case '5':
 561     case '6':
 562     case '7':
 563     case '8':
 564     case '9': {
 565       n = getValue(z, &r);
 566       if( n<=0 ) break;
 567       if( z[n]==':' ){
 568         /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
 569         ** specified number of hours, minutes, seconds, and fractional seconds
 570         ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
 571         ** omitted.
 572         */
 573         const char *z2 = z;
 574         DateTime tx;
 575         int day;
 576         if( !isdigit(*(u8*)z2) ) z2++;
 577         memset(&tx, 0, sizeof(tx));
 578         if( parseHhMmSs(z2, &tx) ) break;
 579         computeJD(&tx);
 580         tx.rJD -= 0.5;
 581         day = (int)tx.rJD;
 582         tx.rJD -= day;
 583         if( z[0]=='-' ) tx.rJD = -tx.rJD;
 584         computeJD(p);
 585         clearYMD_HMS_TZ(p);
 586        p->rJD += tx.rJD;
 587         rc = 0;
 588         break;
 589       }
 590       z += n;
 591       while( isspace(*(u8*)z) ) z++;
 592       n = strlen(z);
 593       if( n>10 || n<3 ) break;
 594       if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
 595       computeJD(p);
 596       rc = 0;
 597       if( n==3 && strcmp(z,"day")==0 ){
 598         p->rJD += r;
 599       }else if( n==4 && strcmp(z,"hour")==0 ){
 600         p->rJD += r/24.0;
 601       }else if( n==6 && strcmp(z,"minute")==0 ){
 602         p->rJD += r/(24.0*60.0);
 603       }else if( n==6 && strcmp(z,"second")==0 ){
 604         p->rJD += r/(24.0*60.0*60.0);
 605       }else if( n==5 && strcmp(z,"month")==0 ){
 606         int x, y;
 607         computeYMD_HMS(p);
 608         p->M += r;
 609         x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
 610         p->Y += x;
 611         p->M -= x*12;
 612         p->validJD = 0;
 613         computeJD(p);
 614         y = r;
 615         if( y!=r ){
 616           p->rJD += (r - y)*30.0;
 617         }
 618       }else if( n==4 && strcmp(z,"year")==0 ){
 619         computeYMD_HMS(p);
 620         p->Y += r;
 621         p->validJD = 0;
 622         computeJD(p);
 623       }else{
 624         rc = 1;
 625       }
 626       clearYMD_HMS_TZ(p);
 627       break;
 628     }
 629     default: {
 630       break;
 631     }
 632   }
 633   return rc;
 634 }
 635
 636 /*
 637 ** Process time function arguments.  argv[0] is a date-time stamp.
 638 ** argv[1] and following are modifiers.  Parse them all and write
 639 ** the resulting time into the DateTime structure p.  Return 0
 640 ** on success and 1 if there are any errors.
 641 */
 642 static int isDate(int argc, sqlite3_value **argv, DateTime *p){
 643   int i;
 644   if( argc==0 ) return 1;
 645   if( SQLITE_NULL==sqlite3_value_type(argv[0]) ||
 646       parseDateOrTime(sqlite3_value_text(argv[0]), p) ) return 1;
 647   for(i=1; i<argc; i++){
 648     if( SQLITE_NULL==sqlite3_value_type(argv[i]) ||
 649         parseModifier(sqlite3_value_text(argv[i]), p) ) return 1;
 650   }
 651   return 0;
 652 }
 653
 654
 655 /*
 656 ** The following routines implement the various date and time functions
 657 ** of SQLite.
 658 */
 659
 660 /*
 661 **    julianday( TIMESTRING, MOD, MOD, ...)
 662 **
 663 ** Return the julian day number of the date specified in the arguments
 664 */
 665 static void juliandayFunc(
 666   sqlite3_context *context,
 667   int argc,
 668   sqlite3_value **argv
 669 ){
 670   DateTime x;
 671   if( isDate(argc, argv, &x)==0 ){
 672     computeJD(&x);
 673     sqlite3_result_double(context, x.rJD);
 674   }
 675 }
 676
 677 /*
 678 **    datetime( TIMESTRING, MOD, MOD, ...)
 679 **
 680 ** Return YYYY-MM-DD HH:MM:SS
 681 */
 682 static void datetimeFunc(
 683   sqlite3_context *context,
 684   int argc,
 685   sqlite3_value **argv
 686 ){
 687   DateTime x;
 688   if( isDate(argc, argv, &x)==0 ){
 689     char zBuf[100];
 690     computeYMD_HMS(&x);
 691     sprintf(zBuf, "%04d-%02d-%02d %02d:%02d:%02d",x.Y, x.M, x.D, x.h, x.m,
 692            (int)(x.s));
 693     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
 694   }
 695 }
 696
 697 /*
 698 **    time( TIMESTRING, MOD, MOD, ...)
 699 **
 700 ** Return HH:MM:SS
 701 */
 702 static void timeFunc(
 703   sqlite3_context *context,
 704   int argc,
 705   sqlite3_value **argv
 706 ){
 707   DateTime x;
 708   if( isDate(argc, argv, &x)==0 ){
 709     char zBuf[100];
 710     computeHMS(&x);
 711     sprintf(zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
 712     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
 713   }
 714 }
 715
 716 /*
 717 **    date( TIMESTRING, MOD, MOD, ...)
 718 **
 719 ** Return YYYY-MM-DD
 720 */
 721 static void dateFunc(
 722   sqlite3_context *context,
 723   int argc,
 724   sqlite3_value **argv
 725 ){
 726   DateTime x;
 727   if( isDate(argc, argv, &x)==0 ){
 728     char zBuf[100];
 729     computeYMD(&x);
 730     sprintf(zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
 731     sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
 732   }
 733 }
 734
 735 /*
 736 **    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
 737 **
 738 ** Return a string described by FORMAT.  Conversions as follows:
 739 **
 740 **   %d  day of month
 741 **   %f  ** fractional seconds  SS.SSS
 742 **   %H  hour 00-24
 743 **   %j  day of year 000-366
 744 **   %J  ** Julian day number
 745 **   %m  month 01-12
 746 **   %M  minute 00-59
 747 **   %s  seconds since 1970-01-01
 748 **   %S  seconds 00-59
 749 **   %w  day of week 0-6  sunday==0
 750 **   %W  week of year 00-53
 751 **   %Y  year 0000-9999
 752 **   %%  %
 753 */
 754 static void strftimeFunc(
 755   sqlite3_context *context,
 756   int argc,
 757   sqlite3_value **argv
 758 ){
 759   DateTime x;
 760   int n, i, j;
 761   char *z;
 762   const char *zFmt = sqlite3_value_text(argv[0]);
 763   char zBuf[100];
 764   if( zFmt==0 || isDate(argc-1, argv+1, &x) ) return;
 765   for(i=0, n=1; zFmt[i]; i++, n++){
 766     if( zFmt[i]=='%' ){
 767       switch( zFmt[i+1] ){
 768         case 'd':
 769         case 'H':
 770         case 'm':
 771         case 'M':
 772         case 'S':
 773         case 'W':
 774           n++;
 775           /* fall thru */
 776         case 'w':
 777         case '%':
 778           break;
 779         case 'f':
 780           n += 8;
 781           break;
 782         case 'j':
 783           n += 3;
 784           break;
 785         case 'Y':
 786           n += 8;
 787           break;
 788         case 's':
 789         case 'J':
 790           n += 50;
 791           break;
 792         default:
 793           return;  /* ERROR.  return a NULL */
 794       }
 795       i++;
 796     }
 797   }
 798   if( n<sizeof(zBuf) ){
 799     z = zBuf;
 800   }else{
 801     z = sqliteMalloc( n );
 802     if( z==0 ) return;
 803   }
 804   computeJD(&x);
 805   computeYMD_HMS(&x);
 806   for(i=j=0; zFmt[i]; i++){
 807     if( zFmt[i]!='%' ){
 808       z[j++] = zFmt[i];
 809     }else{
 810       i++;
 811       switch( zFmt[i] ){
 812         case 'd':  sprintf(&z[j],"%02d",x.D); j+=2; break;
 813         case 'f': {
 814           int s = x.s;
 815           int ms = (x.s - s)*1000.0;
 816           sprintf(&z[j],"%02d.%03d",s,ms);
 817           j += strlen(&z[j]);
 818           break;
 819         }
 820         case 'H':  sprintf(&z[j],"%02d",x.h); j+=2; break;
 821         case 'W': /* Fall thru */
 822         case 'j': {
 823           int n;             /* Number of days since 1st day of year */
 824           DateTime y = x;
 825           y.validJD = 0;
 826           y.M = 1;
 827           y.D = 1;
 828           computeJD(&y);
 829           n = x.rJD - y.rJD;
 830           if( zFmt[i]=='W' ){
 831             int wd;   /* 0=Monday, 1=Tuesday, ... 6=Sunday */
 832             wd = ((int)(x.rJD+0.5)) % 7;
 833             sprintf(&z[j],"%02d",(n+7-wd)/7);
 834             j += 2;
 835           }else{
 836             sprintf(&z[j],"%03d",n+1);
 837             j += 3;
 838           }
 839           break;
 840         }
 841         case 'J':  sprintf(&z[j],"%.16g",x.rJD); j+=strlen(&z[j]); break;
 842         case 'm':  sprintf(&z[j],"%02d",x.M); j+=2; break;
 843         case 'M':  sprintf(&z[j],"%02d",x.m); j+=2; break;
 844         case 's': {
 845           sprintf(&z[j],"%d",(int)((x.rJD-2440587.5)*86400.0 + 0.5));
 846           j += strlen(&z[j]);
 847           break;
 848         }
 849         case 'S':  sprintf(&z[j],"%02d",(int)(x.s+0.5)); j+=2; break;
 850         case 'w':  z[j++] = (((int)(x.rJD+1.5)) % 7) + '0'; break;
 851         case 'Y':  sprintf(&z[j],"%04d",x.Y); j+=strlen(&z[j]); break;
 852         case '%':  z[j++] = '%'; break;
 853       }
 854     }
 855   }
 856   z[j] = 0;
 857   sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT);
 858   if( z!=zBuf ){
 859     sqliteFree(z);
 860   }
 861 }
 862
 863 /*
 864 ** current_time()
 865 **
 866 ** This function returns the same value as time('now').
 867 */
 868 static void ctimeFunc(
 869   sqlite3_context *context,
 870   int argc,
 871   sqlite3_value **argv
 872 ){
 873   sqlite3_value *pVal = sqlite3ValueNew();
 874   if( pVal ){
 875     sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC);
 876     timeFunc(context, 1, &pVal);
 877     sqlite3ValueFree(pVal);
 878   }
 879 }
 880
 881 /*
 882 ** current_date()
 883 **
 884 ** This function returns the same value as date('now').
 885 */
 886 static void cdateFunc(
 887   sqlite3_context *context,
 888   int argc,
 889   sqlite3_value **argv
 890 ){
 891   sqlite3_value *pVal = sqlite3ValueNew();
 892   if( pVal ){
 893     sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC);
 894     dateFunc(context, 1, &pVal);
 895     sqlite3ValueFree(pVal);
 896   }
 897 }
 898
 899 /*
 900 ** current_timestamp()
 901 **
 902 ** This function returns the same value as datetime('now').
 903 */
 904 static void ctimestampFunc(
 905   sqlite3_context *context,
 906   int argc,
 907   sqlite3_value **argv
 908 ){
 909   sqlite3_value *pVal = sqlite3ValueNew();
 910   if( pVal ){
 911     sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC);
 912     datetimeFunc(context, 1, &pVal);
 913     sqlite3ValueFree(pVal);
 914   }
 915 }
 916 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
 917
 918 #ifdef SQLITE_OMIT_DATETIME_FUNCS
 919 /*
 920 ** If the library is compiled to omit the full-scale date and time
 921 ** handling (to get a smaller binary), the following minimal version
 922 ** of the functions current_time(), current_date() and current_timestamp()
 923 ** are included instead. This is to support column declarations that
 924 ** include "DEFAULT CURRENT_TIME" etc.
 925 **
 926 ** This function uses the C-library functions time(), gmtime()
 927 ** and strftime(). The format string to pass to strftime() is supplied
 928 ** as the user-data for the function.
 929 */
 930 static void currentTimeFunc(
 931   sqlite3_context *context,
 932   int argc,
 933   sqlite3_value **argv
 934 ){
 935   time_t t;
 936   char *zFormat = (char *)sqlite3_user_data(context);
 937   char zBuf[20];
 938
 939   time(&t);
 940 #ifdef SQLITE_TEST
 941   {
 942     extern int sqlite3_current_time;  /* See os_XXX.c */
 943     if( sqlite3_current_time ){
 944       t = sqlite3_current_time;
 945     }
 946   }
 947 #endif
 948
 949   sqlite3OsEnterMutex();
 950   strftime(zBuf, 20, zFormat, gmtime(&t));
 951   sqlite3OsLeaveMutex();
 952
 953   sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
 954 }
 955 #endif
 956
 957 /*
 958 ** This function registered all of the above C functions as SQL
 959 ** functions.  This should be the only routine in this file with
 960 ** external linkage.
 961 */
 962 void sqlite3RegisterDateTimeFunctions(sqlite3 *db){
 963 #ifndef SQLITE_OMIT_DATETIME_FUNCS
 964   static const struct {
 965      char *zName;
 966      int nArg;
 967      void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
 968   } aFuncs[] = {
 969     { "julianday", -1, juliandayFunc   },
 970     { "date",      -1, dateFunc        },
 971     { "time",      -1, timeFunc        },
 972     { "datetime",  -1, datetimeFunc    },
 973     { "strftime",  -1, strftimeFunc    },
 974     { "current_time",       0, ctimeFunc      },
 975     { "current_timestamp",  0, ctimestampFunc },
 976     { "current_date",       0, cdateFunc      },
 977   };
 978   int i;
 979
 980   for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
 981     sqlite3_create_function(db, aFuncs[i].zName, aFuncs[i].nArg,
 982         SQLITE_UTF8, 0, aFuncs[i].xFunc, 0, 0);
 983   }
 984 #else
 985   static const struct {
 986      char *zName;
 987      char *zFormat;
 988   } aFuncs[] = {
 989     { "current_time", "%H:%M:%S" },
 990     { "current_date", "%Y-%m-%d" },
 991     { "current_timestamp", "%Y-%m-%d %H:%M:%S" }
 992   };
 993   int i;
 994
 995   for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
 996     sqlite3_create_function(db, aFuncs[i].zName, 0, SQLITE_UTF8,
 997         aFuncs[i].zFormat, currentTimeFunc, 0, 0);
 998   }
 999 #endif
1000 }