Simplifications to the implementation of the sum() SQL function.
[sqlite.git] / src / date.c
blob313c7f913727b36b4616aa15140ec324dd4b61bd
1 /*
2 ** 2003 October 31
3 **
4 ** The author disclaims copyright to this source code. In place of
5 ** 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.
11 *************************************************************************
12 ** This file contains the C functions that implement date and time
13 ** functions for SQLite.
15 ** There is only one exported symbol in this file - the function
16 ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
17 ** All other code has file scope.
19 ** SQLite processes all times and dates as julian day numbers. The
20 ** dates and times are stored as the number of days since noon
21 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian
22 ** calendar system.
24 ** 1970-01-01 00:00:00 is JD 2440587.5
25 ** 2000-01-01 00:00:00 is JD 2451544.5
27 ** This implementation requires years to be expressed as a 4-digit number
28 ** which means that only dates between 0000-01-01 and 9999-12-31 can
29 ** be represented, even though julian day numbers allow a much wider
30 ** range of dates.
32 ** The Gregorian calendar system is used for all dates and times,
33 ** even those that predate the Gregorian calendar. Historians usually
34 ** use the julian calendar for dates prior to 1582-10-15 and for some
35 ** dates afterwards, depending on locale. Beware of this difference.
37 ** The conversion algorithms are implemented based on descriptions
38 ** in the following text:
40 ** Jean Meeus
41 ** Astronomical Algorithms, 2nd Edition, 1998
42 ** ISBN 0-943396-61-1
43 ** Willmann-Bell, Inc
44 ** Richmond, Virginia (USA)
46 #include "sqliteInt.h"
47 #include <stdlib.h>
48 #include <assert.h>
49 #include <time.h>
51 #ifndef SQLITE_OMIT_DATETIME_FUNCS
54 ** The MSVC CRT on Windows CE may not have a localtime() function.
55 ** So declare a substitute. The substitute function itself is
56 ** defined in "os_win.c".
58 #if !defined(SQLITE_OMIT_LOCALTIME) && defined(_WIN32_WCE) && \
59 (!defined(SQLITE_MSVC_LOCALTIME_API) || !SQLITE_MSVC_LOCALTIME_API)
60 struct tm *__cdecl localtime(const time_t *);
61 #endif
64 ** A structure for holding a single date and time.
66 typedef struct DateTime DateTime;
67 struct 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 rawS; /* Raw numeric value stored in s */
75 char validYMD; /* True (1) if Y,M,D are valid */
76 char validHMS; /* True (1) if h,m,s are valid */
77 char validTZ; /* True (1) if tz is valid */
78 char tzSet; /* Timezone was set explicitly */
79 char isError; /* An overflow has occurred */
84 ** Convert zDate into one or more integers according to the conversion
85 ** specifier zFormat.
87 ** zFormat[] contains 4 characters for each integer converted, except for
88 ** the last integer which is specified by three characters. The meaning
89 ** of a four-character format specifiers ABCD is:
91 ** A: number of digits to convert. Always "2" or "4".
92 ** B: minimum value. Always "0" or "1".
93 ** C: maximum value, decoded as:
94 ** a: 12
95 ** b: 14
96 ** c: 24
97 ** d: 31
98 ** e: 59
99 ** f: 9999
100 ** D: the separator character, or \000 to indicate this is the
101 ** last number to convert.
103 ** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would
104 ** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-".
105 ** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates
106 ** the 2-digit day which is the last integer in the set.
108 ** The function returns the number of successful conversions.
110 static int getDigits(const char *zDate, const char *zFormat, ...){
111 /* The aMx[] array translates the 3rd character of each format
112 ** spec into a max size: a b c d e f */
113 static const u16 aMx[] = { 12, 14, 24, 31, 59, 9999 };
114 va_list ap;
115 int cnt = 0;
116 char nextC;
117 va_start(ap, zFormat);
119 char N = zFormat[0] - '0';
120 char min = zFormat[1] - '0';
121 int val = 0;
122 u16 max;
124 assert( zFormat[2]>='a' && zFormat[2]<='f' );
125 max = aMx[zFormat[2] - 'a'];
126 nextC = zFormat[3];
127 val = 0;
128 while( N-- ){
129 if( !sqlite3Isdigit(*zDate) ){
130 goto end_getDigits;
132 val = val*10 + *zDate - '0';
133 zDate++;
135 if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){
136 goto end_getDigits;
138 *va_arg(ap,int*) = val;
139 zDate++;
140 cnt++;
141 zFormat += 4;
142 }while( nextC );
143 end_getDigits:
144 va_end(ap);
145 return cnt;
149 ** Parse a timezone extension on the end of a date-time.
150 ** The extension is of the form:
152 ** (+/-)HH:MM
154 ** Or the "zulu" notation:
156 ** Z
158 ** If the parse is successful, write the number of minutes
159 ** of change in p->tz and return 0. If a parser error occurs,
160 ** return non-zero.
162 ** A missing specifier is not considered an error.
164 static int parseTimezone(const char *zDate, DateTime *p){
165 int sgn = 0;
166 int nHr, nMn;
167 int c;
168 while( sqlite3Isspace(*zDate) ){ zDate++; }
169 p->tz = 0;
170 c = *zDate;
171 if( c=='-' ){
172 sgn = -1;
173 }else if( c=='+' ){
174 sgn = +1;
175 }else if( c=='Z' || c=='z' ){
176 zDate++;
177 goto zulu_time;
178 }else{
179 return c!=0;
181 zDate++;
182 if( getDigits(zDate, "20b:20e", &nHr, &nMn)!=2 ){
183 return 1;
185 zDate += 5;
186 p->tz = sgn*(nMn + nHr*60);
187 zulu_time:
188 while( sqlite3Isspace(*zDate) ){ zDate++; }
189 p->tzSet = 1;
190 return *zDate!=0;
194 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
195 ** The HH, MM, and SS must each be exactly 2 digits. The
196 ** fractional seconds FFFF can be one or more digits.
198 ** Return 1 if there is a parsing error and 0 on success.
200 static int parseHhMmSs(const char *zDate, DateTime *p){
201 int h, m, s;
202 double ms = 0.0;
203 if( getDigits(zDate, "20c:20e", &h, &m)!=2 ){
204 return 1;
206 zDate += 5;
207 if( *zDate==':' ){
208 zDate++;
209 if( getDigits(zDate, "20e", &s)!=1 ){
210 return 1;
212 zDate += 2;
213 if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){
214 double rScale = 1.0;
215 zDate++;
216 while( sqlite3Isdigit(*zDate) ){
217 ms = ms*10.0 + *zDate - '0';
218 rScale *= 10.0;
219 zDate++;
221 ms /= rScale;
223 }else{
224 s = 0;
226 p->validJD = 0;
227 p->rawS = 0;
228 p->validHMS = 1;
229 p->h = h;
230 p->m = m;
231 p->s = s + ms;
232 if( parseTimezone(zDate, p) ) return 1;
233 p->validTZ = (p->tz!=0)?1:0;
234 return 0;
238 ** Put the DateTime object into its error state.
240 static void datetimeError(DateTime *p){
241 memset(p, 0, sizeof(*p));
242 p->isError = 1;
246 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume
247 ** that the YYYY-MM-DD is according to the Gregorian calendar.
249 ** Reference: Meeus page 61
251 static void computeJD(DateTime *p){
252 int Y, M, D, A, B, X1, X2;
254 if( p->validJD ) return;
255 if( p->validYMD ){
256 Y = p->Y;
257 M = p->M;
258 D = p->D;
259 }else{
260 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */
261 M = 1;
262 D = 1;
264 if( Y<-4713 || Y>9999 || p->rawS ){
265 datetimeError(p);
266 return;
268 if( M<=2 ){
269 Y--;
270 M += 12;
272 A = Y/100;
273 B = 2 - A + (A/4);
274 X1 = 36525*(Y+4716)/100;
275 X2 = 306001*(M+1)/10000;
276 p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
277 p->validJD = 1;
278 if( p->validHMS ){
279 p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000);
280 if( p->validTZ ){
281 p->iJD -= p->tz*60000;
282 p->validYMD = 0;
283 p->validHMS = 0;
284 p->validTZ = 0;
290 ** Parse dates of the form
292 ** YYYY-MM-DD HH:MM:SS.FFF
293 ** YYYY-MM-DD HH:MM:SS
294 ** YYYY-MM-DD HH:MM
295 ** YYYY-MM-DD
297 ** Write the result into the DateTime structure and return 0
298 ** on success and 1 if the input string is not a well-formed
299 ** date.
301 static int parseYyyyMmDd(const char *zDate, DateTime *p){
302 int Y, M, D, neg;
304 if( zDate[0]=='-' ){
305 zDate++;
306 neg = 1;
307 }else{
308 neg = 0;
310 if( getDigits(zDate, "40f-21a-21d", &Y, &M, &D)!=3 ){
311 return 1;
313 zDate += 10;
314 while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }
315 if( parseHhMmSs(zDate, p)==0 ){
316 /* We got the time */
317 }else if( *zDate==0 ){
318 p->validHMS = 0;
319 }else{
320 return 1;
322 p->validJD = 0;
323 p->validYMD = 1;
324 p->Y = neg ? -Y : Y;
325 p->M = M;
326 p->D = D;
327 if( p->validTZ ){
328 computeJD(p);
330 return 0;
334 ** Set the time to the current time reported by the VFS.
336 ** Return the number of errors.
338 static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
339 p->iJD = sqlite3StmtCurrentTime(context);
340 if( p->iJD>0 ){
341 p->validJD = 1;
342 return 0;
343 }else{
344 return 1;
349 ** Input "r" is a numeric quantity which might be a julian day number,
350 ** or the number of seconds since 1970. If the value if r is within
351 ** range of a julian day number, install it as such and set validJD.
352 ** If the value is a valid unix timestamp, put it in p->s and set p->rawS.
354 static void setRawDateNumber(DateTime *p, double r){
355 p->s = r;
356 p->rawS = 1;
357 if( r>=0.0 && r<5373484.5 ){
358 p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
359 p->validJD = 1;
364 ** Attempt to parse the given string into a julian day number. Return
365 ** the number of errors.
367 ** The following are acceptable forms for the input string:
369 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM
370 ** DDDD.DD
371 ** now
373 ** In the first form, the +/-HH:MM is always optional. The fractional
374 ** seconds extension (the ".FFF") is optional. The seconds portion
375 ** (":SS.FFF") is option. The year and date can be omitted as long
376 ** as there is a time string. The time string can be omitted as long
377 ** as there is a year and date.
379 static int parseDateOrTime(
380 sqlite3_context *context,
381 const char *zDate,
382 DateTime *p
384 double r;
385 if( parseYyyyMmDd(zDate,p)==0 ){
386 return 0;
387 }else if( parseHhMmSs(zDate, p)==0 ){
388 return 0;
389 }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){
390 return setDateTimeToCurrent(context, p);
391 }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){
392 setRawDateNumber(p, r);
393 return 0;
395 return 1;
398 /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999.
399 ** Multiplying this by 86400000 gives 464269060799999 as the maximum value
400 ** for DateTime.iJD.
402 ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with
403 ** such a large integer literal, so we have to encode it.
405 #define INT_464269060799999 ((((i64)0x1a640)<<32)|0x1072fdff)
408 ** Return TRUE if the given julian day number is within range.
410 ** The input is the JulianDay times 86400000.
412 static int validJulianDay(sqlite3_int64 iJD){
413 return iJD>=0 && iJD<=INT_464269060799999;
417 ** Compute the Year, Month, and Day from the julian day number.
419 static void computeYMD(DateTime *p){
420 int Z, A, B, C, D, E, X1;
421 if( p->validYMD ) return;
422 if( !p->validJD ){
423 p->Y = 2000;
424 p->M = 1;
425 p->D = 1;
426 }else if( !validJulianDay(p->iJD) ){
427 datetimeError(p);
428 return;
429 }else{
430 Z = (int)((p->iJD + 43200000)/86400000);
431 A = (int)((Z - 1867216.25)/36524.25);
432 A = Z + 1 + A - (A/4);
433 B = A + 1524;
434 C = (int)((B - 122.1)/365.25);
435 D = (36525*(C&32767))/100;
436 E = (int)((B-D)/30.6001);
437 X1 = (int)(30.6001*E);
438 p->D = B - D - X1;
439 p->M = E<14 ? E-1 : E-13;
440 p->Y = p->M>2 ? C - 4716 : C - 4715;
442 p->validYMD = 1;
446 ** Compute the Hour, Minute, and Seconds from the julian day number.
448 static void computeHMS(DateTime *p){
449 int s;
450 if( p->validHMS ) return;
451 computeJD(p);
452 s = (int)((p->iJD + 43200000) % 86400000);
453 p->s = s/1000.0;
454 s = (int)p->s;
455 p->s -= s;
456 p->h = s/3600;
457 s -= p->h*3600;
458 p->m = s/60;
459 p->s += s - p->m*60;
460 p->rawS = 0;
461 p->validHMS = 1;
465 ** Compute both YMD and HMS
467 static void computeYMD_HMS(DateTime *p){
468 computeYMD(p);
469 computeHMS(p);
473 ** Clear the YMD and HMS and the TZ
475 static void clearYMD_HMS_TZ(DateTime *p){
476 p->validYMD = 0;
477 p->validHMS = 0;
478 p->validTZ = 0;
481 #ifndef SQLITE_OMIT_LOCALTIME
483 ** On recent Windows platforms, the localtime_s() function is available
484 ** as part of the "Secure CRT". It is essentially equivalent to
485 ** localtime_r() available under most POSIX platforms, except that the
486 ** order of the parameters is reversed.
488 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
490 ** If the user has not indicated to use localtime_r() or localtime_s()
491 ** already, check for an MSVC build environment that provides
492 ** localtime_s().
494 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \
495 && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
496 #undef HAVE_LOCALTIME_S
497 #define HAVE_LOCALTIME_S 1
498 #endif
501 ** The following routine implements the rough equivalent of localtime_r()
502 ** using whatever operating-system specific localtime facility that
503 ** is available. This routine returns 0 on success and
504 ** non-zero on any kind of error.
506 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is true then this
507 ** routine will always fail.
509 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
510 ** library function localtime_r() is used to assist in the calculation of
511 ** local time.
513 static int osLocaltime(time_t *t, struct tm *pTm){
514 int rc;
515 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S
516 struct tm *pX;
517 #if SQLITE_THREADSAFE>0
518 sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER);
519 #endif
520 sqlite3_mutex_enter(mutex);
521 pX = localtime(t);
522 #ifndef SQLITE_UNTESTABLE
523 if( sqlite3GlobalConfig.bLocaltimeFault ) pX = 0;
524 #endif
525 if( pX ) *pTm = *pX;
526 sqlite3_mutex_leave(mutex);
527 rc = pX==0;
528 #else
529 #ifndef SQLITE_UNTESTABLE
530 if( sqlite3GlobalConfig.bLocaltimeFault ) return 1;
531 #endif
532 #if HAVE_LOCALTIME_R
533 rc = localtime_r(t, pTm)==0;
534 #else
535 rc = localtime_s(pTm, t);
536 #endif /* HAVE_LOCALTIME_R */
537 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
538 return rc;
540 #endif /* SQLITE_OMIT_LOCALTIME */
543 #ifndef SQLITE_OMIT_LOCALTIME
545 ** Compute the difference (in milliseconds) between localtime and UTC
546 ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs,
547 ** return this value and set *pRc to SQLITE_OK.
549 ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value
550 ** is undefined in this case.
552 static sqlite3_int64 localtimeOffset(
553 DateTime *p, /* Date at which to calculate offset */
554 sqlite3_context *pCtx, /* Write error here if one occurs */
555 int *pRc /* OUT: Error code. SQLITE_OK or ERROR */
557 DateTime x, y;
558 time_t t;
559 struct tm sLocal;
561 /* Initialize the contents of sLocal to avoid a compiler warning. */
562 memset(&sLocal, 0, sizeof(sLocal));
564 x = *p;
565 computeYMD_HMS(&x);
566 if( x.Y<1971 || x.Y>=2038 ){
567 /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
568 ** works for years between 1970 and 2037. For dates outside this range,
569 ** SQLite attempts to map the year into an equivalent year within this
570 ** range, do the calculation, then map the year back.
572 x.Y = 2000;
573 x.M = 1;
574 x.D = 1;
575 x.h = 0;
576 x.m = 0;
577 x.s = 0.0;
578 } else {
579 int s = (int)(x.s + 0.5);
580 x.s = s;
582 x.tz = 0;
583 x.validJD = 0;
584 computeJD(&x);
585 t = (time_t)(x.iJD/1000 - 21086676*(i64)10000);
586 if( osLocaltime(&t, &sLocal) ){
587 sqlite3_result_error(pCtx, "local time unavailable", -1);
588 *pRc = SQLITE_ERROR;
589 return 0;
591 y.Y = sLocal.tm_year + 1900;
592 y.M = sLocal.tm_mon + 1;
593 y.D = sLocal.tm_mday;
594 y.h = sLocal.tm_hour;
595 y.m = sLocal.tm_min;
596 y.s = sLocal.tm_sec;
597 y.validYMD = 1;
598 y.validHMS = 1;
599 y.validJD = 0;
600 y.rawS = 0;
601 y.validTZ = 0;
602 y.isError = 0;
603 computeJD(&y);
604 *pRc = SQLITE_OK;
605 return y.iJD - x.iJD;
607 #endif /* SQLITE_OMIT_LOCALTIME */
610 ** The following table defines various date transformations of the form
612 ** 'NNN days'
614 ** Where NNN is an arbitrary floating-point number and "days" can be one
615 ** of several units of time.
617 static const struct {
618 u8 eType; /* Transformation type code */
619 u8 nName; /* Length of th name */
620 char *zName; /* Name of the transformation */
621 double rLimit; /* Maximum NNN value for this transform */
622 double rXform; /* Constant used for this transform */
623 } aXformType[] = {
624 { 0, 6, "second", 464269060800.0, 86400000.0/(24.0*60.0*60.0) },
625 { 0, 6, "minute", 7737817680.0, 86400000.0/(24.0*60.0) },
626 { 0, 4, "hour", 128963628.0, 86400000.0/24.0 },
627 { 0, 3, "day", 5373485.0, 86400000.0 },
628 { 1, 5, "month", 176546.0, 30.0*86400000.0 },
629 { 2, 4, "year", 14713.0, 365.0*86400000.0 },
633 ** Process a modifier to a date-time stamp. The modifiers are
634 ** as follows:
636 ** NNN days
637 ** NNN hours
638 ** NNN minutes
639 ** NNN.NNNN seconds
640 ** NNN months
641 ** NNN years
642 ** start of month
643 ** start of year
644 ** start of week
645 ** start of day
646 ** weekday N
647 ** unixepoch
648 ** localtime
649 ** utc
651 ** Return 0 on success and 1 if there is any kind of error. If the error
652 ** is in a system call (i.e. localtime()), then an error message is written
653 ** to context pCtx. If the error is an unrecognized modifier, no error is
654 ** written to pCtx.
656 static int parseModifier(
657 sqlite3_context *pCtx, /* Function context */
658 const char *z, /* The text of the modifier */
659 int n, /* Length of zMod in bytes */
660 DateTime *p /* The date/time value to be modified */
662 int rc = 1;
663 double r;
664 switch(sqlite3UpperToLower[(u8)z[0]] ){
665 #ifndef SQLITE_OMIT_LOCALTIME
666 case 'l': {
667 /* localtime
669 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
670 ** show local time.
672 if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){
673 computeJD(p);
674 p->iJD += localtimeOffset(p, pCtx, &rc);
675 clearYMD_HMS_TZ(p);
677 break;
679 #endif
680 case 'u': {
682 ** unixepoch
684 ** Treat the current value of p->s as the number of
685 ** seconds since 1970. Convert to a real julian day number.
687 if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){
688 r = p->s*1000.0 + 210866760000000.0;
689 if( r>=0.0 && r<464269060800000.0 ){
690 clearYMD_HMS_TZ(p);
691 p->iJD = (sqlite3_int64)r;
692 p->validJD = 1;
693 p->rawS = 0;
694 rc = 0;
697 #ifndef SQLITE_OMIT_LOCALTIME
698 else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){
699 if( p->tzSet==0 ){
700 sqlite3_int64 c1;
701 computeJD(p);
702 c1 = localtimeOffset(p, pCtx, &rc);
703 if( rc==SQLITE_OK ){
704 p->iJD -= c1;
705 clearYMD_HMS_TZ(p);
706 p->iJD += c1 - localtimeOffset(p, pCtx, &rc);
708 p->tzSet = 1;
709 }else{
710 rc = SQLITE_OK;
713 #endif
714 break;
716 case 'w': {
718 ** weekday N
720 ** Move the date to the same time on the next occurrence of
721 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the
722 ** date is already on the appropriate weekday, this is a no-op.
724 if( sqlite3_strnicmp(z, "weekday ", 8)==0
725 && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)
726 && (n=(int)r)==r && n>=0 && r<7 ){
727 sqlite3_int64 Z;
728 computeYMD_HMS(p);
729 p->validTZ = 0;
730 p->validJD = 0;
731 computeJD(p);
732 Z = ((p->iJD + 129600000)/86400000) % 7;
733 if( Z>n ) Z -= 7;
734 p->iJD += (n - Z)*86400000;
735 clearYMD_HMS_TZ(p);
736 rc = 0;
738 break;
740 case 's': {
742 ** start of TTTTT
744 ** Move the date backwards to the beginning of the current day,
745 ** or month or year.
747 if( sqlite3_strnicmp(z, "start of ", 9)!=0 ) break;
748 if( !p->validJD && !p->validYMD && !p->validHMS ) break;
749 z += 9;
750 computeYMD(p);
751 p->validHMS = 1;
752 p->h = p->m = 0;
753 p->s = 0.0;
754 p->rawS = 0;
755 p->validTZ = 0;
756 p->validJD = 0;
757 if( sqlite3_stricmp(z,"month")==0 ){
758 p->D = 1;
759 rc = 0;
760 }else if( sqlite3_stricmp(z,"year")==0 ){
761 p->M = 1;
762 p->D = 1;
763 rc = 0;
764 }else if( sqlite3_stricmp(z,"day")==0 ){
765 rc = 0;
767 break;
769 case '+':
770 case '-':
771 case '0':
772 case '1':
773 case '2':
774 case '3':
775 case '4':
776 case '5':
777 case '6':
778 case '7':
779 case '8':
780 case '9': {
781 double rRounder;
782 int i;
783 for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){}
784 if( !sqlite3AtoF(z, &r, n, SQLITE_UTF8) ){
785 rc = 1;
786 break;
788 if( z[n]==':' ){
789 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
790 ** specified number of hours, minutes, seconds, and fractional seconds
791 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be
792 ** omitted.
794 const char *z2 = z;
795 DateTime tx;
796 sqlite3_int64 day;
797 if( !sqlite3Isdigit(*z2) ) z2++;
798 memset(&tx, 0, sizeof(tx));
799 if( parseHhMmSs(z2, &tx) ) break;
800 computeJD(&tx);
801 tx.iJD -= 43200000;
802 day = tx.iJD/86400000;
803 tx.iJD -= day*86400000;
804 if( z[0]=='-' ) tx.iJD = -tx.iJD;
805 computeJD(p);
806 clearYMD_HMS_TZ(p);
807 p->iJD += tx.iJD;
808 rc = 0;
809 break;
812 /* If control reaches this point, it means the transformation is
813 ** one of the forms like "+NNN days". */
814 z += n;
815 while( sqlite3Isspace(*z) ) z++;
816 n = sqlite3Strlen30(z);
817 if( n>10 || n<3 ) break;
818 if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
819 computeJD(p);
820 rc = 1;
821 rRounder = r<0 ? -0.5 : +0.5;
822 for(i=0; i<ArraySize(aXformType); i++){
823 if( aXformType[i].nName==n
824 && sqlite3_strnicmp(aXformType[i].zName, z, n)==0
825 && r>-aXformType[i].rLimit && r<aXformType[i].rLimit
827 switch( aXformType[i].eType ){
828 case 1: { /* Special processing to add months */
829 int x;
830 computeYMD_HMS(p);
831 p->M += (int)r;
832 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
833 p->Y += x;
834 p->M -= x*12;
835 p->validJD = 0;
836 r -= (int)r;
837 break;
839 case 2: { /* Special processing to add years */
840 int y = (int)r;
841 computeYMD_HMS(p);
842 p->Y += y;
843 p->validJD = 0;
844 r -= (int)r;
845 break;
848 computeJD(p);
849 p->iJD += (sqlite3_int64)(r*aXformType[i].rXform + rRounder);
850 rc = 0;
851 break;
854 clearYMD_HMS_TZ(p);
855 break;
857 default: {
858 break;
861 return rc;
865 ** Process time function arguments. argv[0] is a date-time stamp.
866 ** argv[1] and following are modifiers. Parse them all and write
867 ** the resulting time into the DateTime structure p. Return 0
868 ** on success and 1 if there are any errors.
870 ** If there are zero parameters (if even argv[0] is undefined)
871 ** then assume a default value of "now" for argv[0].
873 static int isDate(
874 sqlite3_context *context,
875 int argc,
876 sqlite3_value **argv,
877 DateTime *p
879 int i, n;
880 const unsigned char *z;
881 int eType;
882 memset(p, 0, sizeof(*p));
883 if( argc==0 ){
884 return setDateTimeToCurrent(context, p);
886 if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
887 || eType==SQLITE_INTEGER ){
888 setRawDateNumber(p, sqlite3_value_double(argv[0]));
889 }else{
890 z = sqlite3_value_text(argv[0]);
891 if( !z || parseDateOrTime(context, (char*)z, p) ){
892 return 1;
895 for(i=1; i<argc; i++){
896 z = sqlite3_value_text(argv[i]);
897 n = sqlite3_value_bytes(argv[i]);
898 if( z==0 || parseModifier(context, (char*)z, n, p) ) return 1;
900 computeJD(p);
901 if( p->isError || !validJulianDay(p->iJD) ) return 1;
902 return 0;
907 ** The following routines implement the various date and time functions
908 ** of SQLite.
912 ** julianday( TIMESTRING, MOD, MOD, ...)
914 ** Return the julian day number of the date specified in the arguments
916 static void juliandayFunc(
917 sqlite3_context *context,
918 int argc,
919 sqlite3_value **argv
921 DateTime x;
922 if( isDate(context, argc, argv, &x)==0 ){
923 computeJD(&x);
924 sqlite3_result_double(context, x.iJD/86400000.0);
929 ** datetime( TIMESTRING, MOD, MOD, ...)
931 ** Return YYYY-MM-DD HH:MM:SS
933 static void datetimeFunc(
934 sqlite3_context *context,
935 int argc,
936 sqlite3_value **argv
938 DateTime x;
939 if( isDate(context, argc, argv, &x)==0 ){
940 char zBuf[100];
941 computeYMD_HMS(&x);
942 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d",
943 x.Y, x.M, x.D, x.h, x.m, (int)(x.s));
944 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
949 ** time( TIMESTRING, MOD, MOD, ...)
951 ** Return HH:MM:SS
953 static void timeFunc(
954 sqlite3_context *context,
955 int argc,
956 sqlite3_value **argv
958 DateTime x;
959 if( isDate(context, argc, argv, &x)==0 ){
960 char zBuf[100];
961 computeHMS(&x);
962 sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
963 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
968 ** date( TIMESTRING, MOD, MOD, ...)
970 ** Return YYYY-MM-DD
972 static void dateFunc(
973 sqlite3_context *context,
974 int argc,
975 sqlite3_value **argv
977 DateTime x;
978 if( isDate(context, argc, argv, &x)==0 ){
979 char zBuf[100];
980 computeYMD(&x);
981 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
982 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
987 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
989 ** Return a string described by FORMAT. Conversions as follows:
991 ** %d day of month
992 ** %f ** fractional seconds SS.SSS
993 ** %H hour 00-24
994 ** %j day of year 000-366
995 ** %J ** julian day number
996 ** %m month 01-12
997 ** %M minute 00-59
998 ** %s seconds since 1970-01-01
999 ** %S seconds 00-59
1000 ** %w day of week 0-6 sunday==0
1001 ** %W week of year 00-53
1002 ** %Y year 0000-9999
1003 ** %% %
1005 static void strftimeFunc(
1006 sqlite3_context *context,
1007 int argc,
1008 sqlite3_value **argv
1010 DateTime x;
1011 u64 n;
1012 size_t i,j;
1013 char *z;
1014 sqlite3 *db;
1015 const char *zFmt;
1016 char zBuf[100];
1017 if( argc==0 ) return;
1018 zFmt = (const char*)sqlite3_value_text(argv[0]);
1019 if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
1020 db = sqlite3_context_db_handle(context);
1021 for(i=0, n=1; zFmt[i]; i++, n++){
1022 if( zFmt[i]=='%' ){
1023 switch( zFmt[i+1] ){
1024 case 'd':
1025 case 'H':
1026 case 'm':
1027 case 'M':
1028 case 'S':
1029 case 'W':
1030 n++;
1031 /* fall thru */
1032 case 'w':
1033 case '%':
1034 break;
1035 case 'f':
1036 n += 8;
1037 break;
1038 case 'j':
1039 n += 3;
1040 break;
1041 case 'Y':
1042 n += 8;
1043 break;
1044 case 's':
1045 case 'J':
1046 n += 50;
1047 break;
1048 default:
1049 return; /* ERROR. return a NULL */
1051 i++;
1054 testcase( n==sizeof(zBuf)-1 );
1055 testcase( n==sizeof(zBuf) );
1056 testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH]+1 );
1057 testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH] );
1058 if( n<sizeof(zBuf) ){
1059 z = zBuf;
1060 }else if( n>(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ){
1061 sqlite3_result_error_toobig(context);
1062 return;
1063 }else{
1064 z = sqlite3DbMallocRawNN(db, (int)n);
1065 if( z==0 ){
1066 sqlite3_result_error_nomem(context);
1067 return;
1070 computeJD(&x);
1071 computeYMD_HMS(&x);
1072 for(i=j=0; zFmt[i]; i++){
1073 if( zFmt[i]!='%' ){
1074 z[j++] = zFmt[i];
1075 }else{
1076 i++;
1077 switch( zFmt[i] ){
1078 case 'd': sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break;
1079 case 'f': {
1080 double s = x.s;
1081 if( s>59.999 ) s = 59.999;
1082 sqlite3_snprintf(7, &z[j],"%06.3f", s);
1083 j += sqlite3Strlen30(&z[j]);
1084 break;
1086 case 'H': sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break;
1087 case 'W': /* Fall thru */
1088 case 'j': {
1089 int nDay; /* Number of days since 1st day of year */
1090 DateTime y = x;
1091 y.validJD = 0;
1092 y.M = 1;
1093 y.D = 1;
1094 computeJD(&y);
1095 nDay = (int)((x.iJD-y.iJD+43200000)/86400000);
1096 if( zFmt[i]=='W' ){
1097 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */
1098 wd = (int)(((x.iJD+43200000)/86400000)%7);
1099 sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);
1100 j += 2;
1101 }else{
1102 sqlite3_snprintf(4, &z[j],"%03d",nDay+1);
1103 j += 3;
1105 break;
1107 case 'J': {
1108 sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0);
1109 j+=sqlite3Strlen30(&z[j]);
1110 break;
1112 case 'm': sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;
1113 case 'M': sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;
1114 case 's': {
1115 sqlite3_snprintf(30,&z[j],"%lld",
1116 (i64)(x.iJD/1000 - 21086676*(i64)10000));
1117 j += sqlite3Strlen30(&z[j]);
1118 break;
1120 case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
1121 case 'w': {
1122 z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
1123 break;
1125 case 'Y': {
1126 sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
1127 break;
1129 default: z[j++] = '%'; break;
1133 z[j] = 0;
1134 sqlite3_result_text(context, z, -1,
1135 z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC);
1139 ** current_time()
1141 ** This function returns the same value as time('now').
1143 static void ctimeFunc(
1144 sqlite3_context *context,
1145 int NotUsed,
1146 sqlite3_value **NotUsed2
1148 UNUSED_PARAMETER2(NotUsed, NotUsed2);
1149 timeFunc(context, 0, 0);
1153 ** current_date()
1155 ** This function returns the same value as date('now').
1157 static void cdateFunc(
1158 sqlite3_context *context,
1159 int NotUsed,
1160 sqlite3_value **NotUsed2
1162 UNUSED_PARAMETER2(NotUsed, NotUsed2);
1163 dateFunc(context, 0, 0);
1167 ** current_timestamp()
1169 ** This function returns the same value as datetime('now').
1171 static void ctimestampFunc(
1172 sqlite3_context *context,
1173 int NotUsed,
1174 sqlite3_value **NotUsed2
1176 UNUSED_PARAMETER2(NotUsed, NotUsed2);
1177 datetimeFunc(context, 0, 0);
1179 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
1181 #ifdef SQLITE_OMIT_DATETIME_FUNCS
1183 ** If the library is compiled to omit the full-scale date and time
1184 ** handling (to get a smaller binary), the following minimal version
1185 ** of the functions current_time(), current_date() and current_timestamp()
1186 ** are included instead. This is to support column declarations that
1187 ** include "DEFAULT CURRENT_TIME" etc.
1189 ** This function uses the C-library functions time(), gmtime()
1190 ** and strftime(). The format string to pass to strftime() is supplied
1191 ** as the user-data for the function.
1193 static void currentTimeFunc(
1194 sqlite3_context *context,
1195 int argc,
1196 sqlite3_value **argv
1198 time_t t;
1199 char *zFormat = (char *)sqlite3_user_data(context);
1200 sqlite3_int64 iT;
1201 struct tm *pTm;
1202 struct tm sNow;
1203 char zBuf[20];
1205 UNUSED_PARAMETER(argc);
1206 UNUSED_PARAMETER(argv);
1208 iT = sqlite3StmtCurrentTime(context);
1209 if( iT<=0 ) return;
1210 t = iT/1000 - 10000*(sqlite3_int64)21086676;
1211 #if HAVE_GMTIME_R
1212 pTm = gmtime_r(&t, &sNow);
1213 #else
1214 sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1215 pTm = gmtime(&t);
1216 if( pTm ) memcpy(&sNow, pTm, sizeof(sNow));
1217 sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1218 #endif
1219 if( pTm ){
1220 strftime(zBuf, 20, zFormat, &sNow);
1221 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
1224 #endif
1227 ** This function registered all of the above C functions as SQL
1228 ** functions. This should be the only routine in this file with
1229 ** external linkage.
1231 void sqlite3RegisterDateTimeFunctions(void){
1232 static FuncDef aDateTimeFuncs[] = {
1233 #ifndef SQLITE_OMIT_DATETIME_FUNCS
1234 PURE_DATE(julianday, -1, 0, 0, juliandayFunc ),
1235 PURE_DATE(date, -1, 0, 0, dateFunc ),
1236 PURE_DATE(time, -1, 0, 0, timeFunc ),
1237 PURE_DATE(datetime, -1, 0, 0, datetimeFunc ),
1238 PURE_DATE(strftime, -1, 0, 0, strftimeFunc ),
1239 DFUNCTION(current_time, 0, 0, 0, ctimeFunc ),
1240 DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
1241 DFUNCTION(current_date, 0, 0, 0, cdateFunc ),
1242 #else
1243 STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc),
1244 STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc),
1245 STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
1246 #endif
1248 sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));