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 char useSubsec; /* Display subsecond precision */
000081 };
000082
000083
000084 /*
000085 ** Convert zDate into one or more integers according to the conversion
000086 ** specifier zFormat.
000087 **
000088 ** zFormat[] contains 4 characters for each integer converted, except for
000089 ** the last integer which is specified by three characters. The meaning
000090 ** of a four-character format specifiers ABCD is:
000091 **
000092 ** A: number of digits to convert. Always "2" or "4".
000093 ** B: minimum value. Always "0" or "1".
000094 ** C: maximum value, decoded as:
000095 ** a: 12
000096 ** b: 14
000097 ** c: 24
000098 ** d: 31
000099 ** e: 59
000100 ** f: 9999
000101 ** D: the separator character, or \000 to indicate this is the
000102 ** last number to convert.
000103 **
000104 ** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would
000105 ** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-".
000106 ** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates
000107 ** the 2-digit day which is the last integer in the set.
000108 **
000109 ** The function returns the number of successful conversions.
000110 */
000111 static int getDigits(const char *zDate, const char *zFormat, ...){
000112 /* The aMx[] array translates the 3rd character of each format
000113 ** spec into a max size: a b c d e f */
000114 static const u16 aMx[] = { 12, 14, 24, 31, 59, 14712 };
000115 va_list ap;
000116 int cnt = 0;
000117 char nextC;
000118 va_start(ap, zFormat);
000119 do{
000120 char N = zFormat[0] - '0';
000121 char min = zFormat[1] - '0';
000122 int val = 0;
000123 u16 max;
000124
000125 assert( zFormat[2]>='a' && zFormat[2]<='f' );
000126 max = aMx[zFormat[2] - 'a'];
000127 nextC = zFormat[3];
000128 val = 0;
000129 while( N-- ){
000130 if( !sqlite3Isdigit(*zDate) ){
000131 goto end_getDigits;
000132 }
000133 val = val*10 + *zDate - '0';
000134 zDate++;
000135 }
000136 if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){
000137 goto end_getDigits;
000138 }
000139 *va_arg(ap,int*) = val;
000140 zDate++;
000141 cnt++;
000142 zFormat += 4;
000143 }while( nextC );
000144 end_getDigits:
000145 va_end(ap);
000146 return cnt;
000147 }
000148
000149 /*
000150 ** Parse a timezone extension on the end of a date-time.
000151 ** The extension is of the form:
000152 **
000153 ** (+/-)HH:MM
000154 **
000155 ** Or the "zulu" notation:
000156 **
000157 ** Z
000158 **
000159 ** If the parse is successful, write the number of minutes
000160 ** of change in p->tz and return 0. If a parser error occurs,
000161 ** return non-zero.
000162 **
000163 ** A missing specifier is not considered an error.
000164 */
000165 static int parseTimezone(const char *zDate, DateTime *p){
000166 int sgn = 0;
000167 int nHr, nMn;
000168 int c;
000169 while( sqlite3Isspace(*zDate) ){ zDate++; }
000170 p->tz = 0;
000171 c = *zDate;
000172 if( c=='-' ){
000173 sgn = -1;
000174 }else if( c=='+' ){
000175 sgn = +1;
000176 }else if( c=='Z' || c=='z' ){
000177 zDate++;
000178 goto zulu_time;
000179 }else{
000180 return c!=0;
000181 }
000182 zDate++;
000183 if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){
000184 return 1;
000185 }
000186 zDate += 5;
000187 p->tz = sgn*(nMn + nHr*60);
000188 zulu_time:
000189 while( sqlite3Isspace(*zDate) ){ zDate++; }
000190 p->tzSet = 1;
000191 return *zDate!=0;
000192 }
000193
000194 /*
000195 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
000196 ** The HH, MM, and SS must each be exactly 2 digits. The
000197 ** fractional seconds FFFF can be one or more digits.
000198 **
000199 ** Return 1 if there is a parsing error and 0 on success.
000200 */
000201 static int parseHhMmSs(const char *zDate, DateTime *p){
000202 int h, m, s;
000203 double ms = 0.0;
000204 if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){
000205 return 1;
000206 }
000207 zDate += 5;
000208 if( *zDate==':' ){
000209 zDate++;
000210 if( getDigits(zDate, "20e", &s)!=1 ){
000211 return 1;
000212 }
000213 zDate += 2;
000214 if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){
000215 double rScale = 1.0;
000216 zDate++;
000217 while( sqlite3Isdigit(*zDate) ){
000218 ms = ms*10.0 + *zDate - '0';
000219 rScale *= 10.0;
000220 zDate++;
000221 }
000222 ms /= rScale;
000223 }
000224 }else{
000225 s = 0;
000226 }
000227 p->validJD = 0;
000228 p->rawS = 0;
000229 p->validHMS = 1;
000230 p->h = h;
000231 p->m = m;
000232 p->s = s + ms;
000233 if( parseTimezone(zDate, p) ) return 1;
000234 p->validTZ = (p->tz!=0)?1:0;
000235 return 0;
000236 }
000237
000238 /*
000239 ** Put the DateTime object into its error state.
000240 */
000241 static void datetimeError(DateTime *p){
000242 memset(p, 0, sizeof(*p));
000243 p->isError = 1;
000244 }
000245
000246 /*
000247 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume
000248 ** that the YYYY-MM-DD is according to the Gregorian calendar.
000249 **
000250 ** Reference: Meeus page 61
000251 */
000252 static void computeJD(DateTime *p){
000253 int Y, M, D, A, B, X1, X2;
000254
000255 if( p->validJD ) return;
000256 if( p->validYMD ){
000257 Y = p->Y;
000258 M = p->M;
000259 D = p->D;
000260 }else{
000261 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */
000262 M = 1;
000263 D = 1;
000264 }
000265 if( Y<-4713 || Y>9999 || p->rawS ){
000266 datetimeError(p);
000267 return;
000268 }
000269 if( M<=2 ){
000270 Y--;
000271 M += 12;
000272 }
000273 A = Y/100;
000274 B = 2 - A + (A/4);
000275 X1 = 36525*(Y+4716)/100;
000276 X2 = 306001*(M+1)/10000;
000277 p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
000278 p->validJD = 1;
000279 if( p->validHMS ){
000280 p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000 + 0.5);
000281 if( p->validTZ ){
000282 p->iJD -= p->tz*60000;
000283 p->validYMD = 0;
000284 p->validHMS = 0;
000285 p->validTZ = 0;
000286 }
000287 }
000288 }
000289
000290 /*
000291 ** Parse dates of the form
000292 **
000293 ** YYYY-MM-DD HH:MM:SS.FFF
000294 ** YYYY-MM-DD HH:MM:SS
000295 ** YYYY-MM-DD HH:MM
000296 ** YYYY-MM-DD
000297 **
000298 ** Write the result into the DateTime structure and return 0
000299 ** on success and 1 if the input string is not a well-formed
000300 ** date.
000301 */
000302 static int parseYyyyMmDd(const char *zDate, DateTime *p){
000303 int Y, M, D, neg;
000304
000305 if( zDate[0]=='-' ){
000306 zDate++;
000307 neg = 1;
000308 }else{
000309 neg = 0;
000310 }
000311 if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){
000312 return 1;
000313 }
000314 zDate += 10;
000315 while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }
000316 if( parseHhMmSs(zDate, p)==0 ){
000317 /* We got the time */
000318 }else if( *zDate==0 ){
000319 p->validHMS = 0;
000320 }else{
000321 return 1;
000322 }
000323 p->validJD = 0;
000324 p->validYMD = 1;
000325 p->Y = neg ? -Y : Y;
000326 p->M = M;
000327 p->D = D;
000328 if( p->validTZ ){
000329 computeJD(p);
000330 }
000331 return 0;
000332 }
000333
000334 /*
000335 ** Set the time to the current time reported by the VFS.
000336 **
000337 ** Return the number of errors.
000338 */
000339 static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
000340 p->iJD = sqlite3StmtCurrentTime(context);
000341 if( p->iJD>0 ){
000342 p->validJD = 1;
000343 return 0;
000344 }else{
000345 return 1;
000346 }
000347 }
000348
000349 /*
000350 ** Input "r" is a numeric quantity which might be a julian day number,
000351 ** or the number of seconds since 1970. If the value if r is within
000352 ** range of a julian day number, install it as such and set validJD.
000353 ** If the value is a valid unix timestamp, put it in p->s and set p->rawS.
000354 */
000355 static void setRawDateNumber(DateTime *p, double r){
000356 p->s = r;
000357 p->rawS = 1;
000358 if( r>=0.0 && r<5373484.5 ){
000359 p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
000360 p->validJD = 1;
000361 }
000362 }
000363
000364 /*
000365 ** Attempt to parse the given string into a julian day number. Return
000366 ** the number of errors.
000367 **
000368 ** The following are acceptable forms for the input string:
000369 **
000370 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM
000371 ** DDDD.DD
000372 ** now
000373 **
000374 ** In the first form, the +/-HH:MM is always optional. The fractional
000375 ** seconds extension (the ".FFF") is optional. The seconds portion
000376 ** (":SS.FFF") is option. The year and date can be omitted as long
000377 ** as there is a time string. The time string can be omitted as long
000378 ** as there is a year and date.
000379 */
000380 static int parseDateOrTime(
000381 sqlite3_context *context,
000382 const char *zDate,
000383 DateTime *p
000384 ){
000385 double r;
000386 if( parseYyyyMmDd(zDate,p)==0 ){
000387 return 0;
000388 }else if( parseHhMmSs(zDate, p)==0 ){
000389 return 0;
000390 }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
000391 return setDateTimeToCurrent(context, p);
000392 }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){
000393 setRawDateNumber(p, r);
000394 return 0;
000395 }else if( (sqlite3StrICmp(zDate,"subsec")==0
000396 || sqlite3StrICmp(zDate,"subsecond")==0)
000397 && sqlite3NotPureFunc(context) ){
000398 p->useSubsec = 1;
000399 return setDateTimeToCurrent(context, p);
000400 }
000401 return 1;
000402 }
000403
000404 /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999.
000405 ** Multiplying this by 86400000 gives 464269060799999 as the maximum value
000406 ** for DateTime.iJD.
000407 **
000408 ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with
000409 ** such a large integer literal, so we have to encode it.
000410 */
000411 #define INT_464269060799999 ((((i64)0x1a640)<<32)|0x1072fdff)
000412
000413 /*
000414 ** Return TRUE if the given julian day number is within range.
000415 **
000416 ** The input is the JulianDay times 86400000.
000417 */
000418 static int validJulianDay(sqlite3_int64 iJD){
000419 return iJD>=0 && iJD<=INT_464269060799999;
000420 }
000421
000422 /*
000423 ** Compute the Year, Month, and Day from the julian day number.
000424 */
000425 static void computeYMD(DateTime *p){
000426 int Z, A, B, C, D, E, X1;
000427 if( p->validYMD ) return;
000428 if( !p->validJD ){
000429 p->Y = 2000;
000430 p->M = 1;
000431 p->D = 1;
000432 }else if( !validJulianDay(p->iJD) ){
000433 datetimeError(p);
000434 return;
000435 }else{
000436 Z = (int)((p->iJD + 43200000)/86400000);
000437 A = (int)((Z - 1867216.25)/36524.25);
000438 A = Z + 1 + A - (A/4);
000439 B = A + 1524;
000440 C = (int)((B - 122.1)/365.25);
000441 D = (36525*(C&32767))/100;
000442 E = (int)((B-D)/30.6001);
000443 X1 = (int)(30.6001*E);
000444 p->D = B - D - X1;
000445 p->M = E<14 ? E-1 : E-13;
000446 p->Y = p->M>2 ? C - 4716 : C - 4715;
000447 }
000448 p->validYMD = 1;
000449 }
000450
000451 /*
000452 ** Compute the Hour, Minute, and Seconds from the julian day number.
000453 */
000454 static void computeHMS(DateTime *p){
000455 int day_ms, day_min; /* milliseconds, minutes into the day */
000456 if( p->validHMS ) return;
000457 computeJD(p);
000458 day_ms = (int)((p->iJD + 43200000) % 86400000);
000459 p->s = (day_ms % 60000)/1000.0;
000460 day_min = day_ms/60000;
000461 p->m = day_min % 60;
000462 p->h = day_min / 60;
000463 p->rawS = 0;
000464 p->validHMS = 1;
000465 }
000466
000467 /*
000468 ** Compute both YMD and HMS
000469 */
000470 static void computeYMD_HMS(DateTime *p){
000471 computeYMD(p);
000472 computeHMS(p);
000473 }
000474
000475 /*
000476 ** Clear the YMD and HMS and the TZ
000477 */
000478 static void clearYMD_HMS_TZ(DateTime *p){
000479 p->validYMD = 0;
000480 p->validHMS = 0;
000481 p->validTZ = 0;
000482 }
000483
000484 #ifndef SQLITE_OMIT_LOCALTIME
000485 /*
000486 ** On recent Windows platforms, the localtime_s() function is available
000487 ** as part of the "Secure CRT". It is essentially equivalent to
000488 ** localtime_r() available under most POSIX platforms, except that the
000489 ** order of the parameters is reversed.
000490 **
000491 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
000492 **
000493 ** If the user has not indicated to use localtime_r() or localtime_s()
000494 ** already, check for an MSVC build environment that provides
000495 ** localtime_s().
000496 */
000497 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \
000498 && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
000499 #undef HAVE_LOCALTIME_S
000500 #define HAVE_LOCALTIME_S 1
000501 #endif
000502
000503 /*
000504 ** The following routine implements the rough equivalent of localtime_r()
000505 ** using whatever operating-system specific localtime facility that
000506 ** is available. This routine returns 0 on success and
000507 ** non-zero on any kind of error.
000508 **
000509 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is non-zero then this
000510 ** routine will always fail. If bLocaltimeFault is nonzero and
000511 ** sqlite3GlobalConfig.xAltLocaltime is not NULL, then xAltLocaltime() is
000512 ** invoked in place of the OS-defined localtime() function.
000513 **
000514 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
000515 ** library function localtime_r() is used to assist in the calculation of
000516 ** local time.
000517 */
000518 static int osLocaltime(time_t *t, struct tm *pTm){
000519 int rc;
000520 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S
000521 struct tm *pX;
000522 #if SQLITE_THREADSAFE>0
000523 sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN);
000524 #endif
000525 sqlite3_mutex_enter(mutex);
000526 pX = localtime(t);
000527 #ifndef SQLITE_UNTESTABLE
000528 if( sqlite3GlobalConfig.bLocaltimeFault ){
000529 if( sqlite3GlobalConfig.xAltLocaltime!=0
000530 && 0==sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm)
000531 ){
000532 pX = pTm;
000533 }else{
000534 pX = 0;
000535 }
000536 }
000537 #endif
000538 if( pX ) *pTm = *pX;
000539 #if SQLITE_THREADSAFE>0
000540 sqlite3_mutex_leave(mutex);
000541 #endif
000542 rc = pX==0;
000543 #else
000544 #ifndef SQLITE_UNTESTABLE
000545 if( sqlite3GlobalConfig.bLocaltimeFault ){
000546 if( sqlite3GlobalConfig.xAltLocaltime!=0 ){
000547 return sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm);
000548 }else{
000549 return 1;
000550 }
000551 }
000552 #endif
000553 #if HAVE_LOCALTIME_R
000554 rc = localtime_r(t, pTm)==0;
000555 #else
000556 rc = localtime_s(pTm, t);
000557 #endif /* HAVE_LOCALTIME_R */
000558 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
000559 return rc;
000560 }
000561 #endif /* SQLITE_OMIT_LOCALTIME */
000562
000563
000564 #ifndef SQLITE_OMIT_LOCALTIME
000565 /*
000566 ** Assuming the input DateTime is UTC, move it to its localtime equivalent.
000567 */
000568 static int toLocaltime(
000569 DateTime *p, /* Date at which to calculate offset */
000570 sqlite3_context *pCtx /* Write error here if one occurs */
000571 ){
000572 time_t t;
000573 struct tm sLocal;
000574 int iYearDiff;
000575
000576 /* Initialize the contents of sLocal to avoid a compiler warning. */
000577 memset(&sLocal, 0, sizeof(sLocal));
000578
000579 computeJD(p);
000580 if( p->iJD<2108667600*(i64)100000 /* 1970-01-01 */
000581 || p->iJD>2130141456*(i64)100000 /* 2038-01-18 */
000582 ){
000583 /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
000584 ** works for years between 1970 and 2037. For dates outside this range,
000585 ** SQLite attempts to map the year into an equivalent year within this
000586 ** range, do the calculation, then map the year back.
000587 */
000588 DateTime x = *p;
000589 computeYMD_HMS(&x);
000590 iYearDiff = (2000 + x.Y%4) - x.Y;
000591 x.Y += iYearDiff;
000592 x.validJD = 0;
000593 computeJD(&x);
000594 t = (time_t)(x.iJD/1000 - 21086676*(i64)10000);
000595 }else{
000596 iYearDiff = 0;
000597 t = (time_t)(p->iJD/1000 - 21086676*(i64)10000);
000598 }
000599 if( osLocaltime(&t, &sLocal) ){
000600 sqlite3_result_error(pCtx, "local time unavailable", -1);
000601 return SQLITE_ERROR;
000602 }
000603 p->Y = sLocal.tm_year + 1900 - iYearDiff;
000604 p->M = sLocal.tm_mon + 1;
000605 p->D = sLocal.tm_mday;
000606 p->h = sLocal.tm_hour;
000607 p->m = sLocal.tm_min;
000608 p->s = sLocal.tm_sec + (p->iJD%1000)*0.001;
000609 p->validYMD = 1;
000610 p->validHMS = 1;
000611 p->validJD = 0;
000612 p->rawS = 0;
000613 p->validTZ = 0;
000614 p->isError = 0;
000615 return SQLITE_OK;
000616 }
000617 #endif /* SQLITE_OMIT_LOCALTIME */
000618
000619 /*
000620 ** The following table defines various date transformations of the form
000621 **
000622 ** 'NNN days'
000623 **
000624 ** Where NNN is an arbitrary floating-point number and "days" can be one
000625 ** of several units of time.
000626 */
000627 static const struct {
000628 u8 nName; /* Length of the name */
000629 char zName[7]; /* Name of the transformation */
000630 float rLimit; /* Maximum NNN value for this transform */
000631 float rXform; /* Constant used for this transform */
000632 } aXformType[] = {
000633 { 6, "second", 4.6427e+14, 1.0 },
000634 { 6, "minute", 7.7379e+12, 60.0 },
000635 { 4, "hour", 1.2897e+11, 3600.0 },
000636 { 3, "day", 5373485.0, 86400.0 },
000637 { 5, "month", 176546.0, 2592000.0 },
000638 { 4, "year", 14713.0, 31536000.0 },
000639 };
000640
000641 /*
000642 ** If the DateTime p is raw number, try to figure out if it is
000643 ** a julian day number of a unix timestamp. Set the p value
000644 ** appropriately.
000645 */
000646 static void autoAdjustDate(DateTime *p){
000647 if( !p->rawS || p->validJD ){
000648 p->rawS = 0;
000649 }else if( p->s>=-21086676*(i64)10000 /* -4713-11-24 12:00:00 */
000650 && p->s<=(25340230*(i64)10000)+799 /* 9999-12-31 23:59:59 */
000651 ){
000652 double r = p->s*1000.0 + 210866760000000.0;
000653 clearYMD_HMS_TZ(p);
000654 p->iJD = (sqlite3_int64)(r + 0.5);
000655 p->validJD = 1;
000656 p->rawS = 0;
000657 }
000658 }
000659
000660 /*
000661 ** Process a modifier to a date-time stamp. The modifiers are
000662 ** as follows:
000663 **
000664 ** NNN days
000665 ** NNN hours
000666 ** NNN minutes
000667 ** NNN.NNNN seconds
000668 ** NNN months
000669 ** NNN years
000670 ** start of month
000671 ** start of year
000672 ** start of week
000673 ** start of day
000674 ** weekday N
000675 ** unixepoch
000676 ** localtime
000677 ** utc
000678 **
000679 ** Return 0 on success and 1 if there is any kind of error. If the error
000680 ** is in a system call (i.e. localtime()), then an error message is written
000681 ** to context pCtx. If the error is an unrecognized modifier, no error is
000682 ** written to pCtx.
000683 */
000684 static int parseModifier(
000685 sqlite3_context *pCtx, /* Function context */
000686 const char *z, /* The text of the modifier */
000687 int n, /* Length of zMod in bytes */
000688 DateTime *p, /* The date/time value to be modified */
000689 int idx /* Parameter index of the modifier */
000690 ){
000691 int rc = 1;
000692 double r;
000693 switch(sqlite3UpperToLower[(u8)z[0]] ){
000694 case 'a': {
000695 /*
000696 ** auto
000697 **
000698 ** If rawS is available, then interpret as a julian day number, or
000699 ** a unix timestamp, depending on its magnitude.
000700 */
000701 if( sqlite3_stricmp(z, "auto")==0 ){
000702 if( idx>1 ) return 1; /* IMP: R-33611-57934 */
000703 autoAdjustDate(p);
000704 rc = 0;
000705 }
000706 break;
000707 }
000708 case 'j': {
000709 /*
000710 ** julianday
000711 **
000712 ** Always interpret the prior number as a julian-day value. If this
000713 ** is not the first modifier, or if the prior argument is not a numeric
000714 ** value in the allowed range of julian day numbers understood by
000715 ** SQLite (0..5373484.5) then the result will be NULL.
000716 */
000717 if( sqlite3_stricmp(z, "julianday")==0 ){
000718 if( idx>1 ) return 1; /* IMP: R-31176-64601 */
000719 if( p->validJD && p->rawS ){
000720 rc = 0;
000721 p->rawS = 0;
000722 }
000723 }
000724 break;
000725 }
000726 #ifndef SQLITE_OMIT_LOCALTIME
000727 case 'l': {
000728 /* localtime
000729 **
000730 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
000731 ** show local time.
000732 */
000733 if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){
000734 rc = toLocaltime(p, pCtx);
000735 }
000736 break;
000737 }
000738 #endif
000739 case 'u': {
000740 /*
000741 ** unixepoch
000742 **
000743 ** Treat the current value of p->s as the number of
000744 ** seconds since 1970. Convert to a real julian day number.
000745 */
000746 if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){
000747 if( idx>1 ) return 1; /* IMP: R-49255-55373 */
000748 r = p->s*1000.0 + 210866760000000.0;
000749 if( r>=0.0 && r<464269060800000.0 ){
000750 clearYMD_HMS_TZ(p);
000751 p->iJD = (sqlite3_int64)(r + 0.5);
000752 p->validJD = 1;
000753 p->rawS = 0;
000754 rc = 0;
000755 }
000756 }
000757 #ifndef SQLITE_OMIT_LOCALTIME
000758 else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){
000759 if( p->tzSet==0 ){
000760 i64 iOrigJD; /* Original localtime */
000761 i64 iGuess; /* Guess at the corresponding utc time */
000762 int cnt = 0; /* Safety to prevent infinite loop */
000763 i64 iErr; /* Guess is off by this much */
000764
000765 computeJD(p);
000766 iGuess = iOrigJD = p->iJD;
000767 iErr = 0;
000768 do{
000769 DateTime new;
000770 memset(&new, 0, sizeof(new));
000771 iGuess -= iErr;
000772 new.iJD = iGuess;
000773 new.validJD = 1;
000774 rc = toLocaltime(&new, pCtx);
000775 if( rc ) return rc;
000776 computeJD(&new);
000777 iErr = new.iJD - iOrigJD;
000778 }while( iErr && cnt++<3 );
000779 memset(p, 0, sizeof(*p));
000780 p->iJD = iGuess;
000781 p->validJD = 1;
000782 p->tzSet = 1;
000783 }
000784 rc = SQLITE_OK;
000785 }
000786 #endif
000787 break;
000788 }
000789 case 'w': {
000790 /*
000791 ** weekday N
000792 **
000793 ** Move the date to the same time on the next occurrence of
000794 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the
000795 ** date is already on the appropriate weekday, this is a no-op.
000796 */
000797 if( sqlite3_strnicmp(z, "weekday ", 8)==0
000798 && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0
000799 && r>=0.0 && r<7.0 && (n=(int)r)==r ){
000800 sqlite3_int64 Z;
000801 computeYMD_HMS(p);
000802 p->validTZ = 0;
000803 p->validJD = 0;
000804 computeJD(p);
000805 Z = ((p->iJD + 129600000)/86400000) % 7;
000806 if( Z>n ) Z -= 7;
000807 p->iJD += (n - Z)*86400000;
000808 clearYMD_HMS_TZ(p);
000809 rc = 0;
000810 }
000811 break;
000812 }
000813 case 's': {
000814 /*
000815 ** start of TTTTT
000816 **
000817 ** Move the date backwards to the beginning of the current day,
000818 ** or month or year.
000819 **
000820 ** subsecond
000821 ** subsec
000822 **
000823 ** Show subsecond precision in the output of datetime() and
000824 ** unixepoch() and strftime('%s').
000825 */
000826 if( sqlite3_strnicmp(z, "start of ", 9)!=0 ){
000827 if( sqlite3_stricmp(z, "subsec")==0
000828 || sqlite3_stricmp(z, "subsecond")==0
000829 ){
000830 p->useSubsec = 1;
000831 rc = 0;
000832 }
000833 break;
000834 }
000835 if( !p->validJD && !p->validYMD && !p->validHMS ) break;
000836 z += 9;
000837 computeYMD(p);
000838 p->validHMS = 1;
000839 p->h = p->m = 0;
000840 p->s = 0.0;
000841 p->rawS = 0;
000842 p->validTZ = 0;
000843 p->validJD = 0;
000844 if( sqlite3_stricmp(z,"month")==0 ){
000845 p->D = 1;
000846 rc = 0;
000847 }else if( sqlite3_stricmp(z,"year")==0 ){
000848 p->M = 1;
000849 p->D = 1;
000850 rc = 0;
000851 }else if( sqlite3_stricmp(z,"day")==0 ){
000852 rc = 0;
000853 }
000854 break;
000855 }
000856 case '+':
000857 case '-':
000858 case '0':
000859 case '1':
000860 case '2':
000861 case '3':
000862 case '4':
000863 case '5':
000864 case '6':
000865 case '7':
000866 case '8':
000867 case '9': {
000868 double rRounder;
000869 int i;
000870 int Y,M,D,h,m,x;
000871 const char *z2 = z;
000872 char z0 = z[0];
000873 for(n=1; z[n]; n++){
000874 if( z[n]==':' ) break;
000875 if( sqlite3Isspace(z[n]) ) break;
000876 if( z[n]=='-' ){
000877 if( n==5 && getDigits(&z[1], "40f", &Y)==1 ) break;
000878 if( n==6 && getDigits(&z[1], "50f", &Y)==1 ) break;
000879 }
000880 }
000881 if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){
000882 assert( rc==1 );
000883 break;
000884 }
000885 if( z[n]=='-' ){
000886 /* A modifier of the form (+|-)YYYY-MM-DD adds or subtracts the
000887 ** specified number of years, months, and days. MM is limited to
000888 ** the range 0-11 and DD is limited to 0-30.
000889 */
000890 if( z0!='+' && z0!='-' ) break; /* Must start with +/- */
000891 if( n==5 ){
000892 if( getDigits(&z[1], "40f-20a-20d", &Y, &M, &D)!=3 ) break;
000893 }else{
000894 assert( n==6 );
000895 if( getDigits(&z[1], "50f-20a-20d", &Y, &M, &D)!=3 ) break;
000896 z++;
000897 }
000898 if( M>=12 ) break; /* M range 0..11 */
000899 if( D>=31 ) break; /* D range 0..30 */
000900 computeYMD_HMS(p);
000901 p->validJD = 0;
000902 if( z0=='-' ){
000903 p->Y -= Y;
000904 p->M -= M;
000905 D = -D;
000906 }else{
000907 p->Y += Y;
000908 p->M += M;
000909 }
000910 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
000911 p->Y += x;
000912 p->M -= x*12;
000913 computeJD(p);
000914 p->validHMS = 0;
000915 p->validYMD = 0;
000916 p->iJD += (i64)D*86400000;
000917 if( z[11]==0 ){
000918 rc = 0;
000919 break;
000920 }
000921 if( sqlite3Isspace(z[11])
000922 && getDigits(&z[12], "20c:20e", &h, &m)==2
000923 ){
000924 z2 = &z[12];
000925 n = 2;
000926 }else{
000927 break;
000928 }
000929 }
000930 if( z2[n]==':' ){
000931 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
000932 ** specified number of hours, minutes, seconds, and fractional seconds
000933 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be
000934 ** omitted.
000935 */
000936
000937 DateTime tx;
000938 sqlite3_int64 day;
000939 if( !sqlite3Isdigit(*z2) ) z2++;
000940 memset(&tx, 0, sizeof(tx));
000941 if( parseHhMmSs(z2, &tx) ) break;
000942 computeJD(&tx);
000943 tx.iJD -= 43200000;
000944 day = tx.iJD/86400000;
000945 tx.iJD -= day*86400000;
000946 if( z0=='-' ) tx.iJD = -tx.iJD;
000947 computeJD(p);
000948 clearYMD_HMS_TZ(p);
000949 p->iJD += tx.iJD;
000950 rc = 0;
000951 break;
000952 }
000953
000954 /* If control reaches this point, it means the transformation is
000955 ** one of the forms like "+NNN days". */
000956 z += n;
000957 while( sqlite3Isspace(*z) ) z++;
000958 n = sqlite3Strlen30(z);
000959 if( n>10 || n<3 ) break;
000960 if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
000961 computeJD(p);
000962 assert( rc==1 );
000963 rRounder = r<0 ? -0.5 : +0.5;
000964 for(i=0; i<ArraySize(aXformType); i++){
000965 if( aXformType[i].nName==n
000966 && sqlite3_strnicmp(aXformType[i].zName, z, n)==0
000967 && r>-aXformType[i].rLimit && r<aXformType[i].rLimit
000968 ){
000969 switch( i ){
000970 case 4: { /* Special processing to add months */
000971 assert( strcmp(aXformType[i].zName,"month")==0 );
000972 computeYMD_HMS(p);
000973 p->M += (int)r;
000974 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
000975 p->Y += x;
000976 p->M -= x*12;
000977 p->validJD = 0;
000978 r -= (int)r;
000979 break;
000980 }
000981 case 5: { /* Special processing to add years */
000982 int y = (int)r;
000983 assert( strcmp(aXformType[i].zName,"year")==0 );
000984 computeYMD_HMS(p);
000985 p->Y += y;
000986 p->validJD = 0;
000987 r -= (int)r;
000988 break;
000989 }
000990 }
000991 computeJD(p);
000992 p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder);
000993 rc = 0;
000994 break;
000995 }
000996 }
000997 clearYMD_HMS_TZ(p);
000998 break;
000999 }
001000 default: {
001001 break;
001002 }
001003 }
001004 return rc;
001005 }
001006
001007 /*
001008 ** Process time function arguments. argv[0] is a date-time stamp.
001009 ** argv[1] and following are modifiers. Parse them all and write
001010 ** the resulting time into the DateTime structure p. Return 0
001011 ** on success and 1 if there are any errors.
001012 **
001013 ** If there are zero parameters (if even argv[0] is undefined)
001014 ** then assume a default value of "now" for argv[0].
001015 */
001016 static int isDate(
001017 sqlite3_context *context,
001018 int argc,
001019 sqlite3_value **argv,
001020 DateTime *p
001021 ){
001022 int i, n;
001023 const unsigned char *z;
001024 int eType;
001025 memset(p, 0, sizeof(*p));
001026 if( argc==0 ){
001027 if( !sqlite3NotPureFunc(context) ) return 1;
001028 return setDateTimeToCurrent(context, p);
001029 }
001030 if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
001031 || eType==SQLITE_INTEGER ){
001032 setRawDateNumber(p, sqlite3_value_double(argv[0]));
001033 }else{
001034 z = sqlite3_value_text(argv[0]);
001035 if( !z || parseDateOrTime(context, (char*)z, p) ){
001036 return 1;
001037 }
001038 }
001039 for(i=1; i<argc; i++){
001040 z = sqlite3_value_text(argv[i]);
001041 n = sqlite3_value_bytes(argv[i]);
001042 if( z==0 || parseModifier(context, (char*)z, n, p, i) ) return 1;
001043 }
001044 computeJD(p);
001045 if( p->isError || !validJulianDay(p->iJD) ) return 1;
001046 return 0;
001047 }
001048
001049
001050 /*
001051 ** The following routines implement the various date and time functions
001052 ** of SQLite.
001053 */
001054
001055 /*
001056 ** julianday( TIMESTRING, MOD, MOD, ...)
001057 **
001058 ** Return the julian day number of the date specified in the arguments
001059 */
001060 static void juliandayFunc(
001061 sqlite3_context *context,
001062 int argc,
001063 sqlite3_value **argv
001064 ){
001065 DateTime x;
001066 if( isDate(context, argc, argv, &x)==0 ){
001067 computeJD(&x);
001068 sqlite3_result_double(context, x.iJD/86400000.0);
001069 }
001070 }
001071
001072 /*
001073 ** unixepoch( TIMESTRING, MOD, MOD, ...)
001074 **
001075 ** Return the number of seconds (including fractional seconds) since
001076 ** the unix epoch of 1970-01-01 00:00:00 GMT.
001077 */
001078 static void unixepochFunc(
001079 sqlite3_context *context,
001080 int argc,
001081 sqlite3_value **argv
001082 ){
001083 DateTime x;
001084 if( isDate(context, argc, argv, &x)==0 ){
001085 computeJD(&x);
001086 if( x.useSubsec ){
001087 sqlite3_result_double(context, (x.iJD - 21086676*(i64)10000000)/1000.0);
001088 }else{
001089 sqlite3_result_int64(context, x.iJD/1000 - 21086676*(i64)10000);
001090 }
001091 }
001092 }
001093
001094 /*
001095 ** datetime( TIMESTRING, MOD, MOD, ...)
001096 **
001097 ** Return YYYY-MM-DD HH:MM:SS
001098 */
001099 static void datetimeFunc(
001100 sqlite3_context *context,
001101 int argc,
001102 sqlite3_value **argv
001103 ){
001104 DateTime x;
001105 if( isDate(context, argc, argv, &x)==0 ){
001106 int Y, s, n;
001107 char zBuf[32];
001108 computeYMD_HMS(&x);
001109 Y = x.Y;
001110 if( Y<0 ) Y = -Y;
001111 zBuf[1] = '0' + (Y/1000)%10;
001112 zBuf[2] = '0' + (Y/100)%10;
001113 zBuf[3] = '0' + (Y/10)%10;
001114 zBuf[4] = '0' + (Y)%10;
001115 zBuf[5] = '-';
001116 zBuf[6] = '0' + (x.M/10)%10;
001117 zBuf[7] = '0' + (x.M)%10;
001118 zBuf[8] = '-';
001119 zBuf[9] = '0' + (x.D/10)%10;
001120 zBuf[10] = '0' + (x.D)%10;
001121 zBuf[11] = ' ';
001122 zBuf[12] = '0' + (x.h/10)%10;
001123 zBuf[13] = '0' + (x.h)%10;
001124 zBuf[14] = ':';
001125 zBuf[15] = '0' + (x.m/10)%10;
001126 zBuf[16] = '0' + (x.m)%10;
001127 zBuf[17] = ':';
001128 if( x.useSubsec ){
001129 s = (int)(1000.0*x.s + 0.5);
001130 zBuf[18] = '0' + (s/10000)%10;
001131 zBuf[19] = '0' + (s/1000)%10;
001132 zBuf[20] = '.';
001133 zBuf[21] = '0' + (s/100)%10;
001134 zBuf[22] = '0' + (s/10)%10;
001135 zBuf[23] = '0' + (s)%10;
001136 zBuf[24] = 0;
001137 n = 24;
001138 }else{
001139 s = (int)x.s;
001140 zBuf[18] = '0' + (s/10)%10;
001141 zBuf[19] = '0' + (s)%10;
001142 zBuf[20] = 0;
001143 n = 20;
001144 }
001145 if( x.Y<0 ){
001146 zBuf[0] = '-';
001147 sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT);
001148 }else{
001149 sqlite3_result_text(context, &zBuf[1], n-1, SQLITE_TRANSIENT);
001150 }
001151 }
001152 }
001153
001154 /*
001155 ** time( TIMESTRING, MOD, MOD, ...)
001156 **
001157 ** Return HH:MM:SS
001158 */
001159 static void timeFunc(
001160 sqlite3_context *context,
001161 int argc,
001162 sqlite3_value **argv
001163 ){
001164 DateTime x;
001165 if( isDate(context, argc, argv, &x)==0 ){
001166 int s, n;
001167 char zBuf[16];
001168 computeHMS(&x);
001169 zBuf[0] = '0' + (x.h/10)%10;
001170 zBuf[1] = '0' + (x.h)%10;
001171 zBuf[2] = ':';
001172 zBuf[3] = '0' + (x.m/10)%10;
001173 zBuf[4] = '0' + (x.m)%10;
001174 zBuf[5] = ':';
001175 if( x.useSubsec ){
001176 s = (int)(1000.0*x.s + 0.5);
001177 zBuf[6] = '0' + (s/10000)%10;
001178 zBuf[7] = '0' + (s/1000)%10;
001179 zBuf[8] = '.';
001180 zBuf[9] = '0' + (s/100)%10;
001181 zBuf[10] = '0' + (s/10)%10;
001182 zBuf[11] = '0' + (s)%10;
001183 zBuf[12] = 0;
001184 n = 12;
001185 }else{
001186 s = (int)x.s;
001187 zBuf[6] = '0' + (s/10)%10;
001188 zBuf[7] = '0' + (s)%10;
001189 zBuf[8] = 0;
001190 n = 8;
001191 }
001192 sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT);
001193 }
001194 }
001195
001196 /*
001197 ** date( TIMESTRING, MOD, MOD, ...)
001198 **
001199 ** Return YYYY-MM-DD
001200 */
001201 static void dateFunc(
001202 sqlite3_context *context,
001203 int argc,
001204 sqlite3_value **argv
001205 ){
001206 DateTime x;
001207 if( isDate(context, argc, argv, &x)==0 ){
001208 int Y;
001209 char zBuf[16];
001210 computeYMD(&x);
001211 Y = x.Y;
001212 if( Y<0 ) Y = -Y;
001213 zBuf[1] = '0' + (Y/1000)%10;
001214 zBuf[2] = '0' + (Y/100)%10;
001215 zBuf[3] = '0' + (Y/10)%10;
001216 zBuf[4] = '0' + (Y)%10;
001217 zBuf[5] = '-';
001218 zBuf[6] = '0' + (x.M/10)%10;
001219 zBuf[7] = '0' + (x.M)%10;
001220 zBuf[8] = '-';
001221 zBuf[9] = '0' + (x.D/10)%10;
001222 zBuf[10] = '0' + (x.D)%10;
001223 zBuf[11] = 0;
001224 if( x.Y<0 ){
001225 zBuf[0] = '-';
001226 sqlite3_result_text(context, zBuf, 11, SQLITE_TRANSIENT);
001227 }else{
001228 sqlite3_result_text(context, &zBuf[1], 10, SQLITE_TRANSIENT);
001229 }
001230 }
001231 }
001232
001233 /*
001234 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
001235 **
001236 ** Return a string described by FORMAT. Conversions as follows:
001237 **
001238 ** %d day of month
001239 ** %f ** fractional seconds SS.SSS
001240 ** %H hour 00-24
001241 ** %j day of year 000-366
001242 ** %J ** julian day number
001243 ** %m month 01-12
001244 ** %M minute 00-59
001245 ** %s seconds since 1970-01-01
001246 ** %S seconds 00-59
001247 ** %w day of week 0-6 Sunday==0
001248 ** %W week of year 00-53
001249 ** %Y year 0000-9999
001250 ** %% %
001251 */
001252 static void strftimeFunc(
001253 sqlite3_context *context,
001254 int argc,
001255 sqlite3_value **argv
001256 ){
001257 DateTime x;
001258 size_t i,j;
001259 sqlite3 *db;
001260 const char *zFmt;
001261 sqlite3_str sRes;
001262
001263
001264 if( argc==0 ) return;
001265 zFmt = (const char*)sqlite3_value_text(argv[0]);
001266 if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
001267 db = sqlite3_context_db_handle(context);
001268 sqlite3StrAccumInit(&sRes, 0, 0, 0, db->aLimit[SQLITE_LIMIT_LENGTH]);
001269
001270 computeJD(&x);
001271 computeYMD_HMS(&x);
001272 for(i=j=0; zFmt[i]; i++){
001273 if( zFmt[i]!='%' ) continue;
001274 if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j));
001275 i++;
001276 j = i + 1;
001277 switch( zFmt[i] ){
001278 case 'd': {
001279 sqlite3_str_appendf(&sRes, "%02d", x.D);
001280 break;
001281 }
001282 case 'f': {
001283 double s = x.s;
001284 if( s>59.999 ) s = 59.999;
001285 sqlite3_str_appendf(&sRes, "%06.3f", s);
001286 break;
001287 }
001288 case 'H': {
001289 sqlite3_str_appendf(&sRes, "%02d", x.h);
001290 break;
001291 }
001292 case 'W': /* Fall thru */
001293 case 'j': {
001294 int nDay; /* Number of days since 1st day of year */
001295 DateTime y = x;
001296 y.validJD = 0;
001297 y.M = 1;
001298 y.D = 1;
001299 computeJD(&y);
001300 nDay = (int)((x.iJD-y.iJD+43200000)/86400000);
001301 if( zFmt[i]=='W' ){
001302 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */
001303 wd = (int)(((x.iJD+43200000)/86400000)%7);
001304 sqlite3_str_appendf(&sRes,"%02d",(nDay+7-wd)/7);
001305 }else{
001306 sqlite3_str_appendf(&sRes,"%03d",nDay+1);
001307 }
001308 break;
001309 }
001310 case 'J': {
001311 sqlite3_str_appendf(&sRes,"%.16g",x.iJD/86400000.0);
001312 break;
001313 }
001314 case 'm': {
001315 sqlite3_str_appendf(&sRes,"%02d",x.M);
001316 break;
001317 }
001318 case 'M': {
001319 sqlite3_str_appendf(&sRes,"%02d",x.m);
001320 break;
001321 }
001322 case 's': {
001323 if( x.useSubsec ){
001324 sqlite3_str_appendf(&sRes,"%.3f",
001325 (x.iJD - 21086676*(i64)10000000)/1000.0);
001326 }else{
001327 i64 iS = (i64)(x.iJD/1000 - 21086676*(i64)10000);
001328 sqlite3_str_appendf(&sRes,"%lld",iS);
001329 }
001330 break;
001331 }
001332 case 'S': {
001333 sqlite3_str_appendf(&sRes,"%02d",(int)x.s);
001334 break;
001335 }
001336 case 'w': {
001337 sqlite3_str_appendchar(&sRes, 1,
001338 (char)(((x.iJD+129600000)/86400000) % 7) + '0');
001339 break;
001340 }
001341 case 'Y': {
001342 sqlite3_str_appendf(&sRes,"%04d",x.Y);
001343 break;
001344 }
001345 case '%': {
001346 sqlite3_str_appendchar(&sRes, 1, '%');
001347 break;
001348 }
001349 default: {
001350 sqlite3_str_reset(&sRes);
001351 return;
001352 }
001353 }
001354 }
001355 if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j));
001356 sqlite3ResultStrAccum(context, &sRes);
001357 }
001358
001359 /*
001360 ** current_time()
001361 **
001362 ** This function returns the same value as time('now').
001363 */
001364 static void ctimeFunc(
001365 sqlite3_context *context,
001366 int NotUsed,
001367 sqlite3_value **NotUsed2
001368 ){
001369 UNUSED_PARAMETER2(NotUsed, NotUsed2);
001370 timeFunc(context, 0, 0);
001371 }
001372
001373 /*
001374 ** current_date()
001375 **
001376 ** This function returns the same value as date('now').
001377 */
001378 static void cdateFunc(
001379 sqlite3_context *context,
001380 int NotUsed,
001381 sqlite3_value **NotUsed2
001382 ){
001383 UNUSED_PARAMETER2(NotUsed, NotUsed2);
001384 dateFunc(context, 0, 0);
001385 }
001386
001387 /*
001388 ** timediff(DATE1, DATE2)
001389 **
001390 ** Return the amount of time that must be added to DATE2 in order to
001391 ** convert it into DATE2. The time difference format is:
001392 **
001393 ** +YYYY-MM-DD HH:MM:SS.SSS
001394 **
001395 ** The initial "+" becomes "-" if DATE1 occurs before DATE2. For
001396 ** date/time values A and B, the following invariant should hold:
001397 **
001398 ** datetime(A) == (datetime(B, timediff(A,B))
001399 **
001400 ** Both DATE arguments must be either a julian day number, or an
001401 ** ISO-8601 string. The unix timestamps are not supported by this
001402 ** routine.
001403 */
001404 static void timediffFunc(
001405 sqlite3_context *context,
001406 int NotUsed1,
001407 sqlite3_value **argv
001408 ){
001409 char sign;
001410 int Y, M;
001411 DateTime d1, d2;
001412 sqlite3_str sRes;
001413 UNUSED_PARAMETER(NotUsed1);
001414 if( isDate(context, 1, &argv[0], &d1) ) return;
001415 if( isDate(context, 1, &argv[1], &d2) ) return;
001416 computeYMD_HMS(&d1);
001417 computeYMD_HMS(&d2);
001418 if( d1.iJD>=d2.iJD ){
001419 sign = '+';
001420 Y = d1.Y - d2.Y;
001421 if( Y ){
001422 d2.Y = d1.Y;
001423 d2.validJD = 0;
001424 computeJD(&d2);
001425 }
001426 M = d1.M - d2.M;
001427 if( M<0 ){
001428 Y--;
001429 M += 12;
001430 }
001431 if( M!=0 ){
001432 d2.M = d1.M;
001433 d2.validJD = 0;
001434 computeJD(&d2);
001435 }
001436 while( d1.iJD<d2.iJD ){
001437 M--;
001438 if( M<0 ){
001439 M = 11;
001440 Y--;
001441 }
001442 d2.M--;
001443 if( d2.M<1 ){
001444 d2.M = 12;
001445 d2.Y--;
001446 }
001447 d2.validJD = 0;
001448 computeJD(&d2);
001449 }
001450 d1.iJD -= d2.iJD;
001451 d1.iJD += (u64)1486995408 * (u64)100000;
001452 }else /* d1<d2 */{
001453 sign = '-';
001454 Y = d2.Y - d1.Y;
001455 if( Y ){
001456 d2.Y = d1.Y;
001457 d2.validJD = 0;
001458 computeJD(&d2);
001459 }
001460 M = d2.M - d1.M;
001461 if( M<0 ){
001462 Y--;
001463 M += 12;
001464 }
001465 if( M!=0 ){
001466 d2.M = d1.M;
001467 d2.validJD = 0;
001468 computeJD(&d2);
001469 }
001470 while( d1.iJD>d2.iJD ){
001471 M--;
001472 if( M<0 ){
001473 M = 11;
001474 Y--;
001475 }
001476 d2.M++;
001477 if( d2.M>12 ){
001478 d2.M = 1;
001479 d2.Y++;
001480 }
001481 d2.validJD = 0;
001482 computeJD(&d2);
001483 }
001484 d1.iJD = d2.iJD - d1.iJD;
001485 d1.iJD += (u64)1486995408 * (u64)100000;
001486 }
001487 d1.validYMD = 0;
001488 d1.validHMS = 0;
001489 d1.validTZ = 0;
001490 computeYMD_HMS(&d1);
001491 sqlite3StrAccumInit(&sRes, 0, 0, 0, 100);
001492 sqlite3_str_appendf(&sRes, "%c%04d-%02d-%02d %02d:%02d:%06.3f",
001493 sign, Y, M, d1.D-1, d1.h, d1.m, d1.s);
001494 sqlite3ResultStrAccum(context, &sRes);
001495 }
001496
001497
001498 /*
001499 ** current_timestamp()
001500 **
001501 ** This function returns the same value as datetime('now').
001502 */
001503 static void ctimestampFunc(
001504 sqlite3_context *context,
001505 int NotUsed,
001506 sqlite3_value **NotUsed2
001507 ){
001508 UNUSED_PARAMETER2(NotUsed, NotUsed2);
001509 datetimeFunc(context, 0, 0);
001510 }
001511 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
001512
001513 #ifdef SQLITE_OMIT_DATETIME_FUNCS
001514 /*
001515 ** If the library is compiled to omit the full-scale date and time
001516 ** handling (to get a smaller binary), the following minimal version
001517 ** of the functions current_time(), current_date() and current_timestamp()
001518 ** are included instead. This is to support column declarations that
001519 ** include "DEFAULT CURRENT_TIME" etc.
001520 **
001521 ** This function uses the C-library functions time(), gmtime()
001522 ** and strftime(). The format string to pass to strftime() is supplied
001523 ** as the user-data for the function.
001524 */
001525 static void currentTimeFunc(
001526 sqlite3_context *context,
001527 int argc,
001528 sqlite3_value **argv
001529 ){
001530 time_t t;
001531 char *zFormat = (char *)sqlite3_user_data(context);
001532 sqlite3_int64 iT;
001533 struct tm *pTm;
001534 struct tm sNow;
001535 char zBuf[20];
001536
001537 UNUSED_PARAMETER(argc);
001538 UNUSED_PARAMETER(argv);
001539
001540 iT = sqlite3StmtCurrentTime(context);
001541 if( iT<=0 ) return;
001542 t = iT/1000 - 10000*(sqlite3_int64)21086676;
001543 #if HAVE_GMTIME_R
001544 pTm = gmtime_r(&t, &sNow);
001545 #else
001546 sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN));
001547 pTm = gmtime(&t);
001548 if( pTm ) memcpy(&sNow, pTm, sizeof(sNow));
001549 sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN));
001550 #endif
001551 if( pTm ){
001552 strftime(zBuf, 20, zFormat, &sNow);
001553 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
001554 }
001555 }
001556 #endif
001557
001558 /*
001559 ** This function registered all of the above C functions as SQL
001560 ** functions. This should be the only routine in this file with
001561 ** external linkage.
001562 */
001563 void sqlite3RegisterDateTimeFunctions(void){
001564 static FuncDef aDateTimeFuncs[] = {
001565 #ifndef SQLITE_OMIT_DATETIME_FUNCS
001566 PURE_DATE(julianday, -1, 0, 0, juliandayFunc ),
001567 PURE_DATE(unixepoch, -1, 0, 0, unixepochFunc ),
001568 PURE_DATE(date, -1, 0, 0, dateFunc ),
001569 PURE_DATE(time, -1, 0, 0, timeFunc ),
001570 PURE_DATE(datetime, -1, 0, 0, datetimeFunc ),
001571 PURE_DATE(strftime, -1, 0, 0, strftimeFunc ),
001572 PURE_DATE(timediff, 2, 0, 0, timediffFunc ),
001573 DFUNCTION(current_time, 0, 0, 0, ctimeFunc ),
001574 DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
001575 DFUNCTION(current_date, 0, 0, 0, cdateFunc ),
001576 #else
001577 STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc),
001578 STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc),
001579 STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
001580 #endif
001581 };
001582 sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
001583 }