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