/*

** 2003 October 31

**

** The author disclaims copyright to this source code. In place of

** a legal notice, here is a blessing:

**

** May you do good and not evil.

** May you find forgiveness for yourself and forgive others.

** May you share freely, never taking more than you give.

**

*************************************************************************

** This file contains the C functions that implement date and time

** functions for SQLite.

**

** There is only one exported symbol in this file - the function

** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.

** All other code has file scope.

**

** SQLite processes all times and dates as julian day numbers. The

** dates and times are stored as the number of days since noon

** in Greenwich on November 24, 4714 B.C. according to the Gregorian

** calendar system.

**

** 1970-01-01 00:00:00 is JD 2440587.5

** 2000-01-01 00:00:00 is JD 2451544.5

**

** This implementation requires years to be expressed as a 4-digit number

** which means that only dates between 0000-01-01 and 9999-12-31 can

** be represented, even though julian day numbers allow a much wider

** range of dates.

**

** The Gregorian calendar system is used for all dates and times,

** even those that predate the Gregorian calendar. Historians usually

** use the julian calendar for dates prior to 1582-10-15 and for some

** dates afterwards, depending on locale. Beware of this difference.

**

** The conversion algorithms are implemented based on descriptions

** in the following text:

**

** Jean Meeus

** Astronomical Algorithms, 2nd Edition, 1998

** ISBN 0-943396-61-1

** Willmann-Bell, Inc

** Richmond, Virginia (USA)

*/

#include "sqliteInt.h"

#include <stdlib.h>

#include <assert.h>

#include <time.h>

#ifndef SQLITE_OMIT_DATETIME_FUNCS

/*

** The MSVC CRT on Windows CE may not have a localtime() function.

** So declare a substitute. The substitute function itself is

** defined in "os_win.c".

*/

#if !defined(SQLITE_OMIT_LOCALTIME) && defined(_WIN32_WCE) && \

(!defined(SQLITE_MSVC_LOCALTIME_API) || !SQLITE_MSVC_LOCALTIME_API)

struct tm *__cdecl localtime(const time_t *);

#endif

/*

** A structure for holding a single date and time.

*/

typedef struct DateTime DateTime;

struct DateTime {

sqlite3_int64 iJD; /* The julian day number times 86400000 */

int Y, M, D; /* Year, month, and day */

int h, m; /* Hour and minutes */

int tz; /* Timezone offset in minutes */

double s; /* Seconds */

char validJD; /* True (1) if iJD is valid */

char rawS; /* Raw numeric value stored in s */

char validYMD; /* True (1) if Y,M,D are valid */

char validHMS; /* True (1) if h,m,s are valid */

char validTZ; /* True (1) if tz is valid */

char tzSet; /* Timezone was set explicitly */

char isError; /* An overflow has occurred */

char useSubsec; /* Display subsecond precision */

};

/*

** Convert zDate into one or more integers according to the conversion

** specifier zFormat.

**

** zFormat[] contains 4 characters for each integer converted, except for

** the last integer which is specified by three characters. The meaning

** of a four-character format specifiers ABCD is:

**

** A: number of digits to convert. Always "2" or "4".

** B: minimum value. Always "0" or "1".

** C: maximum value, decoded as:

** a: 12

** b: 14

** c: 24

** d: 31

** e: 59

** f: 9999

** D: the separator character, or \000 to indicate this is the

** last number to convert.

**

** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would

** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-".

** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates

** the 2-digit day which is the last integer in the set.

**

** The function returns the number of successful conversions.

*/

static int getDigits(const char *zDate, const char *zFormat, ...){

/* The aMx[] array translates the 3rd character of each format

** spec into a max size: a b c d e f */

static const u16 aMx[] = { 12, 14, 24, 31, 59, 14712 };

va_list ap;

int cnt = 0;

char nextC;

va_start(ap, zFormat);

do{

char N = zFormat[0] - '0';

char min = zFormat[1] - '0';

int val = 0;

u16 max;

assert( zFormat[2]>='a' && zFormat[2]<='f' );

max = aMx[zFormat[2] - 'a'];

nextC = zFormat[3];

val = 0;

while( N-- ){

if( !sqlite3Isdigit(*zDate) ){

goto end_getDigits;

}

val = val*10 + *zDate - '0';

zDate++;

}

if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){

goto end_getDigits;

}

*va_arg(ap,int*) = val;

zDate++;

cnt++;

zFormat += 4;

}while( nextC );

