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