| /* |
| ** 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 */ |
| }; |
| |
| |
| /* |
| ** 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, 9999 }; |
| 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); |
| 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; |
| } |
| 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 s; |
| if( p->validHMS ) return; |
| computeJD(p); |
| s = (int)((p->iJD + 43200000) % 86400000); |
| p->s = s/1000.0; |
| s = (int)p->s; |
| p->s -= s; |
| p->h = s/3600; |
| s -= p->h*3600; |
| p->m = s/60; |
| p->s += s - p->m*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 true then this |
| ** routine will always fail. |
| ** |
| ** 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_MASTER); |
| #endif |
| sqlite3_mutex_enter(mutex); |
| pX = localtime(t); |
| #ifndef SQLITE_UNTESTABLE |
| if( sqlite3GlobalConfig.bLocaltimeFault ) pX = 0; |
| #endif |
| if( pX ) *pTm = *pX; |
| sqlite3_mutex_leave(mutex); |
| rc = pX==0; |
| #else |
| #ifndef SQLITE_UNTESTABLE |
| if( sqlite3GlobalConfig.bLocaltimeFault ) 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 |
| /* |
| ** Compute the difference (in milliseconds) between localtime and UTC |
| ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs, |
| ** return this value and set *pRc to SQLITE_OK. |
| ** |
| ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value |
| ** is undefined in this case. |
| */ |
| static sqlite3_int64 localtimeOffset( |
| DateTime *p, /* Date at which to calculate offset */ |
| sqlite3_context *pCtx, /* Write error here if one occurs */ |
| int *pRc /* OUT: Error code. SQLITE_OK or ERROR */ |
| ){ |
| DateTime x, y; |
| time_t t; |
| struct tm sLocal; |
| |
| /* Initialize the contents of sLocal to avoid a compiler warning. */ |
| memset(&sLocal, 0, sizeof(sLocal)); |
| |
| x = *p; |
| computeYMD_HMS(&x); |
| if( x.Y<1971 || x.Y>=2038 ){ |
| /* 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. |
| */ |
| x.Y = 2000; |
| x.M = 1; |
| x.D = 1; |
| x.h = 0; |
| x.m = 0; |
| x.s = 0.0; |
| } else { |
| int s = (int)(x.s + 0.5); |
| x.s = s; |
| } |
| x.tz = 0; |
| x.validJD = 0; |
| computeJD(&x); |
| t = (time_t)(x.iJD/1000 - 21086676*(i64)10000); |
| if( osLocaltime(&t, &sLocal) ){ |
| sqlite3_result_error(pCtx, "local time unavailable", -1); |
| *pRc = SQLITE_ERROR; |
| return 0; |
| } |
| y.Y = sLocal.tm_year + 1900; |
| y.M = sLocal.tm_mon + 1; |
| y.D = sLocal.tm_mday; |
| y.h = sLocal.tm_hour; |
| y.m = sLocal.tm_min; |
| y.s = sLocal.tm_sec; |
| y.validYMD = 1; |
| y.validHMS = 1; |
| y.validJD = 0; |
| y.rawS = 0; |
| y.validTZ = 0; |
| y.isError = 0; |
| computeJD(&y); |
| *pRc = SQLITE_OK; |
| return y.iJD - x.iJD; |
| } |
| #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 eType; /* Transformation type code */ |
| u8 nName; /* Length of th name */ |
| char *zName; /* Name of the transformation */ |
| double rLimit; /* Maximum NNN value for this transform */ |
| double rXform; /* Constant used for this transform */ |
| } aXformType[] = { |
| { 0, 6, "second", 464269060800.0, 86400000.0/(24.0*60.0*60.0) }, |
| { 0, 6, "minute", 7737817680.0, 86400000.0/(24.0*60.0) }, |
| { 0, 4, "hour", 128963628.0, 86400000.0/24.0 }, |
| { 0, 3, "day", 5373485.0, 86400000.0 }, |
| { 1, 5, "month", 176546.0, 30.0*86400000.0 }, |
| { 2, 4, "year", 14713.0, 365.0*86400000.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 rc = 1; |
| double r; |
| switch(sqlite3UpperToLower[(u8)z[0]] ){ |
| #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) ){ |
| computeJD(p); |
| p->iJD += localtimeOffset(p, pCtx, &rc); |
| clearYMD_HMS_TZ(p); |
| } |
| 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 ){ |
| r = p->s*1000.0 + 210866760000000.0; |
| if( r>=0.0 && r<464269060800000.0 ){ |
| clearYMD_HMS_TZ(p); |
| p->iJD = (sqlite3_int64)r; |
| 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 ){ |
| sqlite3_int64 c1; |
| computeJD(p); |
| c1 = localtimeOffset(p, pCtx, &rc); |
| if( rc==SQLITE_OK ){ |
| p->iJD -= c1; |
| clearYMD_HMS_TZ(p); |
| p->iJD += c1 - localtimeOffset(p, pCtx, &rc); |
| } |
| p->tzSet = 1; |
| }else{ |
| 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 |
| && (n=(int)r)==r && n>=0 && r<7 ){ |
| 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. |
| */ |
| if( sqlite3_strnicmp(z, "start of ", 9)!=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; |
| for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){} |
| if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){ |
| rc = 1; |
| break; |
| } |
| if( z[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. |
| */ |
| const char *z2 = z; |
| 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( z[0]=='-' ) 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); |
| 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( aXformType[i].eType ){ |
| case 1: { /* Special processing to add months */ |
| int x; |
| 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 2: { /* Special processing to add years */ |
| int y = (int)r; |
| computeYMD_HMS(p); |
| p->Y += y; |
| p->validJD = 0; |
| r -= (int)r; |
| break; |
| } |
| } |
| computeJD(p); |
| p->iJD += (sqlite3_int64)(r*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 ){ |
| 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) ) 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); |
| } |
| } |
| |
| /* |
| ** 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 ){ |
| char zBuf[100]; |
| computeYMD_HMS(&x); |
| sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d", |
| x.Y, x.M, x.D, x.h, x.m, (int)(x.s)); |
| sqlite3_result_text(context, zBuf, -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 ){ |
| char zBuf[100]; |
| computeHMS(&x); |
| sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s); |
| sqlite3_result_text(context, zBuf, -1, 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 ){ |
| char zBuf[100]; |
| computeYMD(&x); |
| sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D); |
| sqlite3_result_text(context, zBuf, -1, 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; |
| u64 n; |
| size_t i,j; |
| char *z; |
| sqlite3 *db; |
| const char *zFmt; |
| char zBuf[100]; |
| 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); |
| for(i=0, n=1; zFmt[i]; i++, n++){ |
| if( zFmt[i]=='%' ){ |
| switch( zFmt[i+1] ){ |
| case 'd': |
| case 'H': |
| case 'm': |
| case 'M': |
| case 'S': |
| case 'W': |
| n++; |
| /* fall thru */ |
| case 'w': |
| case '%': |
| break; |
| case 'f': |
| n += 8; |
| break; |
| case 'j': |
| n += 3; |
| break; |
| case 'Y': |
| n += 8; |
| break; |
| case 's': |
| case 'J': |
| n += 50; |
| break; |
| default: |
| return; /* ERROR. return a NULL */ |
| } |
| i++; |
| } |
| } |
| testcase( n==sizeof(zBuf)-1 ); |
| testcase( n==sizeof(zBuf) ); |
| testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH]+1 ); |
| testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ); |
| if( n<sizeof(zBuf) ){ |
| z = zBuf; |
| }else if( n>(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ){ |
| sqlite3_result_error_toobig(context); |
| return; |
| }else{ |
| z = sqlite3DbMallocRawNN(db, (int)n); |
| if( z==0 ){ |
| sqlite3_result_error_nomem(context); |
| return; |
| } |
| } |
| computeJD(&x); |
| computeYMD_HMS(&x); |
| for(i=j=0; zFmt[i]; i++){ |
| if( zFmt[i]!='%' ){ |
| z[j++] = zFmt[i]; |
| }else{ |
| i++; |
| switch( zFmt[i] ){ |
| case 'd': sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break; |
| case 'f': { |
| double s = x.s; |
| if( s>59.999 ) s = 59.999; |
| sqlite3_snprintf(7, &z[j],"%06.3f", s); |
| j += sqlite3Strlen30(&z[j]); |
| break; |
| } |
| case 'H': sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; 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( zFmt[i]=='W' ){ |
| int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */ |
| wd = (int)(((x.iJD+43200000)/86400000)%7); |
| sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7); |
| j += 2; |
| }else{ |
| sqlite3_snprintf(4, &z[j],"%03d",nDay+1); |
| j += 3; |
| } |
| break; |
| } |
| case 'J': { |
| sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0); |
| j+=sqlite3Strlen30(&z[j]); |
| break; |
| } |
| case 'm': sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break; |
| case 'M': sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break; |
| case 's': { |
| sqlite3_snprintf(30,&z[j],"%lld", |
| (i64)(x.iJD/1000 - 21086676*(i64)10000)); |
| j += sqlite3Strlen30(&z[j]); |
| break; |
| } |
| case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break; |
| case 'w': { |
| z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0'; |
| break; |
| } |
| case 'Y': { |
| sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]); |
| break; |
| } |
| default: z[j++] = '%'; break; |
| } |
| } |
| } |
| z[j] = 0; |
| sqlite3_result_text(context, z, -1, |
| z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC); |
| } |
| |
| /* |
| ** 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); |
| } |
| |
| /* |
| ** 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_MASTER)); |
| pTm = gmtime(&t); |
| if( pTm ) memcpy(&sNow, pTm, sizeof(sNow)); |
| sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER)); |
| #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(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 ), |
| 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)); |
| } |