end_getDigits:

va_end(ap);

return cnt;

}

/*

** Parse a timezone extension on the end of a date-time.

** The extension is of the form:

**

** (+/-)HH:MM

**

** Or the "zulu" notation:

**

** Z

**

** If the parse is successful, write the number of minutes

** of change in p->tz and return 0. If a parser error occurs,

** return non-zero.

**

** A missing specifier is not considered an error.

*/

static int parseTimezone(const char *zDate, DateTime *p){

int sgn = 0;

int nHr, nMn;

int c;

while( sqlite3Isspace(*zDate) ){ zDate++; }

p->tz = 0;

c = *zDate;

if( c=='-' ){

sgn = -1;

}else if( c=='+' ){

sgn = +1;

}else if( c=='Z' || c=='z' ){

zDate++;

goto zulu_time;

}else{

return c!=0;

}

zDate++;

if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){

return 1;

}

zDate += 5;

p->tz = sgn*(nMn + nHr*60);

zulu_time:

while( sqlite3Isspace(*zDate) ){ zDate++; }

p->tzSet = 1;

return *zDate!=0;

}

/*

** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.

** The HH, MM, and SS must each be exactly 2 digits. The

** fractional seconds FFFF can be one or more digits.

**

** Return 1 if there is a parsing error and 0 on success.

*/

static int parseHhMmSs(const char *zDate, DateTime *p){

int h, m, s;

double ms = 0.0;

if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){

return 1;

}

zDate += 5;

if( *zDate==':' ){

zDate++;

if( getDigits(zDate, "20e", &s)!=1 ){

return 1;

}

zDate += 2;

if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){

double rScale = 1.0;

zDate++;

while( sqlite3Isdigit(*zDate) ){

ms = ms*10.0 + *zDate - '0';

rScale *= 10.0;

zDate++;

}

ms /= rScale;

}

}else{

s = 0;

}

p->validJD = 0;

p->rawS = 0;

p->validHMS = 1;

p->h = h;

p->m = m;

p->s = s + ms;

if( parseTimezone(zDate, p) ) return 1;

p->validTZ = (p->tz!=0)?1:0;

return 0;

}

/*

** Put the DateTime object into its error state.

*/

static void datetimeError(DateTime *p){

memset(p, 0, sizeof(*p));

p->isError = 1;

}

/*

** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume

** that the YYYY-MM-DD is according to the Gregorian calendar.

**

** Reference: Meeus page 61

*/

static void computeJD(DateTime *p){

int Y, M, D, A, B, X1, X2;

if( p->validJD ) return;

if( p->validYMD ){

Y = p->Y;

M = p->M;

D = p->D;

}else{

Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */

M = 1;

D = 1;

}

if( Y<-4713 || Y>9999 || p->rawS ){

datetimeError(p);

return;

}

if( M<=2 ){

Y--;

M += 12;

}

A = Y/100;

B = 2 - A + (A/4);

X1 = 36525*(Y+4716)/100;

X2 = 306001*(M+1)/10000;

p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);

p->validJD = 1;

if( p->validHMS ){

p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000 + 0.5);

if( p->validTZ ){

p->iJD -= p->tz*60000;

p->validYMD = 0;

p->validHMS = 0;

p->validTZ = 0;

}

}

}

/*

** Parse dates of the form

**

** YYYY-MM-DD HH:MM:SS.FFF

** YYYY-MM-DD HH:MM:SS

** YYYY-MM-DD HH:MM

** YYYY-MM-DD

**

** Write the result into the DateTime structure and return 0

** on success and 1 if the input string is not a well-formed

** date.

*/

