000001 /*
000002 ** 2003 October 31
000003 **
000004 ** The author disclaims copyright to this source code. In place of
000005 ** a legal notice, here is a blessing:
000006 **
000007 ** May you do good and not evil.
000008 ** May you find forgiveness for yourself and forgive others.
000009 ** May you share freely, never taking more than you give.
000010 **
000011 *************************************************************************
000012 ** This file contains the C functions that implement date and time
000013 ** functions for SQLite.
000014 **
000015 ** There is only one exported symbol in this file - the function
000016 ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
000017 ** All other code has file scope.
000018 **
000019 ** SQLite processes all times and dates as julian day numbers. The
000020 ** dates and times are stored as the number of days since noon
000021 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian
000022 ** calendar system.
000023 **
000024 ** 1970-01-01 00:00:00 is JD 2440587.5
000025 ** 2000-01-01 00:00:00 is JD 2451544.5
000026 **
000027 ** This implementation requires years to be expressed as a 4-digit number
000028 ** which means that only dates between 0000-01-01 and 9999-12-31 can
000029 ** be represented, even though julian day numbers allow a much wider
000030 ** range of dates.
000031 **
000032 ** The Gregorian calendar system is used for all dates and times,
000033 ** even those that predate the Gregorian calendar. Historians usually
000034 ** use the julian calendar for dates prior to 1582-10-15 and for some
000035 ** dates afterwards, depending on locale. Beware of this difference.
000036 **
000037 ** The conversion algorithms are implemented based on descriptions
000038 ** in the following text:
000039 **
000040 ** Jean Meeus
000041 ** Astronomical Algorithms, 2nd Edition, 1998
000042 ** ISBN 0-943396-61-1
000043 ** Willmann-Bell, Inc
000044 ** Richmond, Virginia (USA)
000045 */
000046 #include "sqliteInt.h"
000047 #include <stdlib.h>
000048 #include <assert.h>
000049 #include <time.h>
000050
000051 #ifndef SQLITE_OMIT_DATETIME_FUNCS
000052
000053 /*
000054 ** The MSVC CRT on Windows CE may not have a localtime() function.
000055 ** So declare a substitute. The substitute function itself is
000056 ** defined in "os_win.c".
000057 */
000058 #if !defined(SQLITE_OMIT_LOCALTIME) && defined(_WIN32_WCE) && \
000059 (!defined(SQLITE_MSVC_LOCALTIME_API) || !SQLITE_MSVC_LOCALTIME_API)
000060 struct tm *__cdecl localtime(const time_t *);
000061 #endif
000062
000063 /*
000064 ** A structure for holding a single date and time.
000065 */
000066 typedef struct DateTime DateTime;
000067 struct DateTime {
000068 sqlite3_int64 iJD; /* The julian day number times 86400000 */
000069 int Y, M, D; /* Year, month, and day */
000070 int h, m; /* Hour and minutes */
000071 int tz; /* Timezone offset in minutes */
000072 double s; /* Seconds */
000073 char validJD; /* True (1) if iJD is valid */
000074 char rawS; /* Raw numeric value stored in s */
000075 char validYMD; /* True (1) if Y,M,D are valid */
000076 char validHMS; /* True (1) if h,m,s are valid */
000077 char validTZ; /* True (1) if tz is valid */
000078 char tzSet; /* Timezone was set explicitly */
000079 char isError; /* An overflow has occurred */
000080 };
000081
000082
000083 /*
000084 ** Convert zDate into one or more integers according to the conversion
000085 ** specifier zFormat.
000086 **
000087 ** zFormat[] contains 4 characters for each integer converted, except for
000088 ** the last integer which is specified by three characters. The meaning
000089 ** of a four-character format specifiers ABCD is:
000090 **
000091 ** A: number of digits to convert. Always "2" or "4".
000092 ** B: minimum value. Always "0" or "1".
000093 ** C: maximum value, decoded as:
000094 ** a: 12
000095 ** b: 14
000096 ** c: 24
000097 ** d: 31
000098 ** e: 59
000099 ** f: 9999
000100 ** D: the separator character, or \000 to indicate this is the
000101 ** last number to convert.
000102 **
000103 ** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would
000104 ** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-".
000105 ** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates
000106 ** the 2-digit day which is the last integer in the set.
000107 **
000108 ** The function returns the number of successful conversions.
000109 */
000110 static int getDigits(const char *zDate, const char *zFormat, ...){
000111 /* The aMx[] array translates the 3rd character of each format
000112 ** spec into a max size: a b c d e f */
000113 static const u16 aMx[] = { 12, 14, 24, 31, 59, 9999 };
000114 va_list ap;
000115 int cnt = 0;
000116 char nextC;
000117 va_start(ap, zFormat);
000118 do{
000119 char N = zFormat[0] - '0';
000120 char min = zFormat[1] - '0';
000121 int val = 0;
000122 u16 max;
000123
000124 assert( zFormat[2]>='a' && zFormat[2]<='f' );
000125 max = aMx[zFormat[2] - 'a'];
000126 nextC = zFormat[3];
000127 val = 0;
000128 while( N-- ){
000129 if( !sqlite3Isdigit(*zDate) ){
000130 goto end_getDigits;
000131 }
000132 val = val*10 + *zDate - '0';
000133 zDate++;
000134 }
000135 if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){
000136 goto end_getDigits;
000137 }
000138 *va_arg(ap,int*) = val;
000139 zDate++;
000140 cnt++;
000141 zFormat += 4;
000142 }while( nextC );
000143 end_getDigits:
000144 va_end(ap);
000145 return cnt;
000146 }
000147
000148 /*
000149 ** Parse a timezone extension on the end of a date-time.
000150 ** The extension is of the form:
000151 **
000152 ** (+/-)HH:MM
000153 **
000154 ** Or the "zulu" notation:
000155 **
000156 ** Z
000157 **
000158 ** If the parse is successful, write the number of minutes
000159 ** of change in p->tz and return 0. If a parser error occurs,
000160 ** return non-zero.
000161 **
000162 ** A missing specifier is not considered an error.
000163 */
000164 static int parseTimezone(const char *zDate, DateTime *p){
000165 int sgn = 0;
000166 int nHr, nMn;
000167 int c;
000168 while( sqlite3Isspace(*zDate) ){ zDate++; }
000169 p->tz = 0;
000170 c = *zDate;
000171 if( c=='-' ){
000172 sgn = -1;
000173 }else if( c=='+' ){
000174 sgn = +1;
000175 }else if( c=='Z' || c=='z' ){
000176 zDate++;
000177 goto zulu_time;
000178 }else{
000179 return c!=0;
000180 }
000181 zDate++;
000182 if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){
000183 return 1;
000184 }
000185 zDate += 5;
000186 p->tz = sgn*(nMn + nHr*60);
000187 zulu_time:
000188 while( sqlite3Isspace(*zDate) ){ zDate++; }
000189 p->tzSet = 1;
000190 return *zDate!=0;
000191 }
000192
000193 /*
000194 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
000195 ** The HH, MM, and SS must each be exactly 2 digits. The
000196 ** fractional seconds FFFF can be one or more digits.
000197 **
000198 ** Return 1 if there is a parsing error and 0 on success.
000199 */
000200 static int parseHhMmSs(const char *zDate, DateTime *p){
000201 int h, m, s;
000202 double ms = 0.0;
000203 if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){
000204 return 1;
000205 }
000206 zDate += 5;
000207 if( *zDate==':' ){
000208 zDate++;
000209 if( getDigits(zDate, "20e", &s)!=1 ){
000210 return 1;
000211 }
000212 zDate += 2;
000213 if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){
000214 double rScale = 1.0;
000215 zDate++;
000216 while( sqlite3Isdigit(*zDate) ){
000217 ms = ms*10.0 + *zDate - '0';
000218 rScale *= 10.0;
000219 zDate++;
000220 }
000221 ms /= rScale;
000222 }
000223 }else{
000224 s = 0;
000225 }
000226 p->validJD = 0;
000227 p->rawS = 0;
000228 p->validHMS = 1;
000229 p->h = h;
000230 p->m = m;
000231 p->s = s + ms;
000232 if( parseTimezone(zDate, p) ) return 1;
000233 p->validTZ = (p->tz!=0)?1:0;
000234 return 0;
000235 }
000236
000237 /*
000238 ** Put the DateTime object into its error state.
000239 */
000240 static void datetimeError(DateTime *p){
000241 memset(p, 0, sizeof(*p));
000242 p->isError = 1;
000243 }
000244
000245 /*
000246 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume
000247 ** that the YYYY-MM-DD is according to the Gregorian calendar.
000248 **
000249 ** Reference: Meeus page 61
000250 */
000251 static void computeJD(DateTime *p){
000252 int Y, M, D, A, B, X1, X2;
000253
000254 if( p->validJD ) return;
000255 if( p->validYMD ){
000256 Y = p->Y;
000257 M = p->M;
000258 D = p->D;
000259 }else{
000260 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */
000261 M = 1;
000262 D = 1;
000263 }
000264 if( Y<-4713 || Y>9999 || p->rawS ){
000265 datetimeError(p);
000266 return;
000267 }
000268 if( M<=2 ){
000269 Y--;
000270 M += 12;
000271 }
000272 A = Y/100;
000273 B = 2 - A + (A/4);
000274 X1 = 36525*(Y+4716)/100;
000275 X2 = 306001*(M+1)/10000;
000276 p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
000277 p->validJD = 1;
000278 if( p->validHMS ){
000279 p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000);
000280 if( p->validTZ ){
000281 p->iJD -= p->tz*60000;
000282 p->validYMD = 0;
000283 p->validHMS = 0;
000284 p->validTZ = 0;
000285 }
000286 }
000287 }
000288
000289 /*
000290 ** Parse dates of the form
000291 **
000292 ** YYYY-MM-DD HH:MM:SS.FFF
000293 ** YYYY-MM-DD HH:MM:SS
000294 ** YYYY-MM-DD HH:MM
000295 ** YYYY-MM-DD
000296 **
000297 ** Write the result into the DateTime structure and return 0
000298 ** on success and 1 if the input string is not a well-formed
000299 ** date.
000300 */
000301 static int parseYyyyMmDd(const char *zDate, DateTime *p){
000302 int Y, M, D, neg;
000303
000304 if( zDate[0]=='-' ){
000305 zDate++;
000306 neg = 1;
000307 }else{
000308 neg = 0;
000309 }
000310 if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){
000311 return 1;
000312 }
000313 zDate += 10;
000314 while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }
000315 if( parseHhMmSs(zDate, p)==0 ){
000316 /* We got the time */
000317 }else if( *zDate==0 ){
000318 p->validHMS = 0;
000319 }else{
000320 return 1;
000321 }
000322 p->validJD = 0;
000323 p->validYMD = 1;
000324 p->Y = neg ? -Y : Y;
000325 p->M = M;
000326 p->D = D;
000327 if( p->validTZ ){
000328 computeJD(p);
000329 }
000330 return 0;
000331 }
000332
000333 /*
000334 ** Set the time to the current time reported by the VFS.
000335 **
000336 ** Return the number of errors.
000337 */
000338 static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
000339 p->iJD = sqlite3StmtCurrentTime(context);
000340 if( p->iJD>0 ){
000341 p->validJD = 1;
000342 return 0;
000343 }else{
000344 return 1;
000345 }
000346 }
000347
000348 /*
000349 ** Input "r" is a numeric quantity which might be a julian day number,
000350 ** or the number of seconds since 1970. If the value if r is within
000351 ** range of a julian day number, install it as such and set validJD.
000352 ** If the value is a valid unix timestamp, put it in p->s and set p->rawS.
000353 */
000354 static void setRawDateNumber(DateTime *p, double r){
000355 p->s = r;
000356 p->rawS = 1;
000357 if( r>=0.0 && r<5373484.5 ){
000358 p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
000359 p->validJD = 1;
000360 }
000361 }
000362
000363 /*
000364 ** Attempt to parse the given string into a julian day number. Return
000365 ** the number of errors.
000366 **
000367 ** The following are acceptable forms for the input string:
000368 **
000369 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM
000370 ** DDDD.DD
000371 ** now
000372 **
000373 ** In the first form, the +/-HH:MM is always optional. The fractional
000374 ** seconds extension (the ".FFF") is optional. The seconds portion
000375 ** (":SS.FFF") is option. The year and date can be omitted as long
000376 ** as there is a time string. The time string can be omitted as long
000377 ** as there is a year and date.
000378 */
000379 static int parseDateOrTime(
000380 sqlite3_context *context,
000381 const char *zDate,
000382 DateTime *p
000383 ){
000384 double r;
000385 if( parseYyyyMmDd(zDate,p)==0 ){
000386 return 0;
000387 }else if( parseHhMmSs(zDate, p)==0 ){
000388 return 0;
000389 }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
000390 return setDateTimeToCurrent(context, p);
000391 }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){
000392 setRawDateNumber(p, r);
000393 return 0;
000394 }
000395 return 1;
000396 }
000397
000398 /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999.
000399 ** Multiplying this by 86400000 gives 464269060799999 as the maximum value
000400 ** for DateTime.iJD.
000401 **
000402 ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with
000403 ** such a large integer literal, so we have to encode it.
000404 */
000405 #define INT_464269060799999 ((((i64)0x1a640)<<32)|0x1072fdff)
000406
000407 /*
000408 ** Return TRUE if the given julian day number is within range.
000409 **
000410 ** The input is the JulianDay times 86400000.
000411 */
000412 static int validJulianDay(sqlite3_int64 iJD){
000413 return iJD>=0 && iJD<=INT_464269060799999;
000414 }
000415
000416 /*
000417 ** Compute the Year, Month, and Day from the julian day number.
000418 */
000419 static void computeYMD(DateTime *p){
000420 int Z, A, B, C, D, E, X1;
000421 if( p->validYMD ) return;
000422 if( !p->validJD ){
000423 p->Y = 2000;
000424 p->M = 1;
000425 p->D = 1;
000426 }else if( !validJulianDay(p->iJD) ){
000427 datetimeError(p);
000428 return;
000429 }else{
000430 Z = (int)((p->iJD + 43200000)/86400000);
000431 A = (int)((Z - 1867216.25)/36524.25);
000432 A = Z + 1 + A - (A/4);
000433 B = A + 1524;
000434 C = (int)((B - 122.1)/365.25);
000435 D = (36525*(C&32767))/100;
000436 E = (int)((B-D)/30.6001);
000437 X1 = (int)(30.6001*E);
000438 p->D = B - D - X1;
000439 p->M = E<14 ? E-1 : E-13;
000440 p->Y = p->M>2 ? C - 4716 : C - 4715;
000441 }
000442 p->validYMD = 1;
000443 }
000444
000445 /*
000446 ** Compute the Hour, Minute, and Seconds from the julian day number.
000447 */
000448 static void computeHMS(DateTime *p){
000449 int s;
000450 if( p->validHMS ) return;
000451 computeJD(p);
000452 s = (int)((p->iJD + 43200000) % 86400000);
000453 p->s = s/1000.0;
000454 s = (int)p->s;
000455 p->s -= s;
000456 p->h = s/3600;
000457 s -= p->h*3600;
000458 p->m = s/60;
000459 p->s += s - p->m*60;
000460 p->rawS = 0;
000461 p->validHMS = 1;
000462 }
000463
000464 /*
000465 ** Compute both YMD and HMS
000466 */
000467 static void computeYMD_HMS(DateTime *p){
000468 computeYMD(p);
000469 computeHMS(p);
000470 }
000471
000472 /*
000473 ** Clear the YMD and HMS and the TZ
000474 */
000475 static void clearYMD_HMS_TZ(DateTime *p){
000476 p->validYMD = 0;
000477 p->validHMS = 0;
000478 p->validTZ = 0;
000479 }
000480
000481 #ifndef SQLITE_OMIT_LOCALTIME
000482 /*
000483 ** On recent Windows platforms, the localtime_s() function is available
000484 ** as part of the "Secure CRT". It is essentially equivalent to
000485 ** localtime_r() available under most POSIX platforms, except that the
000486 ** order of the parameters is reversed.
000487 **
000488 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
000489 **
000490 ** If the user has not indicated to use localtime_r() or localtime_s()
000491 ** already, check for an MSVC build environment that provides
000492 ** localtime_s().
000493 */
000494 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \
000495 && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
000496 #undef HAVE_LOCALTIME_S
000497 #define HAVE_LOCALTIME_S 1
000498 #endif
000499
000500 /*
000501 ** The following routine implements the rough equivalent of localtime_r()
000502 ** using whatever operating-system specific localtime facility that
000503 ** is available. This routine returns 0 on success and
000504 ** non-zero on any kind of error.
000505 **
000506 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is true then this
000507 ** routine will always fail.
000508 **
000509 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
000510 ** library function localtime_r() is used to assist in the calculation of
000511 ** local time.
000512 */
000513 static int osLocaltime(time_t *t, struct tm *pTm){
000514 int rc;
000515 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S
000516 struct tm *pX;
000517 #if SQLITE_THREADSAFE>0
000518 sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER);
000519 #endif
000520 sqlite3_mutex_enter(mutex);
000521 pX = localtime(t);
000522 #ifndef SQLITE_UNTESTABLE
000523 if( sqlite3GlobalConfig.bLocaltimeFault ) pX = 0;
000524 #endif
000525 if( pX ) *pTm = *pX;
000526 sqlite3_mutex_leave(mutex);
000527 rc = pX==0;
000528 #else
000529 #ifndef SQLITE_UNTESTABLE
000530 if( sqlite3GlobalConfig.bLocaltimeFault ) return 1;
000531 #endif
000532 #if HAVE_LOCALTIME_R
000533 rc = localtime_r(t, pTm)==0;
000534 #else
000535 rc = localtime_s(pTm, t);
000536 #endif /* HAVE_LOCALTIME_R */
000537 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
000538 return rc;
000539 }
000540 #endif /* SQLITE_OMIT_LOCALTIME */
000541
000542
000543 #ifndef SQLITE_OMIT_LOCALTIME
000544 /*
000545 ** Compute the difference (in milliseconds) between localtime and UTC
000546 ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs,
000547 ** return this value and set *pRc to SQLITE_OK.
000548 **
000549 ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value
000550 ** is undefined in this case.
000551 */
000552 static sqlite3_int64 localtimeOffset(
000553 DateTime *p, /* Date at which to calculate offset */
000554 sqlite3_context *pCtx, /* Write error here if one occurs */
000555 int *pRc /* OUT: Error code. SQLITE_OK or ERROR */
000556 ){
000557 DateTime x, y;
000558 time_t t;
000559 struct tm sLocal;
000560
000561 /* Initialize the contents of sLocal to avoid a compiler warning. */
000562 memset(&sLocal, 0, sizeof(sLocal));
000563
000564 x = *p;
000565 computeYMD_HMS(&x);
000566 if( x.Y<1971 || x.Y>=2038 ){
000567 /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
000568 ** works for years between 1970 and 2037. For dates outside this range,
000569 ** SQLite attempts to map the year into an equivalent year within this
000570 ** range, do the calculation, then map the year back.
000571 */
000572 x.Y = 2000;
000573 x.M = 1;
000574 x.D = 1;
000575 x.h = 0;
000576 x.m = 0;
000577 x.s = 0.0;
000578 } else {
000579 int s = (int)(x.s + 0.5);
000580 x.s = s;
000581 }
000582 x.tz = 0;
000583 x.validJD = 0;
000584 computeJD(&x);
000585 t = (time_t)(x.iJD/1000 - 21086676*(i64)10000);
000586 if( osLocaltime(&t, &sLocal) ){
000587 sqlite3_result_error(pCtx, "local time unavailable", -1);
000588 *pRc = SQLITE_ERROR;
000589 return 0;
000590 }
000591 y.Y = sLocal.tm_year + 1900;
000592 y.M = sLocal.tm_mon + 1;
000593 y.D = sLocal.tm_mday;
000594 y.h = sLocal.tm_hour;
000595 y.m = sLocal.tm_min;
000596 y.s = sLocal.tm_sec;
000597 y.validYMD = 1;
000598 y.validHMS = 1;
000599 y.validJD = 0;
000600 y.rawS = 0;
000601 y.validTZ = 0;
000602 y.isError = 0;
000603 computeJD(&y);
000604 *pRc = SQLITE_OK;
000605 return y.iJD - x.iJD;
000606 }
000607 #endif /* SQLITE_OMIT_LOCALTIME */
000608
000609 /*
000610 ** The following table defines various date transformations of the form
000611 **
000612 ** 'NNN days'
000613 **
000614 ** Where NNN is an arbitrary floating-point number and "days" can be one
000615 ** of several units of time.
000616 */
000617 static const struct {
000618 u8 eType; /* Transformation type code */
000619 u8 nName; /* Length of th name */
000620 char *zName; /* Name of the transformation */
000621 double rLimit; /* Maximum NNN value for this transform */
000622 double rXform; /* Constant used for this transform */
000623 } aXformType[] = {
000624 { 0, 6, "second", 464269060800.0, 86400000.0/(24.0*60.0*60.0) },
000625 { 0, 6, "minute", 7737817680.0, 86400000.0/(24.0*60.0) },
000626 { 0, 4, "hour", 128963628.0, 86400000.0/24.0 },
000627 { 0, 3, "day", 5373485.0, 86400000.0 },
000628 { 1, 5, "month", 176546.0, 30.0*86400000.0 },
000629 { 2, 4, "year", 14713.0, 365.0*86400000.0 },
000630 };
000631
000632 /*
000633 ** Process a modifier to a date-time stamp. The modifiers are
000634 ** as follows:
000635 **
000636 ** NNN days
000637 ** NNN hours
000638 ** NNN minutes
000639 ** NNN.NNNN seconds
000640 ** NNN months
000641 ** NNN years
000642 ** start of month
000643 ** start of year
000644 ** start of week
000645 ** start of day
000646 ** weekday N
000647 ** unixepoch
000648 ** localtime
000649 ** utc
000650 **
000651 ** Return 0 on success and 1 if there is any kind of error. If the error
000652 ** is in a system call (i.e. localtime()), then an error message is written
000653 ** to context pCtx. If the error is an unrecognized modifier, no error is
000654 ** written to pCtx.
000655 */
000656 static int parseModifier(
000657 sqlite3_context *pCtx, /* Function context */
000658 const char *z, /* The text of the modifier */
000659 int n, /* Length of zMod in bytes */
000660 DateTime *p /* The date/time value to be modified */
000661 ){
000662 int rc = 1;
000663 double r;
000664 switch(sqlite3UpperToLower[(u8)z[0]] ){
000665 #ifndef SQLITE_OMIT_LOCALTIME
000666 case 'l': {
000667 /* localtime
000668 **
000669 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
000670 ** show local time.
000671 */
000672 if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){
000673 computeJD(p);
000674 p->iJD += localtimeOffset(p, pCtx, &rc);
000675 clearYMD_HMS_TZ(p);
000676 }
000677 break;
000678 }
000679 #endif
000680 case 'u': {
000681 /*
000682 ** unixepoch
000683 **
000684 ** Treat the current value of p->s as the number of
000685 ** seconds since 1970. Convert to a real julian day number.
000686 */
000687 if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){
000688 r = p->s*1000.0 + 210866760000000.0;
000689 if( r>=0.0 && r<464269060800000.0 ){
000690 clearYMD_HMS_TZ(p);
000691 p->iJD = (sqlite3_int64)r;
000692 p->validJD = 1;
000693 p->rawS = 0;
000694 rc = 0;
000695 }
000696 }
000697 #ifndef SQLITE_OMIT_LOCALTIME
000698 else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){
000699 if( p->tzSet==0 ){
000700 sqlite3_int64 c1;
000701 computeJD(p);
000702 c1 = localtimeOffset(p, pCtx, &rc);
000703 if( rc==SQLITE_OK ){
000704 p->iJD -= c1;
000705 clearYMD_HMS_TZ(p);
000706 p->iJD += c1 - localtimeOffset(p, pCtx, &rc);
000707 }
000708 p->tzSet = 1;
000709 }else{
000710 rc = SQLITE_OK;
000711 }
000712 }
000713 #endif
000714 break;
000715 }
000716 case 'w': {
000717 /*
000718 ** weekday N
000719 **
000720 ** Move the date to the same time on the next occurrence of
000721 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the
000722 ** date is already on the appropriate weekday, this is a no-op.
000723 */
000724 if( sqlite3_strnicmp(z, "weekday ", 8)==0
000725 && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0
000726 && (n=(int)r)==r && n>=0 && r<7 ){
000727 sqlite3_int64 Z;
000728 computeYMD_HMS(p);
000729 p->validTZ = 0;
000730 p->validJD = 0;
000731 computeJD(p);
000732 Z = ((p->iJD + 129600000)/86400000) % 7;
000733 if( Z>n ) Z -= 7;
000734 p->iJD += (n - Z)*86400000;
000735 clearYMD_HMS_TZ(p);
000736 rc = 0;
000737 }
000738 break;
000739 }
000740 case 's': {
000741 /*
000742 ** start of TTTTT
000743 **
000744 ** Move the date backwards to the beginning of the current day,
000745 ** or month or year.
000746 */
000747 if( sqlite3_strnicmp(z, "start of ", 9)!=0 ) break;
000748 if( !p->validJD && !p->validYMD && !p->validHMS ) break;
000749 z += 9;
000750 computeYMD(p);
000751 p->validHMS = 1;
000752 p->h = p->m = 0;
000753 p->s = 0.0;
000754 p->rawS = 0;
000755 p->validTZ = 0;
000756 p->validJD = 0;
000757 if( sqlite3_stricmp(z,"month")==0 ){
000758 p->D = 1;
000759 rc = 0;
000760 }else if( sqlite3_stricmp(z,"year")==0 ){
000761 p->M = 1;
000762 p->D = 1;
000763 rc = 0;
000764 }else if( sqlite3_stricmp(z,"day")==0 ){
000765 rc = 0;
000766 }
000767 break;
000768 }
000769 case '+':
000770 case '-':
000771 case '0':
000772 case '1':
000773 case '2':
000774 case '3':
000775 case '4':
000776 case '5':
000777 case '6':
000778 case '7':
000779 case '8':
000780 case '9': {
000781 double rRounder;
000782 int i;
000783 for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){}
000784 if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){
000785 rc = 1;
000786 break;
000787 }
000788 if( z[n]==':' ){
000789 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
000790 ** specified number of hours, minutes, seconds, and fractional seconds
000791 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be
000792 ** omitted.
000793 */
000794 const char *z2 = z;
000795 DateTime tx;
000796 sqlite3_int64 day;
000797 if( !sqlite3Isdigit(*z2) ) z2++;
000798 memset(&tx, 0, sizeof(tx));
000799 if( parseHhMmSs(z2, &tx) ) break;
000800 computeJD(&tx);
000801 tx.iJD -= 43200000;
000802 day = tx.iJD/86400000;
000803 tx.iJD -= day*86400000;
000804 if( z[0]=='-' ) tx.iJD = -tx.iJD;
000805 computeJD(p);
000806 clearYMD_HMS_TZ(p);
000807 p->iJD += tx.iJD;
000808 rc = 0;
000809 break;
000810 }
000811
000812 /* If control reaches this point, it means the transformation is
000813 ** one of the forms like "+NNN days". */
000814 z += n;
000815 while( sqlite3Isspace(*z) ) z++;
000816 n = sqlite3Strlen30(z);
000817 if( n>10 || n<3 ) break;
000818 if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
000819 computeJD(p);
000820 rc = 1;
000821 rRounder = r<0 ? -0.5 : +0.5;
000822 for(i=0; i<ArraySize(aXformType); i++){
000823 if( aXformType[i].nName==n
000824 && sqlite3_strnicmp(aXformType[i].zName, z, n)==0
000825 && r>-aXformType[i].rLimit && r<aXformType[i].rLimit
000826 ){
000827 switch( aXformType[i].eType ){
000828 case 1: { /* Special processing to add months */
000829 int x;
000830 computeYMD_HMS(p);
000831 p->M += (int)r;
000832 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
000833 p->Y += x;
000834 p->M -= x*12;
000835 p->validJD = 0;
000836 r -= (int)r;
000837 break;
000838 }
000839 case 2: { /* Special processing to add years */
000840 int y = (int)r;
000841 computeYMD_HMS(p);
000842 p->Y += y;
000843 p->validJD = 0;
000844 r -= (int)r;
000845 break;
000846 }
000847 }
000848 computeJD(p);
000849 p->iJD += (sqlite3_int64)(r*aXformType[i].rXform + rRounder);
000850 rc = 0;
000851 break;
000852 }
000853 }
000854 clearYMD_HMS_TZ(p);
000855 break;
000856 }
000857 default: {
000858 break;
000859 }
000860 }
000861 return rc;
000862 }
000863
000864 /*
000865 ** Process time function arguments. argv[0] is a date-time stamp.
000866 ** argv[1] and following are modifiers. Parse them all and write
000867 ** the resulting time into the DateTime structure p. Return 0
000868 ** on success and 1 if there are any errors.
000869 **
000870 ** If there are zero parameters (if even argv[0] is undefined)
000871 ** then assume a default value of "now" for argv[0].
000872 */
000873 static int isDate(
000874 sqlite3_context *context,
000875 int argc,
000876 sqlite3_value **argv,
000877 DateTime *p
000878 ){
000879 int i, n;
000880 const unsigned char *z;
000881 int eType;
000882 memset(p, 0, sizeof(*p));
000883 if( argc==0 ){
000884 return setDateTimeToCurrent(context, p);
000885 }
000886 if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
000887 || eType==SQLITE_INTEGER ){
000888 setRawDateNumber(p, sqlite3_value_double(argv[0]));
000889 }else{
000890 z = sqlite3_value_text(argv[0]);
000891 if( !z || parseDateOrTime(context, (char*)z, p) ){
000892 return 1;
000893 }
000894 }
000895 for(i=1; i<argc; i++){
000896 z = sqlite3_value_text(argv[i]);
000897 n = sqlite3_value_bytes(argv[i]);
000898 if( z==0 || parseModifier(context, (char*)z, n, p) ) return 1;
000899 }
000900 computeJD(p);
000901 if( p->isError || !validJulianDay(p->iJD) ) return 1;
000902 return 0;
000903 }
000904
000905
000906 /*
000907 ** The following routines implement the various date and time functions
000908 ** of SQLite.
000909 */
000910
000911 /*
000912 ** julianday( TIMESTRING, MOD, MOD, ...)
000913 **
000914 ** Return the julian day number of the date specified in the arguments
000915 */
000916 static void juliandayFunc(
000917 sqlite3_context *context,
000918 int argc,
000919 sqlite3_value **argv
000920 ){
000921 DateTime x;
000922 if( isDate(context, argc, argv, &x)==0 ){
000923 computeJD(&x);
000924 sqlite3_result_double(context, x.iJD/86400000.0);
000925 }
000926 }
000927
000928 /*
000929 ** datetime( TIMESTRING, MOD, MOD, ...)
000930 **
000931 ** Return YYYY-MM-DD HH:MM:SS
000932 */
000933 static void datetimeFunc(
000934 sqlite3_context *context,
000935 int argc,
000936 sqlite3_value **argv
000937 ){
000938 DateTime x;
000939 if( isDate(context, argc, argv, &x)==0 ){
000940 char zBuf[100];
000941 computeYMD_HMS(&x);
000942 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d",
000943 x.Y, x.M, x.D, x.h, x.m, (int)(x.s));
000944 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
000945 }
000946 }
000947
000948 /*
000949 ** time( TIMESTRING, MOD, MOD, ...)
000950 **
000951 ** Return HH:MM:SS
000952 */
000953 static void timeFunc(
000954 sqlite3_context *context,
000955 int argc,
000956 sqlite3_value **argv
000957 ){
000958 DateTime x;
000959 if( isDate(context, argc, argv, &x)==0 ){
000960 char zBuf[100];
000961 computeHMS(&x);
000962 sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
000963 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
000964 }
000965 }
000966
000967 /*
000968 ** date( TIMESTRING, MOD, MOD, ...)
000969 **
000970 ** Return YYYY-MM-DD
000971 */
000972 static void dateFunc(
000973 sqlite3_context *context,
000974 int argc,
000975 sqlite3_value **argv
000976 ){
000977 DateTime x;
000978 if( isDate(context, argc, argv, &x)==0 ){
000979 char zBuf[100];
000980 computeYMD(&x);
000981 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
000982 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
000983 }
000984 }
000985
000986 /*
000987 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
000988 **
000989 ** Return a string described by FORMAT. Conversions as follows:
000990 **
000991 ** %d day of month
000992 ** %f ** fractional seconds SS.SSS
000993 ** %H hour 00-24
000994 ** %j day of year 000-366
000995 ** %J ** julian day number
000996 ** %m month 01-12
000997 ** %M minute 00-59
000998 ** %s seconds since 1970-01-01
000999 ** %S seconds 00-59
001000 ** %w day of week 0-6 sunday==0
001001 ** %W week of year 00-53
001002 ** %Y year 0000-9999
001003 ** %% %
001004 */
001005 static void strftimeFunc(
001006 sqlite3_context *context,
001007 int argc,
001008 sqlite3_value **argv
001009 ){
001010 DateTime x;
001011 u64 n;
001012 size_t i,j;
001013 char *z;
001014 sqlite3 *db;
001015 const char *zFmt;
001016 char zBuf[100];
001017 if( argc==0 ) return;
001018 zFmt = (const char*)sqlite3_value_text(argv[0]);
001019 if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
001020 db = sqlite3_context_db_handle(context);
001021 for(i=0, n=1; zFmt[i]; i++, n++){
001022 if( zFmt[i]=='%' ){
001023 switch( zFmt[i+1] ){
001024 case 'd':
001025 case 'H':
001026 case 'm':
001027 case 'M':
001028 case 'S':
001029 case 'W':
001030 n++;
001031 /* fall thru */
001032 case 'w':
001033 case '%':
001034 break;
001035 case 'f':
001036 n += 8;
001037 break;
001038 case 'j':
001039 n += 3;
001040 break;
001041 case 'Y':
001042 n += 8;
001043 break;
001044 case 's':
001045 case 'J':
001046 n += 50;
001047 break;
001048 default:
001049 return; /* ERROR. return a NULL */
001050 }
001051 i++;
001052 }
001053 }
001054 testcase( n==sizeof(zBuf)-1 );
001055 testcase( n==sizeof(zBuf) );
001056 testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH]+1 );
001057 testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH] );
001058 if( n<sizeof(zBuf) ){
001059 z = zBuf;
001060 }else if( n>(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ){
001061 sqlite3_result_error_toobig(context);
001062 return;
001063 }else{
001064 z = sqlite3DbMallocRawNN(db, (int)n);
001065 if( z==0 ){
001066 sqlite3_result_error_nomem(context);
001067 return;
001068 }
001069 }
001070 computeJD(&x);
001071 computeYMD_HMS(&x);
001072 for(i=j=0; zFmt[i]; i++){
001073 if( zFmt[i]!='%' ){
001074 z[j++] = zFmt[i];
001075 }else{
001076 i++;
001077 switch( zFmt[i] ){
001078 case 'd': sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break;
001079 case 'f': {
001080 double s = x.s;
001081 if( s>59.999 ) s = 59.999;
001082 sqlite3_snprintf(7, &z[j],"%06.3f", s);
001083 j += sqlite3Strlen30(&z[j]);
001084 break;
001085 }
001086 case 'H': sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break;
001087 case 'W': /* Fall thru */
001088 case 'j': {
001089 int nDay; /* Number of days since 1st day of year */
001090 DateTime y = x;
001091 y.validJD = 0;
001092 y.M = 1;
001093 y.D = 1;
001094 computeJD(&y);
001095 nDay = (int)((x.iJD-y.iJD+43200000)/86400000);
001096 if( zFmt[i]=='W' ){
001097 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */
001098 wd = (int)(((x.iJD+43200000)/86400000)%7);
001099 sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);
001100 j += 2;
001101 }else{
001102 sqlite3_snprintf(4, &z[j],"%03d",nDay+1);
001103 j += 3;
001104 }
001105 break;
001106 }
001107 case 'J': {
001108 sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0);
001109 j+=sqlite3Strlen30(&z[j]);
001110 break;
001111 }
001112 case 'm': sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;
001113 case 'M': sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;
001114 case 's': {
001115 sqlite3_snprintf(30,&z[j],"%lld",
001116 (i64)(x.iJD/1000 - 21086676*(i64)10000));
001117 j += sqlite3Strlen30(&z[j]);
001118 break;
001119 }
001120 case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
001121 case 'w': {
001122 z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
001123 break;
001124 }
001125 case 'Y': {
001126 sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
001127 break;
001128 }
001129 default: z[j++] = '%'; break;
001130 }
001131 }
001132 }
001133 z[j] = 0;
001134 sqlite3_result_text(context, z, -1,
001135 z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC);
001136 }
001137
001138 /*
001139 ** current_time()
001140 **
001141 ** This function returns the same value as time('now').
001142 */
001143 static void ctimeFunc(
001144 sqlite3_context *context,
001145 int NotUsed,
001146 sqlite3_value **NotUsed2
001147 ){
001148 UNUSED_PARAMETER2(NotUsed, NotUsed2);
001149 timeFunc(context, 0, 0);
001150 }
001151
001152 /*
001153 ** current_date()
001154 **
001155 ** This function returns the same value as date('now').
001156 */
001157 static void cdateFunc(
001158 sqlite3_context *context,
001159 int NotUsed,
001160 sqlite3_value **NotUsed2
001161 ){
001162 UNUSED_PARAMETER2(NotUsed, NotUsed2);
001163 dateFunc(context, 0, 0);
001164 }
001165
001166 /*
001167 ** current_timestamp()
001168 **
001169 ** This function returns the same value as datetime('now').
001170 */
001171 static void ctimestampFunc(
001172 sqlite3_context *context,
001173 int NotUsed,
001174 sqlite3_value **NotUsed2
001175 ){
001176 UNUSED_PARAMETER2(NotUsed, NotUsed2);
001177 datetimeFunc(context, 0, 0);
001178 }
001179 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
001180
001181 #ifdef SQLITE_OMIT_DATETIME_FUNCS
001182 /*
001183 ** If the library is compiled to omit the full-scale date and time
001184 ** handling (to get a smaller binary), the following minimal version
001185 ** of the functions current_time(), current_date() and current_timestamp()
001186 ** are included instead. This is to support column declarations that
001187 ** include "DEFAULT CURRENT_TIME" etc.
001188 **
001189 ** This function uses the C-library functions time(), gmtime()
001190 ** and strftime(). The format string to pass to strftime() is supplied
001191 ** as the user-data for the function.
001192 */
001193 static void currentTimeFunc(
001194 sqlite3_context *context,
001195 int argc,
001196 sqlite3_value **argv
001197 ){
001198 time_t t;
001199 char *zFormat = (char *)sqlite3_user_data(context);
001200 sqlite3_int64 iT;
001201 struct tm *pTm;
001202 struct tm sNow;
001203 char zBuf[20];
001204
001205 UNUSED_PARAMETER(argc);
001206 UNUSED_PARAMETER(argv);
001207
001208 iT = sqlite3StmtCurrentTime(context);
001209 if( iT<=0 ) return;
001210 t = iT/1000 - 10000*(sqlite3_int64)21086676;
001211 #if HAVE_GMTIME_R
001212 pTm = gmtime_r(&t, &sNow);
001213 #else
001214 sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
001215 pTm = gmtime(&t);
001216 if( pTm ) memcpy(&sNow, pTm, sizeof(sNow));
001217 sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
001218 #endif
001219 if( pTm ){
001220 strftime(zBuf, 20, zFormat, &sNow);
001221 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
001222 }
001223 }
001224 #endif
001225
001226 /*
001227 ** This function registered all of the above C functions as SQL
001228 ** functions. This should be the only routine in this file with
001229 ** external linkage.
001230 */
001231 void sqlite3RegisterDateTimeFunctions(void){
001232 static FuncDef aDateTimeFuncs[] = {
001233 #ifndef SQLITE_OMIT_DATETIME_FUNCS
001234 PURE_DATE(julianday, -1, 0, 0, juliandayFunc ),
001235 PURE_DATE(date, -1, 0, 0, dateFunc ),
001236 PURE_DATE(time, -1, 0, 0, timeFunc ),
001237 PURE_DATE(datetime, -1, 0, 0, datetimeFunc ),
001238 PURE_DATE(strftime, -1, 0, 0, strftimeFunc ),
001239 DFUNCTION(current_time, 0, 0, 0, ctimeFunc ),
001240 DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
001241 DFUNCTION(current_date, 0, 0, 0, cdateFunc ),
001242 #else
001243 STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc),
001244 STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc),
001245 STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
001246 #endif
001247 };
001248 sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
001249 }