diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-05 17:28:19 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-05 17:28:19 +0000 |
commit | 18657a960e125336f704ea058e25c27bd3900dcb (patch) | |
tree | 17b438b680ed45a996d7b59951e6aa34023783f2 /src/date.c | |
parent | Initial commit. (diff) | |
download | sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip |
Adding upstream version 3.40.1.upstream/3.40.1upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'src/date.c')
-rw-r--r-- | src/date.c | 1357 |
1 files changed, 1357 insertions, 0 deletions
diff --git a/src/date.c b/src/date.c new file mode 100644 index 0000000..a3e58bc --- /dev/null +++ b/src/date.c @@ -0,0 +1,1357 @@ +/* +** 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 + 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; + } + 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 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 }, +}; + +/* +** 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 */ + if( !p->rawS || p->validJD ){ + rc = 0; + 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 */ + ){ + r = p->s*1000.0 + 210866760000000.0; + clearYMD_HMS_TZ(p); + p->iJD = (sqlite3_int64)(r + 0.5); + p->validJD = 1; + p->rawS = 0; + 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 */ + int 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. + */ + 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( i ){ + case 4: { /* Special processing to add months */ + int x; + 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); + 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; + char zBuf[24]; + 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] = ':'; + s = (int)x.s; + zBuf[18] = '0' + (s/10)%10; + zBuf[19] = '0' + (s)%10; + zBuf[20] = 0; + if( x.Y<0 ){ + zBuf[0] = '-'; + sqlite3_result_text(context, zBuf, 20, SQLITE_TRANSIENT); + }else{ + sqlite3_result_text(context, &zBuf[1], 19, 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; + 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] = ':'; + s = (int)x.s; + zBuf[6] = '0' + (s/10)%10; + zBuf[7] = '0' + (s)%10; + zBuf[8] = 0; + sqlite3_result_text(context, zBuf, 8, 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++){ + if( zFmt[i]!='%' ) continue; + if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j)); + i++; + j = i + 1; + switch( zFmt[i] ){ + case 'd': { + sqlite3_str_appendf(&sRes, "%02d", 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 'H': { + sqlite3_str_appendf(&sRes, "%02d", x.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( zFmt[i]=='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 's': { + 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 'w': { + sqlite3_str_appendchar(&sRes, 1, + (char)(((x.iJD+129600000)/86400000) % 7) + '0'); + 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); +} + +/* +** 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 ), + 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)); +} |