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 ** sqliteRegisterDateTimeFunctions() found at the bottom of the file.
17 ** All other code has file scope.
18 **
19 ** $Id: date.c,v 1.16.2.2 2004/07/20 00:40:01 drh Exp $
20 **
21 ** NOTES:
22 **
23 ** SQLite processes all times and dates as Julian Day numbers. The
24 ** dates and times are stored as the number of days since noon
25 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian
26 ** calendar system.
27 **
28 ** 1970-01-01 00:00:00 is JD 2440587.5
29 ** 2000-01-01 00:00:00 is JD 2451544.5
30 **
31 ** This implemention requires years to be expressed as a 4-digit number
32 ** which means that only dates between 0000-01-01 and 9999-12-31 can
33 ** be represented, even though julian day numbers allow a much wider
34 ** range of dates.
35 **
36 ** The Gregorian calendar system is used for all dates and times,
37 ** even those that predate the Gregorian calendar. Historians usually
38 ** use the Julian calendar for dates prior to 1582-10-15 and for some
39 ** dates afterwards, depending on locale. Beware of this difference.
40 **
41 ** The conversion algorithms are implemented based on descriptions
42 ** in the following text:
43 **
44 ** Jean Meeus
45 ** Astronomical Algorithms, 2nd Edition, 1998
46 ** ISBM 0-943396-61-1
47 ** Willmann-Bell, Inc
48 ** Richmond, Virginia (USA)
49 */
50 #include "os.h"
51 #include "sqliteInt.h"
52 #include <ctype.h>
53 #include <stdlib.h>
54 #include <assert.h>
55 #include <time.h>
56
57 #ifndef SQLITE_OMIT_DATETIME_FUNCS
58
59 /*
60 ** A structure for holding a single date and time.
61 */
62 typedef struct DateTime DateTime;
63 struct DateTime {
64 double rJD; /* The julian day number */
65 int Y, M, D; /* Year, month, and day */
66 int h, m; /* Hour and minutes */
67 int tz; /* Timezone offset in minutes */
68 double s; /* Seconds */
69 char validYMD; /* True if Y,M,D are valid */
70 char validHMS; /* True if h,m,s are valid */
71 char validJD; /* True if rJD is valid */
72 char validTZ; /* True if tz is valid */
73 };
74
75
76 /*
77 ** Convert zDate into one or more integers. Additional arguments
78 ** come in groups of 5 as follows:
79 **
80 ** N number of digits in the integer
81 ** min minimum allowed value of the integer
82 ** max maximum allowed value of the integer
83 ** nextC first character after the integer
84 ** pVal where to write the integers value.
85 **
86 ** Conversions continue until one with nextC==0 is encountered.
87 ** The function returns the number of successful conversions.
88 */
getDigits(const char * zDate,...)89 static int getDigits(const char *zDate, ...){
90 va_list ap;
91 int val;
92 int N;
93 int min;
94 int max;
95 int nextC;
96 int *pVal;
97 int cnt = 0;
98 va_start(ap, zDate);
99 do{
100 N = va_arg(ap, int);
101 min = va_arg(ap, int);
102 max = va_arg(ap, int);
103 nextC = va_arg(ap, int);
104 pVal = va_arg(ap, int*);
105 val = 0;
106 while( N-- ){
107 if( !isdigit(*zDate) ){
108 return cnt;
109 }
110 val = val*10 + *zDate - '0';
111 zDate++;
112 }
113 if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){
114 return cnt;
115 }
116 *pVal = val;
117 zDate++;
118 cnt++;
119 }while( nextC );
120 return cnt;
121 }
122
123 /*
124 ** Read text from z[] and convert into a floating point number. Return
125 ** the number of digits converted.
126 */
getValue(const char * z,double * pR)127 static int getValue(const char *z, double *pR){
128 const char *zEnd;
129 *pR = sqliteAtoF(z, &zEnd);
130 return zEnd - z;
131 }
132
133 /*
134 ** Parse a timezone extension on the end of a date-time.
135 ** The extension is of the form:
136 **
137 ** (+/-)HH:MM
138 **
139 ** If the parse is successful, write the number of minutes
140 ** of change in *pnMin and return 0. If a parser error occurs,
141 ** return 0.
142 **
143 ** A missing specifier is not considered an error.
144 */
parseTimezone(const char * zDate,DateTime * p)145 static int parseTimezone(const char *zDate, DateTime *p){
146 int sgn = 0;
147 int nHr, nMn;
148 while( isspace(*zDate) ){ zDate++; }
149 p->tz = 0;
150 if( *zDate=='-' ){
151 sgn = -1;
152 }else if( *zDate=='+' ){
153 sgn = +1;
154 }else{
155 return *zDate!=0;
156 }
157 zDate++;
158 if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){
159 return 1;
160 }
161 zDate += 5;
162 p->tz = sgn*(nMn + nHr*60);
163 while( isspace(*zDate) ){ zDate++; }
164 return *zDate!=0;
165 }
166
167 /*
168 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
169 ** The HH, MM, and SS must each be exactly 2 digits. The
170 ** fractional seconds FFFF can be one or more digits.
171 **
172 ** Return 1 if there is a parsing error and 0 on success.
173 */
parseHhMmSs(const char * zDate,DateTime * p)174 static int parseHhMmSs(const char *zDate, DateTime *p){
175 int h, m, s;
176 double ms = 0.0;
177 if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){
178 return 1;
179 }
180 zDate += 5;
181 if( *zDate==':' ){
182 zDate++;
183 if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
184 return 1;
185 }
186 zDate += 2;
187 if( *zDate=='.' && isdigit(zDate[1]) ){
188 double rScale = 1.0;
189 zDate++;
190 while( isdigit(*zDate) ){
191 ms = ms*10.0 + *zDate - '0';
192 rScale *= 10.0;
193 zDate++;
194 }
195 ms /= rScale;
196 }
197 }else{
198 s = 0;
199 }
200 p->validJD = 0;
201 p->validHMS = 1;
202 p->h = h;
203 p->m = m;
204 p->s = s + ms;
205 if( parseTimezone(zDate, p) ) return 1;
206 p->validTZ = p->tz!=0;
207 return 0;
208 }
209
210 /*
211 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume
212 ** that the YYYY-MM-DD is according to the Gregorian calendar.
213 **
214 ** Reference: Meeus page 61
215 */
computeJD(DateTime * p)216 static void computeJD(DateTime *p){
217 int Y, M, D, A, B, X1, X2;
218
219 if( p->validJD ) return;
220 if( p->validYMD ){
221 Y = p->Y;
222 M = p->M;
223 D = p->D;
224 }else{
225 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */
226 M = 1;
227 D = 1;
228 }
229 if( M<=2 ){
230 Y--;
231 M += 12;
232 }
233 A = Y/100;
234 B = 2 - A + (A/4);
235 X1 = 365.25*(Y+4716);
236 X2 = 30.6001*(M+1);
237 p->rJD = X1 + X2 + D + B - 1524.5;
238 p->validJD = 1;
239 p->validYMD = 0;
240 if( p->validHMS ){
241 p->rJD += (p->h*3600.0 + p->m*60.0 + p->s)/86400.0;
242 if( p->validTZ ){
243 p->rJD += p->tz*60/86400.0;
244 p->validHMS = 0;
245 p->validTZ = 0;
246 }
247 }
248 }
249
250 /*
251 ** Parse dates of the form
252 **
253 ** YYYY-MM-DD HH:MM:SS.FFF
254 ** YYYY-MM-DD HH:MM:SS
255 ** YYYY-MM-DD HH:MM
256 ** YYYY-MM-DD
257 **
258 ** Write the result into the DateTime structure and return 0
259 ** on success and 1 if the input string is not a well-formed
260 ** date.
261 */
parseYyyyMmDd(const char * zDate,DateTime * p)262 static int parseYyyyMmDd(const char *zDate, DateTime *p){
263 int Y, M, D, neg;
264
265 if( zDate[0]=='-' ){
266 zDate++;
267 neg = 1;
268 }else{
269 neg = 0;
270 }
271 if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){
272 return 1;
273 }
274 zDate += 10;
275 while( isspace(*zDate) ){ zDate++; }
276 if( parseHhMmSs(zDate, p)==0 ){
277 /* We got the time */
278 }else if( *zDate==0 ){
279 p->validHMS = 0;
280 }else{
281 return 1;
282 }
283 p->validJD = 0;
284 p->validYMD = 1;
285 p->Y = neg ? -Y : Y;
286 p->M = M;
287 p->D = D;
288 if( p->validTZ ){
289 computeJD(p);
290 }
291 return 0;
292 }
293
294 /*
295 ** Attempt to parse the given string into a Julian Day Number. Return
296 ** the number of errors.
297 **
298 ** The following are acceptable forms for the input string:
299 **
300 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM
301 ** DDDD.DD
302 ** now
303 **
304 ** In the first form, the +/-HH:MM is always optional. The fractional
305 ** seconds extension (the ".FFF") is optional. The seconds portion
306 ** (":SS.FFF") is option. The year and date can be omitted as long
307 ** as there is a time string. The time string can be omitted as long
308 ** as there is a year and date.
309 */
parseDateOrTime(const char * zDate,DateTime * p)310 static int parseDateOrTime(const char *zDate, DateTime *p){
311 memset(p, 0, sizeof(*p));
312 if( parseYyyyMmDd(zDate,p)==0 ){
313 return 0;
314 }else if( parseHhMmSs(zDate, p)==0 ){
315 return 0;
316 }else if( sqliteStrICmp(zDate,"now")==0){
317 double r;
318 if( sqliteOsCurrentTime(&r)==0 ){
319 p->rJD = r;
320 p->validJD = 1;
321 return 0;
322 }
323 return 1;
324 }else if( sqliteIsNumber(zDate) ){
325 p->rJD = sqliteAtoF(zDate, 0);
326 p->validJD = 1;
327 return 0;
328 }
329 return 1;
330 }
331
332 /*
333 ** Compute the Year, Month, and Day from the julian day number.
334 */
computeYMD(DateTime * p)335 static void computeYMD(DateTime *p){
336 int Z, A, B, C, D, E, X1;
337 if( p->validYMD ) return;
338 if( !p->validJD ){
339 p->Y = 2000;
340 p->M = 1;
341 p->D = 1;
342 }else{
343 Z = p->rJD + 0.5;
344 A = (Z - 1867216.25)/36524.25;
345 A = Z + 1 + A - (A/4);
346 B = A + 1524;
347 C = (B - 122.1)/365.25;
348 D = 365.25*C;
349 E = (B-D)/30.6001;
350 X1 = 30.6001*E;
351 p->D = B - D - X1;
352 p->M = E<14 ? E-1 : E-13;
353 p->Y = p->M>2 ? C - 4716 : C - 4715;
354 }
355 p->validYMD = 1;
356 }
357
358 /*
359 ** Compute the Hour, Minute, and Seconds from the julian day number.
360 */
computeHMS(DateTime * p)361 static void computeHMS(DateTime *p){
362 int Z, s;
363 if( p->validHMS ) return;
364 Z = p->rJD + 0.5;
365 s = (p->rJD + 0.5 - Z)*86400000.0 + 0.5;
366 p->s = 0.001*s;
367 s = p->s;
368 p->s -= s;
369 p->h = s/3600;
370 s -= p->h*3600;
371 p->m = s/60;
372 p->s += s - p->m*60;
373 p->validHMS = 1;
374 }
375
376 /*
377 ** Compute both YMD and HMS
378 */
computeYMD_HMS(DateTime * p)379 static void computeYMD_HMS(DateTime *p){
380 computeYMD(p);
381 computeHMS(p);
382 }
383
384 /*
385 ** Clear the YMD and HMS and the TZ
386 */
clearYMD_HMS_TZ(DateTime * p)387 static void clearYMD_HMS_TZ(DateTime *p){
388 p->validYMD = 0;
389 p->validHMS = 0;
390 p->validTZ = 0;
391 }
392
393 /*
394 ** Compute the difference (in days) between localtime and UTC (a.k.a. GMT)
395 ** for the time value p where p is in UTC.
396 */
localtimeOffset(DateTime * p)397 static double localtimeOffset(DateTime *p){
398 DateTime x, y;
399 time_t t;
400 struct tm *pTm;
401 x = *p;
402 computeYMD_HMS(&x);
403 if( x.Y<1971 || x.Y>=2038 ){
404 x.Y = 2000;
405 x.M = 1;
406 x.D = 1;
407 x.h = 0;
408 x.m = 0;
409 x.s = 0.0;
410 } else {
411 int s = x.s + 0.5;
412 x.s = s;
413 }
414 x.tz = 0;
415 x.validJD = 0;
416 computeJD(&x);
417 t = (x.rJD-2440587.5)*86400.0 + 0.5;
418 sqliteOsEnterMutex();
419 pTm = localtime(&t);
420 y.Y = pTm->tm_year + 1900;
421 y.M = pTm->tm_mon + 1;
422 y.D = pTm->tm_mday;
423 y.h = pTm->tm_hour;
424 y.m = pTm->tm_min;
425 y.s = pTm->tm_sec;
426 sqliteOsLeaveMutex();
427 y.validYMD = 1;
428 y.validHMS = 1;
429 y.validJD = 0;
430 y.validTZ = 0;
431 computeJD(&y);
432 return y.rJD - x.rJD;
433 }
434
435 /*
436 ** Process a modifier to a date-time stamp. The modifiers are
437 ** as follows:
438 **
439 ** NNN days
440 ** NNN hours
441 ** NNN minutes
442 ** NNN.NNNN seconds
443 ** NNN months
444 ** NNN years
445 ** start of month
446 ** start of year
447 ** start of week
448 ** start of day
449 ** weekday N
450 ** unixepoch
451 ** localtime
452 ** utc
453 **
454 ** Return 0 on success and 1 if there is any kind of error.
455 */
parseModifier(const char * zMod,DateTime * p)456 static int parseModifier(const char *zMod, DateTime *p){
457 int rc = 1;
458 int n;
459 double r;
460 char *z, zBuf[30];
461 z = zBuf;
462 for(n=0; n<sizeof(zBuf)-1 && zMod[n]; n++){
463 z[n] = tolower(zMod[n]);
464 }
465 z[n] = 0;
466 switch( z[0] ){
467 case 'l': {
468 /* localtime
469 **
470 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
471 ** show local time.
472 */
473 if( strcmp(z, "localtime")==0 ){
474 computeJD(p);
475 p->rJD += localtimeOffset(p);
476 clearYMD_HMS_TZ(p);
477 rc = 0;
478 }
479 break;
480 }
481 case 'u': {
482 /*
483 ** unixepoch
484 **
485 ** Treat the current value of p->rJD as the number of
486 ** seconds since 1970. Convert to a real julian day number.
487 */
488 if( strcmp(z, "unixepoch")==0 && p->validJD ){
489 p->rJD = p->rJD/86400.0 + 2440587.5;
490 clearYMD_HMS_TZ(p);
491 rc = 0;
492 }else if( strcmp(z, "utc")==0 ){
493 double c1;
494 computeJD(p);
495 c1 = localtimeOffset(p);
496 p->rJD -= c1;
497 clearYMD_HMS_TZ(p);
498 p->rJD += c1 - localtimeOffset(p);
499 rc = 0;
500 }
501 break;
502 }
503 case 'w': {
504 /*
505 ** weekday N
506 **
507 ** Move the date to the same time on the next occurrance of
508 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the
509 ** date is already on the appropriate weekday, this is a no-op.
510 */
511 if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0
512 && (n=r)==r && n>=0 && r<7 ){
513 int Z;
514 computeYMD_HMS(p);
515 p->validTZ = 0;
516 p->validJD = 0;
517 computeJD(p);
518 Z = p->rJD + 1.5;
519 Z %= 7;
520 if( Z>n ) Z -= 7;
521 p->rJD += n - Z;
522 clearYMD_HMS_TZ(p);
523 rc = 0;
524 }
525 break;
526 }
527 case 's': {
528 /*
529 ** start of TTTTT
530 **
531 ** Move the date backwards to the beginning of the current day,
532 ** or month or year.
533 */
534 if( strncmp(z, "start of ", 9)!=0 ) break;
535 z += 9;
536 computeYMD(p);
537 p->validHMS = 1;
538 p->h = p->m = 0;
539 p->s = 0.0;
540 p->validTZ = 0;
541 p->validJD = 0;
542 if( strcmp(z,"month")==0 ){
543 p->D = 1;
544 rc = 0;
545 }else if( strcmp(z,"year")==0 ){
546 computeYMD(p);
547 p->M = 1;
548 p->D = 1;
549 rc = 0;
550 }else if( strcmp(z,"day")==0 ){
551 rc = 0;
552 }
553 break;
554 }
555 case '+':
556 case '-':
557 case '0':
558 case '1':
559 case '2':
560 case '3':
561 case '4':
562 case '5':
563 case '6':
564 case '7':
565 case '8':
566 case '9': {
567 n = getValue(z, &r);
568 if( n<=0 ) break;
569 if( z[n]==':' ){
570 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
571 ** specified number of hours, minutes, seconds, and fractional seconds
572 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be
573 ** omitted.
574 */
575 const char *z2 = z;
576 DateTime tx;
577 int day;
578 if( !isdigit(*z2) ) z2++;
579 memset(&tx, 0, sizeof(tx));
580 if( parseHhMmSs(z2, &tx) ) break;
581 computeJD(&tx);
582 tx.rJD -= 0.5;
583 day = (int)tx.rJD;
584 tx.rJD -= day;
585 if( z[0]=='-' ) tx.rJD = -tx.rJD;
586 computeJD(p);
587 clearYMD_HMS_TZ(p);
588 p->rJD += tx.rJD;
589 rc = 0;
590 break;
591 }
592 z += n;
593 while( isspace(z[0]) ) z++;
594 n = strlen(z);
595 if( n>10 || n<3 ) break;
596 if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
597 computeJD(p);
598 rc = 0;
599 if( n==3 && strcmp(z,"day")==0 ){
600 p->rJD += r;
601 }else if( n==4 && strcmp(z,"hour")==0 ){
602 p->rJD += r/24.0;
603 }else if( n==6 && strcmp(z,"minute")==0 ){
604 p->rJD += r/(24.0*60.0);
605 }else if( n==6 && strcmp(z,"second")==0 ){
606 p->rJD += r/(24.0*60.0*60.0);
607 }else if( n==5 && strcmp(z,"month")==0 ){
608 int x, y;
609 computeYMD_HMS(p);
610 p->M += r;
611 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
612 p->Y += x;
613 p->M -= x*12;
614 p->validJD = 0;
615 computeJD(p);
616 y = r;
617 if( y!=r ){
618 p->rJD += (r - y)*30.0;
619 }
620 }else if( n==4 && strcmp(z,"year")==0 ){
621 computeYMD_HMS(p);
622 p->Y += r;
623 p->validJD = 0;
624 computeJD(p);
625 }else{
626 rc = 1;
627 }
628 clearYMD_HMS_TZ(p);
629 break;
630 }
631 default: {
632 break;
633 }
634 }
635 return rc;
636 }
637
638 /*
639 ** Process time function arguments. argv[0] is a date-time stamp.
640 ** argv[1] and following are modifiers. Parse them all and write
641 ** the resulting time into the DateTime structure p. Return 0
642 ** on success and 1 if there are any errors.
643 */
isDate(int argc,const char ** argv,DateTime * p)644 static int isDate(int argc, const char **argv, DateTime *p){
645 int i;
646 if( argc==0 ) return 1;
647 if( argv[0]==0 || parseDateOrTime(argv[0], p) ) return 1;
648 for(i=1; i<argc; i++){
649 if( argv[i]==0 || parseModifier(argv[i], p) ) return 1;
650 }
651 return 0;
652 }
653
654
655 /*
656 ** The following routines implement the various date and time functions
657 ** of SQLite.
658 */
659
660 /*
661 ** julianday( TIMESTRING, MOD, MOD, ...)
662 **
663 ** Return the julian day number of the date specified in the arguments
664 */
juliandayFunc(sqlite_func * context,int argc,const char ** argv)665 static void juliandayFunc(sqlite_func *context, int argc, const char **argv){
666 DateTime x;
667 if( isDate(argc, argv, &x)==0 ){
668 computeJD(&x);
669 sqlite_set_result_double(context, x.rJD);
670 }
671 }
672
673 /*
674 ** datetime( TIMESTRING, MOD, MOD, ...)
675 **
676 ** Return YYYY-MM-DD HH:MM:SS
677 */
datetimeFunc(sqlite_func * context,int argc,const char ** argv)678 static void datetimeFunc(sqlite_func *context, int argc, const char **argv){
679 DateTime x;
680 if( isDate(argc, argv, &x)==0 ){
681 char zBuf[100];
682 computeYMD_HMS(&x);
683 sprintf(zBuf, "%04d-%02d-%02d %02d:%02d:%02d",x.Y, x.M, x.D, x.h, x.m,
684 (int)(x.s));
685 sqlite_set_result_string(context, zBuf, -1);
686 }
687 }
688
689 /*
690 ** time( TIMESTRING, MOD, MOD, ...)
691 **
692 ** Return HH:MM:SS
693 */
timeFunc(sqlite_func * context,int argc,const char ** argv)694 static void timeFunc(sqlite_func *context, int argc, const char **argv){
695 DateTime x;
696 if( isDate(argc, argv, &x)==0 ){
697 char zBuf[100];
698 computeHMS(&x);
699 sprintf(zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
700 sqlite_set_result_string(context, zBuf, -1);
701 }
702 }
703
704 /*
705 ** date( TIMESTRING, MOD, MOD, ...)
706 **
707 ** Return YYYY-MM-DD
708 */
dateFunc(sqlite_func * context,int argc,const char ** argv)709 static void dateFunc(sqlite_func *context, int argc, const char **argv){
710 DateTime x;
711 if( isDate(argc, argv, &x)==0 ){
712 char zBuf[100];
713 computeYMD(&x);
714 sprintf(zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
715 sqlite_set_result_string(context, zBuf, -1);
716 }
717 }
718
719 /*
720 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
721 **
722 ** Return a string described by FORMAT. Conversions as follows:
723 **
724 ** %d day of month
725 ** %f ** fractional seconds SS.SSS
726 ** %H hour 00-24
727 ** %j day of year 000-366
728 ** %J ** Julian day number
729 ** %m month 01-12
730 ** %M minute 00-59
731 ** %s seconds since 1970-01-01
732 ** %S seconds 00-59
733 ** %w day of week 0-6 sunday==0
734 ** %W week of year 00-53
735 ** %Y year 0000-9999
736 ** %% %
737 */
strftimeFunc(sqlite_func * context,int argc,const char ** argv)738 static void strftimeFunc(sqlite_func *context, int argc, const char **argv){
739 DateTime x;
740 int n, i, j;
741 char *z;
742 const char *zFmt = argv[0];
743 char zBuf[100];
744 if( argv[0]==0 || isDate(argc-1, argv+1, &x) ) return;
745 for(i=0, n=1; zFmt[i]; i++, n++){
746 if( zFmt[i]=='%' ){
747 switch( zFmt[i+1] ){
748 case 'd':
749 case 'H':
750 case 'm':
751 case 'M':
752 case 'S':
753 case 'W':
754 n++;
755 /* fall thru */
756 case 'w':
757 case '%':
758 break;
759 case 'f':
760 n += 8;
761 break;
762 case 'j':
763 n += 3;
764 break;
765 case 'Y':
766 n += 8;
767 break;
768 case 's':
769 case 'J':
770 n += 50;
771 break;
772 default:
773 return; /* ERROR. return a NULL */
774 }
775 i++;
776 }
777 }
778 if( n<sizeof(zBuf) ){
779 z = zBuf;
780 }else{
781 z = sqliteMalloc( n );
782 if( z==0 ) return;
783 }
784 computeJD(&x);
785 computeYMD_HMS(&x);
786 for(i=j=0; zFmt[i]; i++){
787 if( zFmt[i]!='%' ){
788 z[j++] = zFmt[i];
789 }else{
790 i++;
791 switch( zFmt[i] ){
792 case 'd': sprintf(&z[j],"%02d",x.D); j+=2; break;
793 case 'f': {
794 int s = x.s;
795 int ms = (x.s - s)*1000.0;
796 sprintf(&z[j],"%02d.%03d",s,ms);
797 j += strlen(&z[j]);
798 break;
799 }
800 case 'H': sprintf(&z[j],"%02d",x.h); j+=2; break;
801 case 'W': /* Fall thru */
802 case 'j': {
803 int n; /* Number of days since 1st day of year */
804 DateTime y = x;
805 y.validJD = 0;
806 y.M = 1;
807 y.D = 1;
808 computeJD(&y);
809 n = x.rJD - y.rJD;
810 if( zFmt[i]=='W' ){
811 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */
812 wd = ((int)(x.rJD+0.5)) % 7;
813 sprintf(&z[j],"%02d",(n+7-wd)/7);
814 j += 2;
815 }else{
816 sprintf(&z[j],"%03d",n+1);
817 j += 3;
818 }
819 break;
820 }
821 case 'J': sprintf(&z[j],"%.16g",x.rJD); j+=strlen(&z[j]); break;
822 case 'm': sprintf(&z[j],"%02d",x.M); j+=2; break;
823 case 'M': sprintf(&z[j],"%02d",x.m); j+=2; break;
824 case 's': {
825 sprintf(&z[j],"%d",(int)((x.rJD-2440587.5)*86400.0 + 0.5));
826 j += strlen(&z[j]);
827 break;
828 }
829 case 'S': sprintf(&z[j],"%02d",(int)(x.s+0.5)); j+=2; break;
830 case 'w': z[j++] = (((int)(x.rJD+1.5)) % 7) + '0'; break;
831 case 'Y': sprintf(&z[j],"%04d",x.Y); j+=strlen(&z[j]); break;
832 case '%': z[j++] = '%'; break;
833 }
834 }
835 }
836 z[j] = 0;
837 sqlite_set_result_string(context, z, -1);
838 if( z!=zBuf ){
839 sqliteFree(z);
840 }
841 }
842
843
844 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
845
846 /*
847 ** This function registered all of the above C functions as SQL
848 ** functions. This should be the only routine in this file with
849 ** external linkage.
850 */
sqliteRegisterDateTimeFunctions(sqlite * db)851 void sqliteRegisterDateTimeFunctions(sqlite *db){
852 #ifndef SQLITE_OMIT_DATETIME_FUNCS
853 static struct {
854 char *zName;
855 int nArg;
856 int dataType;
857 void (*xFunc)(sqlite_func*,int,const char**);
858 } aFuncs[] = {
859 { "julianday", -1, SQLITE_NUMERIC, juliandayFunc },
860 { "date", -1, SQLITE_TEXT, dateFunc },
861 { "time", -1, SQLITE_TEXT, timeFunc },
862 { "datetime", -1, SQLITE_TEXT, datetimeFunc },
863 { "strftime", -1, SQLITE_TEXT, strftimeFunc },
864 };
865 int i;
866
867 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
868 sqlite_create_function(db, aFuncs[i].zName,
869 aFuncs[i].nArg, aFuncs[i].xFunc, 0);
870 if( aFuncs[i].xFunc ){
871 sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
872 }
873 }
874 #endif
875 }
876