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