static int parseYyyyMmDd(const char *zDate, DateTime *p){

int Y, M, D, neg;

if( zDate[0]=='-' ){

zDate++;

neg = 1;

}else{

neg = 0;

}

if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){

return 1;

}

zDate += 10;

while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }

if( parseHhMmSs(zDate, p)==0 ){

/* We got the time */

}else if( *zDate==0 ){

p->validHMS = 0;

}else{

return 1;

}

p->validJD = 0;

p->validYMD = 1;

p->Y = neg ? -Y : Y;

p->M = M;

p->D = D;

if( p->validTZ ){

computeJD(p);

}

return 0;

}

/*

** Set the time to the current time reported by the VFS.

**

** Return the number of errors.

*/

static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){

p->iJD = sqlite3StmtCurrentTime(context);

if( p->iJD>0 ){

p->validJD = 1;

return 0;

}else{

return 1;

}

}

/*

** Input "r" is a numeric quantity which might be a julian day number,

** or the number of seconds since 1970. If the value if r is within

** range of a julian day number, install it as such and set validJD.

** If the value is a valid unix timestamp, put it in p->s and set p->rawS.

*/

static void setRawDateNumber(DateTime *p, double r){

p->s = r;

p->rawS = 1;

if( r>=0.0 && r<5373484.5 ){

p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);

p->validJD = 1;

}

}

/*

** Attempt to parse the given string into a julian day number. Return

** the number of errors.

**

** The following are acceptable forms for the input string:

**

** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM

** DDDD.DD

** now

**

** In the first form, the +/-HH:MM is always optional. The fractional

** seconds extension (the ".FFF") is optional. The seconds portion

** (":SS.FFF") is option. The year and date can be omitted as long

** as there is a time string. The time string can be omitted as long

** as there is a year and date.

*/

static int parseDateOrTime(

sqlite3_context *context,

const char *zDate,

DateTime *p

){

double r;

if( parseYyyyMmDd(zDate,p)==0 ){

return 0;

}else if( parseHhMmSs(zDate, p)==0 ){

return 0;

}else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){

return setDateTimeToCurrent(context, p);

}else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){

setRawDateNumber(p, r);

return 0;

}else if( (sqlite3StrICmp(zDate,"subsec")==0

|| sqlite3StrICmp(zDate,"subsecond")==0)

&& sqlite3NotPureFunc(context) ){

p->useSubsec = 1;

return setDateTimeToCurrent(context, p);

}

return 1;

}

/* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999.

** Multiplying this by 86400000 gives 464269060799999 as the maximum value

** for DateTime.iJD.

**

** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with

** such a large integer literal, so we have to encode it.

*/

#define INT_464269060799999 ((((i64)0x1a640)<<32)|0x1072fdff)

/*

** Return TRUE if the given julian day number is within range.

**

** The input is the JulianDay times 86400000.

*/

static int validJulianDay(sqlite3_int64 iJD){

return iJD>=0 && iJD<=INT_464269060799999;

}

/*

** Compute the Year, Month, and Day from the julian day number.

*/

static void computeYMD(DateTime *p){

int Z, A, B, C, D, E, X1;

if( p->validYMD ) return;

if( !p->validJD ){

p->Y = 2000;

p->M = 1;

p->D = 1;

}else if( !validJulianDay(p->iJD) ){

datetimeError(p);

return;

}else{

Z = (int)((p->iJD + 43200000)/86400000);

A = (int)((Z - 1867216.25)/36524.25);

A = Z + 1 + A - (A/4);

B = A + 1524;

C = (int)((B - 122.1)/365.25);

D = (36525*(C&32767))/100;

E = (int)((B-D)/30.6001);

X1 = (int)(30.6001*E);

p->D = B - D - X1;

p->M = E<14 ? E-1 : E-13;

p->Y = p->M>2 ? C - 4716 : C - 4715;

}

p->validYMD = 1;

}

