| /* |
| ** 2003 October 31 |
| ** |
| ** The author disclaims copyright to this source code. In place of |
| ** a legal notice, here is a blessing: |
| ** |
| ** May you do good and not evil. |
| ** May you find forgiveness for yourself and forgive others. |
| ** May you share freely, never taking more than you give. |
| ** |
| ************************************************************************* |
| ** This file contains the C functions that implement date and time |
| ** functions for SQLite. |
| ** |
| ** There is only one exported symbol in this file - the function |
| ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file. |
| ** All other code has file scope. |
| ** |
| ** SQLite processes all times and dates as julian day numbers. The |
| ** dates and times are stored as the number of days since noon |
| ** in Greenwich on November 24, 4714 B.C. according to the Gregorian |
| ** calendar system. |
| ** |
| ** 1970-01-01 00:00:00 is JD 2440587.5 |
| ** 2000-01-01 00:00:00 is JD 2451544.5 |
| ** |
| ** This implementation requires years to be expressed as a 4-digit number |
| ** which means that only dates between 0000-01-01 and 9999-12-31 can |
| ** be represented, even though julian day numbers allow a much wider |
| ** range of dates. |
| ** |
| ** The Gregorian calendar system is used for all dates and times, |
| ** even those that predate the Gregorian calendar. Historians usually |
| ** use the julian calendar for dates prior to 1582-10-15 and for some |
| ** dates afterwards, depending on locale. Beware of this difference. |
| ** |
| ** The conversion algorithms are implemented based on descriptions |
| ** in the following text: |
| ** |
| ** Jean Meeus |
| ** Astronomical Algorithms, 2nd Edition, 1998 |
| ** ISBN 0-943396-61-1 |
| ** Willmann-Bell, Inc |
| ** Richmond, Virginia (USA) |
| */ |
| #include "sqliteInt.h" |
| #include <stdlib.h> |
| #include <assert.h> |
| #include <time.h> |
| |
| #ifndef SQLITE_OMIT_DATETIME_FUNCS |
| |
| /* |
| ** The MSVC CRT on Windows CE may not have a localtime() function. |
| ** So declare a substitute. The substitute function itself is |
| ** defined in "os_win.c". |
| */ |
| #if !defined(SQLITE_OMIT_LOCALTIME) && defined(_WIN32_WCE) && \ |
| (!defined(SQLITE_MSVC_LOCALTIME_API) || !SQLITE_MSVC_LOCALTIME_API) |
| struct tm *__cdecl localtime(const time_t *); |
| #endif |
| |
| /* |
| ** A structure for holding a single date and time. |
| */ |
| typedef struct DateTime DateTime; |
| struct DateTime { |
| sqlite3_int64 iJD; /* The julian day number times 86400000 */ |
| int Y, M, D; /* Year, month, and day */ |
| int h, m; /* Hour and minutes */ |
| int tz; /* Timezone offset in minutes */ |
| double s; /* Seconds */ |
| char validJD; /* True (1) if iJD is valid */ |
| char rawS; /* Raw numeric value stored in s */ |
| char validYMD; /* True (1) if Y,M,D are valid */ |
| char validHMS; /* True (1) if h,m,s are valid */ |
| char validTZ; /* True (1) if tz is valid */ |
| char tzSet; /* Timezone was set explicitly */ |
| char isError; /* An overflow has occurred */ |
| char useSubsec; /* Display subsecond precision */ |
| }; |
| |
| |
| /* |
| ** Convert zDate into one or more integers according to the conversion |
| ** specifier zFormat. |
| ** |
| ** zFormat[] contains 4 characters for each integer converted, except for |
| ** the last integer which is specified by three characters. The meaning |
| ** of a four-character format specifiers ABCD is: |
| ** |
| ** A: number of digits to convert. Always "2" or "4". |
| ** B: minimum value. Always "0" or "1". |
| ** C: maximum value, decoded as: |
| ** a: 12 |
| ** b: 14 |
| ** c: 24 |
| ** d: 31 |
| ** e: 59 |
| ** f: 9999 |
| ** D: the separator character, or \000 to indicate this is the |
| ** last number to convert. |
| ** |
| ** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would |
| ** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-". |
| ** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates |
| ** the 2-digit day which is the last integer in the set. |
| ** |
| ** The function returns the number of successful conversions. |
| */ |
| static int getDigits(const char *zDate, const char *zFormat, ...){ |
| /* The aMx[] array translates the 3rd character of each format |
| ** spec into a max size: a b c d e f */ |
| static const u16 aMx[] = { 12, 14, 24, 31, 59, 14712 }; |
| va_list ap; |
| int cnt = 0; |
| char nextC; |
| va_start(ap, zFormat); |
| do{ |
| char N = zFormat[0] - '0'; |
| char min = zFormat[1] - '0'; |
| int val = 0; |
| u16 max; |
| |
| assert( zFormat[2]>='a' && zFormat[2]<='f' ); |
| max = aMx[zFormat[2] - 'a']; |
| nextC = zFormat[3]; |
| val = 0; |
| while( N-- ){ |
| if( !sqlite3Isdigit(*zDate) ){ |
| goto end_getDigits; |
| } |
| val = val*10 + *zDate - '0'; |
| zDate++; |
| } |
| if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){ |
| goto end_getDigits; |
| } |
| *va_arg(ap,int*) = val; |
| zDate++; |
| cnt++; |
| zFormat += 4; |
| }while( nextC ); |
| end_getDigits: |
| va_end(ap); |
| return cnt; |
| } |
| |
| /* |
| ** Parse a timezone extension on the end of a date-time. |
| ** The extension is of the form: |
| ** |
| ** (+/-)HH:MM |
| ** |
| ** Or the "zulu" notation: |
| ** |
| ** Z |
| ** |
| ** If the parse is successful, write the number of minutes |
| ** of change in p->tz and return 0. If a parser error occurs, |
| ** return non-zero. |
| ** |
| ** A missing specifier is not considered an error. |
| */ |
| static int parseTimezone(const char *zDate, DateTime *p){ |
| int sgn = 0; |
| int nHr, nMn; |
| int c; |
| while( sqlite3Isspace(*zDate) ){ zDate++; } |
| p->tz = 0; |
| c = *zDate; |
| if( c=='-' ){ |
| sgn = -1; |
| }else if( c=='+' ){ |
| sgn = +1; |
| }else if( c=='Z' || c=='z' ){ |
| zDate++; |
| goto zulu_time; |
| }else{ |
| return c!=0; |
| } |
| zDate++; |
| if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){ |
| return 1; |
| } |
| zDate += 5; |
| p->tz = sgn*(nMn + nHr*60); |
| zulu_time: |
| while( sqlite3Isspace(*zDate) ){ zDate++; } |
| p->tzSet = 1; |
| return *zDate!=0; |
| } |
| |
| /* |
| ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF. |
| ** The HH, MM, and SS must each be exactly 2 digits. The |
| ** fractional seconds FFFF can be one or more digits. |
| ** |
| ** Return 1 if there is a parsing error and 0 on success. |
| */ |
| static int parseHhMmSs(const char *zDate, DateTime *p){ |
| int h, m, s; |
| double ms = 0.0; |
| if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){ |
| return 1; |
| } |
| zDate += 5; |
| if( *zDate==':' ){ |
| zDate++; |
| if( getDigits(zDate, "20e", &s)!=1 ){ |
| return 1; |
| } |
| zDate += 2; |
| if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){ |
| double rScale = 1.0; |
| zDate++; |
| while( sqlite3Isdigit(*zDate) ){ |
| ms = ms*10.0 + *zDate - '0'; |
| rScale *= 10.0; |
| zDate++; |
| } |
| ms /= rScale; |
| } |
| }else{ |
| s = 0; |
| } |
| p->validJD = 0; |
| p->rawS = 0; |
| p->validHMS = 1; |
| p->h = h; |
| p->m = m; |
| p->s = s + ms; |
| if( parseTimezone(zDate, p) ) return 1; |
| p->validTZ = (p->tz!=0)?1:0; |
| return 0; |
| } |
| |
| /* |
| ** Put the DateTime object into its error state. |
| */ |
| static void datetimeError(DateTime *p){ |
| memset(p, 0, sizeof(*p)); |
| p->isError = 1; |
| } |
| |
| /* |
| ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume |
| ** that the YYYY-MM-DD is according to the Gregorian calendar. |
| ** |
| ** Reference: Meeus page 61 |
| */ |
| static void computeJD(DateTime *p){ |
| int Y, M, D, A, B, X1, X2; |
| |
| if( p->validJD ) return; |
| if( p->validYMD ){ |
| Y = p->Y; |
| M = p->M; |
| D = p->D; |
| }else{ |
| Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */ |
| M = 1; |
| D = 1; |
| } |
| if( Y<-4713 || Y>9999 || p->rawS ){ |
| datetimeError(p); |
| return; |
| } |
| if( M<=2 ){ |
| Y--; |
| M += 12; |
| } |
| A = Y/100; |
| B = 2 - A + (A/4); |
| X1 = 36525*(Y+4716)/100; |
| X2 = 306001*(M+1)/10000; |
| p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000); |
| p->validJD = 1; |
| if( p->validHMS ){ |
| p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000 + 0.5); |
| if( p->validTZ ){ |
| p->iJD -= p->tz*60000; |
| p->validYMD = 0; |
| p->validHMS = 0; |
| p->validTZ = 0; |
| } |
| } |
| } |
| |
| /* |
| ** Parse dates of the form |
| ** |
| ** YYYY-MM-DD HH:MM:SS.FFF |
| ** YYYY-MM-DD HH:MM:SS |
| ** YYYY-MM-DD HH:MM |
| ** YYYY-MM-DD |
| ** |
| ** Write the result into the DateTime structure and return 0 |
| ** on success and 1 if the input string is not a well-formed |
| ** date. |
| */ |
| static int parseYyyyMmDd(const char *zDate, DateTime *p){ |
| int Y, M, D, neg; |
| |
| if( zDate[0]=='-' ){ |
| zDate++; |
| neg = 1; |
| }else{ |
| neg = 0; |
| } |
| if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){ |
| return 1; |
| } |
| zDate += 10; |
| while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; } |
| if( parseHhMmSs(zDate, p)==0 ){ |
| /* We got the time */ |
| }else if( *zDate==0 ){ |
| p->validHMS = 0; |
| }else{ |
| return 1; |
| } |
| p->validJD = 0; |
| p->validYMD = 1; |
| p->Y = neg ? -Y : Y; |
| p->M = M; |
| p->D = D; |
| if( p->validTZ ){ |
| computeJD(p); |
| } |
| return 0; |
| } |
| |
| /* |
| ** Set the time to the current time reported by the VFS. |
| ** |
| ** Return the number of errors. |
| */ |
| static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){ |
| p->iJD = sqlite3StmtCurrentTime(context); |
| if( p->iJD>0 ){ |
| p->validJD = 1; |
| return 0; |
| }else{ |
| return 1; |
| } |
| } |
| |
| /* |
| ** Input "r" is a numeric quantity which might be a julian day number, |
| ** or the number of seconds since 1970. If the value if r is within |
| ** range of a julian day number, install it as such and set validJD. |
| ** If the value is a valid unix timestamp, put it in p->s and set p->rawS. |
| */ |
| static void setRawDateNumber(DateTime *p, double r){ |
| p->s = r; |
| p->rawS = 1; |
| if( r>=0.0 && r<5373484.5 ){ |
| p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5); |
| p->validJD = 1; |
| } |
| } |
| |
| /* |
| ** Attempt to parse the given string into a julian day number. Return |
| ** the number of errors. |
| ** |
| ** The following are acceptable forms for the input string: |
| ** |
| ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM |
| ** DDDD.DD |
| ** now |
| ** |
| ** In the first form, the +/-HH:MM is always optional. The fractional |
| ** seconds extension (the ".FFF") is optional. The seconds portion |
| ** (":SS.FFF") is option. The year and date can be omitted as long |
| ** as there is a time string. The time string can be omitted as long |
| ** as there is a year and date. |
| */ |
| static int parseDateOrTime( |
| sqlite3_context *context, |
| const char *zDate, |
| DateTime *p |
| ){ |
| double r; |
| if( parseYyyyMmDd(zDate,p)==0 ){ |
| return 0; |
| }else if( parseHhMmSs(zDate, p)==0 ){ |
| return 0; |
| }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){ |
| return setDateTimeToCurrent(context, p); |
| }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){ |
| setRawDateNumber(p, r); |
| return 0; |
| }else if( (sqlite3StrICmp(zDate,"subsec")==0 |
| || sqlite3StrICmp(zDate,"subsecond")==0) |
| && sqlite3NotPureFunc(context) ){ |
| p->useSubsec = 1; |
| return setDateTimeToCurrent(context, p); |
| } |
| return 1; |
| } |
| |
| /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999. |
| ** Multiplying this by 86400000 gives 464269060799999 as the maximum value |
| ** for DateTime.iJD. |
| ** |
| ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with |
| ** such a large integer literal, so we have to encode it. |
| */ |
| #define INT_464269060799999 ((((i64)0x1a640)<<32)|0x1072fdff) |
| |
| /* |
| ** Return TRUE if the given julian day number is within range. |
| ** |
| ** The input is the JulianDay times 86400000. |
| */ |
| static int validJulianDay(sqlite3_int64 iJD){ |
| return iJD>=0 && iJD<=INT_464269060799999; |
| } |
| |
| /* |
| ** Compute the Year, Month, and Day from the julian day number. |
| */ |
| static void computeYMD(DateTime *p){ |
| int Z, A, B, C, D, E, X1; |
| if( p->validYMD ) return; |
| if( !p->validJD ){ |
| p->Y = 2000; |
| p->M = 1; |
| p->D = 1; |
| }else if( !validJulianDay(p->iJD) ){ |
| datetimeError(p); |
| return; |
| }else{ |
| Z = (int)((p->iJD + 43200000)/86400000); |
| A = (int)((Z - 1867216.25)/36524.25); |
| A = Z + 1 + A - (A/4); |
| B = A + 1524; |
| C = (int)((B - 122.1)/365.25); |
| D = (36525*(C&32767))/100; |
| E = (int)((B-D)/30.6001); |
| X1 = (int)(30.6001*E); |
| p->D = B - D - X1; |
| p->M = E<14 ? E-1 : E-13; |
| p->Y = p->M>2 ? C - 4716 : C - 4715; |
| } |
| p->validYMD = 1; |
| } |
| |
| /* |
| ** Compute the Hour, Minute, and Seconds from the julian day number. |
| */ |
| static void computeHMS(DateTime *p){ |
| int day_ms, day_min; /* milliseconds, minutes into the day */ |
| if( p->validHMS ) return; |
| computeJD(p); |
| day_ms = (int)((p->iJD + 43200000) % 86400000); |
| p->s = (day_ms % 60000)/1000.0; |
| day_min = day_ms/60000; |
| p->m = day_min % 60; |
| p->h = day_min / 60; |
| p->rawS = 0; |
| p->validHMS = 1; |
| } |
| |
| /* |
| ** Compute both YMD and HMS |
| */ |
| static void computeYMD_HMS(DateTime *p){ |
| computeYMD(p); |
| computeHMS(p); |
| } |
| |
| /* |
| ** Clear the YMD and HMS and the TZ |
| */ |
| static void clearYMD_HMS_TZ(DateTime *p){ |
| p->validYMD = 0; |
| p->validHMS = 0; |
| p->validTZ = 0; |
| } |
| |
| #ifndef SQLITE_OMIT_LOCALTIME |
| /* |
| ** On recent Windows platforms, the localtime_s() function is available |
| ** as part of the "Secure CRT". It is essentially equivalent to |
| ** localtime_r() available under most POSIX platforms, except that the |
| ** order of the parameters is reversed. |
| ** |
| ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx. |
| ** |
| ** If the user has not indicated to use localtime_r() or localtime_s() |
| ** already, check for an MSVC build environment that provides |
| ** localtime_s(). |
| */ |
| #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \ |
| && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE) |
| #undef HAVE_LOCALTIME_S |
| #define HAVE_LOCALTIME_S 1 |
| #endif |
| |
| /* |
| ** The following routine implements the rough equivalent of localtime_r() |
| ** using whatever operating-system specific localtime facility that |
| ** is available. This routine returns 0 on success and |
| ** non-zero on any kind of error. |
| ** |
| ** If the sqlite3GlobalConfig.bLocaltimeFault variable is non-zero then this |
| ** routine will always fail. If bLocaltimeFault is nonzero and |
| ** sqlite3GlobalConfig.xAltLocaltime is not NULL, then xAltLocaltime() is |
| ** invoked in place of the OS-defined localtime() function. |
| ** |
| ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C |
| ** library function localtime_r() is used to assist in the calculation of |
| ** local time. |
| */ |
| static int osLocaltime(time_t *t, struct tm *pTm){ |
| int rc; |
| #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S |
| struct tm *pX; |
| #if SQLITE_THREADSAFE>0 |
| sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN); |
| #endif |
| sqlite3_mutex_enter(mutex); |
| pX = localtime(t); |
| #ifndef SQLITE_UNTESTABLE |
| if( sqlite3GlobalConfig.bLocaltimeFault ){ |
| if( sqlite3GlobalConfig.xAltLocaltime!=0 |
| && 0==sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm) |
| ){ |
| pX = pTm; |
| }else{ |
| pX = 0; |
| } |
| } |
| #endif |
| if( pX ) *pTm = *pX; |
| #if SQLITE_THREADSAFE>0 |
| sqlite3_mutex_leave(mutex); |
| #endif |
| rc = pX==0; |
| #else |
| #ifndef SQLITE_UNTESTABLE |
| if( sqlite3GlobalConfig.bLocaltimeFault ){ |
| if( sqlite3GlobalConfig.xAltLocaltime!=0 ){ |
| return sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm); |
| }else{ |
| return 1; |
| } |
| } |
| #endif |
| #if HAVE_LOCALTIME_R |
| rc = localtime_r(t, pTm)==0; |
| #else |
| rc = localtime_s(pTm, t); |
| #endif /* HAVE_LOCALTIME_R */ |
| #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */ |
| return rc; |
| } |
| #endif /* SQLITE_OMIT_LOCALTIME */ |
| |
| |
| #ifndef SQLITE_OMIT_LOCALTIME |
| /* |
| ** Assuming the input DateTime is UTC, move it to its localtime equivalent. |
| */ |
| static int toLocaltime( |
| DateTime *p, /* Date at which to calculate offset */ |
| sqlite3_context *pCtx /* Write error here if one occurs */ |
| ){ |
| time_t t; |
| struct tm sLocal; |
| int iYearDiff; |
| |
| /* Initialize the contents of sLocal to avoid a compiler warning. */ |
| memset(&sLocal, 0, sizeof(sLocal)); |
| |
| computeJD(p); |
| if( p->iJD<2108667600*(i64)100000 /* 1970-01-01 */ |
| || p->iJD>2130141456*(i64)100000 /* 2038-01-18 */ |
| ){ |
| /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only |
| ** works for years between 1970 and 2037. For dates outside this range, |
| ** SQLite attempts to map the year into an equivalent year within this |
| ** range, do the calculation, then map the year back. |
| */ |
| DateTime x = *p; |
| computeYMD_HMS(&x); |
| iYearDiff = (2000 + x.Y%4) - x.Y; |
| x.Y += iYearDiff; |
| x.validJD = 0; |
| computeJD(&x); |
| t = (time_t)(x.iJD/1000 - 21086676*(i64)10000); |
| }else{ |
| iYearDiff = 0; |
| t = (time_t)(p->iJD/1000 - 21086676*(i64)10000); |
| } |
| if( osLocaltime(&t, &sLocal) ){ |
| sqlite3_result_error(pCtx, "local time unavailable", -1); |
| return SQLITE_ERROR; |
| } |
| p->Y = sLocal.tm_year + 1900 - iYearDiff; |
| p->M = sLocal.tm_mon + 1; |
| p->D = sLocal.tm_mday; |
| p->h = sLocal.tm_hour; |
| p->m = sLocal.tm_min; |
| p->s = sLocal.tm_sec + (p->iJD%1000)*0.001; |
| p->validYMD = 1; |
| p->validHMS = 1; |
| p->validJD = 0; |
| p->rawS = 0; |
| p->validTZ = 0; |
| p->isError = 0; |
| return SQLITE_OK; |
| } |
| #endif /* SQLITE_OMIT_LOCALTIME */ |
| |
| /* |
| ** The following table defines various date transformations of the form |
| ** |
| ** 'NNN days' |
| ** |
| ** Where NNN is an arbitrary floating-point number and "days" can be one |
| ** of several units of time. |
| */ |
| static const struct { |
| u8 nName; /* Length of the name */ |
| char zName[7]; /* Name of the transformation */ |
| float rLimit; /* Maximum NNN value for this transform */ |
| float rXform; /* Constant used for this transform */ |
| } aXformType[] = { |
| { 6, "second", 4.6427e+14, 1.0 }, |
| { 6, "minute", 7.7379e+12, 60.0 }, |
| { 4, "hour", 1.2897e+11, 3600.0 }, |
| { 3, "day", 5373485.0, 86400.0 }, |
| { 5, "month", 176546.0, 2592000.0 }, |
| { 4, "year", 14713.0, 31536000.0 }, |
| }; |
| |
| /* |
| ** If the DateTime p is raw number, try to figure out if it is |
| ** a julian day number of a unix timestamp. Set the p value |
| ** appropriately. |
| */ |
| static void autoAdjustDate(DateTime *p){ |
| if( !p->rawS || p->validJD ){ |
| p->rawS = 0; |
| }else if( p->s>=-21086676*(i64)10000 /* -4713-11-24 12:00:00 */ |
| && p->s<=(25340230*(i64)10000)+799 /* 9999-12-31 23:59:59 */ |
| ){ |
| double r = p->s*1000.0 + 210866760000000.0; |
| clearYMD_HMS_TZ(p); |
| p->iJD = (sqlite3_int64)(r + 0.5); |
| p->validJD = 1; |
| p->rawS = 0; |
| } |
| } |
| |
| /* |
| ** Process a modifier to a date-time stamp. The modifiers are |
| ** as follows: |
| ** |
| ** NNN days |
| ** NNN hours |
| ** NNN minutes |
| ** NNN.NNNN seconds |
| ** NNN months |
| ** NNN years |
| ** start of month |
| ** start of year |
| ** start of week |
| ** start of day |
| ** weekday N |
| ** unixepoch |
| ** localtime |
| ** utc |
| ** |
| ** Return 0 on success and 1 if there is any kind of error. If the error |
| ** is in a system call (i.e. localtime()), then an error message is written |
| ** to context pCtx. If the error is an unrecognized modifier, no error is |
| ** written to pCtx. |
| */ |
| static int parseModifier( |
| sqlite3_context *pCtx, /* Function context */ |
| const char *z, /* The text of the modifier */ |
| int n, /* Length of zMod in bytes */ |
| DateTime *p, /* The date/time value to be modified */ |
| int idx /* Parameter index of the modifier */ |
| ){ |
| int rc = 1; |
| double r; |
| switch(sqlite3UpperToLower[(u8)z[0]] ){ |
| case 'a': { |
| /* |
| ** auto |
| ** |
| ** If rawS is available, then interpret as a julian day number, or |
| ** a unix timestamp, depending on its magnitude. |
| */ |
| if( sqlite3_stricmp(z, "auto")==0 ){ |
| if( idx>1 ) return 1; /* IMP: R-33611-57934 */ |
| autoAdjustDate(p); |
| rc = 0; |
| } |
| break; |
| } |
| case 'j': { |
| /* |
| ** julianday |
| ** |
| ** Always interpret the prior number as a julian-day value. If this |
| ** is not the first modifier, or if the prior argument is not a numeric |
| ** value in the allowed range of julian day numbers understood by |
| ** SQLite (0..5373484.5) then the result will be NULL. |
| */ |
| if( sqlite3_stricmp(z, "julianday")==0 ){ |
| if( idx>1 ) return 1; /* IMP: R-31176-64601 */ |
| if( p->validJD && p->rawS ){ |
| rc = 0; |
| p->rawS = 0; |
| } |
| } |
| break; |
| } |
| #ifndef SQLITE_OMIT_LOCALTIME |
| case 'l': { |
| /* localtime |
| ** |
| ** Assuming the current time value is UTC (a.k.a. GMT), shift it to |
| ** show local time. |
| */ |
| if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){ |
| rc = toLocaltime(p, pCtx); |
| } |
| break; |
| } |
| #endif |
| case 'u': { |
| /* |
| ** unixepoch |
| ** |
| ** Treat the current value of p->s as the number of |
| ** seconds since 1970. Convert to a real julian day number. |
| */ |
| if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){ |
| if( idx>1 ) return 1; /* IMP: R-49255-55373 */ |
| r = p->s*1000.0 + 210866760000000.0; |
| if( r>=0.0 && r<464269060800000.0 ){ |
| clearYMD_HMS_TZ(p); |
| p->iJD = (sqlite3_int64)(r + 0.5); |
| p->validJD = 1; |
| p->rawS = 0; |
| rc = 0; |
| } |
| } |
| #ifndef SQLITE_OMIT_LOCALTIME |
| else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){ |
| if( p->tzSet==0 ){ |
| i64 iOrigJD; /* Original localtime */ |
| i64 iGuess; /* Guess at the corresponding utc time */ |
| int cnt = 0; /* Safety to prevent infinite loop */ |
| i64 iErr; /* Guess is off by this much */ |
| |
| computeJD(p); |
| iGuess = iOrigJD = p->iJD; |
| iErr = 0; |
| do{ |
| DateTime new; |
| memset(&new, 0, sizeof(new)); |
| iGuess -= iErr; |
| new.iJD = iGuess; |
| new.validJD = 1; |
| rc = toLocaltime(&new, pCtx); |
| if( rc ) return rc; |
| computeJD(&new); |
| iErr = new.iJD - iOrigJD; |
| }while( iErr && cnt++<3 ); |
| memset(p, 0, sizeof(*p)); |
| p->iJD = iGuess; |
| p->validJD = 1; |
| p->tzSet = 1; |
| } |
| rc = SQLITE_OK; |
| } |
| #endif |
| break; |
| } |
| case 'w': { |
| /* |
| ** weekday N |
| ** |
| ** Move the date to the same time on the next occurrence of |
| ** weekday N where 0==Sunday, 1==Monday, and so forth. If the |
| ** date is already on the appropriate weekday, this is a no-op. |
| */ |
| if( sqlite3_strnicmp(z, "weekday ", 8)==0 |
| && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0 |
| && r>=0.0 && r<7.0 && (n=(int)r)==r ){ |
| sqlite3_int64 Z; |
| computeYMD_HMS(p); |
| p->validTZ = 0; |
| p->validJD = 0; |
| computeJD(p); |
| Z = ((p->iJD + 129600000)/86400000) % 7; |
| if( Z>n ) Z -= 7; |
| p->iJD += (n - Z)*86400000; |
| clearYMD_HMS_TZ(p); |
| rc = 0; |
| } |
| break; |
| } |
| case 's': { |
| /* |
| ** start of TTTTT |
| ** |
| ** Move the date backwards to the beginning of the current day, |
| ** or month or year. |
| ** |
| ** subsecond |
| ** subsec |
| ** |
| ** Show subsecond precision in the output of datetime() and |
| ** unixepoch() and strftime('%s'). |
| */ |
| if( sqlite3_strnicmp(z, "start of ", 9)!=0 ){ |
| if( sqlite3_stricmp(z, "subsec")==0 |
| || sqlite3_stricmp(z, "subsecond")==0 |
| ){ |
| p->useSubsec = 1; |
| rc = 0; |
| } |
| break; |
| } |
| if( !p->validJD && !p->validYMD && !p->validHMS ) break; |
| z += 9; |
| computeYMD(p); |
| p->validHMS = 1; |
| p->h = p->m = 0; |
| p->s = 0.0; |
| p->rawS = 0; |
| p->validTZ = 0; |
| p->validJD = 0; |
| if( sqlite3_stricmp(z,"month")==0 ){ |
| p->D = 1; |
| rc = 0; |
| }else if( sqlite3_stricmp(z,"year")==0 ){ |
| p->M = 1; |
| p->D = 1; |
| rc = 0; |
| }else if( sqlite3_stricmp(z,"day")==0 ){ |
| rc = 0; |
| } |
| break; |
| } |
| case '+': |
| case '-': |
| case '0': |
| case '1': |
| case '2': |
| case '3': |
| case '4': |
| case '5': |
| case '6': |
| case '7': |
| case '8': |
| case '9': { |
| double rRounder; |
| int i; |
| int Y,M,D,h,m,x; |
| const char *z2 = z; |
| char z0 = z[0]; |
| for(n=1; z[n]; n++){ |
| if( z[n]==':' ) break; |
| if( sqlite3Isspace(z[n]) ) break; |
| if( z[n]=='-' ){ |
| if( n==5 && getDigits(&z[1], "40f", &Y)==1 ) break; |
| if( n==6 && getDigits(&z[1], "50f", &Y)==1 ) break; |
| } |
| } |
| if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){ |
| assert( rc==1 ); |
| break; |
| } |
| if( z[n]=='-' ){ |
| /* A modifier of the form (+|-)YYYY-MM-DD adds or subtracts the |
| ** specified number of years, months, and days. MM is limited to |
| ** the range 0-11 and DD is limited to 0-30. |
| */ |
| if( z0!='+' && z0!='-' ) break; /* Must start with +/- */ |
| if( n==5 ){ |
| if( getDigits(&z[1], "40f-20a-20d", &Y, &M, &D)!=3 ) break; |
| }else{ |
| assert( n==6 ); |
| if( getDigits(&z[1], "50f-20a-20d", &Y, &M, &D)!=3 ) break; |
| z++; |
| } |
| if( M>=12 ) break; /* M range 0..11 */ |
| if( D>=31 ) break; /* D range 0..30 */ |
| computeYMD_HMS(p); |
| p->validJD = 0; |
| if( z0=='-' ){ |
| p->Y -= Y; |
| p->M -= M; |
| D = -D; |
| }else{ |
| p->Y += Y; |
| p->M += M; |
| } |
| x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; |
| p->Y += x; |
| p->M -= x*12; |
| computeJD(p); |
| p->validHMS = 0; |
| p->validYMD = 0; |
| p->iJD += (i64)D*86400000; |
| if( z[11]==0 ){ |
| rc = 0; |
| break; |
| } |
| if( sqlite3Isspace(z[11]) |
| && getDigits(&z[12], "20c:20e", &h, &m)==2 |
| ){ |
| z2 = &z[12]; |
| n = 2; |
| }else{ |
| break; |
| } |
| } |
| if( z2[n]==':' ){ |
| /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the |
| ** specified number of hours, minutes, seconds, and fractional seconds |
| ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be |
| ** omitted. |
| */ |
| |
| DateTime tx; |
| sqlite3_int64 day; |
| if( !sqlite3Isdigit(*z2) ) z2++; |
| memset(&tx, 0, sizeof(tx)); |
| if( parseHhMmSs(z2, &tx) ) break; |
| computeJD(&tx); |
| tx.iJD -= 43200000; |
| day = tx.iJD/86400000; |
| tx.iJD -= day*86400000; |
| if( z0=='-' ) tx.iJD = -tx.iJD; |
| computeJD(p); |
| clearYMD_HMS_TZ(p); |
| p->iJD += tx.iJD; |
| rc = 0; |
| break; |
| } |
| |
| /* If control reaches this point, it means the transformation is |
| ** one of the forms like "+NNN days". */ |
| z += n; |
| while( sqlite3Isspace(*z) ) z++; |
| n = sqlite3Strlen30(z); |
| if( n>10 || n<3 ) break; |
| if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--; |
| computeJD(p); |
| assert( rc==1 ); |
| rRounder = r<0 ? -0.5 : +0.5; |
| for(i=0; i<ArraySize(aXformType); i++){ |
| if( aXformType[i].nName==n |
| && sqlite3_strnicmp(aXformType[i].zName, z, n)==0 |
| && r>-aXformType[i].rLimit && r<aXformType[i].rLimit |
| ){ |
| switch( i ){ |
| case 4: { /* Special processing to add months */ |
| assert( strcmp(aXformType[i].zName,"month")==0 ); |
| computeYMD_HMS(p); |
| p->M += (int)r; |
| x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; |
| p->Y += x; |
| p->M -= x*12; |
| p->validJD = 0; |
| r -= (int)r; |
| break; |
| } |
| case 5: { /* Special processing to add years */ |
| int y = (int)r; |
| assert( strcmp(aXformType[i].zName,"year")==0 ); |
| computeYMD_HMS(p); |
| p->Y += y; |
| p->validJD = 0; |
| r -= (int)r; |
| break; |
| } |
| } |
| computeJD(p); |
| p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder); |
| rc = 0; |
| break; |
| } |
| } |
| clearYMD_HMS_TZ(p); |
| break; |
| } |
| default: { |
| break; |
| } |
| } |
| return rc; |
| } |
| |
| /* |
| ** Process time function arguments. argv[0] is a date-time stamp. |
| ** argv[1] and following are modifiers. Parse them all and write |
| ** the resulting time into the DateTime structure p. Return 0 |
| ** on success and 1 if there are any errors. |
| ** |
| ** If there are zero parameters (if even argv[0] is undefined) |
| ** then assume a default value of "now" for argv[0]. |
| */ |
| static int isDate( |
| sqlite3_context *context, |
| int argc, |
| sqlite3_value **argv, |
| DateTime *p |
| ){ |
| int i, n; |
| const unsigned char *z; |
| int eType; |
| memset(p, 0, sizeof(*p)); |
| if( argc==0 ){ |
| if( !sqlite3NotPureFunc(context) ) return 1; |
| return setDateTimeToCurrent(context, p); |
| } |
| if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT |
| || eType==SQLITE_INTEGER ){ |
| setRawDateNumber(p, sqlite3_value_double(argv[0])); |
| }else{ |
| z = sqlite3_value_text(argv[0]); |
| if( !z || parseDateOrTime(context, (char*)z, p) ){ |
| return 1; |
| } |
| } |
| for(i=1; i<argc; i++){ |
| z = sqlite3_value_text(argv[i]); |
| n = sqlite3_value_bytes(argv[i]); |
| if( z==0 || parseModifier(context, (char*)z, n, p, i) ) return 1; |
| } |
| computeJD(p); |
| if( p->isError || !validJulianDay(p->iJD) ) return 1; |
| return 0; |
| } |
| |
| |
| /* |
| ** The following routines implement the various date and time functions |
| ** of SQLite. |
| */ |
| |
| /* |
| ** julianday( TIMESTRING, MOD, MOD, ...) |
| ** |
| ** Return the julian day number of the date specified in the arguments |
| */ |
| static void juliandayFunc( |
| sqlite3_context *context, |
| int argc, |
| sqlite3_value **argv |
| ){ |
| DateTime x; |
| if( isDate(context, argc, argv, &x)==0 ){ |
| computeJD(&x); |
| sqlite3_result_double(context, x.iJD/86400000.0); |
| } |
| } |
| |
| /* |
| ** unixepoch( TIMESTRING, MOD, MOD, ...) |
| ** |
| ** Return the number of seconds (including fractional seconds) since |
| ** the unix epoch of 1970-01-01 00:00:00 GMT. |
| */ |
| static void unixepochFunc( |
| sqlite3_context *context, |
| int argc, |
| sqlite3_value **argv |
| ){ |
| DateTime x; |
| if( isDate(context, argc, argv, &x)==0 ){ |
| computeJD(&x); |
| if( x.useSubsec ){ |
| sqlite3_result_double(context, (x.iJD - 21086676*(i64)10000000)/1000.0); |
| }else{ |
| sqlite3_result_int64(context, x.iJD/1000 - 21086676*(i64)10000); |
| } |
| } |
| } |
| |
| /* |
| ** datetime( TIMESTRING, MOD, MOD, ...) |
| ** |
| ** Return YYYY-MM-DD HH:MM:SS |
| */ |
| static void datetimeFunc( |
| sqlite3_context *context, |
| int argc, |
| sqlite3_value **argv |
| ){ |
| DateTime x; |
| if( isDate(context, argc, argv, &x)==0 ){ |
| int Y, s, n; |
| char zBuf[32]; |
| computeYMD_HMS(&x); |
| Y = x.Y; |
| if( Y<0 ) Y = -Y; |
| zBuf[1] = '0' + (Y/1000)%10; |
| zBuf[2] = '0' + (Y/100)%10; |
| zBuf[3] = '0' + (Y/10)%10; |
| zBuf[4] = '0' + (Y)%10; |
| zBuf[5] = '-'; |
| zBuf[6] = '0' + (x.M/10)%10; |
| zBuf[7] = '0' + (x.M)%10; |
| zBuf[8] = '-'; |
| zBuf[9] = '0' + (x.D/10)%10; |
| zBuf[10] = '0' + (x.D)%10; |
| zBuf[11] = ' '; |
| zBuf[12] = '0' + (x.h/10)%10; |
| zBuf[13] = '0' + (x.h)%10; |
| zBuf[14] = ':'; |
| zBuf[15] = '0' + (x.m/10)%10; |
| zBuf[16] = '0' + (x.m)%10; |
| zBuf[17] = ':'; |
| if( x.useSubsec ){ |
| s = (int)(1000.0*x.s + 0.5); |
| zBuf[18] = '0' + (s/10000)%10; |
| zBuf[19] = '0' + (s/1000)%10; |
| zBuf[20] = '.'; |
| zBuf[21] = '0' + (s/100)%10; |
| zBuf[22] = '0' + (s/10)%10; |
| zBuf[23] = '0' + (s)%10; |
| zBuf[24] = 0; |
| n = 24; |
| }else{ |
| s = (int)x.s; |
| zBuf[18] = '0' + (s/10)%10; |
| zBuf[19] = '0' + (s)%10; |
| zBuf[20] = 0; |
| n = 20; |
| } |
| if( x.Y<0 ){ |
| zBuf[0] = '-'; |
| sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT); |
| }else{ |
| sqlite3_result_text(context, &zBuf[1], n-1, SQLITE_TRANSIENT); |
| } |
| } |
| } |
| |
| /* |
| ** time( TIMESTRING, MOD, MOD, ...) |
| ** |
| ** Return HH:MM:SS |
| */ |
| static void timeFunc( |
| sqlite3_context *context, |
| int argc, |
| sqlite3_value **argv |
| ){ |
| DateTime x; |
| if( isDate(context, argc, argv, &x)==0 ){ |
| int s, n; |
| char zBuf[16]; |
| computeHMS(&x); |
| zBuf[0] = '0' + (x.h/10)%10; |
| zBuf[1] = '0' + (x.h)%10; |
| zBuf[2] = ':'; |
| zBuf[3] = '0' + (x.m/10)%10; |
| zBuf[4] = '0' + (x.m)%10; |
| zBuf[5] = ':'; |
| if( x.useSubsec ){ |
| s = (int)(1000.0*x.s + 0.5); |
| zBuf[6] = '0' + (s/10000)%10; |
| zBuf[7] = '0' + (s/1000)%10; |
| zBuf[8] = '.'; |
| zBuf[9] = '0' + (s/100)%10; |
| zBuf[10] = '0' + (s/10)%10; |
| zBuf[11] = '0' + (s)%10; |
| zBuf[12] = 0; |
| n = 12; |
| }else{ |
| s = (int)x.s; |
| zBuf[6] = '0' + (s/10)%10; |
| zBuf[7] = '0' + (s)%10; |
| zBuf[8] = 0; |
| n = 8; |
| } |
| sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT); |
| } |
| } |
| |
| /* |
| ** date( TIMESTRING, MOD, MOD, ...) |
| ** |
| ** Return YYYY-MM-DD |
| */ |
| static void dateFunc( |
| sqlite3_context *context, |
| int argc, |
| sqlite3_value **argv |
| ){ |
| DateTime x; |
| if( isDate(context, argc, argv, &x)==0 ){ |
| int Y; |
| char zBuf[16]; |
| computeYMD(&x); |
| Y = x.Y; |
| if( Y<0 ) Y = -Y; |
| zBuf[1] = '0' + (Y/1000)%10; |
| zBuf[2] = '0' + (Y/100)%10; |
| zBuf[3] = '0' + (Y/10)%10; |
| zBuf[4] = '0' + (Y)%10; |
| zBuf[5] = '-'; |
| zBuf[6] = '0' + (x.M/10)%10; |
| zBuf[7] = '0' + (x.M)%10; |
| zBuf[8] = '-'; |
| zBuf[9] = '0' + (x.D/10)%10; |
| zBuf[10] = '0' + (x.D)%10; |
| zBuf[11] = 0; |
| if( x.Y<0 ){ |
| zBuf[0] = '-'; |
| sqlite3_result_text(context, zBuf, 11, SQLITE_TRANSIENT); |
| }else{ |
| sqlite3_result_text(context, &zBuf[1], 10, SQLITE_TRANSIENT); |
| } |
| } |
| } |
| |
| /* |
| ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...) |
| ** |
| ** Return a string described by FORMAT. Conversions as follows: |
| ** |
| ** %d day of month |
| ** %f ** fractional seconds SS.SSS |
| ** %H hour 00-24 |
| ** %j day of year 000-366 |
| ** %J ** julian day number |
| ** %m month 01-12 |
| ** %M minute 00-59 |
| ** %s seconds since 1970-01-01 |
| ** %S seconds 00-59 |
| ** %w day of week 0-6 Sunday==0 |
| ** %W week of year 00-53 |
| ** %Y year 0000-9999 |
| ** %% % |
| */ |
| static void strftimeFunc( |
| sqlite3_context *context, |
| int argc, |
| sqlite3_value **argv |
| ){ |
| DateTime x; |
| size_t i,j; |
| sqlite3 *db; |
| const char *zFmt; |
| sqlite3_str sRes; |
| |
| |
| if( argc==0 ) return; |
| zFmt = (const char*)sqlite3_value_text(argv[0]); |
| if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return; |
| db = sqlite3_context_db_handle(context); |
| sqlite3StrAccumInit(&sRes, 0, 0, 0, db->aLimit[SQLITE_LIMIT_LENGTH]); |
| |
| computeJD(&x); |
| computeYMD_HMS(&x); |
| for(i=j=0; zFmt[i]; i++){ |
| char cf; |
| if( zFmt[i]!='%' ) continue; |
| if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j)); |
| i++; |
| j = i + 1; |
| cf = zFmt[i]; |
| switch( cf ){ |
| case 'd': /* Fall thru */ |
| case 'e': { |
| sqlite3_str_appendf(&sRes, cf=='d' ? "%02d" : "%2d", x.D); |
| break; |
| } |
| case 'f': { |
| double s = x.s; |
| if( s>59.999 ) s = 59.999; |
| sqlite3_str_appendf(&sRes, "%06.3f", s); |
| break; |
| } |
| case 'F': { |
| sqlite3_str_appendf(&sRes, "%04d-%02d-%02d", x.Y, x.M, x.D); |
| break; |
| } |
| case 'H': |
| case 'k': { |
| sqlite3_str_appendf(&sRes, cf=='H' ? "%02d" : "%2d", x.h); |
| break; |
| } |
| case 'I': /* Fall thru */ |
| case 'l': { |
| int h = x.h; |
| if( h>12 ) h -= 12; |
| if( h==0 ) h = 12; |
| sqlite3_str_appendf(&sRes, cf=='I' ? "%02d" : "%2d", h); |
| break; |
| } |
| case 'W': /* Fall thru */ |
| case 'j': { |
| int nDay; /* Number of days since 1st day of year */ |
| DateTime y = x; |
| y.validJD = 0; |
| y.M = 1; |
| y.D = 1; |
| computeJD(&y); |
| nDay = (int)((x.iJD-y.iJD+43200000)/86400000); |
| if( cf=='W' ){ |
| int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */ |
| wd = (int)(((x.iJD+43200000)/86400000)%7); |
| sqlite3_str_appendf(&sRes,"%02d",(nDay+7-wd)/7); |
| }else{ |
| sqlite3_str_appendf(&sRes,"%03d",nDay+1); |
| } |
| break; |
| } |
| case 'J': { |
| sqlite3_str_appendf(&sRes,"%.16g",x.iJD/86400000.0); |
| break; |
| } |
| case 'm': { |
| sqlite3_str_appendf(&sRes,"%02d",x.M); |
| break; |
| } |
| case 'M': { |
| sqlite3_str_appendf(&sRes,"%02d",x.m); |
| break; |
| } |
| case 'p': /* Fall thru */ |
| case 'P': { |
| if( x.h>=12 ){ |
| sqlite3_str_append(&sRes, cf=='p' ? "PM" : "pm", 2); |
| }else{ |
| sqlite3_str_append(&sRes, cf=='p' ? "AM" : "am", 2); |
| } |
| break; |
| } |
| case 'R': { |
| sqlite3_str_appendf(&sRes, "%02d:%02d", x.h, x.m); |
| break; |
| } |
| case 's': { |
| if( x.useSubsec ){ |
| sqlite3_str_appendf(&sRes,"%.3f", |
| (x.iJD - 21086676*(i64)10000000)/1000.0); |
| }else{ |
| i64 iS = (i64)(x.iJD/1000 - 21086676*(i64)10000); |
| sqlite3_str_appendf(&sRes,"%lld",iS); |
| } |
| break; |
| } |
| case 'S': { |
| sqlite3_str_appendf(&sRes,"%02d",(int)x.s); |
| break; |
| } |
| case 'T': { |
| sqlite3_str_appendf(&sRes,"%02d:%02d:%02d", x.h, x.m, (int)x.s); |
| break; |
| } |
| case 'u': /* Fall thru */ |
| case 'w': { |
| char c = (char)(((x.iJD+129600000)/86400000) % 7) + '0'; |
| if( c=='0' && cf=='u' ) c = '7'; |
| sqlite3_str_appendchar(&sRes, 1, c); |
| break; |
| } |
| case 'Y': { |
| sqlite3_str_appendf(&sRes,"%04d",x.Y); |
| break; |
| } |
| case '%': { |
| sqlite3_str_appendchar(&sRes, 1, '%'); |
| break; |
| } |
| default: { |
| sqlite3_str_reset(&sRes); |
| return; |
| } |
| } |
| } |
| if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j)); |
| sqlite3ResultStrAccum(context, &sRes); |
| } |
| |
| /* |
| ** current_time() |
| ** |
| ** This function returns the same value as time('now'). |
| */ |
| static void ctimeFunc( |
| sqlite3_context *context, |
| int NotUsed, |
| sqlite3_value **NotUsed2 |
| ){ |
| UNUSED_PARAMETER2(NotUsed, NotUsed2); |
| timeFunc(context, 0, 0); |
| } |
| |
| /* |
| ** current_date() |
| ** |
| ** This function returns the same value as date('now'). |
| */ |
| static void cdateFunc( |
| sqlite3_context *context, |
| int NotUsed, |
| sqlite3_value **NotUsed2 |
| ){ |
| UNUSED_PARAMETER2(NotUsed, NotUsed2); |
| dateFunc(context, 0, 0); |
| } |
| |
| /* |
| ** timediff(DATE1, DATE2) |
| ** |
| ** Return the amount of time that must be added to DATE2 in order to |
| ** convert it into DATE2. The time difference format is: |
| ** |
| ** +YYYY-MM-DD HH:MM:SS.SSS |
| ** |
| ** The initial "+" becomes "-" if DATE1 occurs before DATE2. For |
| ** date/time values A and B, the following invariant should hold: |
| ** |
| ** datetime(A) == (datetime(B, timediff(A,B)) |
| ** |
| ** Both DATE arguments must be either a julian day number, or an |
| ** ISO-8601 string. The unix timestamps are not supported by this |
| ** routine. |
| */ |
| static void timediffFunc( |
| sqlite3_context *context, |
| int NotUsed1, |
| sqlite3_value **argv |
| ){ |
| char sign; |
| int Y, M; |
| DateTime d1, d2; |
| sqlite3_str sRes; |
| UNUSED_PARAMETER(NotUsed1); |
| if( isDate(context, 1, &argv[0], &d1) ) return; |
| if( isDate(context, 1, &argv[1], &d2) ) return; |
| computeYMD_HMS(&d1); |
| computeYMD_HMS(&d2); |
| if( d1.iJD>=d2.iJD ){ |
| sign = '+'; |
| Y = d1.Y - d2.Y; |
| if( Y ){ |
| d2.Y = d1.Y; |
| d2.validJD = 0; |
| computeJD(&d2); |
| } |
| M = d1.M - d2.M; |
| if( M<0 ){ |
| Y--; |
| M += 12; |
| } |
| if( M!=0 ){ |
| d2.M = d1.M; |
| d2.validJD = 0; |
| computeJD(&d2); |
| } |
| while( d1.iJD<d2.iJD ){ |
| M--; |
| if( M<0 ){ |
| M = 11; |
| Y--; |
| } |
| d2.M--; |
| if( d2.M<1 ){ |
| d2.M = 12; |
| d2.Y--; |
| } |
| d2.validJD = 0; |
| computeJD(&d2); |
| } |
| d1.iJD -= d2.iJD; |
| d1.iJD += (u64)1486995408 * (u64)100000; |
| }else /* d1<d2 */{ |
| sign = '-'; |
| Y = d2.Y - d1.Y; |
| if( Y ){ |
| d2.Y = d1.Y; |
| d2.validJD = 0; |
| computeJD(&d2); |
| } |
| M = d2.M - d1.M; |
| if( M<0 ){ |
| Y--; |
| M += 12; |
| } |
| if( M!=0 ){ |
| d2.M = d1.M; |
| d2.validJD = 0; |
| computeJD(&d2); |
| } |
| while( d1.iJD>d2.iJD ){ |
| M--; |
| if( M<0 ){ |
| M = 11; |
| Y--; |
| } |
| d2.M++; |
| if( d2.M>12 ){ |
| d2.M = 1; |
| d2.Y++; |
| } |
| d2.validJD = 0; |
| computeJD(&d2); |
| } |
| d1.iJD = d2.iJD - d1.iJD; |
| d1.iJD += (u64)1486995408 * (u64)100000; |
| } |
| d1.validYMD = 0; |
| d1.validHMS = 0; |
| d1.validTZ = 0; |
| computeYMD_HMS(&d1); |
| sqlite3StrAccumInit(&sRes, 0, 0, 0, 100); |
| sqlite3_str_appendf(&sRes, "%c%04d-%02d-%02d %02d:%02d:%06.3f", |
| sign, Y, M, d1.D-1, d1.h, d1.m, d1.s); |
| sqlite3ResultStrAccum(context, &sRes); |
| } |
| |
| |
| /* |
| ** current_timestamp() |
| ** |
| ** This function returns the same value as datetime('now'). |
| */ |
| static void ctimestampFunc( |
| sqlite3_context *context, |
| int NotUsed, |
| sqlite3_value **NotUsed2 |
| ){ |
| UNUSED_PARAMETER2(NotUsed, NotUsed2); |
| datetimeFunc(context, 0, 0); |
| } |
| #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */ |
| |
| #ifdef SQLITE_OMIT_DATETIME_FUNCS |
| /* |
| ** If the library is compiled to omit the full-scale date and time |
| ** handling (to get a smaller binary), the following minimal version |
| ** of the functions current_time(), current_date() and current_timestamp() |
| ** are included instead. This is to support column declarations that |
| ** include "DEFAULT CURRENT_TIME" etc. |
| ** |
| ** This function uses the C-library functions time(), gmtime() |
| ** and strftime(). The format string to pass to strftime() is supplied |
| ** as the user-data for the function. |
| */ |
| static void currentTimeFunc( |
| sqlite3_context *context, |
| int argc, |
| sqlite3_value **argv |
| ){ |
| time_t t; |
| char *zFormat = (char *)sqlite3_user_data(context); |
| sqlite3_int64 iT; |
| struct tm *pTm; |
| struct tm sNow; |
| char zBuf[20]; |
| |
| UNUSED_PARAMETER(argc); |
| UNUSED_PARAMETER(argv); |
| |
| iT = sqlite3StmtCurrentTime(context); |
| if( iT<=0 ) return; |
| t = iT/1000 - 10000*(sqlite3_int64)21086676; |
| #if HAVE_GMTIME_R |
| pTm = gmtime_r(&t, &sNow); |
| #else |
| sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN)); |
| pTm = gmtime(&t); |
| if( pTm ) memcpy(&sNow, pTm, sizeof(sNow)); |
| sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN)); |
| #endif |
| if( pTm ){ |
| strftime(zBuf, 20, zFormat, &sNow); |
| sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); |
| } |
| } |
| #endif |
| |
| /* |
| ** This function registered all of the above C functions as SQL |
| ** functions. This should be the only routine in this file with |
| ** external linkage. |
| */ |
| void sqlite3RegisterDateTimeFunctions(void){ |
| static FuncDef aDateTimeFuncs[] = { |
| #ifndef SQLITE_OMIT_DATETIME_FUNCS |
| PURE_DATE(julianday, -1, 0, 0, juliandayFunc ), |
| PURE_DATE(unixepoch, -1, 0, 0, unixepochFunc ), |
| PURE_DATE(date, -1, 0, 0, dateFunc ), |
| PURE_DATE(time, -1, 0, 0, timeFunc ), |
| PURE_DATE(datetime, -1, 0, 0, datetimeFunc ), |
| PURE_DATE(strftime, -1, 0, 0, strftimeFunc ), |
| PURE_DATE(timediff, 2, 0, 0, timediffFunc ), |
| DFUNCTION(current_time, 0, 0, 0, ctimeFunc ), |
| DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc), |
| DFUNCTION(current_date, 0, 0, 0, cdateFunc ), |
| #else |
| STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc), |
| STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc), |
| STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc), |
| #endif |
| }; |
| sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs)); |
| } |