1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sql/SQLite/date.c Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,1093 @@
1.4 +/*
1.5 +** 2003 October 31
1.6 +**
1.7 +** The author disclaims copyright to this source code. In place of
1.8 +** a legal notice, here is a blessing:
1.9 +**
1.10 +** May you do good and not evil.
1.11 +** May you find forgiveness for yourself and forgive others.
1.12 +** May you share freely, never taking more than you give.
1.13 +**
1.14 +*************************************************************************
1.15 +** This file contains the C functions that implement date and time
1.16 +** functions for SQLite.
1.17 +**
1.18 +** There is only one exported symbol in this file - the function
1.19 +** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
1.20 +** All other code has file scope.
1.21 +**
1.22 +** $Id: date.c,v 1.87 2008/07/28 19:34:53 drh Exp $
1.23 +**
1.24 +** SQLite processes all times and dates as Julian Day numbers. The
1.25 +** dates and times are stored as the number of days since noon
1.26 +** in Greenwich on November 24, 4714 B.C. according to the Gregorian
1.27 +** calendar system.
1.28 +**
1.29 +** 1970-01-01 00:00:00 is JD 2440587.5
1.30 +** 2000-01-01 00:00:00 is JD 2451544.5
1.31 +**
1.32 +** This implemention requires years to be expressed as a 4-digit number
1.33 +** which means that only dates between 0000-01-01 and 9999-12-31 can
1.34 +** be represented, even though julian day numbers allow a much wider
1.35 +** range of dates.
1.36 +**
1.37 +** The Gregorian calendar system is used for all dates and times,
1.38 +** even those that predate the Gregorian calendar. Historians usually
1.39 +** use the Julian calendar for dates prior to 1582-10-15 and for some
1.40 +** dates afterwards, depending on locale. Beware of this difference.
1.41 +**
1.42 +** The conversion algorithms are implemented based on descriptions
1.43 +** in the following text:
1.44 +**
1.45 +** Jean Meeus
1.46 +** Astronomical Algorithms, 2nd Edition, 1998
1.47 +** ISBM 0-943396-61-1
1.48 +** Willmann-Bell, Inc
1.49 +** Richmond, Virginia (USA)
1.50 +*/
1.51 +#include "sqliteInt.h"
1.52 +#include <ctype.h>
1.53 +#include <stdlib.h>
1.54 +#include <assert.h>
1.55 +#include <time.h>
1.56 +
1.57 +#ifndef SQLITE_OMIT_DATETIME_FUNCS
1.58 +
1.59 +/*
1.60 +** On recent Windows platforms, the localtime_s() function is available
1.61 +** as part of the "Secure CRT". It is essentially equivalent to
1.62 +** localtime_r() available under most POSIX platforms, except that the
1.63 +** order of the parameters is reversed.
1.64 +**
1.65 +** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
1.66 +**
1.67 +** If the user has not indicated to use localtime_r() or localtime_s()
1.68 +** already, check for an MSVC build environment that provides
1.69 +** localtime_s().
1.70 +*/
1.71 +#if !defined(HAVE_LOCALTIME_R) && !defined(HAVE_LOCALTIME_S) && \
1.72 + defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
1.73 +#define HAVE_LOCALTIME_S 1
1.74 +#endif
1.75 +
1.76 +/*
1.77 +** A structure for holding a single date and time.
1.78 +*/
1.79 +typedef struct DateTime DateTime;
1.80 +struct DateTime {
1.81 + sqlite3_int64 iJD; /* The julian day number times 86400000 */
1.82 + int Y, M, D; /* Year, month, and day */
1.83 + int h, m; /* Hour and minutes */
1.84 + int tz; /* Timezone offset in minutes */
1.85 + double s; /* Seconds */
1.86 + char validYMD; /* True if Y,M,D are valid */
1.87 + char validHMS; /* True if h,m,s are valid */
1.88 + char validJD; /* True if iJD is valid */
1.89 + char validTZ; /* True if tz is valid */
1.90 +};
1.91 +
1.92 +
1.93 +/*
1.94 +** Convert zDate into one or more integers. Additional arguments
1.95 +** come in groups of 5 as follows:
1.96 +**
1.97 +** N number of digits in the integer
1.98 +** min minimum allowed value of the integer
1.99 +** max maximum allowed value of the integer
1.100 +** nextC first character after the integer
1.101 +** pVal where to write the integers value.
1.102 +**
1.103 +** Conversions continue until one with nextC==0 is encountered.
1.104 +** The function returns the number of successful conversions.
1.105 +*/
1.106 +static int getDigits(const char *zDate, ...){
1.107 + va_list ap;
1.108 + int val;
1.109 + int N;
1.110 + int min;
1.111 + int max;
1.112 + int nextC;
1.113 + int *pVal;
1.114 + int cnt = 0;
1.115 + va_start(ap, zDate);
1.116 + do{
1.117 + N = va_arg(ap, int);
1.118 + min = va_arg(ap, int);
1.119 + max = va_arg(ap, int);
1.120 + nextC = va_arg(ap, int);
1.121 + pVal = va_arg(ap, int*);
1.122 + val = 0;
1.123 + while( N-- ){
1.124 + if( !isdigit(*(u8*)zDate) ){
1.125 + goto end_getDigits;
1.126 + }
1.127 + val = val*10 + *zDate - '0';
1.128 + zDate++;
1.129 + }
1.130 + if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){
1.131 + goto end_getDigits;
1.132 + }
1.133 + *pVal = val;
1.134 + zDate++;
1.135 + cnt++;
1.136 + }while( nextC );
1.137 +end_getDigits:
1.138 + va_end(ap);
1.139 + return cnt;
1.140 +}
1.141 +
1.142 +/*
1.143 +** Read text from z[] and convert into a floating point number. Return
1.144 +** the number of digits converted.
1.145 +*/
1.146 +#define getValue sqlite3AtoF
1.147 +
1.148 +/*
1.149 +** Parse a timezone extension on the end of a date-time.
1.150 +** The extension is of the form:
1.151 +**
1.152 +** (+/-)HH:MM
1.153 +**
1.154 +** Or the "zulu" notation:
1.155 +**
1.156 +** Z
1.157 +**
1.158 +** If the parse is successful, write the number of minutes
1.159 +** of change in p->tz and return 0. If a parser error occurs,
1.160 +** return non-zero.
1.161 +**
1.162 +** A missing specifier is not considered an error.
1.163 +*/
1.164 +static int parseTimezone(const char *zDate, DateTime *p){
1.165 + int sgn = 0;
1.166 + int nHr, nMn;
1.167 + int c;
1.168 + while( isspace(*(u8*)zDate) ){ zDate++; }
1.169 + p->tz = 0;
1.170 + c = *zDate;
1.171 + if( c=='-' ){
1.172 + sgn = -1;
1.173 + }else if( c=='+' ){
1.174 + sgn = +1;
1.175 + }else if( c=='Z' || c=='z' ){
1.176 + zDate++;
1.177 + goto zulu_time;
1.178 + }else{
1.179 + return c!=0;
1.180 + }
1.181 + zDate++;
1.182 + if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){
1.183 + return 1;
1.184 + }
1.185 + zDate += 5;
1.186 + p->tz = sgn*(nMn + nHr*60);
1.187 +zulu_time:
1.188 + while( isspace(*(u8*)zDate) ){ zDate++; }
1.189 + return *zDate!=0;
1.190 +}
1.191 +
1.192 +/*
1.193 +** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
1.194 +** The HH, MM, and SS must each be exactly 2 digits. The
1.195 +** fractional seconds FFFF can be one or more digits.
1.196 +**
1.197 +** Return 1 if there is a parsing error and 0 on success.
1.198 +*/
1.199 +static int parseHhMmSs(const char *zDate, DateTime *p){
1.200 + int h, m, s;
1.201 + double ms = 0.0;
1.202 + if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){
1.203 + return 1;
1.204 + }
1.205 + zDate += 5;
1.206 + if( *zDate==':' ){
1.207 + zDate++;
1.208 + if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
1.209 + return 1;
1.210 + }
1.211 + zDate += 2;
1.212 + if( *zDate=='.' && isdigit((u8)zDate[1]) ){
1.213 + double rScale = 1.0;
1.214 + zDate++;
1.215 + while( isdigit(*(u8*)zDate) ){
1.216 + ms = ms*10.0 + *zDate - '0';
1.217 + rScale *= 10.0;
1.218 + zDate++;
1.219 + }
1.220 + ms /= rScale;
1.221 + }
1.222 + }else{
1.223 + s = 0;
1.224 + }
1.225 + p->validJD = 0;
1.226 + p->validHMS = 1;
1.227 + p->h = h;
1.228 + p->m = m;
1.229 + p->s = s + ms;
1.230 + if( parseTimezone(zDate, p) ) return 1;
1.231 + p->validTZ = p->tz!=0;
1.232 + return 0;
1.233 +}
1.234 +
1.235 +/*
1.236 +** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume
1.237 +** that the YYYY-MM-DD is according to the Gregorian calendar.
1.238 +**
1.239 +** Reference: Meeus page 61
1.240 +*/
1.241 +static void computeJD(DateTime *p){
1.242 + int Y, M, D, A, B, X1, X2;
1.243 +
1.244 + if( p->validJD ) return;
1.245 + if( p->validYMD ){
1.246 + Y = p->Y;
1.247 + M = p->M;
1.248 + D = p->D;
1.249 + }else{
1.250 + Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */
1.251 + M = 1;
1.252 + D = 1;
1.253 + }
1.254 + if( M<=2 ){
1.255 + Y--;
1.256 + M += 12;
1.257 + }
1.258 + A = Y/100;
1.259 + B = 2 - A + (A/4);
1.260 + X1 = 365.25*(Y+4716);
1.261 + X2 = 30.6001*(M+1);
1.262 + p->iJD = (X1 + X2 + D + B - 1524.5)*86400000;
1.263 + p->validJD = 1;
1.264 + if( p->validHMS ){
1.265 + p->iJD += p->h*3600000 + p->m*60000 + p->s*1000;
1.266 + if( p->validTZ ){
1.267 + p->iJD -= p->tz*60000;
1.268 + p->validYMD = 0;
1.269 + p->validHMS = 0;
1.270 + p->validTZ = 0;
1.271 + }
1.272 + }
1.273 +}
1.274 +
1.275 +/*
1.276 +** Parse dates of the form
1.277 +**
1.278 +** YYYY-MM-DD HH:MM:SS.FFF
1.279 +** YYYY-MM-DD HH:MM:SS
1.280 +** YYYY-MM-DD HH:MM
1.281 +** YYYY-MM-DD
1.282 +**
1.283 +** Write the result into the DateTime structure and return 0
1.284 +** on success and 1 if the input string is not a well-formed
1.285 +** date.
1.286 +*/
1.287 +static int parseYyyyMmDd(const char *zDate, DateTime *p){
1.288 + int Y, M, D, neg;
1.289 +
1.290 + if( zDate[0]=='-' ){
1.291 + zDate++;
1.292 + neg = 1;
1.293 + }else{
1.294 + neg = 0;
1.295 + }
1.296 + if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){
1.297 + return 1;
1.298 + }
1.299 + zDate += 10;
1.300 + while( isspace(*(u8*)zDate) || 'T'==*(u8*)zDate ){ zDate++; }
1.301 + if( parseHhMmSs(zDate, p)==0 ){
1.302 + /* We got the time */
1.303 + }else if( *zDate==0 ){
1.304 + p->validHMS = 0;
1.305 + }else{
1.306 + return 1;
1.307 + }
1.308 + p->validJD = 0;
1.309 + p->validYMD = 1;
1.310 + p->Y = neg ? -Y : Y;
1.311 + p->M = M;
1.312 + p->D = D;
1.313 + if( p->validTZ ){
1.314 + computeJD(p);
1.315 + }
1.316 + return 0;
1.317 +}
1.318 +
1.319 +/*
1.320 +** Set the time to the current time reported by the VFS
1.321 +*/
1.322 +static void setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
1.323 + double r;
1.324 + sqlite3 *db = sqlite3_context_db_handle(context);
1.325 + sqlite3OsCurrentTime(db->pVfs, &r);
1.326 + p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
1.327 + p->validJD = 1;
1.328 +}
1.329 +
1.330 +/*
1.331 +** Attempt to parse the given string into a Julian Day Number. Return
1.332 +** the number of errors.
1.333 +**
1.334 +** The following are acceptable forms for the input string:
1.335 +**
1.336 +** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM
1.337 +** DDDD.DD
1.338 +** now
1.339 +**
1.340 +** In the first form, the +/-HH:MM is always optional. The fractional
1.341 +** seconds extension (the ".FFF") is optional. The seconds portion
1.342 +** (":SS.FFF") is option. The year and date can be omitted as long
1.343 +** as there is a time string. The time string can be omitted as long
1.344 +** as there is a year and date.
1.345 +*/
1.346 +static int parseDateOrTime(
1.347 + sqlite3_context *context,
1.348 + const char *zDate,
1.349 + DateTime *p
1.350 +){
1.351 + if( parseYyyyMmDd(zDate,p)==0 ){
1.352 + return 0;
1.353 + }else if( parseHhMmSs(zDate, p)==0 ){
1.354 + return 0;
1.355 + }else if( sqlite3StrICmp(zDate,"now")==0){
1.356 + setDateTimeToCurrent(context, p);
1.357 + return 0;
1.358 + }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){
1.359 + double r;
1.360 + getValue(zDate, &r);
1.361 + p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
1.362 + p->validJD = 1;
1.363 + return 0;
1.364 + }
1.365 + return 1;
1.366 +}
1.367 +
1.368 +/*
1.369 +** Compute the Year, Month, and Day from the julian day number.
1.370 +*/
1.371 +static void computeYMD(DateTime *p){
1.372 + int Z, A, B, C, D, E, X1;
1.373 + if( p->validYMD ) return;
1.374 + if( !p->validJD ){
1.375 + p->Y = 2000;
1.376 + p->M = 1;
1.377 + p->D = 1;
1.378 + }else{
1.379 + Z = (p->iJD + 43200000)/86400000;
1.380 + A = (Z - 1867216.25)/36524.25;
1.381 + A = Z + 1 + A - (A/4);
1.382 + B = A + 1524;
1.383 + C = (B - 122.1)/365.25;
1.384 + D = 365.25*C;
1.385 + E = (B-D)/30.6001;
1.386 + X1 = 30.6001*E;
1.387 + p->D = B - D - X1;
1.388 + p->M = E<14 ? E-1 : E-13;
1.389 + p->Y = p->M>2 ? C - 4716 : C - 4715;
1.390 + }
1.391 + p->validYMD = 1;
1.392 +}
1.393 +
1.394 +/*
1.395 +** Compute the Hour, Minute, and Seconds from the julian day number.
1.396 +*/
1.397 +static void computeHMS(DateTime *p){
1.398 + int s;
1.399 + if( p->validHMS ) return;
1.400 + computeJD(p);
1.401 + s = (p->iJD + 43200000) % 86400000;
1.402 + p->s = s/1000.0;
1.403 + s = p->s;
1.404 + p->s -= s;
1.405 + p->h = s/3600;
1.406 + s -= p->h*3600;
1.407 + p->m = s/60;
1.408 + p->s += s - p->m*60;
1.409 + p->validHMS = 1;
1.410 +}
1.411 +
1.412 +/*
1.413 +** Compute both YMD and HMS
1.414 +*/
1.415 +static void computeYMD_HMS(DateTime *p){
1.416 + computeYMD(p);
1.417 + computeHMS(p);
1.418 +}
1.419 +
1.420 +/*
1.421 +** Clear the YMD and HMS and the TZ
1.422 +*/
1.423 +static void clearYMD_HMS_TZ(DateTime *p){
1.424 + p->validYMD = 0;
1.425 + p->validHMS = 0;
1.426 + p->validTZ = 0;
1.427 +}
1.428 +
1.429 +#ifndef SQLITE_OMIT_LOCALTIME
1.430 +/*
1.431 +** Compute the difference (in milliseconds)
1.432 +** between localtime and UTC (a.k.a. GMT)
1.433 +** for the time value p where p is in UTC.
1.434 +*/
1.435 +static int localtimeOffset(DateTime *p){
1.436 + DateTime x, y;
1.437 + time_t t;
1.438 + x = *p;
1.439 + computeYMD_HMS(&x);
1.440 + if( x.Y<1971 || x.Y>=2038 ){
1.441 + x.Y = 2000;
1.442 + x.M = 1;
1.443 + x.D = 1;
1.444 + x.h = 0;
1.445 + x.m = 0;
1.446 + x.s = 0.0;
1.447 + } else {
1.448 + int s = x.s + 0.5;
1.449 + x.s = s;
1.450 + }
1.451 + x.tz = 0;
1.452 + x.validJD = 0;
1.453 + computeJD(&x);
1.454 + t = x.iJD/1000 - 2440587.5*86400.0;
1.455 +#ifdef HAVE_LOCALTIME_R
1.456 + {
1.457 + struct tm sLocal;
1.458 + localtime_r(&t, &sLocal);
1.459 + y.Y = sLocal.tm_year + 1900;
1.460 + y.M = sLocal.tm_mon + 1;
1.461 + y.D = sLocal.tm_mday;
1.462 + y.h = sLocal.tm_hour;
1.463 + y.m = sLocal.tm_min;
1.464 + y.s = sLocal.tm_sec;
1.465 + }
1.466 +#elif defined(HAVE_LOCALTIME_S)
1.467 + {
1.468 + struct tm sLocal;
1.469 + localtime_s(&sLocal, &t);
1.470 + y.Y = sLocal.tm_year + 1900;
1.471 + y.M = sLocal.tm_mon + 1;
1.472 + y.D = sLocal.tm_mday;
1.473 + y.h = sLocal.tm_hour;
1.474 + y.m = sLocal.tm_min;
1.475 + y.s = sLocal.tm_sec;
1.476 + }
1.477 +#else
1.478 + {
1.479 + struct tm *pTm;
1.480 + sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1.481 + pTm = localtime(&t);
1.482 + y.Y = pTm->tm_year + 1900;
1.483 + y.M = pTm->tm_mon + 1;
1.484 + y.D = pTm->tm_mday;
1.485 + y.h = pTm->tm_hour;
1.486 + y.m = pTm->tm_min;
1.487 + y.s = pTm->tm_sec;
1.488 + sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1.489 + }
1.490 +#endif
1.491 + y.validYMD = 1;
1.492 + y.validHMS = 1;
1.493 + y.validJD = 0;
1.494 + y.validTZ = 0;
1.495 + computeJD(&y);
1.496 + return y.iJD - x.iJD;
1.497 +}
1.498 +#endif /* SQLITE_OMIT_LOCALTIME */
1.499 +
1.500 +/*
1.501 +** Process a modifier to a date-time stamp. The modifiers are
1.502 +** as follows:
1.503 +**
1.504 +** NNN days
1.505 +** NNN hours
1.506 +** NNN minutes
1.507 +** NNN.NNNN seconds
1.508 +** NNN months
1.509 +** NNN years
1.510 +** start of month
1.511 +** start of year
1.512 +** start of week
1.513 +** start of day
1.514 +** weekday N
1.515 +** unixepoch
1.516 +** localtime
1.517 +** utc
1.518 +**
1.519 +** Return 0 on success and 1 if there is any kind of error.
1.520 +*/
1.521 +static int parseModifier(const char *zMod, DateTime *p){
1.522 + int rc = 1;
1.523 + int n;
1.524 + double r;
1.525 + char *z, zBuf[30];
1.526 + z = zBuf;
1.527 + for(n=0; n<sizeof(zBuf)-1 && zMod[n]; n++){
1.528 + z[n] = tolower(zMod[n]);
1.529 + }
1.530 + z[n] = 0;
1.531 + switch( z[0] ){
1.532 +#ifndef SQLITE_OMIT_LOCALTIME
1.533 + case 'l': {
1.534 + /* localtime
1.535 + **
1.536 + ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
1.537 + ** show local time.
1.538 + */
1.539 + if( strcmp(z, "localtime")==0 ){
1.540 + computeJD(p);
1.541 + p->iJD += localtimeOffset(p);
1.542 + clearYMD_HMS_TZ(p);
1.543 + rc = 0;
1.544 + }
1.545 + break;
1.546 + }
1.547 +#endif
1.548 + case 'u': {
1.549 + /*
1.550 + ** unixepoch
1.551 + **
1.552 + ** Treat the current value of p->iJD as the number of
1.553 + ** seconds since 1970. Convert to a real julian day number.
1.554 + */
1.555 + if( strcmp(z, "unixepoch")==0 && p->validJD ){
1.556 + p->iJD = p->iJD/86400.0 + 2440587.5*86400000.0;
1.557 + clearYMD_HMS_TZ(p);
1.558 + rc = 0;
1.559 + }
1.560 +#ifndef SQLITE_OMIT_LOCALTIME
1.561 + else if( strcmp(z, "utc")==0 ){
1.562 + double c1;
1.563 + computeJD(p);
1.564 + c1 = localtimeOffset(p);
1.565 + p->iJD -= c1;
1.566 + clearYMD_HMS_TZ(p);
1.567 + p->iJD += c1 - localtimeOffset(p);
1.568 + rc = 0;
1.569 + }
1.570 +#endif
1.571 + break;
1.572 + }
1.573 + case 'w': {
1.574 + /*
1.575 + ** weekday N
1.576 + **
1.577 + ** Move the date to the same time on the next occurrence of
1.578 + ** weekday N where 0==Sunday, 1==Monday, and so forth. If the
1.579 + ** date is already on the appropriate weekday, this is a no-op.
1.580 + */
1.581 + if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0
1.582 + && (n=r)==r && n>=0 && r<7 ){
1.583 + sqlite3_int64 Z;
1.584 + computeYMD_HMS(p);
1.585 + p->validTZ = 0;
1.586 + p->validJD = 0;
1.587 + computeJD(p);
1.588 + Z = ((p->iJD + 129600000)/86400000) % 7;
1.589 + if( Z>n ) Z -= 7;
1.590 + p->iJD += (n - Z)*86400000;
1.591 + clearYMD_HMS_TZ(p);
1.592 + rc = 0;
1.593 + }
1.594 + break;
1.595 + }
1.596 + case 's': {
1.597 + /*
1.598 + ** start of TTTTT
1.599 + **
1.600 + ** Move the date backwards to the beginning of the current day,
1.601 + ** or month or year.
1.602 + */
1.603 + if( strncmp(z, "start of ", 9)!=0 ) break;
1.604 + z += 9;
1.605 + computeYMD(p);
1.606 + p->validHMS = 1;
1.607 + p->h = p->m = 0;
1.608 + p->s = 0.0;
1.609 + p->validTZ = 0;
1.610 + p->validJD = 0;
1.611 + if( strcmp(z,"month")==0 ){
1.612 + p->D = 1;
1.613 + rc = 0;
1.614 + }else if( strcmp(z,"year")==0 ){
1.615 + computeYMD(p);
1.616 + p->M = 1;
1.617 + p->D = 1;
1.618 + rc = 0;
1.619 + }else if( strcmp(z,"day")==0 ){
1.620 + rc = 0;
1.621 + }
1.622 + break;
1.623 + }
1.624 + case '+':
1.625 + case '-':
1.626 + case '0':
1.627 + case '1':
1.628 + case '2':
1.629 + case '3':
1.630 + case '4':
1.631 + case '5':
1.632 + case '6':
1.633 + case '7':
1.634 + case '8':
1.635 + case '9': {
1.636 + n = getValue(z, &r);
1.637 + assert( n>=1 );
1.638 + if( z[n]==':' ){
1.639 + /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
1.640 + ** specified number of hours, minutes, seconds, and fractional seconds
1.641 + ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be
1.642 + ** omitted.
1.643 + */
1.644 + const char *z2 = z;
1.645 + DateTime tx;
1.646 + sqlite3_int64 day;
1.647 + if( !isdigit(*(u8*)z2) ) z2++;
1.648 + memset(&tx, 0, sizeof(tx));
1.649 + if( parseHhMmSs(z2, &tx) ) break;
1.650 + computeJD(&tx);
1.651 + tx.iJD -= 43200000;
1.652 + day = tx.iJD/86400000;
1.653 + tx.iJD -= day*86400000;
1.654 + if( z[0]=='-' ) tx.iJD = -tx.iJD;
1.655 + computeJD(p);
1.656 + clearYMD_HMS_TZ(p);
1.657 + p->iJD += tx.iJD;
1.658 + rc = 0;
1.659 + break;
1.660 + }
1.661 + z += n;
1.662 + while( isspace(*(u8*)z) ) z++;
1.663 + n = strlen(z);
1.664 + if( n>10 || n<3 ) break;
1.665 + if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
1.666 + computeJD(p);
1.667 + rc = 0;
1.668 + if( n==3 && strcmp(z,"day")==0 ){
1.669 + p->iJD += r*86400000.0 + 0.5;
1.670 + }else if( n==4 && strcmp(z,"hour")==0 ){
1.671 + p->iJD += r*(86400000.0/24.0) + 0.5;
1.672 + }else if( n==6 && strcmp(z,"minute")==0 ){
1.673 + p->iJD += r*(86400000.0/(24.0*60.0)) + 0.5;
1.674 + }else if( n==6 && strcmp(z,"second")==0 ){
1.675 + p->iJD += r*(86400000.0/(24.0*60.0*60.0)) + 0.5;
1.676 + }else if( n==5 && strcmp(z,"month")==0 ){
1.677 + int x, y;
1.678 + computeYMD_HMS(p);
1.679 + p->M += r;
1.680 + x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
1.681 + p->Y += x;
1.682 + p->M -= x*12;
1.683 + p->validJD = 0;
1.684 + computeJD(p);
1.685 + y = r;
1.686 + if( y!=r ){
1.687 + p->iJD += (r - y)*30.0*86400000.0 + 0.5;
1.688 + }
1.689 + }else if( n==4 && strcmp(z,"year")==0 ){
1.690 + computeYMD_HMS(p);
1.691 + p->Y += r;
1.692 + p->validJD = 0;
1.693 + computeJD(p);
1.694 + }else{
1.695 + rc = 1;
1.696 + }
1.697 + clearYMD_HMS_TZ(p);
1.698 + break;
1.699 + }
1.700 + default: {
1.701 + break;
1.702 + }
1.703 + }
1.704 + return rc;
1.705 +}
1.706 +
1.707 +/*
1.708 +** Process time function arguments. argv[0] is a date-time stamp.
1.709 +** argv[1] and following are modifiers. Parse them all and write
1.710 +** the resulting time into the DateTime structure p. Return 0
1.711 +** on success and 1 if there are any errors.
1.712 +**
1.713 +** If there are zero parameters (if even argv[0] is undefined)
1.714 +** then assume a default value of "now" for argv[0].
1.715 +*/
1.716 +static int isDate(
1.717 + sqlite3_context *context,
1.718 + int argc,
1.719 + sqlite3_value **argv,
1.720 + DateTime *p
1.721 +){
1.722 + int i;
1.723 + const unsigned char *z;
1.724 + int eType;
1.725 + memset(p, 0, sizeof(*p));
1.726 + if( argc==0 ){
1.727 + setDateTimeToCurrent(context, p);
1.728 + }else if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
1.729 + || eType==SQLITE_INTEGER ){
1.730 + p->iJD = sqlite3_value_double(argv[0])*86400000.0 + 0.5;
1.731 + p->validJD = 1;
1.732 + }else{
1.733 + z = sqlite3_value_text(argv[0]);
1.734 + if( !z || parseDateOrTime(context, (char*)z, p) ){
1.735 + return 1;
1.736 + }
1.737 + }
1.738 + for(i=1; i<argc; i++){
1.739 + if( (z = sqlite3_value_text(argv[i]))==0 || parseModifier((char*)z, p) ){
1.740 + return 1;
1.741 + }
1.742 + }
1.743 + return 0;
1.744 +}
1.745 +
1.746 +
1.747 +/*
1.748 +** The following routines implement the various date and time functions
1.749 +** of SQLite.
1.750 +*/
1.751 +
1.752 +/*
1.753 +** julianday( TIMESTRING, MOD, MOD, ...)
1.754 +**
1.755 +** Return the julian day number of the date specified in the arguments
1.756 +*/
1.757 +static void juliandayFunc(
1.758 + sqlite3_context *context,
1.759 + int argc,
1.760 + sqlite3_value **argv
1.761 +){
1.762 + DateTime x;
1.763 + if( isDate(context, argc, argv, &x)==0 ){
1.764 + computeJD(&x);
1.765 + sqlite3_result_double(context, x.iJD/86400000.0);
1.766 + }
1.767 +}
1.768 +
1.769 +/*
1.770 +** datetime( TIMESTRING, MOD, MOD, ...)
1.771 +**
1.772 +** Return YYYY-MM-DD HH:MM:SS
1.773 +*/
1.774 +static void datetimeFunc(
1.775 + sqlite3_context *context,
1.776 + int argc,
1.777 + sqlite3_value **argv
1.778 +){
1.779 + DateTime x;
1.780 + if( isDate(context, argc, argv, &x)==0 ){
1.781 + char zBuf[100];
1.782 + computeYMD_HMS(&x);
1.783 + sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d",
1.784 + x.Y, x.M, x.D, x.h, x.m, (int)(x.s));
1.785 + sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
1.786 + }
1.787 +}
1.788 +
1.789 +/*
1.790 +** time( TIMESTRING, MOD, MOD, ...)
1.791 +**
1.792 +** Return HH:MM:SS
1.793 +*/
1.794 +static void timeFunc(
1.795 + sqlite3_context *context,
1.796 + int argc,
1.797 + sqlite3_value **argv
1.798 +){
1.799 + DateTime x;
1.800 + if( isDate(context, argc, argv, &x)==0 ){
1.801 + char zBuf[100];
1.802 + computeHMS(&x);
1.803 + sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
1.804 + sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
1.805 + }
1.806 +}
1.807 +
1.808 +/*
1.809 +** date( TIMESTRING, MOD, MOD, ...)
1.810 +**
1.811 +** Return YYYY-MM-DD
1.812 +*/
1.813 +static void dateFunc(
1.814 + sqlite3_context *context,
1.815 + int argc,
1.816 + sqlite3_value **argv
1.817 +){
1.818 + DateTime x;
1.819 + if( isDate(context, argc, argv, &x)==0 ){
1.820 + char zBuf[100];
1.821 + computeYMD(&x);
1.822 + sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
1.823 + sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
1.824 + }
1.825 +}
1.826 +
1.827 +/*
1.828 +** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
1.829 +**
1.830 +** Return a string described by FORMAT. Conversions as follows:
1.831 +**
1.832 +** %d day of month
1.833 +** %f ** fractional seconds SS.SSS
1.834 +** %H hour 00-24
1.835 +** %j day of year 000-366
1.836 +** %J ** Julian day number
1.837 +** %m month 01-12
1.838 +** %M minute 00-59
1.839 +** %s seconds since 1970-01-01
1.840 +** %S seconds 00-59
1.841 +** %w day of week 0-6 sunday==0
1.842 +** %W week of year 00-53
1.843 +** %Y year 0000-9999
1.844 +** %% %
1.845 +*/
1.846 +static void strftimeFunc(
1.847 + sqlite3_context *context,
1.848 + int argc,
1.849 + sqlite3_value **argv
1.850 +){
1.851 + DateTime x;
1.852 + u64 n;
1.853 + int i, j;
1.854 + char *z;
1.855 + sqlite3 *db;
1.856 + const char *zFmt = (const char*)sqlite3_value_text(argv[0]);
1.857 + char zBuf[100];
1.858 + if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
1.859 + db = sqlite3_context_db_handle(context);
1.860 + for(i=0, n=1; zFmt[i]; i++, n++){
1.861 + if( zFmt[i]=='%' ){
1.862 + switch( zFmt[i+1] ){
1.863 + case 'd':
1.864 + case 'H':
1.865 + case 'm':
1.866 + case 'M':
1.867 + case 'S':
1.868 + case 'W':
1.869 + n++;
1.870 + /* fall thru */
1.871 + case 'w':
1.872 + case '%':
1.873 + break;
1.874 + case 'f':
1.875 + n += 8;
1.876 + break;
1.877 + case 'j':
1.878 + n += 3;
1.879 + break;
1.880 + case 'Y':
1.881 + n += 8;
1.882 + break;
1.883 + case 's':
1.884 + case 'J':
1.885 + n += 50;
1.886 + break;
1.887 + default:
1.888 + return; /* ERROR. return a NULL */
1.889 + }
1.890 + i++;
1.891 + }
1.892 + }
1.893 + if( n<sizeof(zBuf) ){
1.894 + z = zBuf;
1.895 + }else if( n>db->aLimit[SQLITE_LIMIT_LENGTH] ){
1.896 + sqlite3_result_error_toobig(context);
1.897 + return;
1.898 + }else{
1.899 + z = sqlite3DbMallocRaw(db, n);
1.900 + if( z==0 ){
1.901 + sqlite3_result_error_nomem(context);
1.902 + return;
1.903 + }
1.904 + }
1.905 + computeJD(&x);
1.906 + computeYMD_HMS(&x);
1.907 + for(i=j=0; zFmt[i]; i++){
1.908 + if( zFmt[i]!='%' ){
1.909 + z[j++] = zFmt[i];
1.910 + }else{
1.911 + i++;
1.912 + switch( zFmt[i] ){
1.913 + case 'd': sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break;
1.914 + case 'f': {
1.915 + double s = x.s;
1.916 + if( s>59.999 ) s = 59.999;
1.917 + sqlite3_snprintf(7, &z[j],"%06.3f", s);
1.918 + j += strlen(&z[j]);
1.919 + break;
1.920 + }
1.921 + case 'H': sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break;
1.922 + case 'W': /* Fall thru */
1.923 + case 'j': {
1.924 + int nDay; /* Number of days since 1st day of year */
1.925 + DateTime y = x;
1.926 + y.validJD = 0;
1.927 + y.M = 1;
1.928 + y.D = 1;
1.929 + computeJD(&y);
1.930 + nDay = (x.iJD - y.iJD)/86400000.0 + 0.5;
1.931 + if( zFmt[i]=='W' ){
1.932 + int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */
1.933 + wd = ((x.iJD+43200000)/86400000) % 7;
1.934 + sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);
1.935 + j += 2;
1.936 + }else{
1.937 + sqlite3_snprintf(4, &z[j],"%03d",nDay+1);
1.938 + j += 3;
1.939 + }
1.940 + break;
1.941 + }
1.942 + case 'J': {
1.943 + sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0);
1.944 + j+=strlen(&z[j]);
1.945 + break;
1.946 + }
1.947 + case 'm': sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;
1.948 + case 'M': sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;
1.949 + case 's': {
1.950 + sqlite3_snprintf(30,&z[j],"%d",
1.951 + (int)(x.iJD/1000.0 - 210866760000.0));
1.952 + j += strlen(&z[j]);
1.953 + break;
1.954 + }
1.955 + case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
1.956 + case 'w': z[j++] = (((x.iJD+129600000)/86400000) % 7) + '0'; break;
1.957 + case 'Y': sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=strlen(&z[j]);break;
1.958 + default: z[j++] = '%'; break;
1.959 + }
1.960 + }
1.961 + }
1.962 + z[j] = 0;
1.963 + sqlite3_result_text(context, z, -1,
1.964 + z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC);
1.965 +}
1.966 +
1.967 +/*
1.968 +** current_time()
1.969 +**
1.970 +** This function returns the same value as time('now').
1.971 +*/
1.972 +static void ctimeFunc(
1.973 + sqlite3_context *context,
1.974 + int argc,
1.975 + sqlite3_value **argv
1.976 +){
1.977 + timeFunc(context, 0, 0);
1.978 +}
1.979 +
1.980 +/*
1.981 +** current_date()
1.982 +**
1.983 +** This function returns the same value as date('now').
1.984 +*/
1.985 +static void cdateFunc(
1.986 + sqlite3_context *context,
1.987 + int argc,
1.988 + sqlite3_value **argv
1.989 +){
1.990 + dateFunc(context, 0, 0);
1.991 +}
1.992 +
1.993 +/*
1.994 +** current_timestamp()
1.995 +**
1.996 +** This function returns the same value as datetime('now').
1.997 +*/
1.998 +static void ctimestampFunc(
1.999 + sqlite3_context *context,
1.1000 + int argc,
1.1001 + sqlite3_value **argv
1.1002 +){
1.1003 + datetimeFunc(context, 0, 0);
1.1004 +}
1.1005 +#endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
1.1006 +
1.1007 +#ifdef SQLITE_OMIT_DATETIME_FUNCS
1.1008 +/*
1.1009 +** If the library is compiled to omit the full-scale date and time
1.1010 +** handling (to get a smaller binary), the following minimal version
1.1011 +** of the functions current_time(), current_date() and current_timestamp()
1.1012 +** are included instead. This is to support column declarations that
1.1013 +** include "DEFAULT CURRENT_TIME" etc.
1.1014 +**
1.1015 +** This function uses the C-library functions time(), gmtime()
1.1016 +** and strftime(). The format string to pass to strftime() is supplied
1.1017 +** as the user-data for the function.
1.1018 +*/
1.1019 +static void currentTimeFunc(
1.1020 + sqlite3_context *context,
1.1021 + int argc,
1.1022 + sqlite3_value **argv
1.1023 +){
1.1024 + time_t t;
1.1025 + char *zFormat = (char *)sqlite3_user_data(context);
1.1026 + sqlite3 *db;
1.1027 + double rT;
1.1028 + char zBuf[20];
1.1029 +
1.1030 + db = sqlite3_context_db_handle(context);
1.1031 + sqlite3OsCurrentTime(db->pVfs, &rT);
1.1032 + t = 86400.0*(rT - 2440587.5) + 0.5;
1.1033 +#ifdef HAVE_GMTIME_R
1.1034 + {
1.1035 + struct tm sNow;
1.1036 + gmtime_r(&t, &sNow);
1.1037 + strftime(zBuf, 20, zFormat, &sNow);
1.1038 + }
1.1039 +#else
1.1040 + {
1.1041 + struct tm *pTm;
1.1042 + sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1.1043 + pTm = gmtime(&t);
1.1044 + strftime(zBuf, 20, zFormat, pTm);
1.1045 + sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1.1046 + }
1.1047 +#endif
1.1048 +
1.1049 + sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
1.1050 +}
1.1051 +#endif
1.1052 +
1.1053 +/*
1.1054 +** This function registered all of the above C functions as SQL
1.1055 +** functions. This should be the only routine in this file with
1.1056 +** external linkage.
1.1057 +*/
1.1058 +void sqlite3RegisterDateTimeFunctions(sqlite3 *db){
1.1059 +#ifndef SQLITE_OMIT_DATETIME_FUNCS
1.1060 + static const struct {
1.1061 + char *zName;
1.1062 + int nArg;
1.1063 + void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
1.1064 + } aFuncs[] = {
1.1065 + { "julianday", -1, juliandayFunc },
1.1066 + { "date", -1, dateFunc },
1.1067 + { "time", -1, timeFunc },
1.1068 + { "datetime", -1, datetimeFunc },
1.1069 + { "strftime", -1, strftimeFunc },
1.1070 + { "current_time", 0, ctimeFunc },
1.1071 + { "current_timestamp", 0, ctimestampFunc },
1.1072 + { "current_date", 0, cdateFunc },
1.1073 + };
1.1074 + int i;
1.1075 +
1.1076 + for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
1.1077 + sqlite3CreateFunc(db, aFuncs[i].zName, aFuncs[i].nArg,
1.1078 + SQLITE_UTF8, 0, aFuncs[i].xFunc, 0, 0);
1.1079 + }
1.1080 +#else
1.1081 + static const struct {
1.1082 + char *zName;
1.1083 + char *zFormat;
1.1084 + } aFuncs[] = {
1.1085 + { "current_time", "%H:%M:%S" },
1.1086 + { "current_date", "%Y-%m-%d" },
1.1087 + { "current_timestamp", "%Y-%m-%d %H:%M:%S" }
1.1088 + };
1.1089 + int i;
1.1090 +
1.1091 + for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
1.1092 + sqlite3CreateFunc(db, aFuncs[i].zName, 0, SQLITE_UTF8,
1.1093 + aFuncs[i].zFormat, currentTimeFunc, 0, 0);
1.1094 + }
1.1095 +#endif
1.1096 +}