/*

** Compute the Hour, Minute, and Seconds from the julian day number.

*/

static void computeHMS(DateTime *p){

int day_ms, day_min; /* milliseconds, minutes into the day */

if( p->validHMS ) return;

computeJD(p);

day_ms = (int)((p->iJD + 43200000) % 86400000);

p->s = (day_ms % 60000)/1000.0;

day_min = day_ms/60000;

p->m = day_min % 60;

p->h = day_min / 60;

p->rawS = 0;

p->validHMS = 1;

}

/*

** Compute both YMD and HMS

*/

static void computeYMD_HMS(DateTime *p){

computeYMD(p);

computeHMS(p);

}

/*

** Clear the YMD and HMS and the TZ

*/

static void clearYMD_HMS_TZ(DateTime *p){

p->validYMD = 0;

p->validHMS = 0;

p->validTZ = 0;

}

#ifndef SQLITE_OMIT_LOCALTIME

/*

** On recent Windows platforms, the localtime_s() function is available

** as part of the "Secure CRT". It is essentially equivalent to

** localtime_r() available under most POSIX platforms, except that the

** order of the parameters is reversed.

**

** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.

**

** If the user has not indicated to use localtime_r() or localtime_s()

** already, check for an MSVC build environment that provides

** localtime_s().

*/

#if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \

&& defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)

#undef HAVE_LOCALTIME_S

#define HAVE_LOCALTIME_S 1

#endif

/*

** The following routine implements the rough equivalent of localtime_r()

** using whatever operating-system specific localtime facility that

** is available. This routine returns 0 on success and

** non-zero on any kind of error.

**

** If the sqlite3GlobalConfig.bLocaltimeFault variable is non-zero then this

** routine will always fail. If bLocaltimeFault is nonzero and

** sqlite3GlobalConfig.xAltLocaltime is not NULL, then xAltLocaltime() is

** invoked in place of the OS-defined localtime() function.

**

** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C

** library function localtime_r() is used to assist in the calculation of

** local time.

*/

static int osLocaltime(time_t *t, struct tm *pTm){

int rc;

#if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S

struct tm *pX;

#if SQLITE_THREADSAFE>0

sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN);

#endif

sqlite3_mutex_enter(mutex);

pX = localtime(t);

#ifndef SQLITE_UNTESTABLE

if( sqlite3GlobalConfig.bLocaltimeFault ){

if( sqlite3GlobalConfig.xAltLocaltime!=0

&& 0==sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm)

){

pX = pTm;

}else{

pX = 0;

}

}

#endif

if( pX ) *pTm = *pX;

#if SQLITE_THREADSAFE>0

sqlite3_mutex_leave(mutex);

#endif

rc = pX==0;

#else

#ifndef SQLITE_UNTESTABLE

if( sqlite3GlobalConfig.bLocaltimeFault ){

if( sqlite3GlobalConfig.xAltLocaltime!=0 ){

return sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm);

}else{

return 1;

}

}

#endif

#if HAVE_LOCALTIME_R

rc = localtime_r(t, pTm)==0;

#else

rc = localtime_s(pTm, t);

#endif /* HAVE_LOCALTIME_R */

#endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */

return rc;

}

#endif /* SQLITE_OMIT_LOCALTIME */

#ifndef SQLITE_OMIT_LOCALTIME

/*

** Assuming the input DateTime is UTC, move it to its localtime equivalent.

*/

