Annotation of embedaddon/sqlite3/src/date.c, revision 1.1.1.1
1.1 misho 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.
10: **
11: *************************************************************************
12: ** This file contains the C functions that implement date and time
13: ** functions for SQLite.
14: **
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.
18: **
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.
23: **
24: ** 1970-01-01 00:00:00 is JD 2440587.5
25: ** 2000-01-01 00:00:00 is JD 2451544.5
26: **
27: ** This implemention 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.
31: **
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.
36: **
37: ** The conversion algorithms are implemented based on descriptions
38: ** in the following text:
39: **
40: ** Jean Meeus
41: ** Astronomical Algorithms, 2nd Edition, 1998
42: ** ISBM 0-943396-61-1
43: ** Willmann-Bell, Inc
44: ** Richmond, Virginia (USA)
45: */
46: #include "sqliteInt.h"
47: #include <stdlib.h>
48: #include <assert.h>
49: #include <time.h>
50:
51: #ifndef SQLITE_OMIT_DATETIME_FUNCS
52:
53:
54: /*
55: ** A structure for holding a single date and time.
56: */
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 */
68: };
69:
70:
71: /*
72: ** Convert zDate into one or more integers. Additional arguments
73: ** come in groups of 5 as follows:
74: **
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.
80: **
81: ** Conversions continue until one with nextC==0 is encountered.
82: ** The function returns the number of successful conversions.
83: */
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;
104: }
105: val = val*10 + *zDate - '0';
106: zDate++;
107: }
108: if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){
109: goto end_getDigits;
110: }
111: *pVal = val;
112: zDate++;
113: cnt++;
114: }while( nextC );
115: end_getDigits:
116: va_end(ap);
117: return cnt;
118: }
119:
120: /*
121: ** Parse a timezone extension on the end of a date-time.
122: ** The extension is of the form:
123: **
124: ** (+/-)HH:MM
125: **
126: ** Or the "zulu" notation:
127: **
128: ** Z
129: **
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.
133: **
134: ** A missing specifier is not considered an error.
135: */
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;
152: }
153: zDate++;
154: if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){
155: return 1;
156: }
157: zDate += 5;
158: p->tz = sgn*(nMn + nHr*60);
159: zulu_time:
160: while( sqlite3Isspace(*zDate) ){ zDate++; }
161: return *zDate!=0;
162: }
163:
164: /*
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.
168: **
169: ** Return 1 if there is a parsing error and 0 on success.
170: */
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;
176: }
177: zDate += 5;
178: if( *zDate==':' ){
179: zDate++;
180: if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
181: return 1;
182: }
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++;
191: }
192: ms /= rScale;
193: }
194: }else{
195: s = 0;
196: }
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;
205: }
206:
207: /*
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.
210: **
211: ** Reference: Meeus page 61
212: */
213: static void computeJD(DateTime *p){
214: int Y, M, D, A, B, X1, X2;
215:
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;
225: }
226: if( M<=2 ){
227: Y--;
228: M += 12;
229: }
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;
243: }
244: }
245: }
246:
247: /*
248: ** Parse dates of the form
249: **
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
254: **
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.
258: */
259: static int parseYyyyMmDd(const char *zDate, DateTime *p){
260: int Y, M, D, neg;
261:
262: if( zDate[0]=='-' ){
263: zDate++;
264: neg = 1;
265: }else{
266: neg = 0;
267: }
268: if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){
269: return 1;
270: }
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;
279: }
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);
287: }
288: return 0;
289: }
290:
291: /*
292: ** Set the time to the current time reported by the VFS.
293: **
294: ** Return the number of errors.
295: */
296: static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
297: sqlite3 *db = sqlite3_context_db_handle(context);
298: if( sqlite3OsCurrentTimeInt64(db->pVfs, &p->iJD)==SQLITE_OK ){
299: p->validJD = 1;
300: return 0;
301: }else{
302: return 1;
303: }
304: }
305:
306: /*
307: ** Attempt to parse the given string into a Julian Day Number. Return
308: ** the number of errors.
309: **
310: ** The following are acceptable forms for the input string:
311: **
312: ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM
313: ** DDDD.DD
314: ** now
315: **
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.
321: */
322: static int parseDateOrTime(
323: sqlite3_context *context,
324: const char *zDate,
325: DateTime *p
326: ){
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;
338: }
339: return 1;
340: }
341:
342: /*
343: ** Compute the Year, Month, and Day from the julian day number.
344: */
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;
364: }
365: p->validYMD = 1;
366: }
367:
368: /*
369: ** Compute the Hour, Minute, and Seconds from the julian day number.
370: */
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;
384: }
385:
386: /*
387: ** Compute both YMD and HMS
388: */
389: static void computeYMD_HMS(DateTime *p){
390: computeYMD(p);
391: computeHMS(p);
392: }
393:
394: /*
395: ** Clear the YMD and HMS and the TZ
396: */
397: static void clearYMD_HMS_TZ(DateTime *p){
398: p->validYMD = 0;
399: p->validHMS = 0;
400: p->validTZ = 0;
401: }
402:
403: /*
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.
408: **
409: ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
410: **
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().
414: */
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
419:
420: #ifndef SQLITE_OMIT_LOCALTIME
421: /*
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.
426: **
427: ** If the sqlite3GlobalConfig.bLocaltimeFault variable is true then this
428: ** routine will always fail.
429: */
430: static int osLocaltime(time_t *t, struct tm *pTm){
431: int rc;
432: #if (!defined(HAVE_LOCALTIME_R) || !HAVE_LOCALTIME_R) \
433: && (!defined(HAVE_LOCALTIME_S) || !HAVE_LOCALTIME_S)
434: struct tm *pX;
435: #if SQLITE_THREADSAFE>0
436: sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER);
437: #endif
438: sqlite3_mutex_enter(mutex);
439: pX = localtime(t);
440: #ifndef SQLITE_OMIT_BUILTIN_TEST
441: if( sqlite3GlobalConfig.bLocaltimeFault ) pX = 0;
442: #endif
443: if( pX ) *pTm = *pX;
444: sqlite3_mutex_leave(mutex);
445: rc = pX==0;
446: #else
447: #ifndef SQLITE_OMIT_BUILTIN_TEST
448: if( sqlite3GlobalConfig.bLocaltimeFault ) return 1;
449: #endif
450: #if defined(HAVE_LOCALTIME_R) && HAVE_LOCALTIME_R
451: rc = localtime_r(t, pTm)==0;
452: #else
453: rc = localtime_s(pTm, t);
454: #endif /* HAVE_LOCALTIME_R */
455: #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
456: return rc;
457: }
458: #endif /* SQLITE_OMIT_LOCALTIME */
459:
460:
461: #ifndef SQLITE_OMIT_LOCALTIME
462: /*
463: ** Compute the difference (in milliseconds) between localtime and UTC
464: ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs,
465: ** return this value and set *pRc to SQLITE_OK.
466: **
467: ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value
468: ** is undefined in this case.
469: */
470: static sqlite3_int64 localtimeOffset(
471: DateTime *p, /* Date at which to calculate offset */
472: sqlite3_context *pCtx, /* Write error here if one occurs */
473: int *pRc /* OUT: Error code. SQLITE_OK or ERROR */
474: ){
475: DateTime x, y;
476: time_t t;
477: struct tm sLocal;
478:
479: /* Initialize the contents of sLocal to avoid a compiler warning. */
480: memset(&sLocal, 0, sizeof(sLocal));
481:
482: x = *p;
483: computeYMD_HMS(&x);
484: if( x.Y<1971 || x.Y>=2038 ){
485: x.Y = 2000;
486: x.M = 1;
487: x.D = 1;
488: x.h = 0;
489: x.m = 0;
490: x.s = 0.0;
491: } else {
492: int s = (int)(x.s + 0.5);
493: x.s = s;
494: }
495: x.tz = 0;
496: x.validJD = 0;
497: computeJD(&x);
498: t = (time_t)(x.iJD/1000 - 21086676*(i64)10000);
499: if( osLocaltime(&t, &sLocal) ){
500: sqlite3_result_error(pCtx, "local time unavailable", -1);
501: *pRc = SQLITE_ERROR;
502: return 0;
503: }
504: y.Y = sLocal.tm_year + 1900;
505: y.M = sLocal.tm_mon + 1;
506: y.D = sLocal.tm_mday;
507: y.h = sLocal.tm_hour;
508: y.m = sLocal.tm_min;
509: y.s = sLocal.tm_sec;
510: y.validYMD = 1;
511: y.validHMS = 1;
512: y.validJD = 0;
513: y.validTZ = 0;
514: computeJD(&y);
515: *pRc = SQLITE_OK;
516: return y.iJD - x.iJD;
517: }
518: #endif /* SQLITE_OMIT_LOCALTIME */
519:
520: /*
521: ** Process a modifier to a date-time stamp. The modifiers are
522: ** as follows:
523: **
524: ** NNN days
525: ** NNN hours
526: ** NNN minutes
527: ** NNN.NNNN seconds
528: ** NNN months
529: ** NNN years
530: ** start of month
531: ** start of year
532: ** start of week
533: ** start of day
534: ** weekday N
535: ** unixepoch
536: ** localtime
537: ** utc
538: **
539: ** Return 0 on success and 1 if there is any kind of error. If the error
540: ** is in a system call (i.e. localtime()), then an error message is written
541: ** to context pCtx. If the error is an unrecognized modifier, no error is
542: ** written to pCtx.
543: */
544: static int parseModifier(sqlite3_context *pCtx, const char *zMod, DateTime *p){
545: int rc = 1;
546: int n;
547: double r;
548: char *z, zBuf[30];
549: z = zBuf;
550: for(n=0; n<ArraySize(zBuf)-1 && zMod[n]; n++){
551: z[n] = (char)sqlite3UpperToLower[(u8)zMod[n]];
552: }
553: z[n] = 0;
554: switch( z[0] ){
555: #ifndef SQLITE_OMIT_LOCALTIME
556: case 'l': {
557: /* localtime
558: **
559: ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
560: ** show local time.
561: */
562: if( strcmp(z, "localtime")==0 ){
563: computeJD(p);
564: p->iJD += localtimeOffset(p, pCtx, &rc);
565: clearYMD_HMS_TZ(p);
566: }
567: break;
568: }
569: #endif
570: case 'u': {
571: /*
572: ** unixepoch
573: **
574: ** Treat the current value of p->iJD as the number of
575: ** seconds since 1970. Convert to a real julian day number.
576: */
577: if( strcmp(z, "unixepoch")==0 && p->validJD ){
578: p->iJD = (p->iJD + 43200)/86400 + 21086676*(i64)10000000;
579: clearYMD_HMS_TZ(p);
580: rc = 0;
581: }
582: #ifndef SQLITE_OMIT_LOCALTIME
583: else if( strcmp(z, "utc")==0 ){
584: sqlite3_int64 c1;
585: computeJD(p);
586: c1 = localtimeOffset(p, pCtx, &rc);
587: if( rc==SQLITE_OK ){
588: p->iJD -= c1;
589: clearYMD_HMS_TZ(p);
590: p->iJD += c1 - localtimeOffset(p, pCtx, &rc);
591: }
592: }
593: #endif
594: break;
595: }
596: case 'w': {
597: /*
598: ** weekday N
599: **
600: ** Move the date to the same time on the next occurrence of
601: ** weekday N where 0==Sunday, 1==Monday, and so forth. If the
602: ** date is already on the appropriate weekday, this is a no-op.
603: */
604: if( strncmp(z, "weekday ", 8)==0
605: && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)
606: && (n=(int)r)==r && n>=0 && r<7 ){
607: sqlite3_int64 Z;
608: computeYMD_HMS(p);
609: p->validTZ = 0;
610: p->validJD = 0;
611: computeJD(p);
612: Z = ((p->iJD + 129600000)/86400000) % 7;
613: if( Z>n ) Z -= 7;
614: p->iJD += (n - Z)*86400000;
615: clearYMD_HMS_TZ(p);
616: rc = 0;
617: }
618: break;
619: }
620: case 's': {
621: /*
622: ** start of TTTTT
623: **
624: ** Move the date backwards to the beginning of the current day,
625: ** or month or year.
626: */
627: if( strncmp(z, "start of ", 9)!=0 ) break;
628: z += 9;
629: computeYMD(p);
630: p->validHMS = 1;
631: p->h = p->m = 0;
632: p->s = 0.0;
633: p->validTZ = 0;
634: p->validJD = 0;
635: if( strcmp(z,"month")==0 ){
636: p->D = 1;
637: rc = 0;
638: }else if( strcmp(z,"year")==0 ){
639: computeYMD(p);
640: p->M = 1;
641: p->D = 1;
642: rc = 0;
643: }else if( strcmp(z,"day")==0 ){
644: rc = 0;
645: }
646: break;
647: }
648: case '+':
649: case '-':
650: case '0':
651: case '1':
652: case '2':
653: case '3':
654: case '4':
655: case '5':
656: case '6':
657: case '7':
658: case '8':
659: case '9': {
660: double rRounder;
661: for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){}
662: if( !sqlite3AtoF(z, &r, n, SQLITE_UTF8) ){
663: rc = 1;
664: break;
665: }
666: if( z[n]==':' ){
667: /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
668: ** specified number of hours, minutes, seconds, and fractional seconds
669: ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be
670: ** omitted.
671: */
672: const char *z2 = z;
673: DateTime tx;
674: sqlite3_int64 day;
675: if( !sqlite3Isdigit(*z2) ) z2++;
676: memset(&tx, 0, sizeof(tx));
677: if( parseHhMmSs(z2, &tx) ) break;
678: computeJD(&tx);
679: tx.iJD -= 43200000;
680: day = tx.iJD/86400000;
681: tx.iJD -= day*86400000;
682: if( z[0]=='-' ) tx.iJD = -tx.iJD;
683: computeJD(p);
684: clearYMD_HMS_TZ(p);
685: p->iJD += tx.iJD;
686: rc = 0;
687: break;
688: }
689: z += n;
690: while( sqlite3Isspace(*z) ) z++;
691: n = sqlite3Strlen30(z);
692: if( n>10 || n<3 ) break;
693: if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
694: computeJD(p);
695: rc = 0;
696: rRounder = r<0 ? -0.5 : +0.5;
697: if( n==3 && strcmp(z,"day")==0 ){
698: p->iJD += (sqlite3_int64)(r*86400000.0 + rRounder);
699: }else if( n==4 && strcmp(z,"hour")==0 ){
700: p->iJD += (sqlite3_int64)(r*(86400000.0/24.0) + rRounder);
701: }else if( n==6 && strcmp(z,"minute")==0 ){
702: p->iJD += (sqlite3_int64)(r*(86400000.0/(24.0*60.0)) + rRounder);
703: }else if( n==6 && strcmp(z,"second")==0 ){
704: p->iJD += (sqlite3_int64)(r*(86400000.0/(24.0*60.0*60.0)) + rRounder);
705: }else if( n==5 && strcmp(z,"month")==0 ){
706: int x, y;
707: computeYMD_HMS(p);
708: p->M += (int)r;
709: x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
710: p->Y += x;
711: p->M -= x*12;
712: p->validJD = 0;
713: computeJD(p);
714: y = (int)r;
715: if( y!=r ){
716: p->iJD += (sqlite3_int64)((r - y)*30.0*86400000.0 + rRounder);
717: }
718: }else if( n==4 && strcmp(z,"year")==0 ){
719: int y = (int)r;
720: computeYMD_HMS(p);
721: p->Y += y;
722: p->validJD = 0;
723: computeJD(p);
724: if( y!=r ){
725: p->iJD += (sqlite3_int64)((r - y)*365.0*86400000.0 + rRounder);
726: }
727: }else{
728: rc = 1;
729: }
730: clearYMD_HMS_TZ(p);
731: break;
732: }
733: default: {
734: break;
735: }
736: }
737: return rc;
738: }
739:
740: /*
741: ** Process time function arguments. argv[0] is a date-time stamp.
742: ** argv[1] and following are modifiers. Parse them all and write
743: ** the resulting time into the DateTime structure p. Return 0
744: ** on success and 1 if there are any errors.
745: **
746: ** If there are zero parameters (if even argv[0] is undefined)
747: ** then assume a default value of "now" for argv[0].
748: */
749: static int isDate(
750: sqlite3_context *context,
751: int argc,
752: sqlite3_value **argv,
753: DateTime *p
754: ){
755: int i;
756: const unsigned char *z;
757: int eType;
758: memset(p, 0, sizeof(*p));
759: if( argc==0 ){
760: return setDateTimeToCurrent(context, p);
761: }
762: if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
763: || eType==SQLITE_INTEGER ){
764: p->iJD = (sqlite3_int64)(sqlite3_value_double(argv[0])*86400000.0 + 0.5);
765: p->validJD = 1;
766: }else{
767: z = sqlite3_value_text(argv[0]);
768: if( !z || parseDateOrTime(context, (char*)z, p) ){
769: return 1;
770: }
771: }
772: for(i=1; i<argc; i++){
773: z = sqlite3_value_text(argv[i]);
774: if( z==0 || parseModifier(context, (char*)z, p) ) return 1;
775: }
776: return 0;
777: }
778:
779:
780: /*
781: ** The following routines implement the various date and time functions
782: ** of SQLite.
783: */
784:
785: /*
786: ** julianday( TIMESTRING, MOD, MOD, ...)
787: **
788: ** Return the julian day number of the date specified in the arguments
789: */
790: static void juliandayFunc(
791: sqlite3_context *context,
792: int argc,
793: sqlite3_value **argv
794: ){
795: DateTime x;
796: if( isDate(context, argc, argv, &x)==0 ){
797: computeJD(&x);
798: sqlite3_result_double(context, x.iJD/86400000.0);
799: }
800: }
801:
802: /*
803: ** datetime( TIMESTRING, MOD, MOD, ...)
804: **
805: ** Return YYYY-MM-DD HH:MM:SS
806: */
807: static void datetimeFunc(
808: sqlite3_context *context,
809: int argc,
810: sqlite3_value **argv
811: ){
812: DateTime x;
813: if( isDate(context, argc, argv, &x)==0 ){
814: char zBuf[100];
815: computeYMD_HMS(&x);
816: sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d",
817: x.Y, x.M, x.D, x.h, x.m, (int)(x.s));
818: sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
819: }
820: }
821:
822: /*
823: ** time( TIMESTRING, MOD, MOD, ...)
824: **
825: ** Return HH:MM:SS
826: */
827: static void timeFunc(
828: sqlite3_context *context,
829: int argc,
830: sqlite3_value **argv
831: ){
832: DateTime x;
833: if( isDate(context, argc, argv, &x)==0 ){
834: char zBuf[100];
835: computeHMS(&x);
836: sqlite3_snprintf(sizeof(zBuf), zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
837: sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
838: }
839: }
840:
841: /*
842: ** date( TIMESTRING, MOD, MOD, ...)
843: **
844: ** Return YYYY-MM-DD
845: */
846: static void dateFunc(
847: sqlite3_context *context,
848: int argc,
849: sqlite3_value **argv
850: ){
851: DateTime x;
852: if( isDate(context, argc, argv, &x)==0 ){
853: char zBuf[100];
854: computeYMD(&x);
855: sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
856: sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
857: }
858: }
859:
860: /*
861: ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
862: **
863: ** Return a string described by FORMAT. Conversions as follows:
864: **
865: ** %d day of month
866: ** %f ** fractional seconds SS.SSS
867: ** %H hour 00-24
868: ** %j day of year 000-366
869: ** %J ** Julian day number
870: ** %m month 01-12
871: ** %M minute 00-59
872: ** %s seconds since 1970-01-01
873: ** %S seconds 00-59
874: ** %w day of week 0-6 sunday==0
875: ** %W week of year 00-53
876: ** %Y year 0000-9999
877: ** %% %
878: */
879: static void strftimeFunc(
880: sqlite3_context *context,
881: int argc,
882: sqlite3_value **argv
883: ){
884: DateTime x;
885: u64 n;
886: size_t i,j;
887: char *z;
888: sqlite3 *db;
889: const char *zFmt = (const char*)sqlite3_value_text(argv[0]);
890: char zBuf[100];
891: if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return;
892: db = sqlite3_context_db_handle(context);
893: for(i=0, n=1; zFmt[i]; i++, n++){
894: if( zFmt[i]=='%' ){
895: switch( zFmt[i+1] ){
896: case 'd':
897: case 'H':
898: case 'm':
899: case 'M':
900: case 'S':
901: case 'W':
902: n++;
903: /* fall thru */
904: case 'w':
905: case '%':
906: break;
907: case 'f':
908: n += 8;
909: break;
910: case 'j':
911: n += 3;
912: break;
913: case 'Y':
914: n += 8;
915: break;
916: case 's':
917: case 'J':
918: n += 50;
919: break;
920: default:
921: return; /* ERROR. return a NULL */
922: }
923: i++;
924: }
925: }
926: testcase( n==sizeof(zBuf)-1 );
927: testcase( n==sizeof(zBuf) );
928: testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH]+1 );
929: testcase( n==(u64)db->aLimit[SQLITE_LIMIT_LENGTH] );
930: if( n<sizeof(zBuf) ){
931: z = zBuf;
932: }else if( n>(u64)db->aLimit[SQLITE_LIMIT_LENGTH] ){
933: sqlite3_result_error_toobig(context);
934: return;
935: }else{
936: z = sqlite3DbMallocRaw(db, (int)n);
937: if( z==0 ){
938: sqlite3_result_error_nomem(context);
939: return;
940: }
941: }
942: computeJD(&x);
943: computeYMD_HMS(&x);
944: for(i=j=0; zFmt[i]; i++){
945: if( zFmt[i]!='%' ){
946: z[j++] = zFmt[i];
947: }else{
948: i++;
949: switch( zFmt[i] ){
950: case 'd': sqlite3_snprintf(3, &z[j],"%02d",x.D); j+=2; break;
951: case 'f': {
952: double s = x.s;
953: if( s>59.999 ) s = 59.999;
954: sqlite3_snprintf(7, &z[j],"%06.3f", s);
955: j += sqlite3Strlen30(&z[j]);
956: break;
957: }
958: case 'H': sqlite3_snprintf(3, &z[j],"%02d",x.h); j+=2; break;
959: case 'W': /* Fall thru */
960: case 'j': {
961: int nDay; /* Number of days since 1st day of year */
962: DateTime y = x;
963: y.validJD = 0;
964: y.M = 1;
965: y.D = 1;
966: computeJD(&y);
967: nDay = (int)((x.iJD-y.iJD+43200000)/86400000);
968: if( zFmt[i]=='W' ){
969: int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */
970: wd = (int)(((x.iJD+43200000)/86400000)%7);
971: sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);
972: j += 2;
973: }else{
974: sqlite3_snprintf(4, &z[j],"%03d",nDay+1);
975: j += 3;
976: }
977: break;
978: }
979: case 'J': {
980: sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0);
981: j+=sqlite3Strlen30(&z[j]);
982: break;
983: }
984: case 'm': sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;
985: case 'M': sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;
986: case 's': {
987: sqlite3_snprintf(30,&z[j],"%lld",
988: (i64)(x.iJD/1000 - 21086676*(i64)10000));
989: j += sqlite3Strlen30(&z[j]);
990: break;
991: }
992: case 'S': sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
993: case 'w': {
994: z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
995: break;
996: }
997: case 'Y': {
998: sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=sqlite3Strlen30(&z[j]);
999: break;
1000: }
1001: default: z[j++] = '%'; break;
1002: }
1003: }
1004: }
1005: z[j] = 0;
1006: sqlite3_result_text(context, z, -1,
1007: z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC);
1008: }
1009:
1010: /*
1011: ** current_time()
1012: **
1013: ** This function returns the same value as time('now').
1014: */
1015: static void ctimeFunc(
1016: sqlite3_context *context,
1017: int NotUsed,
1018: sqlite3_value **NotUsed2
1019: ){
1020: UNUSED_PARAMETER2(NotUsed, NotUsed2);
1021: timeFunc(context, 0, 0);
1022: }
1023:
1024: /*
1025: ** current_date()
1026: **
1027: ** This function returns the same value as date('now').
1028: */
1029: static void cdateFunc(
1030: sqlite3_context *context,
1031: int NotUsed,
1032: sqlite3_value **NotUsed2
1033: ){
1034: UNUSED_PARAMETER2(NotUsed, NotUsed2);
1035: dateFunc(context, 0, 0);
1036: }
1037:
1038: /*
1039: ** current_timestamp()
1040: **
1041: ** This function returns the same value as datetime('now').
1042: */
1043: static void ctimestampFunc(
1044: sqlite3_context *context,
1045: int NotUsed,
1046: sqlite3_value **NotUsed2
1047: ){
1048: UNUSED_PARAMETER2(NotUsed, NotUsed2);
1049: datetimeFunc(context, 0, 0);
1050: }
1051: #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
1052:
1053: #ifdef SQLITE_OMIT_DATETIME_FUNCS
1054: /*
1055: ** If the library is compiled to omit the full-scale date and time
1056: ** handling (to get a smaller binary), the following minimal version
1057: ** of the functions current_time(), current_date() and current_timestamp()
1058: ** are included instead. This is to support column declarations that
1059: ** include "DEFAULT CURRENT_TIME" etc.
1060: **
1061: ** This function uses the C-library functions time(), gmtime()
1062: ** and strftime(). The format string to pass to strftime() is supplied
1063: ** as the user-data for the function.
1064: */
1065: static void currentTimeFunc(
1066: sqlite3_context *context,
1067: int argc,
1068: sqlite3_value **argv
1069: ){
1070: time_t t;
1071: char *zFormat = (char *)sqlite3_user_data(context);
1072: sqlite3 *db;
1073: sqlite3_int64 iT;
1074: struct tm *pTm;
1075: struct tm sNow;
1076: char zBuf[20];
1077:
1078: UNUSED_PARAMETER(argc);
1079: UNUSED_PARAMETER(argv);
1080:
1081: db = sqlite3_context_db_handle(context);
1082: if( sqlite3OsCurrentTimeInt64(db->pVfs, &iT) ) return;
1083: t = iT/1000 - 10000*(sqlite3_int64)21086676;
1084: #ifdef HAVE_GMTIME_R
1085: pTm = gmtime_r(&t, &sNow);
1086: #else
1087: sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1088: pTm = gmtime(&t);
1089: if( pTm ) memcpy(&sNow, pTm, sizeof(sNow));
1090: sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1091: #endif
1092: if( pTm ){
1093: strftime(zBuf, 20, zFormat, &sNow);
1094: sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
1095: }
1096: }
1097: #endif
1098:
1099: /*
1100: ** This function registered all of the above C functions as SQL
1101: ** functions. This should be the only routine in this file with
1102: ** external linkage.
1103: */
1104: void sqlite3RegisterDateTimeFunctions(void){
1105: static SQLITE_WSD FuncDef aDateTimeFuncs[] = {
1106: #ifndef SQLITE_OMIT_DATETIME_FUNCS
1107: FUNCTION(julianday, -1, 0, 0, juliandayFunc ),
1108: FUNCTION(date, -1, 0, 0, dateFunc ),
1109: FUNCTION(time, -1, 0, 0, timeFunc ),
1110: FUNCTION(datetime, -1, 0, 0, datetimeFunc ),
1111: FUNCTION(strftime, -1, 0, 0, strftimeFunc ),
1112: FUNCTION(current_time, 0, 0, 0, ctimeFunc ),
1113: FUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
1114: FUNCTION(current_date, 0, 0, 0, cdateFunc ),
1115: #else
1116: STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc),
1117: STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc),
1118: STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
1119: #endif
1120: };
1121: int i;
1122: FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions);
1123: FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aDateTimeFuncs);
1124:
1125: for(i=0; i<ArraySize(aDateTimeFuncs); i++){
1126: sqlite3FuncDefInsert(pHash, &aFunc[i]);
1127: }
1128: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to date.c CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / src