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