static int toLocaltime(

DateTime *p, /* Date at which to calculate offset */

sqlite3_context *pCtx /* Write error here if one occurs */

){

time_t t;

struct tm sLocal;

int iYearDiff;

/* Initialize the contents of sLocal to avoid a compiler warning. */

memset(&sLocal, 0, sizeof(sLocal));

computeJD(p);

if( p->iJD<2108667600*(i64)100000 /* 1970-01-01 */

|| p->iJD>2130141456*(i64)100000 /* 2038-01-18 */

){

/* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only

** works for years between 1970 and 2037. For dates outside this range,

** SQLite attempts to map the year into an equivalent year within this

** range, do the calculation, then map the year back.

*/

DateTime x = *p;

computeYMD_HMS(&x);

iYearDiff = (2000 + x.Y%4) - x.Y;

x.Y += iYearDiff;

x.validJD = 0;

computeJD(&x);

t = (time_t)(x.iJD/1000 - 21086676*(i64)10000);

}else{

iYearDiff = 0;

t = (time_t)(p->iJD/1000 - 21086676*(i64)10000);

}

if( osLocaltime(&t, &sLocal) ){

sqlite3_result_error(pCtx, "local time unavailable", -1);

return SQLITE_ERROR;

}

p->Y = sLocal.tm_year + 1900 - iYearDiff;

p->M = sLocal.tm_mon + 1;

p->D = sLocal.tm_mday;

p->h = sLocal.tm_hour;

p->m = sLocal.tm_min;

p->s = sLocal.tm_sec + (p->iJD%1000)*0.001;

p->validYMD = 1;

p->validHMS = 1;

p->validJD = 0;

p->rawS = 0;

p->validTZ = 0;

p->isError = 0;

return SQLITE_OK;

}

#endif /* SQLITE_OMIT_LOCALTIME */

/*

** The following table defines various date transformations of the form

**

** 'NNN days'

**

** Where NNN is an arbitrary floating-point number and "days" can be one

** of several units of time.

*/

static const struct {

u8 nName; /* Length of the name */

char zName[7]; /* Name of the transformation */

float rLimit; /* Maximum NNN value for this transform */

float rXform; /* Constant used for this transform */

} aXformType[] = {

{ 6, "second", 4.6427e+14, 1.0 },

{ 6, "minute", 7.7379e+12, 60.0 },

{ 4, "hour", 1.2897e+11, 3600.0 },

{ 3, "day", 5373485.0, 86400.0 },

{ 5, "month", 176546.0, 2592000.0 },

{ 4, "year", 14713.0, 31536000.0 },

};

/*

** If the DateTime p is raw number, try to figure out if it is

** a julian day number of a unix timestamp. Set the p value

** appropriately.

*/

static void autoAdjustDate(DateTime *p){

if( !p->rawS || p->validJD ){

p->rawS = 0;

}else if( p->s>=-21086676*(i64)10000 /* -4713-11-24 12:00:00 */

&& p->s<=(25340230*(i64)10000)+799 /* 9999-12-31 23:59:59 */

){

double r = p->s*1000.0 + 210866760000000.0;

clearYMD_HMS_TZ(p);

p->iJD = (sqlite3_int64)(r + 0.5);

p->validJD = 1;

p->rawS = 0;

}

}

/*

** Process a modifier to a date-time stamp. The modifiers are

** as follows:

**

** NNN days

** NNN hours

** NNN minutes

** NNN.NNNN seconds

** NNN months

** NNN years

** start of month

** start of year

** start of week

** start of day

** weekday N

** unixepoch

** localtime

** utc

**

** Return 0 on success and 1 if there is any kind of error. If the error

** is in a system call (i.e. localtime()), then an error message is written

** to context pCtx. If the error is an unrecognized modifier, no error is

** written to pCtx.

*/

