xref: /titanic_41/usr/src/lib/libsqlite/src/date.c (revision c5c4113dfcabb1eed3d4bdf7609de5170027a794)
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