AryaWu/sqlite
0
1/*2** 2003 October 313**4** The author disclaims copyright to this source code. In place of5** a legal notice, here is a blessing:6**7** May you do good and not evil.8** May you find forgiveness for yourself and forgive others.9** May you share freely, never taking more than you give.10**11*************************************************************************12** This file contains the C functions that implement date and time13** functions for SQLite. 14**15** There is only one exported symbol in this file - the function16** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.17** All other code has file scope.18**19** SQLite processes all times and dates as julian day numbers. The20** dates and times are stored as the number of days since noon21** in Greenwich on November 24, 4714 B.C. according to the Gregorian22** calendar system. 23**24** 1970-01-01 00:00:00 is JD 2440587.525** 2000-01-01 00:00:00 is JD 2451544.526**27** This implementation requires years to be expressed as a 4-digit number28** which means that only dates between 0000-01-01 and 9999-12-31 can29** be represented, even though julian day numbers allow a much wider30** range of dates.31**32** The Gregorian calendar system is used for all dates and times,33** even those that predate the Gregorian calendar. Historians usually34** use the julian calendar for dates prior to 1582-10-15 and for some35** dates afterwards, depending on locale. Beware of this difference.36**37** The conversion algorithms are implemented based on descriptions38** in the following text:39**40** Jean Meeus41** Astronomical Algorithms, 2nd Edition, 199842** ISBN 0-943396-61-143** Willmann-Bell, Inc44** Richmond, Virginia (USA)45*/46#include "sqliteInt.h"47#include <stdlib.h>48#include <assert.h>49#include <time.h>50 51#ifndef SQLITE_OMIT_DATETIME_FUNCS52 53/*54** The MSVC CRT on Windows CE may not have a localtime() function.55** So declare a substitute. The substitute function itself is56** defined in "os_win.c".57*/58#if !defined(SQLITE_OMIT_LOCALTIME) && defined(_WIN32_WCE) && \59 (!defined(SQLITE_MSVC_LOCALTIME_API) || !SQLITE_MSVC_LOCALTIME_API)60struct tm *__cdecl localtime(const time_t *);61#endif62 63/*64** A structure for holding a single date and time.65*/66typedef struct DateTime DateTime;67struct DateTime {68 sqlite3_int64 iJD; /* The julian day number times 86400000 */69 int Y, M, D; /* Year, month, and day */70 int h, m; /* Hour and minutes */71 int tz; /* Timezone offset in minutes */72 double s; /* Seconds */73 char validJD; /* True (1) if iJD is valid */74 char validYMD; /* True (1) if Y,M,D are valid */75 char validHMS; /* True (1) if h,m,s are valid */76 char nFloor; /* Days to implement "floor" */77 unsigned rawS : 1; /* Raw numeric value stored in s */78 unsigned isError : 1; /* An overflow has occurred */79 unsigned useSubsec : 1; /* Display subsecond precision */80 unsigned isUtc : 1; /* Time is known to be UTC */81 unsigned isLocal : 1; /* Time is known to be localtime */82};83 84 85/*86** Convert zDate into one or more integers according to the conversion87** specifier zFormat.88**89** zFormat[] contains 4 characters for each integer converted, except for90** the last integer which is specified by three characters. The meaning91** of a four-character format specifiers ABCD is:92**93** A: number of digits to convert. Always "2" or "4".94** B: minimum value. Always "0" or "1".95** C: maximum value, decoded as:96** a: 1297** b: 1498** c: 2499** d: 31100** e: 59101** f: 9999102** D: the separator character, or \000 to indicate this is the103** last number to convert.104**105** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would106** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-".107** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates108** the 2-digit day which is the last integer in the set.109**110** The function returns the number of successful conversions.111*/112static int getDigits(const char *zDate, const char *zFormat, ...){113 /* The aMx[] array translates the 3rd character of each format114 ** spec into a max size: a b c d e f */115 static const u16 aMx[] = { 12, 14, 24, 31, 59, 14712 };116 va_list ap;117 int cnt = 0;118 char nextC;119 va_start(ap, zFormat);120 do{121 char N = zFormat[0] - '0';122 char min = zFormat[1] - '0';123 int val = 0;124 u16 max;125 126 assert( zFormat[2]>='a' && zFormat[2]<='f' );127 max = aMx[zFormat[2] - 'a'];128 nextC = zFormat[3];129 val = 0;130 while( N-- ){131 if( !sqlite3Isdigit(*zDate) ){132 goto end_getDigits;133 }134 val = val*10 + *zDate - '0';135 zDate++;136 }137 if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){138 goto end_getDigits;139 }140 *va_arg(ap,int*) = val;141 zDate++;142 cnt++;143 zFormat += 4;144 }while( nextC );145end_getDigits:146 va_end(ap);147 return cnt;148}149 150/*151** Parse a timezone extension on the end of a date-time.152** The extension is of the form:153**154** (+/-)HH:MM155**156** Or the "zulu" notation:157**158** Z159**160** If the parse is successful, write the number of minutes161** of change in p->tz and return 0. If a parser error occurs,162** return non-zero.163**164** A missing specifier is not considered an error.165*/166static int parseTimezone(const char *zDate, DateTime *p){167 int sgn = 0;168 int nHr, nMn;169 int c;170 while( sqlite3Isspace(*zDate) ){ zDate++; }171 p->tz = 0;172 c = *zDate;173 if( c=='-' ){174 sgn = -1;175 }else if( c=='+' ){176 sgn = +1;177 }else if( c=='Z' || c=='z' ){178 zDate++;179 p->isLocal = 0;180 p->isUtc = 1;181 goto zulu_time;182 }else{183 return c!=0;184 }185 zDate++;186 if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){187 return 1;188 }189 zDate += 5;190 p->tz = sgn*(nMn + nHr*60);191 if( p->tz==0 ){ /* Forum post 2025-09-17T10:12:14z */192 p->isLocal = 0;193 p->isUtc = 1;194 }195zulu_time:196 while( sqlite3Isspace(*zDate) ){ zDate++; }197 return *zDate!=0;198}199 200/*201** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.202** The HH, MM, and SS must each be exactly 2 digits. The203** fractional seconds FFFF can be one or more digits.204**205** Return 1 if there is a parsing error and 0 on success.206*/207static int parseHhMmSs(const char *zDate, DateTime *p){208 int h, m, s;209 double ms = 0.0;210 if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){211 return 1;212 }213 zDate += 5;214 if( *zDate==':' ){215 zDate++;216 if( getDigits(zDate, "20e", &s)!=1 ){217 return 1;218 }219 zDate += 2;220 if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){221 double rScale = 1.0;222 zDate++;223 while( sqlite3Isdigit(*zDate) ){224 ms = ms*10.0 + *zDate - '0';225 rScale *= 10.0;226 zDate++;227 }228 ms /= rScale;229 /* Truncate to avoid problems with sub-milliseconds230 ** rounding. https://sqlite.org/forum/forumpost/766a2c9231 */231 if( ms>0.999 ) ms = 0.999;232 }233 }else{234 s = 0;235 }236 p->validJD = 0;237 p->rawS = 0;238 p->validHMS = 1;239 p->h = h;240 p->m = m;241 p->s = s + ms;242 if( parseTimezone(zDate, p) ) return 1;243 return 0;244}245 246/*247** Put the DateTime object into its error state.248*/249static void datetimeError(DateTime *p){250 memset(p, 0, sizeof(*p));251 p->isError = 1;252}253 254/*255** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume256** that the YYYY-MM-DD is according to the Gregorian calendar.257**258** Reference: Meeus page 61259*/260static void computeJD(DateTime *p){261 int Y, M, D, A, B, X1, X2;262 263 if( p->validJD ) return;264 if( p->validYMD ){265 Y = p->Y;266 M = p->M;267 D = p->D;268 }else{269 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */270 M = 1;271 D = 1;272 }273 if( Y<-4713 || Y>9999 || p->rawS ){274 datetimeError(p);275 return;276 }277 if( M<=2 ){278 Y--;279 M += 12;280 }281 A = (Y+4800)/100;282 B = 38 - A + (A/4);283 X1 = 36525*(Y+4716)/100;284 X2 = 306001*(M+1)/10000;285 p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);286 p->validJD = 1;287 if( p->validHMS ){288 p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000 + 0.5);289 if( p->tz ){290 p->iJD -= p->tz*60000;291 p->validYMD = 0;292 p->validHMS = 0;293 p->tz = 0;294 p->isUtc = 1;295 p->isLocal = 0;296 }297 }298}299 300/*301** Given the YYYY-MM-DD information current in p, determine if there302** is day-of-month overflow and set nFloor to the number of days that303** would need to be subtracted from the date in order to bring the304** date back to the end of the month.305*/306static void computeFloor(DateTime *p){307 assert( p->validYMD || p->isError );308 assert( p->D>=0 && p->D<=31 );309 assert( p->M>=0 && p->M<=12 );310 if( p->D<=28 ){311 p->nFloor = 0;312 }else if( (1<<p->M) & 0x15aa ){313 p->nFloor = 0;314 }else if( p->M!=2 ){315 p->nFloor = (p->D==31);316 }else if( p->Y%4!=0 || (p->Y%100==0 && p->Y%400!=0) ){317 p->nFloor = p->D - 28;318 }else{319 p->nFloor = p->D - 29;320 }321}322 323/*324** Parse dates of the form325**326** YYYY-MM-DD HH:MM:SS.FFF327** YYYY-MM-DD HH:MM:SS328** YYYY-MM-DD HH:MM329** YYYY-MM-DD330**331** Write the result into the DateTime structure and return 0332** on success and 1 if the input string is not a well-formed333** date.334*/335static int parseYyyyMmDd(const char *zDate, DateTime *p){336 int Y, M, D, neg;337 338 if( zDate[0]=='-' ){339 zDate++;340 neg = 1;341 }else{342 neg = 0;343 }344 if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){345 return 1;346 }347 zDate += 10;348 while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }349 if( parseHhMmSs(zDate, p)==0 ){350 /* We got the time */351 }else if( *zDate==0 ){352 p->validHMS = 0;353 }else{354 return 1;355 }356 p->validJD = 0;357 p->validYMD = 1;358 p->Y = neg ? -Y : Y;359 p->M = M;360 p->D = D;361 computeFloor(p);362 if( p->tz ){363 computeJD(p);364 }365 return 0;366}367 368 369static void clearYMD_HMS_TZ(DateTime *p); /* Forward declaration */370 371/*372** Set the time to the current time reported by the VFS.373**374** Return the number of errors.375*/376static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){377 p->iJD = sqlite3StmtCurrentTime(context);378 if( p->iJD>0 ){379 p->validJD = 1;380 p->isUtc = 1;381 p->isLocal = 0;382 clearYMD_HMS_TZ(p);383 return 0;384 }else{385 return 1;386 }387}388 389/*390** Input "r" is a numeric quantity which might be a julian day number,391** or the number of seconds since 1970. If the value if r is within392** range of a julian day number, install it as such and set validJD.393** If the value is a valid unix timestamp, put it in p->s and set p->rawS.394*/395static void setRawDateNumber(DateTime *p, double r){396 p->s = r;397 p->rawS = 1;398 if( r>=0.0 && r<5373484.5 ){399 p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);400 p->validJD = 1;401 }402}403 404/*405** Attempt to parse the given string into a julian day number. Return406** the number of errors.407**408** The following are acceptable forms for the input string:409**410** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM411** DDDD.DD 412** now413**414** In the first form, the +/-HH:MM is always optional. The fractional415** seconds extension (the ".FFF") is optional. The seconds portion416** (":SS.FFF") is option. The year and date can be omitted as long417** as there is a time string. The time string can be omitted as long418** as there is a year and date.419*/420static int parseDateOrTime(421 sqlite3_context *context, 422 const char *zDate, 423 DateTime *p424){425 double r;426 if( parseYyyyMmDd(zDate,p)==0 ){427 return 0;428 }else if( parseHhMmSs(zDate, p)==0 ){429 return 0;430 }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){431 return setDateTimeToCurrent(context, p);432 }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){433 setRawDateNumber(p, r);434 return 0;435 }else if( (sqlite3StrICmp(zDate,"subsec")==0436 || sqlite3StrICmp(zDate,"subsecond")==0)437 && sqlite3NotPureFunc(context) ){438 p->useSubsec = 1;439 return setDateTimeToCurrent(context, p);440 }441 return 1;442}443 444/* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999.445** Multiplying this by 86400000 gives 464269060799999 as the maximum value446** for DateTime.iJD.447**448** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with 449** such a large integer literal, so we have to encode it.450*/451#define INT_464269060799999 ((((i64)0x1a640)<<32)|0x1072fdff)452 453/*454** Return TRUE if the given julian day number is within range.455**456** The input is the JulianDay times 86400000.457*/458static int validJulianDay(sqlite3_int64 iJD){459 return iJD>=0 && iJD<=INT_464269060799999;460}461 462/*463** Compute the Year, Month, and Day from the julian day number.464*/465static void computeYMD(DateTime *p){466 int Z, alpha, A, B, C, D, E, X1;467 if( p->validYMD ) return;468 if( !p->validJD ){469 p->Y = 2000;470 p->M = 1;471 p->D = 1;472 }else if( !validJulianDay(p->iJD) ){473 datetimeError(p);474 return;475 }else{476 Z = (int)((p->iJD + 43200000)/86400000);477 alpha = (int)((Z + 32044.75)/36524.25) - 52;478 A = Z + 1 + alpha - ((alpha+100)/4) + 25;479 B = A + 1524;480 C = (int)((B - 122.1)/365.25);481 D = (36525*(C&32767))/100;482 E = (int)((B-D)/30.6001);483 X1 = (int)(30.6001*E);484 p->D = B - D - X1;485 p->M = E<14 ? E-1 : E-13;486 p->Y = p->M>2 ? C - 4716 : C - 4715;487 }488 p->validYMD = 1;489}490 491/*492** Compute the Hour, Minute, and Seconds from the julian day number.493*/494static void computeHMS(DateTime *p){495 int day_ms, day_min; /* milliseconds, minutes into the day */496 if( p->validHMS ) return;497 computeJD(p);498 day_ms = (int)((p->iJD + 43200000) % 86400000);499 p->s = (day_ms % 60000)/1000.0;500 day_min = day_ms/60000;501 p->m = day_min % 60;502 p->h = day_min / 60;503 p->rawS = 0;504 p->validHMS = 1;505}506 507/*508** Compute both YMD and HMS509*/510static void computeYMD_HMS(DateTime *p){511 computeYMD(p);512 computeHMS(p);513}514 515/*516** Clear the YMD and HMS and the TZ517*/518static void clearYMD_HMS_TZ(DateTime *p){519 p->validYMD = 0;520 p->validHMS = 0;521 p->tz = 0;522}523 524#ifndef SQLITE_OMIT_LOCALTIME525/*526** On recent Windows platforms, the localtime_s() function is available527** as part of the "Secure CRT". It is essentially equivalent to 528** localtime_r() available under most POSIX platforms, except that the 529** order of the parameters is reversed.530**531** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.532**533** If the user has not indicated to use localtime_r() or localtime_s()534** already, check for an MSVC build environment that provides 535** localtime_s().536*/537#if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \538 && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)539#undef HAVE_LOCALTIME_S540#define HAVE_LOCALTIME_S 1541#endif542 543/*544** The following routine implements the rough equivalent of localtime_r()545** using whatever operating-system specific localtime facility that546** is available. This routine returns 0 on success and547** non-zero on any kind of error.548**549** If the sqlite3GlobalConfig.bLocaltimeFault variable is non-zero then this550** routine will always fail. If bLocaltimeFault is nonzero and551** sqlite3GlobalConfig.xAltLocaltime is not NULL, then xAltLocaltime() is552** invoked in place of the OS-defined localtime() function.553**554** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C555** library function localtime_r() is used to assist in the calculation of556** local time.557*/558static int osLocaltime(time_t *t, struct tm *pTm){559 int rc;560#if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S561 struct tm *pX;562#if SQLITE_THREADSAFE>0563 sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN);564#endif565 sqlite3_mutex_enter(mutex);566 pX = localtime(t);567#ifndef SQLITE_UNTESTABLE568 if( sqlite3GlobalConfig.bLocaltimeFault ){569 if( sqlite3GlobalConfig.xAltLocaltime!=0570 && 0==sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm)571 ){572 pX = pTm;573 }else{574 pX = 0;575 }576 }577#endif578 if( pX ) *pTm = *pX;579#if SQLITE_THREADSAFE>0580 sqlite3_mutex_leave(mutex);581#endif582 rc = pX==0;583#else584#ifndef SQLITE_UNTESTABLE585 if( sqlite3GlobalConfig.bLocaltimeFault ){586 if( sqlite3GlobalConfig.xAltLocaltime!=0 ){587 return sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm);588 }else{589 return 1;590 }591 }592#endif593#if HAVE_LOCALTIME_R594 rc = localtime_r(t, pTm)==0;595#else596 rc = localtime_s(pTm, t);597#endif /* HAVE_LOCALTIME_R */598#endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */599 return rc;600}601#endif /* SQLITE_OMIT_LOCALTIME */602 603 604#ifndef SQLITE_OMIT_LOCALTIME605/*606** Assuming the input DateTime is UTC, move it to its localtime equivalent.607*/608static int toLocaltime(609 DateTime *p, /* Date at which to calculate offset */610 sqlite3_context *pCtx /* Write error here if one occurs */611){612 time_t t;613 struct tm sLocal;614 int iYearDiff;615 616 /* Initialize the contents of sLocal to avoid a compiler warning. */617 memset(&sLocal, 0, sizeof(sLocal));618 619 computeJD(p);620 if( p->iJD<2108667600*(i64)100000 /* 1970-01-01 */621 || p->iJD>2130141456*(i64)100000 /* 2038-01-18 */622 ){623 /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only624 ** works for years between 1970 and 2037. For dates outside this range,625 ** SQLite attempts to map the year into an equivalent year within this626 ** range, do the calculation, then map the year back.627 */628 DateTime x = *p;629 computeYMD_HMS(&x);630 iYearDiff = (2000 + x.Y%4) - x.Y;631 x.Y += iYearDiff;632 x.validJD = 0;633 computeJD(&x);634 t = (time_t)(x.iJD/1000 - 21086676*(i64)10000);635 }else{636 iYearDiff = 0;637 t = (time_t)(p->iJD/1000 - 21086676*(i64)10000);638 }639 if( osLocaltime(&t, &sLocal) ){640 sqlite3_result_error(pCtx, "local time unavailable", -1);641 return SQLITE_ERROR;642 }643 p->Y = sLocal.tm_year + 1900 - iYearDiff;644 p->M = sLocal.tm_mon + 1;645 p->D = sLocal.tm_mday;646 p->h = sLocal.tm_hour;647 p->m = sLocal.tm_min;648 p->s = sLocal.tm_sec + (p->iJD%1000)*0.001;649 p->validYMD = 1;650 p->validHMS = 1;651 p->validJD = 0;652 p->rawS = 0;653 p->tz = 0;654 p->isError = 0;655 return SQLITE_OK;656}657#endif /* SQLITE_OMIT_LOCALTIME */658 659/*660** The following table defines various date transformations of the form661**662** 'NNN days'663**664** Where NNN is an arbitrary floating-point number and "days" can be one665** of several units of time.666*/667static const struct {668 u8 nName; /* Length of the name */669 char zName[7]; /* Name of the transformation */670 float rLimit; /* Maximum NNN value for this transform */671 float rXform; /* Constant used for this transform */672} aXformType[] = {673 /* 0 */ { 6, "second", 4.6427e+14, 1.0 },674 /* 1 */ { 6, "minute", 7.7379e+12, 60.0 },675 /* 2 */ { 4, "hour", 1.2897e+11, 3600.0 },676 /* 3 */ { 3, "day", 5373485.0, 86400.0 },677 /* 4 */ { 5, "month", 176546.0, 2592000.0 },678 /* 5 */ { 4, "year", 14713.0, 31536000.0 },679};680 681/*682** If the DateTime p is raw number, try to figure out if it is683** a julian day number of a unix timestamp. Set the p value684** appropriately.685*/686static void autoAdjustDate(DateTime *p){687 if( !p->rawS || p->validJD ){688 p->rawS = 0;689 }else if( p->s>=-21086676*(i64)10000 /* -4713-11-24 12:00:00 */690 && p->s<=(25340230*(i64)10000)+799 /* 9999-12-31 23:59:59 */691 ){692 double r = p->s*1000.0 + 210866760000000.0;693 clearYMD_HMS_TZ(p);694 p->iJD = (sqlite3_int64)(r + 0.5);695 p->validJD = 1;696 p->rawS = 0;697 }698}699 700/*701** Process a modifier to a date-time stamp. The modifiers are702** as follows:703**704** NNN days705** NNN hours706** NNN minutes707** NNN.NNNN seconds708** NNN months709** NNN years710** +/-YYYY-MM-DD HH:MM:SS.SSS711** ceiling712** floor713** start of month714** start of year715** start of week716** start of day717** weekday N718** unixepoch719** auto720** localtime721** utc722** subsec723** subsecond724**725** Return 0 on success and 1 if there is any kind of error. If the error726** is in a system call (i.e. localtime()), then an error message is written727** to context pCtx. If the error is an unrecognized modifier, no error is728** written to pCtx.729*/730static int parseModifier(731 sqlite3_context *pCtx, /* Function context */732 const char *z, /* The text of the modifier */733 int n, /* Length of zMod in bytes */734 DateTime *p, /* The date/time value to be modified */735 int idx /* Parameter index of the modifier */736){737 int rc = 1;738 double r;739 switch(sqlite3UpperToLower[(u8)z[0]] ){740 case 'a': {741 /*742 ** auto743 **744 ** If rawS is available, then interpret as a julian day number, or745 ** a unix timestamp, depending on its magnitude.746 */747 if( sqlite3_stricmp(z, "auto")==0 ){748 if( idx>1 ) return 1; /* IMP: R-33611-57934 */749 autoAdjustDate(p);750 rc = 0;751 }752 break;753 }754 case 'c': {755 /*756 ** ceiling757 **758 ** Resolve day-of-month overflow by rolling forward into the next759 ** month. As this is the default action, this modifier is really760 ** a no-op that is only included for symmetry. See "floor".761 */762 if( sqlite3_stricmp(z, "ceiling")==0 ){763 computeJD(p);764 clearYMD_HMS_TZ(p);765 rc = 0;766 p->nFloor = 0;767 }768 break;769 }770 case 'f': {771 /*772 ** floor773 **774 ** Resolve day-of-month overflow by rolling back to the end of the775 ** previous month.776 */777 if( sqlite3_stricmp(z, "floor")==0 ){778 computeJD(p);779 p->iJD -= p->nFloor*86400000;780 clearYMD_HMS_TZ(p);781 rc = 0;782 }783 break;784 }785 case 'j': {786 /*787 ** julianday788 **789 ** Always interpret the prior number as a julian-day value. If this790 ** is not the first modifier, or if the prior argument is not a numeric791 ** value in the allowed range of julian day numbers understood by792 ** SQLite (0..5373484.5) then the result will be NULL.793 */794 if( sqlite3_stricmp(z, "julianday")==0 ){795 if( idx>1 ) return 1; /* IMP: R-31176-64601 */796 if( p->validJD && p->rawS ){797 rc = 0;798 p->rawS = 0;799 }800 }801 break;802 }803#ifndef SQLITE_OMIT_LOCALTIME804 case 'l': {805 /* localtime806 **807 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to808 ** show local time.809 */810 if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){811 rc = p->isLocal ? SQLITE_OK : toLocaltime(p, pCtx);812 p->isUtc = 0;813 p->isLocal = 1;814 }815 break;816 }817#endif818 case 'u': {819 /*820 ** unixepoch821 **822 ** Treat the current value of p->s as the number of823 ** seconds since 1970. Convert to a real julian day number.824 */825 if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){826 if( idx>1 ) return 1; /* IMP: R-49255-55373 */827 r = p->s*1000.0 + 210866760000000.0;828 if( r>=0.0 && r<464269060800000.0 ){829 clearYMD_HMS_TZ(p);830 p->iJD = (sqlite3_int64)(r + 0.5);831 p->validJD = 1;832 p->rawS = 0;833 rc = 0;834 }835 }836#ifndef SQLITE_OMIT_LOCALTIME837 else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){838 if( p->isUtc==0 ){839 i64 iOrigJD; /* Original localtime */840 i64 iGuess; /* Guess at the corresponding utc time */841 int cnt = 0; /* Safety to prevent infinite loop */842 i64 iErr; /* Guess is off by this much */843 844 computeJD(p);845 iGuess = iOrigJD = p->iJD;846 iErr = 0;847 do{848 DateTime new;849 memset(&new, 0, sizeof(new));850 iGuess -= iErr;851 new.iJD = iGuess;852 new.validJD = 1;853 rc = toLocaltime(&new, pCtx);854 if( rc ) return rc;855 computeJD(&new);856 iErr = new.iJD - iOrigJD;857 }while( iErr && cnt++<3 );858 memset(p, 0, sizeof(*p));859 p->iJD = iGuess;860 p->validJD = 1;861 p->isUtc = 1;862 p->isLocal = 0;863 }864 rc = SQLITE_OK;865 }866#endif867 break;868 }869 case 'w': {870 /*871 ** weekday N872 **873 ** Move the date to the same time on the next occurrence of874 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the875 ** date is already on the appropriate weekday, this is a no-op.876 */877 if( sqlite3_strnicmp(z, "weekday ", 8)==0878 && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0879 && r>=0.0 && r<7.0 && (n=(int)r)==r ){880 sqlite3_int64 Z;881 computeYMD_HMS(p);882 p->tz = 0;883 p->validJD = 0;884 computeJD(p);885 Z = ((p->iJD + 129600000)/86400000) % 7;886 if( Z>n ) Z -= 7;887 p->iJD += (n - Z)*86400000;888 clearYMD_HMS_TZ(p);889 rc = 0;890 }891 break;892 }893 case 's': {894 /*895 ** start of TTTTT896 **897 ** Move the date backwards to the beginning of the current day,898 ** or month or year.899 **900 ** subsecond901 ** subsec902 **903 ** Show subsecond precision in the output of datetime() and904 ** unixepoch() and strftime('%s').905 */906 if( sqlite3_strnicmp(z, "start of ", 9)!=0 ){907 if( sqlite3_stricmp(z, "subsec")==0908 || sqlite3_stricmp(z, "subsecond")==0909 ){910 p->useSubsec = 1;911 rc = 0;912 }913 break;914 } 915 if( !p->validJD && !p->validYMD && !p->validHMS ) break;916 z += 9;917 computeYMD(p);918 p->validHMS = 1;919 p->h = p->m = 0;920 p->s = 0.0;921 p->rawS = 0;922 p->tz = 0;923 p->validJD = 0;924 if( sqlite3_stricmp(z,"month")==0 ){925 p->D = 1;926 rc = 0;927 }else if( sqlite3_stricmp(z,"year")==0 ){928 p->M = 1;929 p->D = 1;930 rc = 0;931 }else if( sqlite3_stricmp(z,"day")==0 ){932 rc = 0;933 }934 break;935 }936 case '+':937 case '-':938 case '0':939 case '1':940 case '2':941 case '3':942 case '4':943 case '5':944 case '6':945 case '7':946 case '8':947 case '9': {948 double rRounder;949 int i;950 int Y,M,D,h,m,x;951 const char *z2 = z;952 char z0 = z[0];953 for(n=1; z[n]; n++){954 if( z[n]==':' ) break;955 if( sqlite3Isspace(z[n]) ) break;956 if( z[n]=='-' ){957 if( n==5 && getDigits(&z[1], "40f", &Y)==1 ) break;958 if( n==6 && getDigits(&z[1], "50f", &Y)==1 ) break;959 }960 }961 if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){962 assert( rc==1 );963 break;964 }965 if( z[n]=='-' ){966 /* A modifier of the form (+|-)YYYY-MM-DD adds or subtracts the967 ** specified number of years, months, and days. MM is limited to968 ** the range 0-11 and DD is limited to 0-30.969 */970 if( z0!='+' && z0!='-' ) break; /* Must start with +/- */971 if( n==5 ){972 if( getDigits(&z[1], "40f-20a-20d", &Y, &M, &D)!=3 ) break;973 }else{974 assert( n==6 );975 if( getDigits(&z[1], "50f-20a-20d", &Y, &M, &D)!=3 ) break;976 z++;977 }978 if( M>=12 ) break; /* M range 0..11 */979 if( D>=31 ) break; /* D range 0..30 */980 computeYMD_HMS(p);981 p->validJD = 0;982 if( z0=='-' ){983 p->Y -= Y;984 p->M -= M;985 D = -D;986 }else{987 p->Y += Y;988 p->M += M;989 }990 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;991 p->Y += x;992 p->M -= x*12;993 computeFloor(p);994 computeJD(p);995 p->validHMS = 0;996 p->validYMD = 0;997 p->iJD += (i64)D*86400000;998 if( z[11]==0 ){999 rc = 0;1000 break;1001 }1002 if( sqlite3Isspace(z[11])1003 && getDigits(&z[12], "20c:20e", &h, &m)==21004 ){1005 z2 = &z[12];1006 n = 2;1007 }else{1008 break;1009 }1010 }1011 if( z2[n]==':' ){1012 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the1013 ** specified number of hours, minutes, seconds, and fractional seconds1014 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be1015 ** omitted.1016 */1017 1018 DateTime tx;1019 sqlite3_int64 day;1020 if( !sqlite3Isdigit(*z2) ) z2++;1021 memset(&tx, 0, sizeof(tx));1022 if( parseHhMmSs(z2, &tx) ) break;1023 computeJD(&tx);1024 tx.iJD -= 43200000;1025 day = tx.iJD/86400000;1026 tx.iJD -= day*86400000;1027 if( z0=='-' ) tx.iJD = -tx.iJD;1028 computeJD(p);1029 clearYMD_HMS_TZ(p);1030 p->iJD += tx.iJD;1031 rc = 0;1032 break;1033 }1034 1035 /* If control reaches this point, it means the transformation is1036 ** one of the forms like "+NNN days". */1037 z += n;1038 while( sqlite3Isspace(*z) ) z++;1039 n = sqlite3Strlen30(z);1040 if( n<3 || n>10 ) break;1041 if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;1042 computeJD(p);1043 assert( rc==1 );1044 rRounder = r<0 ? -0.5 : +0.5;1045 p->nFloor = 0;1046 for(i=0; i<ArraySize(aXformType); i++){1047 if( aXformType[i].nName==n1048 && sqlite3_strnicmp(aXformType[i].zName, z, n)==01049 && r>-aXformType[i].rLimit && r<aXformType[i].rLimit1050 ){1051 switch( i ){1052 case 4: { /* Special processing to add months */1053 assert( strcmp(aXformType[4].zName,"month")==0 );1054 computeYMD_HMS(p);1055 p->M += (int)r;1056 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;1057 p->Y += x;1058 p->M -= x*12;1059 computeFloor(p);1060 p->validJD = 0;1061 r -= (int)r;1062 break;1063 }1064 case 5: { /* Special processing to add years */1065 int y = (int)r;1066 assert( strcmp(aXformType[5].zName,"year")==0 );1067 computeYMD_HMS(p);1068 assert( p->M>=0 && p->M<=12 );1069 p->Y += y;1070 computeFloor(p);1071 p->validJD = 0;1072 r -= (int)r;1073 break;1074 }1075 }1076 computeJD(p);1077 p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder);1078 rc = 0;1079 break;1080 }1081 }1082 clearYMD_HMS_TZ(p);1083 break;1084 }1085 default: {1086 break;1087 }1088 }1089 return rc;1090}1091 1092/*1093** Process time function arguments. argv[0] is a date-time stamp.1094** argv[1] and following are modifiers. Parse them all and write1095** the resulting time into the DateTime structure p. Return 01096** on success and 1 if there are any errors.1097**1098** If there are zero parameters (if even argv[0] is undefined)1099** then assume a default value of "now" for argv[0].1100*/1101static int isDate(1102 sqlite3_context *context, 1103 int argc, 1104 sqlite3_value **argv, 1105 DateTime *p1106){1107 int i, n;1108 const unsigned char *z;1109 int eType;1110 memset(p, 0, sizeof(*p));1111 if( argc==0 ){1112 if( !sqlite3NotPureFunc(context) ) return 1;1113 return setDateTimeToCurrent(context, p);1114 }1115 if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT1116 || eType==SQLITE_INTEGER ){1117 setRawDateNumber(p, sqlite3_value_double(argv[0]));1118 }else{1119 z = sqlite3_value_text(argv[0]);1120 if( !z || parseDateOrTime(context, (char*)z, p) ){1121 return 1;1122 }1123 }1124 for(i=1; i<argc; i++){1125 z = sqlite3_value_text(argv[i]);1126 n = sqlite3_value_bytes(argv[i]);1127 if( z==0 || parseModifier(context, (char*)z, n, p, i) ) return 1;1128 }1129 computeJD(p);1130 if( p->isError || !validJulianDay(p->iJD) ) return 1;1131 if( argc==1 && p->validYMD && p->D>28 ){1132 /* Make sure a YYYY-MM-DD is normalized.1133 ** Example: 2023-02-31 -> 2023-03-03 */1134 assert( p->validJD );1135 p->validYMD = 0; 1136 }1137 return 0;1138}1139 1140 1141/*1142** The following routines implement the various date and time functions1143** of SQLite.1144*/1145 1146/*1147** julianday( TIMESTRING, MOD, MOD, ...)1148**1149** Return the julian day number of the date specified in the arguments1150*/1151static void juliandayFunc(1152 sqlite3_context *context,1153 int argc,1154 sqlite3_value **argv1155){1156 DateTime x;1157 if( isDate(context, argc, argv, &x)==0 ){1158 computeJD(&x);1159 sqlite3_result_double(context, x.iJD/86400000.0);1160 }1161}1162 1163/*1164** unixepoch( TIMESTRING, MOD, MOD, ...)1165**1166** Return the number of seconds (including fractional seconds) since1167** the unix epoch of 1970-01-01 00:00:00 GMT.1168*/1169static void unixepochFunc(1170 sqlite3_context *context,1171 int argc,1172 sqlite3_value **argv1173){1174 DateTime x;1175 if( isDate(context, argc, argv, &x)==0 ){1176 computeJD(&x);1177 if( x.useSubsec ){1178 sqlite3_result_double(context, (x.iJD - 21086676*(i64)10000000)/1000.0);1179 }else{1180 sqlite3_result_int64(context, x.iJD/1000 - 21086676*(i64)10000);1181 }1182 }1183}1184 1185/*1186** datetime( TIMESTRING, MOD, MOD, ...)1187**1188** Return YYYY-MM-DD HH:MM:SS1189*/1190static void datetimeFunc(1191 sqlite3_context *context,1192 int argc,1193 sqlite3_value **argv1194){1195 DateTime x;1196 if( isDate(context, argc, argv, &x)==0 ){1197 int Y, s, n;1198 char zBuf[32];1199 computeYMD_HMS(&x);1200 Y = x.Y;