static int parseModifier(

sqlite3_context *pCtx, /* Function context */

const char *z, /* The text of the modifier */

int n, /* Length of zMod in bytes */

DateTime *p, /* The date/time value to be modified */

int idx /* Parameter index of the modifier */

){

int rc = 1;

double r;

switch(sqlite3UpperToLower[(u8)z[0]] ){

case 'a': {

/*

** auto

**

** If rawS is available, then interpret as a julian day number, or

** a unix timestamp, depending on its magnitude.

*/

if( sqlite3_stricmp(z, "auto")==0 ){

if( idx>1 ) return 1; /* IMP: R-33611-57934 */

autoAdjustDate(p);

rc = 0;

}

break;

}

case 'j': {

/*

** julianday

**

** Always interpret the prior number as a julian-day value. If this

** is not the first modifier, or if the prior argument is not a numeric

** value in the allowed range of julian day numbers understood by

** SQLite (0..5373484.5) then the result will be NULL.

*/

if( sqlite3_stricmp(z, "julianday")==0 ){

if( idx>1 ) return 1; /* IMP: R-31176-64601 */

if( p->validJD && p->rawS ){

rc = 0;

p->rawS = 0;

}

}

break;

}

#ifndef SQLITE_OMIT_LOCALTIME

case 'l': {

/* localtime

**

** Assuming the current time value is UTC (a.k.a. GMT), shift it to

** show local time.

*/

if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){

rc = toLocaltime(p, pCtx);

}

break;

}

#endif

case 'u': {

/*

** unixepoch

**

** Treat the current value of p->s as the number of

** seconds since 1970. Convert to a real julian day number.

*/

if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){

if( idx>1 ) return 1; /* IMP: R-49255-55373 */

r = p->s*1000.0 + 210866760000000.0;

if( r>=0.0 && r<464269060800000.0 ){

clearYMD_HMS_TZ(p);

p->iJD = (sqlite3_int64)(r + 0.5);

p->validJD = 1;

p->rawS = 0;

rc = 0;

}

}

#ifndef SQLITE_OMIT_LOCALTIME

else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){

if( p->tzSet==0 ){

i64 iOrigJD; /* Original localtime */

i64 iGuess; /* Guess at the corresponding utc time */

int cnt = 0; /* Safety to prevent infinite loop */

i64 iErr; /* Guess is off by this much */

computeJD(p);

iGuess = iOrigJD = p->iJD;

iErr = 0;

do{

DateTime new;

memset(&new, 0, sizeof(new));

iGuess -= iErr;

new.iJD = iGuess;

new.validJD = 1;

rc = toLocaltime(&new, pCtx);

if( rc ) return rc;

computeJD(&new);

iErr = new.iJD - iOrigJD;

}while( iErr && cnt++<3 );

memset(p, 0, sizeof(*p));

p->iJD = iGuess;

p->validJD = 1;

p->tzSet = 1;

}

rc = SQLITE_OK;

}

#endif

break;

}

case 'w': {

/*

** weekday N

**

** Move the date to the same time on the next occurrence of

** weekday N where 0==Sunday, 1==Monday, and so forth. If the

** date is already on the appropriate weekday, this is a no-op.

*/

if( sqlite3_strnicmp(z, "weekday ", 8)==0

&& sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0

&& r>=0.0 && r<7.0 && (n=(int)r)==r ){

sqlite3_int64 Z;

computeYMD_HMS(p);

p->validTZ = 0;

p->validJD = 0;

computeJD(p);

Z = ((p->iJD + 129600000)/86400000) % 7;

if( Z>n ) Z -= 7;

p->iJD += (n - Z)*86400000;

clearYMD_HMS_TZ(p);

rc = 0;

}

break;

}

case 's': {

/*

** start of TTTTT

**

** Move the date backwards to the beginning of the current day,

** or month or year.

**

** subsecond

** subsec

**

** Show subsecond precision in the output of datetime() and

** unixepoch() and strftime('%s').

*/

if( sqlite3_strnicmp(z, "start of ", 9)!=0 ){

if( sqlite3_stricmp(z, "subsec")==0

|| sqlite3_stricmp(z, "subsecond")==0

){

p->useSubsec = 1;

rc = 0;

}

break;

}

if( !p->validJD && !p->validYMD && !p->validHMS ) break;

z += 9;

computeYMD(p);

p->validHMS = 1;

p->h = p->m = 0;

p->s = 0.0;

p->rawS = 0;

p->validTZ = 0;

p->validJD = 0;

if( sqlite3_stricmp(z,"month")==0 ){

p->D = 1;

rc = 0;

}else if( sqlite3_stricmp(z,"year")==0 ){

p->M = 1;

p->D = 1;

rc = 0;

}else if( sqlite3_stricmp(z,"day")==0 ){

rc = 0;

}

break;

}

