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