|
1 /* |
|
2 ** 2003 October 31 |
|
3 ** |
|
4 ** The author disclaims copyright to this source code. In place of |
|
5 ** a legal notice, here is a blessing: |
|
6 ** |
|
7 ** May you do good and not evil. |
|
8 ** May you find forgiveness for yourself and forgive others. |
|
9 ** May you share freely, never taking more than you give. |
|
10 ** |
|
11 ************************************************************************* |
|
12 ** This file contains the C functions that implement date and time |
|
13 ** functions for SQLite. |
|
14 ** |
|
15 ** There is only one exported symbol in this file - the function |
|
16 ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file. |
|
17 ** All other code has file scope. |
|
18 ** |
|
19 ** $Id: date.c,v 1.54 2006/01/31 20:49:13 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 "sqliteInt.h" |
|
51 #include "os.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 */ |
|
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(*(u8*)zDate) ){ |
|
108 goto end_getDigits; |
|
109 } |
|
110 val = val*10 + *zDate - '0'; |
|
111 zDate++; |
|
112 } |
|
113 if( val<min || val>max || (nextC!=0 && nextC!=*zDate) ){ |
|
114 goto end_getDigits; |
|
115 } |
|
116 *pVal = val; |
|
117 zDate++; |
|
118 cnt++; |
|
119 }while( nextC ); |
|
120 end_getDigits: |
|
121 va_end(ap); |
|
122 return cnt; |
|
123 } |
|
124 |
|
125 /* |
|
126 ** Read text from z[] and convert into a floating point number. Return |
|
127 ** the number of digits converted. |
|
128 */ |
|
129 #define getValue sqlite3AtoF |
|
130 |
|
131 /* |
|
132 ** Parse a timezone extension on the end of a date-time. |
|
133 ** The extension is of the form: |
|
134 ** |
|
135 ** (+/-)HH:MM |
|
136 ** |
|
137 ** If the parse is successful, write the number of minutes |
|
138 ** of change in *pnMin and return 0. If a parser error occurs, |
|
139 ** return 0. |
|
140 ** |
|
141 ** A missing specifier is not considered an error. |
|
142 */ |
|
143 static int parseTimezone(const char *zDate, DateTime *p){ |
|
144 int sgn = 0; |
|
145 int nHr, nMn; |
|
146 while( isspace(*(u8*)zDate) ){ zDate++; } |
|
147 p->tz = 0; |
|
148 if( *zDate=='-' ){ |
|
149 sgn = -1; |
|
150 }else if( *zDate=='+' ){ |
|
151 sgn = +1; |
|
152 }else{ |
|
153 return *zDate!=0; |
|
154 } |
|
155 zDate++; |
|
156 if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){ |
|
157 return 1; |
|
158 } |
|
159 zDate += 5; |
|
160 p->tz = sgn*(nMn + nHr*60); |
|
161 while( isspace(*(u8*)zDate) ){ zDate++; } |
|
162 return *zDate!=0; |
|
163 } |
|
164 |
|
165 /* |
|
166 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF. |
|
167 ** The HH, MM, and SS must each be exactly 2 digits. The |
|
168 ** fractional seconds FFFF can be one or more digits. |
|
169 ** |
|
170 ** Return 1 if there is a parsing error and 0 on success. |
|
171 */ |
|
172 static int parseHhMmSs(const char *zDate, DateTime *p){ |
|
173 int h, m, s; |
|
174 double ms = 0.0; |
|
175 if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){ |
|
176 return 1; |
|
177 } |
|
178 zDate += 5; |
|
179 if( *zDate==':' ){ |
|
180 zDate++; |
|
181 if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){ |
|
182 return 1; |
|
183 } |
|
184 zDate += 2; |
|
185 if( *zDate=='.' && isdigit((u8)zDate[1]) ){ |
|
186 double rScale = 1.0; |
|
187 zDate++; |
|
188 while( isdigit(*(u8*)zDate) ){ |
|
189 ms = ms*10.0 + *zDate - '0'; |
|
190 rScale *= 10.0; |
|
191 zDate++; |
|
192 } |
|
193 ms /= rScale; |
|
194 } |
|
195 }else{ |
|
196 s = 0; |
|
197 } |
|
198 p->validJD = 0; |
|
199 p->validHMS = 1; |
|
200 p->h = h; |
|
201 p->m = m; |
|
202 p->s = s + ms; |
|
203 if( parseTimezone(zDate, p) ) return 1; |
|
204 p->validTZ = p->tz!=0; |
|
205 return 0; |
|
206 } |
|
207 |
|
208 /* |
|
209 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume |
|
210 ** that the YYYY-MM-DD is according to the Gregorian calendar. |
|
211 ** |
|
212 ** Reference: Meeus page 61 |
|
213 */ |
|
214 static void computeJD(DateTime *p){ |
|
215 int Y, M, D, A, B, X1, X2; |
|
216 |
|
217 if( p->validJD ) return; |
|
218 if( p->validYMD ){ |
|
219 Y = p->Y; |
|
220 M = p->M; |
|
221 D = p->D; |
|
222 }else{ |
|
223 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */ |
|
224 M = 1; |
|
225 D = 1; |
|
226 } |
|
227 if( M<=2 ){ |
|
228 Y--; |
|
229 M += 12; |
|
230 } |
|
231 A = Y/100; |
|
232 B = 2 - A + (A/4); |
|
233 X1 = 365.25*(Y+4716); |
|
234 X2 = 30.6001*(M+1); |
|
235 p->rJD = X1 + X2 + D + B - 1524.5; |
|
236 p->validJD = 1; |
|
237 p->validYMD = 0; |
|
238 if( p->validHMS ){ |
|
239 p->rJD += (p->h*3600.0 + p->m*60.0 + p->s)/86400.0; |
|
240 if( p->validTZ ){ |
|
241 p->rJD -= p->tz*60/86400.0; |
|
242 p->validHMS = 0; |
|
243 p->validTZ = 0; |
|
244 } |
|
245 } |
|
246 } |
|
247 |
|
248 /* |
|
249 ** Parse dates of the form |
|
250 ** |
|
251 ** YYYY-MM-DD HH:MM:SS.FFF |
|
252 ** YYYY-MM-DD HH:MM:SS |
|
253 ** YYYY-MM-DD HH:MM |
|
254 ** YYYY-MM-DD |
|
255 ** |
|
256 ** Write the result into the DateTime structure and return 0 |
|
257 ** on success and 1 if the input string is not a well-formed |
|
258 ** date. |
|
259 */ |
|
260 static int parseYyyyMmDd(const char *zDate, DateTime *p){ |
|
261 int Y, M, D, neg; |
|
262 |
|
263 if( zDate[0]=='-' ){ |
|
264 zDate++; |
|
265 neg = 1; |
|
266 }else{ |
|
267 neg = 0; |
|
268 } |
|
269 if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){ |
|
270 return 1; |
|
271 } |
|
272 zDate += 10; |
|
273 while( isspace(*(u8*)zDate) || 'T'==*(u8*)zDate ){ zDate++; } |
|
274 if( parseHhMmSs(zDate, p)==0 ){ |
|
275 /* We got the time */ |
|
276 }else if( *zDate==0 ){ |
|
277 p->validHMS = 0; |
|
278 }else{ |
|
279 return 1; |
|
280 } |
|
281 p->validJD = 0; |
|
282 p->validYMD = 1; |
|
283 p->Y = neg ? -Y : Y; |
|
284 p->M = M; |
|
285 p->D = D; |
|
286 if( p->validTZ ){ |
|
287 computeJD(p); |
|
288 } |
|
289 return 0; |
|
290 } |
|
291 |
|
292 /* |
|
293 ** Attempt to parse the given string into a Julian Day Number. Return |
|
294 ** the number of errors. |
|
295 ** |
|
296 ** The following are acceptable forms for the input string: |
|
297 ** |
|
298 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM |
|
299 ** DDDD.DD |
|
300 ** now |
|
301 ** |
|
302 ** In the first form, the +/-HH:MM is always optional. The fractional |
|
303 ** seconds extension (the ".FFF") is optional. The seconds portion |
|
304 ** (":SS.FFF") is option. The year and date can be omitted as long |
|
305 ** as there is a time string. The time string can be omitted as long |
|
306 ** as there is a year and date. |
|
307 */ |
|
308 static int parseDateOrTime(const char *zDate, DateTime *p){ |
|
309 memset(p, 0, sizeof(*p)); |
|
310 if( parseYyyyMmDd(zDate,p)==0 ){ |
|
311 return 0; |
|
312 }else if( parseHhMmSs(zDate, p)==0 ){ |
|
313 return 0; |
|
314 }else if( sqlite3StrICmp(zDate,"now")==0){ |
|
315 double r; |
|
316 sqlite3OsCurrentTime(&r); |
|
317 p->rJD = r; |
|
318 p->validJD = 1; |
|
319 return 0; |
|
320 }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){ |
|
321 getValue(zDate, &p->rJD); |
|
322 p->validJD = 1; |
|
323 return 0; |
|
324 } |
|
325 return 1; |
|
326 } |
|
327 |
|
328 /* |
|
329 ** Compute the Year, Month, and Day from the julian day number. |
|
330 */ |
|
331 static void computeYMD(DateTime *p){ |
|
332 int Z, A, B, C, D, E, X1; |
|
333 if( p->validYMD ) return; |
|
334 if( !p->validJD ){ |
|
335 p->Y = 2000; |
|
336 p->M = 1; |
|
337 p->D = 1; |
|
338 }else{ |
|
339 Z = p->rJD + 0.5; |
|
340 A = (Z - 1867216.25)/36524.25; |
|
341 A = Z + 1 + A - (A/4); |
|
342 B = A + 1524; |
|
343 C = (B - 122.1)/365.25; |
|
344 D = 365.25*C; |
|
345 E = (B-D)/30.6001; |
|
346 X1 = 30.6001*E; |
|
347 p->D = B - D - X1; |
|
348 p->M = E<14 ? E-1 : E-13; |
|
349 p->Y = p->M>2 ? C - 4716 : C - 4715; |
|
350 } |
|
351 p->validYMD = 1; |
|
352 } |
|
353 |
|
354 /* |
|
355 ** Compute the Hour, Minute, and Seconds from the julian day number. |
|
356 */ |
|
357 static void computeHMS(DateTime *p){ |
|
358 int Z, s; |
|
359 if( p->validHMS ) return; |
|
360 Z = p->rJD + 0.5; |
|
361 s = (p->rJD + 0.5 - Z)*86400000.0 + 0.5; |
|
362 p->s = 0.001*s; |
|
363 s = p->s; |
|
364 p->s -= s; |
|
365 p->h = s/3600; |
|
366 s -= p->h*3600; |
|
367 p->m = s/60; |
|
368 p->s += s - p->m*60; |
|
369 p->validHMS = 1; |
|
370 } |
|
371 |
|
372 /* |
|
373 ** Compute both YMD and HMS |
|
374 */ |
|
375 static void computeYMD_HMS(DateTime *p){ |
|
376 computeYMD(p); |
|
377 computeHMS(p); |
|
378 } |
|
379 |
|
380 /* |
|
381 ** Clear the YMD and HMS and the TZ |
|
382 */ |
|
383 static void clearYMD_HMS_TZ(DateTime *p){ |
|
384 p->validYMD = 0; |
|
385 p->validHMS = 0; |
|
386 p->validTZ = 0; |
|
387 } |
|
388 |
|
389 /* |
|
390 ** Compute the difference (in days) between localtime and UTC (a.k.a. GMT) |
|
391 ** for the time value p where p is in UTC. |
|
392 */ |
|
393 static double localtimeOffset(DateTime *p){ |
|
394 DateTime x, y; |
|
395 time_t t; |
|
396 struct tm *pTm; |
|
397 x = *p; |
|
398 computeYMD_HMS(&x); |
|
399 if( x.Y<1971 || x.Y>=2038 ){ |
|
400 x.Y = 2000; |
|
401 x.M = 1; |
|
402 x.D = 1; |
|
403 x.h = 0; |
|
404 x.m = 0; |
|
405 x.s = 0.0; |
|
406 } else { |
|
407 int s = x.s + 0.5; |
|
408 x.s = s; |
|
409 } |
|
410 x.tz = 0; |
|
411 x.validJD = 0; |
|
412 computeJD(&x); |
|
413 t = (x.rJD-2440587.5)*86400.0 + 0.5; |
|
414 sqlite3OsEnterMutex(); |
|
415 pTm = localtime(&t); |
|
416 y.Y = pTm->tm_year + 1900; |
|
417 y.M = pTm->tm_mon + 1; |
|
418 y.D = pTm->tm_mday; |
|
419 y.h = pTm->tm_hour; |
|
420 y.m = pTm->tm_min; |
|
421 y.s = pTm->tm_sec; |
|
422 sqlite3OsLeaveMutex(); |
|
423 y.validYMD = 1; |
|
424 y.validHMS = 1; |
|
425 y.validJD = 0; |
|
426 y.validTZ = 0; |
|
427 computeJD(&y); |
|
428 return y.rJD - x.rJD; |
|
429 } |
|
430 |
|
431 /* |
|
432 ** Process a modifier to a date-time stamp. The modifiers are |
|
433 ** as follows: |
|
434 ** |
|
435 ** NNN days |
|
436 ** NNN hours |
|
437 ** NNN minutes |
|
438 ** NNN.NNNN seconds |
|
439 ** NNN months |
|
440 ** NNN years |
|
441 ** start of month |
|
442 ** start of year |
|
443 ** start of week |
|
444 ** start of day |
|
445 ** weekday N |
|
446 ** unixepoch |
|
447 ** localtime |
|
448 ** utc |
|
449 ** |
|
450 ** Return 0 on success and 1 if there is any kind of error. |
|
451 */ |
|
452 static int parseModifier(const char *zMod, DateTime *p){ |
|
453 int rc = 1; |
|
454 int n; |
|
455 double r; |
|
456 char *z, zBuf[30]; |
|
457 z = zBuf; |
|
458 for(n=0; n<sizeof(zBuf)-1 && zMod[n]; n++){ |
|
459 z[n] = tolower(zMod[n]); |
|
460 } |
|
461 z[n] = 0; |
|
462 switch( z[0] ){ |
|
463 case 'l': { |
|
464 /* localtime |
|
465 ** |
|
466 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to |
|
467 ** show local time. |
|
468 */ |
|
469 if( strcmp(z, "localtime")==0 ){ |
|
470 computeJD(p); |
|
471 p->rJD += localtimeOffset(p); |
|
472 clearYMD_HMS_TZ(p); |
|
473 rc = 0; |
|
474 } |
|
475 break; |
|
476 } |
|
477 case 'u': { |
|
478 /* |
|
479 ** unixepoch |
|
480 ** |
|
481 ** Treat the current value of p->rJD as the number of |
|
482 ** seconds since 1970. Convert to a real julian day number. |
|
483 */ |
|
484 if( strcmp(z, "unixepoch")==0 && p->validJD ){ |
|
485 p->rJD = p->rJD/86400.0 + 2440587.5; |
|
486 clearYMD_HMS_TZ(p); |
|
487 rc = 0; |
|
488 }else if( strcmp(z, "utc")==0 ){ |
|
489 double c1; |
|
490 computeJD(p); |
|
491 c1 = localtimeOffset(p); |
|
492 p->rJD -= c1; |
|
493 clearYMD_HMS_TZ(p); |
|
494 p->rJD += c1 - localtimeOffset(p); |
|
495 rc = 0; |
|
496 } |
|
497 break; |
|
498 } |
|
499 case 'w': { |
|
500 /* |
|
501 ** weekday N |
|
502 ** |
|
503 ** Move the date to the same time on the next occurrence of |
|
504 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the |
|
505 ** date is already on the appropriate weekday, this is a no-op. |
|
506 */ |
|
507 if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0 |
|
508 && (n=r)==r && n>=0 && r<7 ){ |
|
509 int Z; |
|
510 computeYMD_HMS(p); |
|
511 p->validTZ = 0; |
|
512 p->validJD = 0; |
|
513 computeJD(p); |
|
514 Z = p->rJD + 1.5; |
|
515 Z %= 7; |
|
516 if( Z>n ) Z -= 7; |
|
517 p->rJD += n - Z; |
|
518 clearYMD_HMS_TZ(p); |
|
519 rc = 0; |
|
520 } |
|
521 break; |
|
522 } |
|
523 case 's': { |
|
524 /* |
|
525 ** start of TTTTT |
|
526 ** |
|
527 ** Move the date backwards to the beginning of the current day, |
|
528 ** or month or year. |
|
529 */ |
|
530 if( strncmp(z, "start of ", 9)!=0 ) break; |
|
531 z += 9; |
|
532 computeYMD(p); |
|
533 p->validHMS = 1; |
|
534 p->h = p->m = 0; |
|
535 p->s = 0.0; |
|
536 p->validTZ = 0; |
|
537 p->validJD = 0; |
|
538 if( strcmp(z,"month")==0 ){ |
|
539 p->D = 1; |
|
540 rc = 0; |
|
541 }else if( strcmp(z,"year")==0 ){ |
|
542 computeYMD(p); |
|
543 p->M = 1; |
|
544 p->D = 1; |
|
545 rc = 0; |
|
546 }else if( strcmp(z,"day")==0 ){ |
|
547 rc = 0; |
|
548 } |
|
549 break; |
|
550 } |
|
551 case '+': |
|
552 case '-': |
|
553 case '0': |
|
554 case '1': |
|
555 case '2': |
|
556 case '3': |
|
557 case '4': |
|
558 case '5': |
|
559 case '6': |
|
560 case '7': |
|
561 case '8': |
|
562 case '9': { |
|
563 n = getValue(z, &r); |
|
564 if( n<=0 ) break; |
|
565 if( z[n]==':' ){ |
|
566 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the |
|
567 ** specified number of hours, minutes, seconds, and fractional seconds |
|
568 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be |
|
569 ** omitted. |
|
570 */ |
|
571 const char *z2 = z; |
|
572 DateTime tx; |
|
573 int day; |
|
574 if( !isdigit(*(u8*)z2) ) z2++; |
|
575 memset(&tx, 0, sizeof(tx)); |
|
576 if( parseHhMmSs(z2, &tx) ) break; |
|
577 computeJD(&tx); |
|
578 tx.rJD -= 0.5; |
|
579 day = (int)tx.rJD; |
|
580 tx.rJD -= day; |
|
581 if( z[0]=='-' ) tx.rJD = -tx.rJD; |
|
582 computeJD(p); |
|
583 clearYMD_HMS_TZ(p); |
|
584 p->rJD += tx.rJD; |
|
585 rc = 0; |
|
586 break; |
|
587 } |
|
588 z += n; |
|
589 while( isspace(*(u8*)z) ) z++; |
|
590 n = strlen(z); |
|
591 if( n>10 || n<3 ) break; |
|
592 if( z[n-1]=='s' ){ z[n-1] = 0; n--; } |
|
593 computeJD(p); |
|
594 rc = 0; |
|
595 if( n==3 && strcmp(z,"day")==0 ){ |
|
596 p->rJD += r; |
|
597 }else if( n==4 && strcmp(z,"hour")==0 ){ |
|
598 p->rJD += r/24.0; |
|
599 }else if( n==6 && strcmp(z,"minute")==0 ){ |
|
600 p->rJD += r/(24.0*60.0); |
|
601 }else if( n==6 && strcmp(z,"second")==0 ){ |
|
602 p->rJD += r/(24.0*60.0*60.0); |
|
603 }else if( n==5 && strcmp(z,"month")==0 ){ |
|
604 int x, y; |
|
605 computeYMD_HMS(p); |
|
606 p->M += r; |
|
607 x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; |
|
608 p->Y += x; |
|
609 p->M -= x*12; |
|
610 p->validJD = 0; |
|
611 computeJD(p); |
|
612 y = r; |
|
613 if( y!=r ){ |
|
614 p->rJD += (r - y)*30.0; |
|
615 } |
|
616 }else if( n==4 && strcmp(z,"year")==0 ){ |
|
617 computeYMD_HMS(p); |
|
618 p->Y += r; |
|
619 p->validJD = 0; |
|
620 computeJD(p); |
|
621 }else{ |
|
622 rc = 1; |
|
623 } |
|
624 clearYMD_HMS_TZ(p); |
|
625 break; |
|
626 } |
|
627 default: { |
|
628 break; |
|
629 } |
|
630 } |
|
631 return rc; |
|
632 } |
|
633 |
|
634 /* |
|
635 ** Process time function arguments. argv[0] is a date-time stamp. |
|
636 ** argv[1] and following are modifiers. Parse them all and write |
|
637 ** the resulting time into the DateTime structure p. Return 0 |
|
638 ** on success and 1 if there are any errors. |
|
639 */ |
|
640 static int isDate(int argc, sqlite3_value **argv, DateTime *p){ |
|
641 int i; |
|
642 if( argc==0 ) return 1; |
|
643 if( SQLITE_NULL==sqlite3_value_type(argv[0]) || |
|
644 parseDateOrTime((char*)sqlite3_value_text(argv[0]), p) ) return 1; |
|
645 for(i=1; i<argc; i++){ |
|
646 if( SQLITE_NULL==sqlite3_value_type(argv[i]) || |
|
647 parseModifier((char*)sqlite3_value_text(argv[i]), p) ) return 1; |
|
648 } |
|
649 return 0; |
|
650 } |
|
651 |
|
652 |
|
653 /* |
|
654 ** The following routines implement the various date and time functions |
|
655 ** of SQLite. |
|
656 */ |
|
657 |
|
658 /* |
|
659 ** julianday( TIMESTRING, MOD, MOD, ...) |
|
660 ** |
|
661 ** Return the julian day number of the date specified in the arguments |
|
662 */ |
|
663 static void juliandayFunc( |
|
664 sqlite3_context *context, |
|
665 int argc, |
|
666 sqlite3_value **argv |
|
667 ){ |
|
668 DateTime x; |
|
669 if( isDate(argc, argv, &x)==0 ){ |
|
670 computeJD(&x); |
|
671 sqlite3_result_double(context, x.rJD); |
|
672 } |
|
673 } |
|
674 |
|
675 /* |
|
676 ** datetime( TIMESTRING, MOD, MOD, ...) |
|
677 ** |
|
678 ** Return YYYY-MM-DD HH:MM:SS |
|
679 */ |
|
680 static void datetimeFunc( |
|
681 sqlite3_context *context, |
|
682 int argc, |
|
683 sqlite3_value **argv |
|
684 ){ |
|
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 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); |
|
692 } |
|
693 } |
|
694 |
|
695 /* |
|
696 ** time( TIMESTRING, MOD, MOD, ...) |
|
697 ** |
|
698 ** Return HH:MM:SS |
|
699 */ |
|
700 static void timeFunc( |
|
701 sqlite3_context *context, |
|
702 int argc, |
|
703 sqlite3_value **argv |
|
704 ){ |
|
705 DateTime x; |
|
706 if( isDate(argc, argv, &x)==0 ){ |
|
707 char zBuf[100]; |
|
708 computeHMS(&x); |
|
709 sprintf(zBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s); |
|
710 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); |
|
711 } |
|
712 } |
|
713 |
|
714 /* |
|
715 ** date( TIMESTRING, MOD, MOD, ...) |
|
716 ** |
|
717 ** Return YYYY-MM-DD |
|
718 */ |
|
719 static void dateFunc( |
|
720 sqlite3_context *context, |
|
721 int argc, |
|
722 sqlite3_value **argv |
|
723 ){ |
|
724 DateTime x; |
|
725 if( isDate(argc, argv, &x)==0 ){ |
|
726 char zBuf[100]; |
|
727 computeYMD(&x); |
|
728 sprintf(zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D); |
|
729 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); |
|
730 } |
|
731 } |
|
732 |
|
733 /* |
|
734 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...) |
|
735 ** |
|
736 ** Return a string described by FORMAT. Conversions as follows: |
|
737 ** |
|
738 ** %d day of month |
|
739 ** %f ** fractional seconds SS.SSS |
|
740 ** %H hour 00-24 |
|
741 ** %j day of year 000-366 |
|
742 ** %J ** Julian day number |
|
743 ** %m month 01-12 |
|
744 ** %M minute 00-59 |
|
745 ** %s seconds since 1970-01-01 |
|
746 ** %S seconds 00-59 |
|
747 ** %w day of week 0-6 sunday==0 |
|
748 ** %W week of year 00-53 |
|
749 ** %Y year 0000-9999 |
|
750 ** %% % |
|
751 */ |
|
752 static void strftimeFunc( |
|
753 sqlite3_context *context, |
|
754 int argc, |
|
755 sqlite3_value **argv |
|
756 ){ |
|
757 DateTime x; |
|
758 int n, i, j; |
|
759 char *z; |
|
760 const char *zFmt = (const char*)sqlite3_value_text(argv[0]); |
|
761 char zBuf[100]; |
|
762 if( zFmt==0 || isDate(argc-1, argv+1, &x) ) return; |
|
763 for(i=0, n=1; zFmt[i]; i++, n++){ |
|
764 if( zFmt[i]=='%' ){ |
|
765 switch( zFmt[i+1] ){ |
|
766 case 'd': |
|
767 case 'H': |
|
768 case 'm': |
|
769 case 'M': |
|
770 case 'S': |
|
771 case 'W': |
|
772 n++; |
|
773 /* fall thru */ |
|
774 case 'w': |
|
775 case '%': |
|
776 break; |
|
777 case 'f': |
|
778 n += 8; |
|
779 break; |
|
780 case 'j': |
|
781 n += 3; |
|
782 break; |
|
783 case 'Y': |
|
784 n += 8; |
|
785 break; |
|
786 case 's': |
|
787 case 'J': |
|
788 n += 50; |
|
789 break; |
|
790 default: |
|
791 return; /* ERROR. return a NULL */ |
|
792 } |
|
793 i++; |
|
794 } |
|
795 } |
|
796 if( n<sizeof(zBuf) ){ |
|
797 z = zBuf; |
|
798 }else{ |
|
799 z = sqliteMalloc( n ); |
|
800 if( z==0 ) return; |
|
801 } |
|
802 computeJD(&x); |
|
803 computeYMD_HMS(&x); |
|
804 for(i=j=0; zFmt[i]; i++){ |
|
805 if( zFmt[i]!='%' ){ |
|
806 z[j++] = zFmt[i]; |
|
807 }else{ |
|
808 i++; |
|
809 switch( zFmt[i] ){ |
|
810 case 'd': sprintf(&z[j],"%02d",x.D); j+=2; break; |
|
811 case 'f': { |
|
812 int s = x.s; |
|
813 int ms = (x.s - s)*1000.0; |
|
814 sprintf(&z[j],"%02d.%03d",s,ms); |
|
815 j += strlen(&z[j]); |
|
816 break; |
|
817 } |
|
818 case 'H': sprintf(&z[j],"%02d",x.h); j+=2; break; |
|
819 case 'W': /* Fall thru */ |
|
820 case 'j': { |
|
821 int nDay; /* Number of days since 1st day of year */ |
|
822 DateTime y = x; |
|
823 y.validJD = 0; |
|
824 y.M = 1; |
|
825 y.D = 1; |
|
826 computeJD(&y); |
|
827 nDay = x.rJD - y.rJD; |
|
828 if( zFmt[i]=='W' ){ |
|
829 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */ |
|
830 wd = ((int)(x.rJD+0.5)) % 7; |
|
831 sprintf(&z[j],"%02d",(nDay+7-wd)/7); |
|
832 j += 2; |
|
833 }else{ |
|
834 sprintf(&z[j],"%03d",nDay+1); |
|
835 j += 3; |
|
836 } |
|
837 break; |
|
838 } |
|
839 case 'J': sprintf(&z[j],"%.16g",x.rJD); j+=strlen(&z[j]); break; |
|
840 case 'm': sprintf(&z[j],"%02d",x.M); j+=2; break; |
|
841 case 'M': sprintf(&z[j],"%02d",x.m); j+=2; break; |
|
842 case 's': { |
|
843 sprintf(&z[j],"%d",(int)((x.rJD-2440587.5)*86400.0 + 0.5)); |
|
844 j += strlen(&z[j]); |
|
845 break; |
|
846 } |
|
847 case 'S': sprintf(&z[j],"%02d",(int)(x.s+0.5)); j+=2; break; |
|
848 case 'w': z[j++] = (((int)(x.rJD+1.5)) % 7) + '0'; break; |
|
849 case 'Y': sprintf(&z[j],"%04d",x.Y); j+=strlen(&z[j]); break; |
|
850 case '%': z[j++] = '%'; break; |
|
851 } |
|
852 } |
|
853 } |
|
854 z[j] = 0; |
|
855 sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT); |
|
856 if( z!=zBuf ){ |
|
857 sqliteFree(z); |
|
858 } |
|
859 } |
|
860 |
|
861 /* |
|
862 ** current_time() |
|
863 ** |
|
864 ** This function returns the same value as time('now'). |
|
865 */ |
|
866 static void ctimeFunc( |
|
867 sqlite3_context *context, |
|
868 int argc, |
|
869 sqlite3_value **argv |
|
870 ){ |
|
871 sqlite3_value *pVal = sqlite3ValueNew(); |
|
872 if( pVal ){ |
|
873 sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC); |
|
874 timeFunc(context, 1, &pVal); |
|
875 sqlite3ValueFree(pVal); |
|
876 } |
|
877 } |
|
878 |
|
879 /* |
|
880 ** current_date() |
|
881 ** |
|
882 ** This function returns the same value as date('now'). |
|
883 */ |
|
884 static void cdateFunc( |
|
885 sqlite3_context *context, |
|
886 int argc, |
|
887 sqlite3_value **argv |
|
888 ){ |
|
889 sqlite3_value *pVal = sqlite3ValueNew(); |
|
890 if( pVal ){ |
|
891 sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC); |
|
892 dateFunc(context, 1, &pVal); |
|
893 sqlite3ValueFree(pVal); |
|
894 } |
|
895 } |
|
896 |
|
897 /* |
|
898 ** current_timestamp() |
|
899 ** |
|
900 ** This function returns the same value as datetime('now'). |
|
901 */ |
|
902 static void ctimestampFunc( |
|
903 sqlite3_context *context, |
|
904 int argc, |
|
905 sqlite3_value **argv |
|
906 ){ |
|
907 sqlite3_value *pVal = sqlite3ValueNew(); |
|
908 if( pVal ){ |
|
909 sqlite3ValueSetStr(pVal, -1, "now", SQLITE_UTF8, SQLITE_STATIC); |
|
910 datetimeFunc(context, 1, &pVal); |
|
911 sqlite3ValueFree(pVal); |
|
912 } |
|
913 } |
|
914 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */ |
|
915 |
|
916 #ifdef SQLITE_OMIT_DATETIME_FUNCS |
|
917 /* |
|
918 ** If the library is compiled to omit the full-scale date and time |
|
919 ** handling (to get a smaller binary), the following minimal version |
|
920 ** of the functions current_time(), current_date() and current_timestamp() |
|
921 ** are included instead. This is to support column declarations that |
|
922 ** include "DEFAULT CURRENT_TIME" etc. |
|
923 ** |
|
924 ** This function uses the C-library functions time(), gmtime() |
|
925 ** and strftime(). The format string to pass to strftime() is supplied |
|
926 ** as the user-data for the function. |
|
927 */ |
|
928 static void currentTimeFunc( |
|
929 sqlite3_context *context, |
|
930 int argc, |
|
931 sqlite3_value **argv |
|
932 ){ |
|
933 time_t t; |
|
934 char *zFormat = (char *)sqlite3_user_data(context); |
|
935 char zBuf[20]; |
|
936 |
|
937 time(&t); |
|
938 #ifdef SQLITE_TEST |
|
939 { |
|
940 extern int sqlite3_current_time; /* See os_XXX.c */ |
|
941 if( sqlite3_current_time ){ |
|
942 t = sqlite3_current_time; |
|
943 } |
|
944 } |
|
945 #endif |
|
946 |
|
947 sqlite3OsEnterMutex(); |
|
948 strftime(zBuf, 20, zFormat, gmtime(&t)); |
|
949 sqlite3OsLeaveMutex(); |
|
950 |
|
951 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); |
|
952 } |
|
953 #endif |
|
954 |
|
955 /* |
|
956 ** This function registered all of the above C functions as SQL |
|
957 ** functions. This should be the only routine in this file with |
|
958 ** external linkage. |
|
959 */ |
|
960 void sqlite3RegisterDateTimeFunctions(sqlite3 *db){ |
|
961 #ifndef SQLITE_OMIT_DATETIME_FUNCS |
|
962 static const struct { |
|
963 char *zName; |
|
964 int nArg; |
|
965 void (*xFunc)(sqlite3_context*,int,sqlite3_value**); |
|
966 } aFuncs[] = { |
|
967 { "julianday", -1, juliandayFunc }, |
|
968 { "date", -1, dateFunc }, |
|
969 { "time", -1, timeFunc }, |
|
970 { "datetime", -1, datetimeFunc }, |
|
971 { "strftime", -1, strftimeFunc }, |
|
972 { "current_time", 0, ctimeFunc }, |
|
973 { "current_timestamp", 0, ctimestampFunc }, |
|
974 { "current_date", 0, cdateFunc }, |
|
975 }; |
|
976 int i; |
|
977 |
|
978 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ |
|
979 sqlite3CreateFunc(db, aFuncs[i].zName, aFuncs[i].nArg, |
|
980 SQLITE_UTF8, 0, aFuncs[i].xFunc, 0, 0); |
|
981 } |
|
982 #else |
|
983 static const struct { |
|
984 char *zName; |
|
985 char *zFormat; |
|
986 } aFuncs[] = { |
|
987 { "current_time", "%H:%M:%S" }, |
|
988 { "current_date", "%Y-%m-%d" }, |
|
989 { "current_timestamp", "%Y-%m-%d %H:%M:%S" } |
|
990 }; |
|
991 int i; |
|
992 |
|
993 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ |
|
994 sqlite3CreateFunc(db, aFuncs[i].zName, 0, SQLITE_UTF8, |
|
995 aFuncs[i].zFormat, currentTimeFunc, 0, 0); |
|
996 } |
|
997 #endif |
|
998 } |