case '+':

case '-':

case '0':

case '1':

case '2':

case '3':

case '4':

case '5':

case '6':

case '7':

case '8':

case '9': {

double rRounder;

int i;

int Y,M,D,h,m,x;

const char *z2 = z;

char z0 = z[0];

for(n=1; z[n]; n++){

if( z[n]==':' ) break;

if( sqlite3Isspace(z[n]) ) break;

if( z[n]=='-' ){

if( n==5 && getDigits(&z[1], "40f", &Y)==1 ) break;

if( n==6 && getDigits(&z[1], "50f", &Y)==1 ) break;

}

}

if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){

assert( rc==1 );

break;

}

if( z[n]=='-' ){

/* A modifier of the form (+|-)YYYY-MM-DD adds or subtracts the

** specified number of years, months, and days. MM is limited to

** the range 0-11 and DD is limited to 0-30.

*/

if( z0!='+' && z0!='-' ) break; /* Must start with +/- */

if( n==5 ){

if( getDigits(&z[1], "40f-20a-20d", &Y, &M, &D)!=3 ) break;

}else{

assert( n==6 );

if( getDigits(&z[1], "50f-20a-20d", &Y, &M, &D)!=3 ) break;

z++;

}

if( M>=12 ) break; /* M range 0..11 */

if( D>=31 ) break; /* D range 0..30 */

computeYMD_HMS(p);

p->validJD = 0;

if( z0=='-' ){

p->Y -= Y;

p->M -= M;

D = -D;

}else{

p->Y += Y;

p->M += M;

}

x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;

p->Y += x;

p->M -= x*12;

computeJD(p);

p->validHMS = 0;

p->validYMD = 0;

p->iJD += (i64)D*86400000;

if( z[11]==0 ){

rc = 0;

break;

}

if( sqlite3Isspace(z[11])

&& getDigits(&z[12], "20c:20e", &h, &m)==2

){

z2 = &z[12];

n = 2;

}else{

break;

}

}

if( z2[n]==':' ){

/* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the

** specified number of hours, minutes, seconds, and fractional seconds

** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be

** omitted.

*/

DateTime tx;

sqlite3_int64 day;

if( !sqlite3Isdigit(*z2) ) z2++;

memset(&tx, 0, sizeof(tx));

if( parseHhMmSs(z2, &tx) ) break;

computeJD(&tx);

tx.iJD -= 43200000;

day = tx.iJD/86400000;

tx.iJD -= day*86400000;

if( z0=='-' ) tx.iJD = -tx.iJD;

computeJD(p);

clearYMD_HMS_TZ(p);

p->iJD += tx.iJD;

rc = 0;

break;

}

/* If control reaches this point, it means the transformation is

** one of the forms like "+NNN days". */

z += n;

while( sqlite3Isspace(*z) ) z++;

n = sqlite3Strlen30(z);

if( n>10 || n<3 ) break;

if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;

computeJD(p);

assert( rc==1 );

rRounder = r<0 ? -0.5 : +0.5;

for(i=0; i<ArraySize(aXformType); i++){

if( aXformType[i].nName==n

&& sqlite3_strnicmp(aXformType[i].zName, z, n)==0

&& r>-aXformType[i].rLimit && r<aXformType[i].rLimit

){

switch( i ){

case 4: { /* Special processing to add months */

assert( strcmp(aXformType[i].zName,"month")==0 );

computeYMD_HMS(p);

p->M += (int)r;

x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;

p->Y += x;

p->M -= x*12;

p->validJD = 0;

r -= (int)r;

break;

}

case 5: { /* Special processing to add years */

int y = (int)r;

assert( strcmp(aXformType[i].zName,"year")==0 );

computeYMD_HMS(p);

p->Y += y;

p->validJD = 0;

r -= (int)r;

break;

}

}

computeJD(p);

p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder);

rc = 0;

break;

}

}

clearYMD_HMS_TZ(p);

break;

}

default: {