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