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$
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 #ifndef PHP_WIN32
57 #include "main/php_reentrancy.h"
58 #endif
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, tmbuf;
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 = php_localtime_r(&t, &tmbuf);
423 if (!pTm) {
424 return 0;
425 }
426 y.Y = pTm->tm_year + 1900;
427 y.M = pTm->tm_mon + 1;
428 y.D = pTm->tm_mday;
429 y.h = pTm->tm_hour;
430 y.m = pTm->tm_min;
431 y.s = pTm->tm_sec;
432 sqliteOsLeaveMutex();
433 y.validYMD = 1;
434 y.validHMS = 1;
435 y.validJD = 0;
436 y.validTZ = 0;
437 computeJD(&y);
438 return y.rJD - x.rJD;
439 }
440
441 /*
442 ** Process a modifier to a date-time stamp. The modifiers are
443 ** as follows:
444 **
445 ** NNN days
446 ** NNN hours
447 ** NNN minutes
448 ** NNN.NNNN seconds
449 ** NNN months
450 ** NNN years
451 ** start of month
452 ** start of year
453 ** start of week
454 ** start of day
455 ** weekday N
456 ** unixepoch
457 ** localtime
458 ** utc
459 **
460 ** Return 0 on success and 1 if there is any kind of error.
461 */
parseModifier(const char * zMod,DateTime * p)462 static int parseModifier(const char *zMod, DateTime *p){
463 int rc = 1;
464 int n;
465 double r;
466 char *z, zBuf[30];
467 z = zBuf;
468 for(n=0; n<sizeof(zBuf)-1 && zMod[n]; n++){
469 z[n] = tolower(zMod[n]);
470 }
471 z[n] = 0;
472 switch( z[0] ){
473 case 'l': {
474 /* localtime
475 **
476 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
477 ** show local time.
478 */
479 if( strcmp(z, "localtime")==0 ){
480 computeJD(p);
481 p->rJD += localtimeOffset(p);
482 clearYMD_HMS_TZ(p);
483 rc = 0;
484 }
485 break;
486 }
487 case 'u': {
488 /*
489 ** unixepoch
490 **
491 ** Treat the current value of p->rJD as the number of
492 ** seconds since 1970. Convert to a real julian day number.
493 */
494 if( strcmp(z, "unixepoch")==0 && p->validJD ){
495 p->rJD = p->rJD/86400.0 + 2440587.5;
496 clearYMD_HMS_TZ(p);
497 rc = 0;
498 }else if( strcmp(z, "utc")==0 ){
499 double c1;
500 computeJD(p);
501 c1 = localtimeOffset(p);
502 p->rJD -= c1;
503 clearYMD_HMS_TZ(p);
504 p->rJD += c1 - localtimeOffset(p);
505 rc = 0;
506 }
507 break;
508 }
509 case 'w': {
510 /*
511 ** weekday N
512 **
513 ** Move the date to the same time on the next occurrance of
514 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the
515 ** date is already on the appropriate weekday, this is a no-op.
516 */
517 if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0
518 && (n=r)==r && n>=0 && r<7 ){
519 int Z;
520 computeYMD_HMS(p);
521 p->validTZ = 0;
522 p->validJD = 0;
523 computeJD(p);
524 Z = p->rJD + 1.5;
525 Z %= 7;
526 if( Z>n ) Z -= 7;
527 p->rJD += n - Z;
528 clearYMD_HMS_TZ(p);
529 rc = 0;
530 }
531 break;
532 }
533 case 's': {
534 /*
535 ** start of TTTTT
536 **
537 ** Move the date backwards to the beginning of the current day,
538 ** or month or year.
539 */
540 if( strncmp(z, "start of ", 9)!=0 ) break;
541 z += 9;
542 computeYMD(p);
543 p->validHMS = 1;
544 p->h = p->m = 0;
545 p->s = 0.0;
546 p->validTZ = 0;
547 p->validJD = 0;
548 if( strcmp(z,"month")==0 ){
549 p->D = 1;
550 rc = 0;
551 }else if( strcmp(z,"year")==0 ){
552 computeYMD(p);
553 p->M = 1;
554 p->D = 1;
555 rc = 0;
556 }else if( strcmp(z,"day")==0 ){
557 rc = 0;
558 }
559 break;
560 }
561 case '+':
562 case '-':
563 case '0':
564 case '1':
565 case '2':
566 case '3':
567 case '4':
568 case '5':
569 case '6':
570 case '7':
571 case '8':
572 case '9': {
573 n = getValue(z, &r);
574 if( n<=0 ) break;
575 if( z[n]==':' ){
576 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
577 ** specified number of hours, minutes, seconds, and fractional seconds
578 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be
579 ** omitted.
580 */
581 const char *z2 = z;
582 DateTime tx;
583 int day;
584 if( !isdigit(*z2) ) z2++;
585 memset(&tx, 0, sizeof(tx));
586 if( parseHhMmSs(z2, &tx) ) break;
587 computeJD(&tx);
588 tx.rJD -= 0.5;
589 day = (int)tx.rJD;
590 tx.rJD -= day;
591 if( z[0]=='-' ) tx.rJD = -tx.rJD;
592 computeJD(p);
593 clearYMD_HMS_TZ(p);
594 p->rJD += tx.rJD;
595 rc = 0;
596 break;
597 }
598 z += n;
599 while( isspace(z[0]) ) z++;
600 n = strlen(z);
601 if( n>10 || n<3 ) break;
602 if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
603 computeJD(p);
604 rc = 0;
605 if( n==3 && strcmp(z,"day")==0 ){
606 p->rJD += r;
607 }else if( n==4 && strcmp(z,"hour")==0 ){
608 p->rJD += r/24.0;
609 }else if( n==6 && strcmp(z,"minute")==0 ){
610 p->rJD += r/(24.0*60.0);
611 }else if( n==6 && strcmp(z,"second")==0 ){
612 p->rJD += r/(24.0*60.0*60.0);
613 }else if( n==5 && strcmp(z,"month")==0 ){
614 int x, y;
615 computeYMD_HMS(p);
616 p->M += r;
617 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
618 p->Y += x;
619 p->M -= x*12;
620 p->validJD = 0;
621 computeJD(p);
622 y = r;
623 if( y!=r ){
624 p->rJD += (r - y)*30.0;
625 }
626 }else if( n==4 && strcmp(z,"year")==0 ){
627 computeYMD_HMS(p);
628 p->Y += r;
629 p->validJD = 0;
630 computeJD(p);
631 }else{
632 rc = 1;
633 }
634 clearYMD_HMS_TZ(p);
635 break;
636 }
637 default: {
638 break;
639 }
640 }
641 return rc;
642 }
643
644 /*
645 ** Process time function arguments. argv[0] is a date-time stamp.
646 ** argv[1] and following are modifiers. Parse them all and write
647 ** the resulting time into the DateTime structure p. Return 0
648 ** on success and 1 if there are any errors.
649 */
isDate(int argc,const char ** argv,DateTime * p)650 static int isDate(int argc, const char **argv, DateTime *p){
651 int i;
652 if( argc==0 ) return 1;
653 if( argv[0]==0 || parseDateOrTime(argv[0], p) ) return 1;
654 for(i=1; i<argc; i++){
655 if( argv[i]==0 || parseModifier(argv[i], p) ) return 1;
656 }
657 return 0;
658 }
659
660
661 /*
662 ** The following routines implement the various date and time functions
663 ** of SQLite.
664 */
665
666 /*
667 ** julianday( TIMESTRING, MOD, MOD, ...)
668 **
669 ** Return the julian day number of the date specified in the arguments
670 */
juliandayFunc(sqlite_func * context,int argc,const char ** argv)671 static void juliandayFunc(sqlite_func *context, int argc, const char **argv){
672 DateTime x;
673 if( isDate(argc, argv, &x)==0 ){
674 computeJD(&x);
675 sqlite_set_result_double(context, x.rJD);
676 }
677 }
678
679 /*
680 ** datetime( TIMESTRING, MOD, MOD, ...)
681 **
682 ** Return YYYY-MM-DD HH:MM:SS
683 */
datetimeFunc(sqlite_func * context,int argc,const char ** argv)684 static void datetimeFunc(sqlite_func *context, int argc, const char **argv){
685 DateTime x;
686 if( isDate(argc, argv, &x)==0 ){
687 char zBuf[100];
688 computeYMD_HMS(&x);
689 sprintf(zBuf, "%04d-%02d-%02d %02d:%02d:%02d",x.Y, x.M, x.D, x.h, x.m,
690 (int)(x.s));
691 sqlite_set_result_string(context, zBuf, -1);
692 }
693 }
694
695 /*
696 ** time( TIMESTRING, MOD, MOD, ...)
697 **
698 ** Return HH:MM:SS
699 */
timeFunc(sqlite_func * context,int argc,const char ** argv)700 static void timeFunc(sqlite_func *context, int argc, const char **argv){
701 DateTime x;
702 if( isDate(argc, argv, &x)==0 ){
703 char zBuf[100];
704 computeHMS(&x);
705 sprintf(zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s);
706 sqlite_set_result_string(context, zBuf, -1);
707 }
708 }
709
710 /*
711 ** date( TIMESTRING, MOD, MOD, ...)
712 **
713 ** Return YYYY-MM-DD
714 */
dateFunc(sqlite_func * context,int argc,const char ** argv)715 static void dateFunc(sqlite_func *context, int argc, const char **argv){
716 DateTime x;
717 if( isDate(argc, argv, &x)==0 ){
718 char zBuf[100];
719 computeYMD(&x);
720 sprintf(zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
721 sqlite_set_result_string(context, zBuf, -1);
722 }
723 }
724
725 /*
726 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
727 **
728 ** Return a string described by FORMAT. Conversions as follows:
729 **
730 ** %d day of month
731 ** %f ** fractional seconds SS.SSS
732 ** %H hour 00-24
733 ** %j day of year 000-366
734 ** %J ** Julian day number
735 ** %m month 01-12
736 ** %M minute 00-59
737 ** %s seconds since 1970-01-01
738 ** %S seconds 00-59
739 ** %w day of week 0-6 sunday==0
740 ** %W week of year 00-53
741 ** %Y year 0000-9999
742 ** %% %
743 */
strftimeFunc(sqlite_func * context,int argc,const char ** argv)744 static void strftimeFunc(sqlite_func *context, int argc, const char **argv){
745 DateTime x;
746 int n, i, j;
747 char *z;
748 const char *zFmt = argv[0];
749 char zBuf[100];
750 if( argv[0]==0 || isDate(argc-1, argv+1, &x) ) return;
751 for(i=0, n=1; zFmt[i]; i++, n++){
752 if( zFmt[i]=='%' ){
753 switch( zFmt[i+1] ){
754 case 'd':
755 case 'H':
756 case 'm':
757 case 'M':
758 case 'S':
759 case 'W':
760 n++;
761 /* fall thru */
762 case 'w':
763 case '%':
764 break;
765 case 'f':
766 n += 8;
767 break;
768 case 'j':
769 n += 3;
770 break;
771 case 'Y':
772 n += 8;
773 break;
774 case 's':
775 case 'J':
776 n += 50;
777 break;
778 default:
779 return; /* ERROR. return a NULL */
780 }
781 i++;
782 }
783 }
784 if( n<sizeof(zBuf) ){
785 z = zBuf;
786 }else{
787 z = sqliteMalloc( n );
788 if( z==0 ) return;
789 }
790 computeJD(&x);
791 computeYMD_HMS(&x);
792 for(i=j=0; zFmt[i]; i++){
793 if( zFmt[i]!='%' ){
794 z[j++] = zFmt[i];
795 }else{
796 i++;
797 switch( zFmt[i] ){
798 case 'd': sprintf(&z[j],"%02d",x.D); j+=2; break;
799 case 'f': {
800 int s = x.s;
801 int ms = (x.s - s)*1000.0;
802 sprintf(&z[j],"%02d.%03d",s,ms);
803 j += strlen(&z[j]);
804 break;
805 }
806 case 'H': sprintf(&z[j],"%02d",x.h); j+=2; break;
807 case 'W': /* Fall thru */
808 case 'j': {
809 int n; /* Number of days since 1st day of year */
810 DateTime y = x;
811 y.validJD = 0;
812 y.M = 1;
813 y.D = 1;
814 computeJD(&y);
815 n = x.rJD - y.rJD;
816 if( zFmt[i]=='W' ){
817 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */
818 wd = ((int)(x.rJD+0.5)) % 7;
819 sprintf(&z[j],"%02d",(n+7-wd)/7);
820 j += 2;
821 }else{
822 sprintf(&z[j],"%03d",n+1);
823 j += 3;
824 }
825 break;
826 }
827 case 'J': sprintf(&z[j],"%.16g",x.rJD); j+=strlen(&z[j]); break;
828 case 'm': sprintf(&z[j],"%02d",x.M); j+=2; break;
829 case 'M': sprintf(&z[j],"%02d",x.m); j+=2; break;
830 case 's': {
831 sprintf(&z[j],"%d",(int)((x.rJD-2440587.5)*86400.0 + 0.5));
832 j += strlen(&z[j]);
833 break;
834 }
835 case 'S': sprintf(&z[j],"%02d",(int)(x.s+0.5)); j+=2; break;
836 case 'w': z[j++] = (((int)(x.rJD+1.5)) % 7) + '0'; break;
837 case 'Y': sprintf(&z[j],"%04d",x.Y); j+=strlen(&z[j]); break;
838 case '%': z[j++] = '%'; break;
839 }
840 }
841 }
842 z[j] = 0;
843 sqlite_set_result_string(context, z, -1);
844 if( z!=zBuf ){
845 sqliteFree(z);
846 }
847 }
848
849
850 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
851
852 /*
853 ** This function registered all of the above C functions as SQL
854 ** functions. This should be the only routine in this file with
855 ** external linkage.
856 */
sqliteRegisterDateTimeFunctions(sqlite * db)857 void sqliteRegisterDateTimeFunctions(sqlite *db){
858 #ifndef SQLITE_OMIT_DATETIME_FUNCS
859 static struct {
860 char *zName;
861 int nArg;
862 int dataType;
863 void (*xFunc)(sqlite_func*,int,const char**);
864 } aFuncs[] = {
865 { "julianday", -1, SQLITE_NUMERIC, juliandayFunc },
866 { "date", -1, SQLITE_TEXT, dateFunc },
867 { "time", -1, SQLITE_TEXT, timeFunc },
868 { "datetime", -1, SQLITE_TEXT, datetimeFunc },
869 { "strftime", -1, SQLITE_TEXT, strftimeFunc },
870 };
871 int i;
872
873 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
874 sqlite_create_function(db, aFuncs[i].zName,
875 aFuncs[i].nArg, aFuncs[i].xFunc, 0);
876 if( aFuncs[i].xFunc ){
877 sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
878 }
879 }
880 #endif
881 }
882
