Merge Chromium + Blink git repositories
[chromium-blink-merge.git] / third_party / sqlite / sqlite-src-3080704 / src / date.c
blob11b04ea004b611457422108af8d83360be9d80ae
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 ** ISBM 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
55 ** A structure for holding a single date and time.
57 typedef struct DateTime DateTime;
58 struct DateTime {
59 sqlite3_int64 iJD; /* The julian day number times 86400000 */
60 int Y, M, D; /* Year, month, and day */
61 int h, m; /* Hour and minutes */
62 int tz; /* Timezone offset in minutes */
63 double s; /* Seconds */
64 char validYMD; /* True (1) if Y,M,D are valid */
65 char validHMS; /* True (1) if h,m,s are valid */
66 char validJD; /* True (1) if iJD is valid */
67 char validTZ; /* True (1) if tz is valid */
72 ** Convert zDate into one or more integers. Additional arguments
73 ** come in groups of 5 as follows:
75 ** N number of digits in the integer
76 ** min minimum allowed value of the integer
77 ** max maximum allowed value of the integer
78 ** nextC first character after the integer
79 ** pVal where to write the integers value.
81 ** Conversions continue until one with nextC==0 is encountered.
82 ** The function returns the number of successful conversions.
84 static int getDigits(const char *zDate, ...){
85 va_list ap;
86 int val;
87 int N;
88 int min;
89 int max;
90 int nextC;
91 int *pVal;
92 int cnt = 0;
93 va_start(ap, zDate);
94 do{
95 N = va_arg(ap, int);
96 min = va_arg(ap, int);
97 max = va_arg(ap, int);
98 nextC = va_arg(ap, int);
99 pVal = va_arg(ap, int*);
100 val = 0;
101 while( N-- ){
102 if( !sqlite3Isdigit(*zDate) ){
103 goto end_getDigits;
105 val = val*10 + *zDate - '0';
106 zDate++;
108 if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){
109 goto end_getDigits;
111 *pVal = val;
112 zDate++;
113 cnt++;
114 }while( nextC );
115 end_getDigits:
116 va_end(ap);
117 return cnt;
121 ** Parse a timezone extension on the end of a date-time.
122 ** The extension is of the form:
124 ** (+/-)HH:MM
126 ** Or the "zulu" notation:
128 ** Z
130 ** If the parse is successful, write the number of minutes
131 ** of change in p->tz and return 0. If a parser error occurs,
132 ** return non-zero.
134 ** A missing specifier is not considered an error.
136 static int parseTimezone(const char *zDate, DateTime *p){
137 int sgn = 0;
138 int nHr, nMn;
139 int c;
140 while( sqlite3Isspace(*zDate) ){ zDate++; }
141 p->tz = 0;
142 c = *zDate;
143 if( c=='-' ){
144 sgn = -1;
145 }else if( c=='+' ){
146 sgn = +1;
147 }else if( c=='Z' || c=='z' ){
148 zDate++;
149 goto zulu_time;
150 }else{
151 return c!=0;
153 zDate++;
154 if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){
155 return 1;
157 zDate += 5;
158 p->tz = sgn*(nMn + nHr*60);
159 zulu_time:
160 while( sqlite3Isspace(*zDate) ){ zDate++; }
161 return *zDate!=0;
165 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
166 ** The HH, MM, and SS must each be exactly 2 digits. The
167 ** fractional seconds FFFF can be one or more digits.
169 ** Return 1 if there is a parsing error and 0 on success.
171 static int parseHhMmSs(const char *zDate, DateTime *p){
172 int h, m, s;
173 double ms = 0.0;
174 if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){
175 return 1;
177 zDate += 5;
178 if( *zDate==':' ){
179 zDate++;
180 if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
181 return 1;
183 zDate += 2;
184 if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){
185 double rScale = 1.0;
186 zDate++;
187 while( sqlite3Isdigit(*zDate) ){
188 ms = ms*10.0 + *zDate - '0';
189 rScale *= 10.0;
190 zDate++;
192 ms /= rScale;
194 }else{
195 s = 0;
197 p->validJD = 0;
198 p->validHMS = 1;
199 p->h = h;
200 p->m = m;
201 p->s = s + ms;
202 if( parseTimezone(zDate, p) ) return 1;
203 p->validTZ = (p->tz!=0)?1:0;
204 return 0;
208 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume
209 ** that the YYYY-MM-DD is according to the Gregorian calendar.
211 ** Reference: Meeus page 61
213 static void computeJD(DateTime *p){
214 int Y, M, D, A, B, X1, X2;
216 if( p->validJD ) return;
217 if( p->validYMD ){
218 Y = p->Y;
219 M = p->M;
220 D = p->D;
221 }else{
222 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */
223 M = 1;
224 D = 1;
226 if( M<=2 ){
227 Y--;
228 M += 12;
230 A = Y/100;
231 B = 2 - A + (A/4);
232 X1 = 36525*(Y+4716)/100;
233 X2 = 306001*(M+1)/10000;
234 p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
235 p->validJD = 1;
236 if( p->validHMS ){
237 p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000);
238 if( p->validTZ ){
239 p->iJD -= p->tz*60000;
240 p->validYMD = 0;
241 p->validHMS = 0;
242 p->validTZ = 0;
248 ** Parse dates of the form
250 ** YYYY-MM-DD HH:MM:SS.FFF
251 ** YYYY-MM-DD HH:MM:SS
252 ** YYYY-MM-DD HH:MM
253 ** YYYY-MM-DD
255 ** Write the result into the DateTime structure and return 0
256 ** on success and 1 if the input string is not a well-formed
257 ** date.
259 static int parseYyyyMmDd(const char *zDate, DateTime *p){
260 int Y, M, D, neg;
262 if( zDate[0]=='-' ){
263 zDate++;
264 neg = 1;
265 }else{
266 neg = 0;
268 if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){
269 return 1;
271 zDate += 10;
272 while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }
273 if( parseHhMmSs(zDate, p)==0 ){
274 /* We got the time */
275 }else if( *zDate==0 ){
276 p->validHMS = 0;
277 }else{
278 return 1;
280 p->validJD = 0;
281 p->validYMD = 1;
282 p->Y = neg ? -Y : Y;
283 p->M = M;
284 p->D = D;
285 if( p->validTZ ){
286 computeJD(p);
288 return 0;
292 ** Set the time to the current time reported by the VFS.
294 ** Return the number of errors.
296 static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
297 p->iJD = sqlite3StmtCurrentTime(context);
298 if( p->iJD>0 ){
299 p->validJD = 1;
300 return 0;
301 }else{
302 return 1;
307 ** Attempt to parse the given string into a Julian Day Number. Return
308 ** the number of errors.
310 ** The following are acceptable forms for the input string:
312 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM
313 ** DDDD.DD
314 ** now
316 ** In the first form, the +/-HH:MM is always optional. The fractional
317 ** seconds extension (the ".FFF") is optional. The seconds portion
318 ** (":SS.FFF") is option. The year and date can be omitted as long
319 ** as there is a time string. The time string can be omitted as long
320 ** as there is a year and date.
322 static int parseDateOrTime(
323 sqlite3_context *context,
324 const char *zDate,
325 DateTime *p
327 double r;
328 if( parseYyyyMmDd(zDate,p)==0 ){
329 return 0;
330 }else if( parseHhMmSs(zDate, p)==0 ){
331 return 0;
332 }else if( sqlite3StrICmp(zDate,"now")==0){
333 return setDateTimeToCurrent(context, p);
334 }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){
335 p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
336 p->validJD = 1;
337 return 0;
339 return 1;
343 ** Compute the Year, Month, and Day from the julian day number.
345 static void computeYMD(DateTime *p){
346 int Z, A, B, C, D, E, X1;
347 if( p->validYMD ) return;
348 if( !p->validJD ){
349 p->Y = 2000;
350 p->M = 1;
351 p->D = 1;
352 }else{
353 Z = (int)((p->iJD + 43200000)/86400000);
354 A = (int)((Z - 1867216.25)/36524.25);
355 A = Z + 1 + A - (A/4);
356 B = A + 1524;
357 C = (int)((B - 122.1)/365.25);
358 D = (36525*C)/100;
359 E = (int)((B-D)/30.6001);
360 X1 = (int)(30.6001*E);
361 p->D = B - D - X1;
362 p->M = E<14 ? E-1 : E-13;
363 p->Y = p->M>2 ? C - 4716 : C - 4715;
365 p->validYMD = 1;
369 ** Compute the Hour, Minute, and Seconds from the julian day number.
371 static void computeHMS(DateTime *p){
372 int s;
373 if( p->validHMS ) return;
374 computeJD(p);
375 s = (int)((p->iJD + 43200000) % 86400000);
376 p->s = s/1000.0;
377 s = (int)p->s;
378 p->s -= s;
379 p->h = s/3600;
380 s -= p->h*3600;
381 p->m = s/60;
382 p->s += s - p->m*60;
383 p->validHMS = 1;
387 ** Compute both YMD and HMS
389 static void computeYMD_HMS(DateTime *p){
390 computeYMD(p);
391 computeHMS(p);
395 ** Clear the YMD and HMS and the TZ
397 static void clearYMD_HMS_TZ(DateTime *p){
398 p->validYMD = 0;
399 p->validHMS = 0;
400 p->validTZ = 0;
404 ** On recent Windows platforms, the localtime_s() function is available
405 ** as part of the "Secure CRT". It is essentially equivalent to
406 ** localtime_r() available under most POSIX platforms, except that the
407 ** order of the parameters is reversed.
409 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
411 ** If the user has not indicated to use localtime_r() or localtime_s()
412 ** already, check for an MSVC build environment that provides
413 ** localtime_s().
415 #if !defined(HAVE_LOCALTIME_R) && !defined(HAVE_LOCALTIME_S) && \
416 defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
417 #define HAVE_LOCALTIME_S 1
418 #endif
420 #ifndef SQLITE_OMIT_LOCALTIME
422 ** The following routine implements the rough equivalent of localtime_r()
423 ** using whatever operating-system specific localtime facility that
424 ** is available. This routine returns 0 on success and
425 ** non-zero on any kind of error.
427 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is true then this
428 ** routine will always fail.
430 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
431 ** library function localtime_r() is used to assist in the calculation of
432 ** local time.
434 static int osLocaltime(time_t *t, struct tm *pTm){
435 int rc;
436 #if (!defined(HAVE_LOCALTIME_R) || !HAVE_LOCALTIME_R) \
437 && (!defined(HAVE_LOCALTIME_S) || !HAVE_LOCALTIME_S)
438 struct tm *pX;
439 #if SQLITE_THREADSAFE>0
440 sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER);
441 #endif
442 sqlite3_mutex_enter(mutex);
443 pX = localtime(t);
444 #ifndef SQLITE_OMIT_BUILTIN_TEST
445 if( sqlite3GlobalConfig.bLocaltimeFault ) pX = 0;
446 #endif
447 if( pX ) *pTm = *pX;
448 sqlite3_mutex_leave(mutex);
449 rc = pX==0;
450 #else
451 #ifndef SQLITE_OMIT_BUILTIN_TEST
452 if( sqlite3GlobalConfig.bLocaltimeFault ) return 1;
453 #endif
454 #if defined(HAVE_LOCALTIME_R) && HAVE_LOCALTIME_R
455 rc = localtime_r(t, pTm)==0;
456 #else
457 rc = localtime_s(pTm, t);
458 #endif /* HAVE_LOCALTIME_R */
459 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
460 return rc;
462 #endif /* SQLITE_OMIT_LOCALTIME */
465 #ifndef SQLITE_OMIT_LOCALTIME
467 ** Compute the difference (in milliseconds) between localtime and UTC
468 ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs,
469 ** return this value and set *pRc to SQLITE_OK.
471 ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value
472 ** is undefined in this case.
474 static sqlite3_int64 localtimeOffset(
475 DateTime *p, /* Date at which to calculate offset */
476 sqlite3_context *pCtx, /* Write error here if one occurs */
477 int *pRc /* OUT: Error code. SQLITE_OK or ERROR */
479 DateTime x, y;
480 time_t t;
481 struct tm sLocal;
483 /* Initialize the contents of sLocal to avoid a compiler warning. */
484 memset(&sLocal, 0, sizeof(sLocal));
486 x = *p;
487 computeYMD_HMS(&x);
488 if( x.Y<1971 || x.Y>=2038 ){
489 /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
490 ** works for years between 1970 and 2037. For dates outside this range,
491 ** SQLite attempts to map the year into an equivalent year within this
492 ** range, do the calculation, then map the year back.
494 x.Y = 2000;
495 x.M = 1;
496 x.D = 1;
497 x.h = 0;
498 x.m = 0;
499 x.s = 0.0;
500 } else {
501 int s = (int)(x.s + 0.5);
502 x.s = s;
504 x.tz = 0;
505 x.validJD = 0;
506 computeJD(&x);
507 t = (time_t)(x.iJD/1000 - 21086676*(i64)10000);
508 if( osLocaltime(&t, &sLocal) ){
509 sqlite3_result_error(pCtx, "local time unavailable", -1);
510 *pRc = SQLITE_ERROR;
511 return 0;
513 y.Y = sLocal.tm_year + 1900;
514 y.M = sLocal.tm_mon + 1;
515 y.D = sLocal.tm_mday;
516 y.h = sLocal.tm_hour;
517 y.m = sLocal.tm_min;
518 y.s = sLocal.tm_sec;
519 y.validYMD = 1;
520 y.validHMS = 1;
521 y.validJD = 0;
522 y.validTZ = 0;
523 computeJD(&y);
524 *pRc = SQLITE_OK;
525 return y.iJD - x.iJD;
527 #endif /* SQLITE_OMIT_LOCALTIME */
530 ** Process a modifier to a date-time stamp. The modifiers are
531 ** as follows:
533 ** NNN days
534 ** NNN hours
535 ** NNN minutes
536 ** NNN.NNNN seconds
537 ** NNN months
538 ** NNN years
539 ** start of month
540 ** start of year
541 ** start of week
542 ** start of day
543 ** weekday N
544 ** unixepoch
545 ** localtime
546 ** utc
548 ** Return 0 on success and 1 if there is any kind of error. If the error
549 ** is in a system call (i.e. localtime()), then an error message is written
550 ** to context pCtx. If the error is an unrecognized modifier, no error is
551 ** written to pCtx.
553 static int parseModifier(sqlite3_context *pCtx, const char *zMod, DateTime *p){
554 int rc = 1;
555 int n;
556 double r;
557 char *z, zBuf[30];
558 z = zBuf;
559 for(n=0; n<ArraySize(zBuf)-1 && zMod[n]; n++){
560 z[n] = (char)sqlite3UpperToLower[(u8)zMod[n]];
562 z[n] = 0;
563 switch( z[0] ){
564 #ifndef SQLITE_OMIT_LOCALTIME
565 case 'l': {
566 /* localtime
568 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
569 ** show local time.
571 if( strcmp(z, "localtime")==0 ){
572 computeJD(p);
573 p->iJD += localtimeOffset(p, pCtx, &rc);
574 clearYMD_HMS_TZ(p);
576 break;
578 #endif
579 case 'u': {
581 ** unixepoch
583 ** Treat the current value of p->iJD as the number of
584 ** seconds since 1970. Convert to a real julian day number.
586 if( strcmp(z, "unixepoch")==0 && p->validJD ){
587 p->iJD = (p->iJD + 43200)/86400 + 21086676*(i64)10000000;
588 clearYMD_HMS_TZ(p);
589 rc = 0;
591 #ifndef SQLITE_OMIT_LOCALTIME
592 else if( strcmp(z, "utc")==0 ){
593 sqlite3_int64 c1;
594 computeJD(p);
595 c1 = localtimeOffset(p, pCtx, &rc);
596 if( rc==SQLITE_OK ){
597 p->iJD -= c1;
598 clearYMD_HMS_TZ(p);
599 p->iJD += c1 - localtimeOffset(p, pCtx, &rc);
602 #endif
603 break;
605 case 'w': {
607 ** weekday N
609 ** Move the date to the same time on the next occurrence of
610 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the
611 ** date is already on the appropriate weekday, this is a no-op.
613 if( strncmp(z, "weekday ", 8)==0
614 && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)
615 && (n=(int)r)==r && n>=0 && r<7 ){
616 sqlite3_int64 Z;
617 computeYMD_HMS(p);
618 p->validTZ = 0;
619 p->validJD = 0;
620 computeJD(p);
621 Z = ((p->iJD + 129600000)/86400000) % 7;
622 if( Z>n ) Z -= 7;
623 p->iJD += (n - Z)*86400000;
624 clearYMD_HMS_TZ(p);
625 rc = 0;
627 break;
629 case 's': {
631 ** start of TTTTT
633 ** Move the date backwards to the beginning of the current day,
634 ** or month or year.
636 if( strncmp(z, "start of ", 9)!=0 ) break;
637 z += 9;
638 computeYMD(p);
639 p->validHMS = 1;
640 p->h = p->m = 0;
641 p->s = 0.0;
642 p->validTZ = 0;
643 p->validJD = 0;
644 if( strcmp(z,"month")==0 ){
645 p->D = 1;
646 rc = 0;
647 }else if( strcmp(z,"year")==0 ){
648 computeYMD(p);
649 p->M = 1;
650 p->D = 1;
651 rc = 0;
652 }else if( strcmp(z,"day")==0 ){
653 rc = 0;
655 break;
657 case '+':
658 case '-':
659 case '0':
660 case '1':
661 case '2':
662 case '3':
663 case '4':
664 case '5':
665 case '6':
666 case '7':
667 case '8':
668 case '9': {
669 double rRounder;
670 for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){}
671 if( !sqlite3AtoF(z, &r, n, SQLITE_UTF8) ){
672 rc = 1;
673 break;
675 if( z[n]==':' ){
676 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
677 ** specified number of hours, minutes, seconds, and fractional seconds
678 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be
679 ** omitted.
681 const char *z2 = z;
682 DateTime tx;
683 sqlite3_int64 day;
684 if( !sqlite3Isdigit(*z2) ) z2++;
685 memset(&tx, 0, sizeof(tx));
686 if( parseHhMmSs(z2, &tx) ) break;
687 computeJD(&tx);
688 tx.iJD -= 43200000;
689 day = tx.iJD/86400000;
690 tx.iJD -= day*86400000;
691 if( z[0]=='-' ) tx.iJD = -tx.iJD;
692 computeJD(p);
693 clearYMD_HMS_TZ(p);
694 p->iJD += tx.iJD;
695 rc = 0;
696 break;
698 z += n;
699 while( sqlite3Isspace(*z) ) z++;
700 n = sqlite3Strlen30(z);
701 if( n>10 || n<3 ) break;
702 if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
703 computeJD(p);
704 rc = 0;
705 rRounder = r<0 ? -0.5 : +0.5;
706 if( n==3 && strcmp(z,"day")==0 ){
707 p->iJD += (sqlite3_int64)(r*86400000.0 + rRounder);
708 }else if( n==4 && strcmp(z,"hour")==0 ){
709 p->iJD += (sqlite3_int64)(r*(86400000.0/24.0) + rRounder);
710 }else if( n==6 && strcmp(z,"minute")==0 ){
711 p->iJD += (sqlite3_int64)(r*(86400000.0/(24.0*60.0)) + rRounder);
712 }else if( n==6 && strcmp(z,"second")==0 ){
713 p->iJD += (sqlite3_int64)(r*(86400000.0/(24.0*60.0*60.0)) + rRounder);
714 }else if( n==5 && strcmp(z,"month")==0 ){
715 int x, y;
716 computeYMD_HMS(p);
717 p->M += (int)r;
718 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
719 p->Y += x;
720 p->M -= x*12;
721 p->validJD = 0;
722 computeJD(p);
723 y = (int)r;
724 if( y!=r ){
725 p->iJD += (sqlite3_int64)((r - y)*30.0*86400000.0 + rRounder);
727 }else if( n==4 && strcmp(z,"year")==0 ){
728 int y = (int)r;
729 computeYMD_HMS(p);
730 p->Y += y;
731 p->validJD = 0;
732 computeJD(p);
733 if( y!=r ){
734 p->iJD += (sqlite3_int64)((r - y)*365.0*86400000.0 + rRounder);
736 }else{
737 rc = 1;
739 clearYMD_HMS_TZ(p);
740 break;
742 default: {
743 break;
746 return rc;
750 ** Process time function arguments. argv[0] is a date-time stamp.
751 ** argv[1] and following are modifiers. Parse them all and write
752 ** the resulting time into the DateTime structure p. Return 0
753 ** on success and 1 if there are any errors.
755 ** If there are zero parameters (if even argv[0] is undefined)
756 ** then assume a default value of "now" for argv[0].
758 static int isDate(
759 sqlite3_context *context,
760 int argc,
761 sqlite3_value **argv,
762 DateTime *p
764 int i;
765 const unsigned char *z;
766 int eType;
767 memset(p, 0, sizeof(*p));
768 if( argc==0 ){
769 return setDateTimeToCurrent(context, p);
771 if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
772 || eType==SQLITE_INTEGER ){
773 p->iJD = (sqlite3_int64)(sqlite3_value_double(argv[0])*86400000.0 + 0.5);
774 p->validJD = 1;
775 }else{
776 z = sqlite3_value_text(argv[0]);
777 if( !z || parseDateOrTime(context, (char*)z, p) ){
778 return 1;
781 for(i=1; i<argc; i++){
782 z = sqlite3_value_text(argv[i]);
783 if( z==0 || parseModifier(context, (char*)z, p) ) return 1;
785 return 0;
790 ** The following routines implement the various date and time functions
791 ** of SQLite.
795 ** julianday( TIMESTRING, MOD, MOD, ...)
797 ** Return the julian day number of the date specified in the arguments
799 static void juliandayFunc(
800 sqlite3_context *context,
801 int argc,
802 sqlite3_value **argv
804 DateTime x;
805 if( isDate(context, argc, argv, &x)==0 ){
806 computeJD(&x);
807 sqlite3_result_double(context, x.iJD/86400000.0);
812 ** datetime( TIMESTRING, MOD, MOD, ...)
814 ** Return YYYY-MM-DD HH:MM:SS
816 static void datetimeFunc(
817 sqlite3_context *context,
818 int argc,
819 sqlite3_value **argv
821 DateTime x;
822 if( isDate(context, argc, argv, &x)==0 ){
823 char zBuf[100];
824 computeYMD_HMS(&x);
825 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d",
826 x.Y, x.M, x.D, x.h, x.m, (int)(x.s));
827 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
832 ** time( TIMESTRING, MOD, MOD, ...)
834 ** Return HH:MM:SS
836 static void timeFunc(
837 sqlite3_context *context,
838 int argc,
839 sqlite3_value **argv
841 DateTime x;
842 if( isDate(context, argc, argv, &x)==0 ){
843 char zBuf[100];
844 computeHMS(&x);
845 sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
846 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
851 ** date( TIMESTRING, MOD, MOD, ...)
853 ** Return YYYY-MM-DD
855 static void dateFunc(
856 sqlite3_context *context,
857 int argc,
858 sqlite3_value **argv
860 DateTime x;
861 if( isDate(context, argc, argv, &x)==0 ){
862 char zBuf[100];
863 computeYMD(&x);
864 sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
865 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
870 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
872 ** Return a string described by FORMAT. Conversions as follows:
874 ** %d day of month
875 ** %f ** fractional seconds SS.SSS
876 ** %H hour 00-24
877 ** %j day of year 000-366
878 ** %J ** Julian day number
879 ** %m month 01-12
880 ** %M minute 00-59
881 ** %s seconds since 1970-01-01
882 ** %S seconds 00-59
883 ** %w day of week 0-6 sunday==0
884 ** %W week of year 00-53
885 ** %Y year 0000-9999
886 ** %% %
888 static void strftimeFunc(
889 sqlite3_context *context,
890 int argc,
891 sqlite3_value **argv
893 DateTime x;
894 u64 n;
895 size_t i,j;
896 char *z;
897 sqlite3 *db;
898 const char *zFmt = (const char*)sqlite3_value_text(argv[0]);
899 char zBuf[100];
900 if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
901 db = sqlite3_context_db_handle(context);
902 for(i=0, n=1; zFmt[i]; i++, n++){
903 if( zFmt[i]=='%' ){
904 switch( zFmt[i+1] ){
905 case 'd':
906 case 'H':
907 case 'm':
908 case 'M':
909 case 'S':
910 case 'W':
911 n++;
912 /* fall thru */
913 case 'w':
914 case '%':
915 break;
916 case 'f':
917 n += 8;
918 break;
919 case 'j':
920 n += 3;
921 break;
922 case 'Y':
923 n += 8;
924 break;
925 case 's':
926 case 'J':
927 n += 50;
928 break;
929 default:
930 return; /* ERROR. return a NULL */
932 i++;
935 testcase( n==sizeof(zBuf)-1 );
936 testcase( n==sizeof(zBuf) );
937 testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH]+1 );
938 testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH] );
939 if( n<sizeof(zBuf) ){
940 z = zBuf;
941 }else if( n>(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ){
942 sqlite3_result_error_toobig(context);
943 return;
944 }else{
945 z = sqlite3DbMallocRaw(db, (int)n);
946 if( z==0 ){
947 sqlite3_result_error_nomem(context);
948 return;
951 computeJD(&x);
952 computeYMD_HMS(&x);
953 for(i=j=0; zFmt[i]; i++){
954 if( zFmt[i]!='%' ){
955 z[j++] = zFmt[i];
956 }else{
957 i++;
958 switch( zFmt[i] ){
959 case 'd': sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break;
960 case 'f': {
961 double s = x.s;
962 if( s>59.999 ) s = 59.999;
963 sqlite3_snprintf(7, &z[j],"%06.3f", s);
964 j += sqlite3Strlen30(&z[j]);
965 break;
967 case 'H': sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break;
968 case 'W': /* Fall thru */
969 case 'j': {
970 int nDay; /* Number of days since 1st day of year */
971 DateTime y = x;
972 y.validJD = 0;
973 y.M = 1;
974 y.D = 1;
975 computeJD(&y);
976 nDay = (int)((x.iJD-y.iJD+43200000)/86400000);
977 if( zFmt[i]=='W' ){
978 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */
979 wd = (int)(((x.iJD+43200000)/86400000)%7);
980 sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);
981 j += 2;
982 }else{
983 sqlite3_snprintf(4, &z[j],"%03d",nDay+1);
984 j += 3;
986 break;
988 case 'J': {
989 sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0);
990 j+=sqlite3Strlen30(&z[j]);
991 break;
993 case 'm': sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;
994 case 'M': sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;
995 case 's': {
996 sqlite3_snprintf(30,&z[j],"%lld",
997 (i64)(x.iJD/1000 - 21086676*(i64)10000));
998 j += sqlite3Strlen30(&z[j]);
999 break;
1001 case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
1002 case 'w': {
1003 z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
1004 break;
1006 case 'Y': {
1007 sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
1008 break;
1010 default: z[j++] = '%'; break;
1014 z[j] = 0;
1015 sqlite3_result_text(context, z, -1,
1016 z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC);
1020 ** current_time()
1022 ** This function returns the same value as time('now').
1024 static void ctimeFunc(
1025 sqlite3_context *context,
1026 int NotUsed,
1027 sqlite3_value **NotUsed2
1029 UNUSED_PARAMETER2(NotUsed, NotUsed2);
1030 timeFunc(context, 0, 0);
1034 ** current_date()
1036 ** This function returns the same value as date('now').
1038 static void cdateFunc(
1039 sqlite3_context *context,
1040 int NotUsed,
1041 sqlite3_value **NotUsed2
1043 UNUSED_PARAMETER2(NotUsed, NotUsed2);
1044 dateFunc(context, 0, 0);
1048 ** current_timestamp()
1050 ** This function returns the same value as datetime('now').
1052 static void ctimestampFunc(
1053 sqlite3_context *context,
1054 int NotUsed,
1055 sqlite3_value **NotUsed2
1057 UNUSED_PARAMETER2(NotUsed, NotUsed2);
1058 datetimeFunc(context, 0, 0);
1060 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
1062 #ifdef SQLITE_OMIT_DATETIME_FUNCS
1064 ** If the library is compiled to omit the full-scale date and time
1065 ** handling (to get a smaller binary), the following minimal version
1066 ** of the functions current_time(), current_date() and current_timestamp()
1067 ** are included instead. This is to support column declarations that
1068 ** include "DEFAULT CURRENT_TIME" etc.
1070 ** This function uses the C-library functions time(), gmtime()
1071 ** and strftime(). The format string to pass to strftime() is supplied
1072 ** as the user-data for the function.
1074 static void currentTimeFunc(
1075 sqlite3_context *context,
1076 int argc,
1077 sqlite3_value **argv
1079 time_t t;
1080 char *zFormat = (char *)sqlite3_user_data(context);
1081 sqlite3 *db;
1082 sqlite3_int64 iT;
1083 struct tm *pTm;
1084 struct tm sNow;
1085 char zBuf[20];
1087 UNUSED_PARAMETER(argc);
1088 UNUSED_PARAMETER(argv);
1090 iT = sqlite3StmtCurrentTime(context);
1091 if( iT<=0 ) return;
1092 t = iT/1000 - 10000*(sqlite3_int64)21086676;
1093 #ifdef HAVE_GMTIME_R
1094 pTm = gmtime_r(&t, &sNow);
1095 #else
1096 sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1097 pTm = gmtime(&t);
1098 if( pTm ) memcpy(&sNow, pTm, sizeof(sNow));
1099 sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1100 #endif
1101 if( pTm ){
1102 strftime(zBuf, 20, zFormat, &sNow);
1103 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
1106 #endif
1109 ** This function registered all of the above C functions as SQL
1110 ** functions. This should be the only routine in this file with
1111 ** external linkage.
1113 void sqlite3RegisterDateTimeFunctions(void){
1114 static SQLITE_WSD FuncDef aDateTimeFuncs[] = {
1115 #ifndef SQLITE_OMIT_DATETIME_FUNCS
1116 FUNCTION(julianday, -1, 0, 0, juliandayFunc ),
1117 FUNCTION(date, -1, 0, 0, dateFunc ),
1118 FUNCTION(time, -1, 0, 0, timeFunc ),
1119 FUNCTION(datetime, -1, 0, 0, datetimeFunc ),
1120 FUNCTION(strftime, -1, 0, 0, strftimeFunc ),
1121 FUNCTION(current_time, 0, 0, 0, ctimeFunc ),
1122 FUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
1123 FUNCTION(current_date, 0, 0, 0, cdateFunc ),
1124 #else
1125 STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc),
1126 STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc),
1127 STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
1128 #endif
1130 int i;
1131 FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions);
1132 FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aDateTimeFuncs);
1134 for(i=0; i<ArraySize(aDateTimeFuncs); i++){
1135 sqlite3FuncDefInsert(pHash, &aFunc[i]);