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 | ** SQLite processes all times and dates as julian day numbers. The |
20 | ** dates and times are stored as the number of days since noon |
21 | ** in Greenwich on November 24, 4714 B.C. according to the Gregorian |
22 | ** calendar system. |
23 | ** |
24 | ** 1970-01-01 00:00:00 is JD 2440587.5 |
25 | ** 2000-01-01 00:00:00 is JD 2451544.5 |
26 | ** |
27 | ** This implementation requires years to be expressed as a 4-digit number |
28 | ** which means that only dates between 0000-01-01 and 9999-12-31 can |
29 | ** be represented, even though julian day numbers allow a much wider |
30 | ** range of dates. |
31 | ** |
32 | ** The Gregorian calendar system is used for all dates and times, |
33 | ** even those that predate the Gregorian calendar. Historians usually |
34 | ** use the julian calendar for dates prior to 1582-10-15 and for some |
35 | ** dates afterwards, depending on locale. Beware of this difference. |
36 | ** |
37 | ** The conversion algorithms are implemented based on descriptions |
38 | ** in the following text: |
39 | ** |
40 | ** Jean Meeus |
41 | ** Astronomical Algorithms, 2nd Edition, 1998 |
42 | ** ISBN 0-943396-61-1 |
43 | ** Willmann-Bell, Inc |
44 | ** Richmond, Virginia (USA) |
45 | */ |
46 | #include "sqliteInt.h" |
47 | #include <stdlib.h> |
48 | #include <assert.h> |
49 | #include <time.h> |
50 | |
51 | #ifndef SQLITE_OMIT_DATETIME_FUNCS |
52 | |
53 | /* |
54 | ** The MSVC CRT on Windows CE may not have a localtime() function. |
55 | ** So declare a substitute. The substitute function itself is |
56 | ** defined in "os_win.c". |
57 | */ |
58 | #if !defined(SQLITE_OMIT_LOCALTIME) && defined(_WIN32_WCE) && \ |
59 | (!defined(SQLITE_MSVC_LOCALTIME_API) || !SQLITE_MSVC_LOCALTIME_API) |
60 | struct tm *__cdecl localtime(const time_t *); |
61 | #endif |
62 | |
63 | /* |
64 | ** A structure for holding a single date and time. |
65 | */ |
66 | typedef struct DateTime DateTime; |
67 | struct DateTime { |
68 | sqlite3_int64 iJD; /* The julian day number times 86400000 */ |
69 | int Y, M, D; /* Year, month, and day */ |
70 | int h, m; /* Hour and minutes */ |
71 | int tz; /* Timezone offset in minutes */ |
72 | double s; /* Seconds */ |
73 | char validJD; /* True (1) if iJD is valid */ |
74 | char rawS; /* Raw numeric value stored in s */ |
75 | char validYMD; /* True (1) if Y,M,D are valid */ |
76 | char validHMS; /* True (1) if h,m,s are valid */ |
77 | char validTZ; /* True (1) if tz is valid */ |
78 | char tzSet; /* Timezone was set explicitly */ |
79 | char isError; /* An overflow has occurred */ |
80 | }; |
81 | |
82 | |
83 | /* |
84 | ** Convert zDate into one or more integers according to the conversion |
85 | ** specifier zFormat. |
86 | ** |
87 | ** zFormat[] contains 4 characters for each integer converted, except for |
88 | ** the last integer which is specified by three characters. The meaning |
89 | ** of a four-character format specifiers ABCD is: |
90 | ** |
91 | ** A: number of digits to convert. Always "2" or "4". |
92 | ** B: minimum value. Always "0" or "1". |
93 | ** C: maximum value, decoded as: |
94 | ** a: 12 |
95 | ** b: 14 |
96 | ** c: 24 |
97 | ** d: 31 |
98 | ** e: 59 |
99 | ** f: 9999 |
100 | ** D: the separator character, or \000 to indicate this is the |
101 | ** last number to convert. |
102 | ** |
103 | ** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would |
104 | ** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-". |
105 | ** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates |
106 | ** the 2-digit day which is the last integer in the set. |
107 | ** |
108 | ** The function returns the number of successful conversions. |
109 | */ |
110 | static int getDigits(const char *zDate, const char *zFormat, ...){ |
111 | /* The aMx[] array translates the 3rd character of each format |
112 | ** spec into a max size: a b c d e f */ |
113 | static const u16 aMx[] = { 12, 14, 24, 31, 59, 9999 }; |
114 | va_list ap; |
115 | int cnt = 0; |
116 | char nextC; |
117 | va_start(ap, zFormat); |
118 | do{ |
119 | char N = zFormat[0] - '0'; |
120 | char min = zFormat[1] - '0'; |
121 | int val = 0; |
122 | u16 max; |
123 | |
124 | assert( zFormat[2]>='a' && zFormat[2]<='f' ); |
125 | max = aMx[zFormat[2] - 'a']; |
126 | nextC = zFormat[3]; |
127 | val = 0; |
128 | while( N-- ){ |
129 | if( !sqlite3Isdigit(*zDate) ){ |
130 | goto end_getDigits; |
131 | } |
132 | val = val*10 + *zDate - '0'; |
133 | zDate++; |
134 | } |
135 | if( val<(int)min || val>(int)max || (nextC!=0 && nextC!=*zDate) ){ |
136 | goto end_getDigits; |
137 | } |
138 | *va_arg(ap,int*) = val; |
139 | zDate++; |
140 | cnt++; |
141 | zFormat += 4; |
142 | }while( nextC ); |
143 | end_getDigits: |
144 | va_end(ap); |
145 | return cnt; |
146 | } |
147 | |
148 | /* |
149 | ** Parse a timezone extension on the end of a date-time. |
150 | ** The extension is of the form: |
151 | ** |
152 | ** (+/-)HH:MM |
153 | ** |
154 | ** Or the "zulu" notation: |
155 | ** |
156 | ** Z |
157 | ** |
158 | ** If the parse is successful, write the number of minutes |
159 | ** of change in p->tz and return 0. If a parser error occurs, |
160 | ** return non-zero. |
161 | ** |
162 | ** A missing specifier is not considered an error. |
163 | */ |
164 | static int parseTimezone(const char *zDate, DateTime *p){ |
165 | int sgn = 0; |
166 | int nHr, nMn; |
167 | int c; |
168 | while( sqlite3Isspace(*zDate) ){ zDate++; } |
169 | p->tz = 0; |
170 | c = *zDate; |
171 | if( c=='-' ){ |
172 | sgn = -1; |
173 | }else if( c=='+' ){ |
174 | sgn = +1; |
175 | }else if( c=='Z' || c=='z' ){ |
176 | zDate++; |
177 | goto zulu_time; |
178 | }else{ |
179 | return c!=0; |
180 | } |
181 | zDate++; |
182 | if( getDigits(zDate, "20b:20e" , &nHr, &nMn)!=2 ){ |
183 | return 1; |
184 | } |
185 | zDate += 5; |
186 | p->tz = sgn*(nMn + nHr*60); |
187 | zulu_time: |
188 | while( sqlite3Isspace(*zDate) ){ zDate++; } |
189 | p->tzSet = 1; |
190 | return *zDate!=0; |
191 | } |
192 | |
193 | /* |
194 | ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF. |
195 | ** The HH, MM, and SS must each be exactly 2 digits. The |
196 | ** fractional seconds FFFF can be one or more digits. |
197 | ** |
198 | ** Return 1 if there is a parsing error and 0 on success. |
199 | */ |
200 | static int parseHhMmSs(const char *zDate, DateTime *p){ |
201 | int h, m, s; |
202 | double ms = 0.0; |
203 | if( getDigits(zDate, "20c:20e" , &h, &m)!=2 ){ |
204 | return 1; |
205 | } |
206 | zDate += 5; |
207 | if( *zDate==':' ){ |
208 | zDate++; |
209 | if( getDigits(zDate, "20e" , &s)!=1 ){ |
210 | return 1; |
211 | } |
212 | zDate += 2; |
213 | if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){ |
214 | double rScale = 1.0; |
215 | zDate++; |
216 | while( sqlite3Isdigit(*zDate) ){ |
217 | ms = ms*10.0 + *zDate - '0'; |
218 | rScale *= 10.0; |
219 | zDate++; |
220 | } |
221 | ms /= rScale; |
222 | } |
223 | }else{ |
224 | s = 0; |
225 | } |
226 | p->validJD = 0; |
227 | p->rawS = 0; |
228 | p->validHMS = 1; |
229 | p->h = h; |
230 | p->m = m; |
231 | p->s = s + ms; |
232 | if( parseTimezone(zDate, p) ) return 1; |
233 | p->validTZ = (p->tz!=0)?1:0; |
234 | return 0; |
235 | } |
236 | |
237 | /* |
238 | ** Put the DateTime object into its error state. |
239 | */ |
240 | static void datetimeError(DateTime *p){ |
241 | memset(p, 0, sizeof(*p)); |
242 | p->isError = 1; |
243 | } |
244 | |
245 | /* |
246 | ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume |
247 | ** that the YYYY-MM-DD is according to the Gregorian calendar. |
248 | ** |
249 | ** Reference: Meeus page 61 |
250 | */ |
251 | static void computeJD(DateTime *p){ |
252 | int Y, M, D, A, B, X1, X2; |
253 | |
254 | if( p->validJD ) return; |
255 | if( p->validYMD ){ |
256 | Y = p->Y; |
257 | M = p->M; |
258 | D = p->D; |
259 | }else{ |
260 | Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */ |
261 | M = 1; |
262 | D = 1; |
263 | } |
264 | if( Y<-4713 || Y>9999 || p->rawS ){ |
265 | datetimeError(p); |
266 | return; |
267 | } |
268 | if( M<=2 ){ |
269 | Y--; |
270 | M += 12; |
271 | } |
272 | A = Y/100; |
273 | B = 2 - A + (A/4); |
274 | X1 = 36525*(Y+4716)/100; |
275 | X2 = 306001*(M+1)/10000; |
276 | p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000); |
277 | p->validJD = 1; |
278 | if( p->validHMS ){ |
279 | p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000 + 0.5); |
280 | if( p->validTZ ){ |
281 | p->iJD -= p->tz*60000; |
282 | p->validYMD = 0; |
283 | p->validHMS = 0; |
284 | p->validTZ = 0; |
285 | } |
286 | } |
287 | } |
288 | |
289 | /* |
290 | ** Parse dates of the form |
291 | ** |
292 | ** YYYY-MM-DD HH:MM:SS.FFF |
293 | ** YYYY-MM-DD HH:MM:SS |
294 | ** YYYY-MM-DD HH:MM |
295 | ** YYYY-MM-DD |
296 | ** |
297 | ** Write the result into the DateTime structure and return 0 |
298 | ** on success and 1 if the input string is not a well-formed |
299 | ** date. |
300 | */ |
301 | static int parseYyyyMmDd(const char *zDate, DateTime *p){ |
302 | int Y, M, D, neg; |
303 | |
304 | if( zDate[0]=='-' ){ |
305 | zDate++; |
306 | neg = 1; |
307 | }else{ |
308 | neg = 0; |
309 | } |
310 | if( getDigits(zDate, "40f-21a-21d" , &Y, &M, &D)!=3 ){ |
311 | return 1; |
312 | } |
313 | zDate += 10; |
314 | while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; } |
315 | if( parseHhMmSs(zDate, p)==0 ){ |
316 | /* We got the time */ |
317 | }else if( *zDate==0 ){ |
318 | p->validHMS = 0; |
319 | }else{ |
320 | return 1; |
321 | } |
322 | p->validJD = 0; |
323 | p->validYMD = 1; |
324 | p->Y = neg ? -Y : Y; |
325 | p->M = M; |
326 | p->D = D; |
327 | if( p->validTZ ){ |
328 | computeJD(p); |
329 | } |
330 | return 0; |
331 | } |
332 | |
333 | /* |
334 | ** Set the time to the current time reported by the VFS. |
335 | ** |
336 | ** Return the number of errors. |
337 | */ |
338 | static int setDateTimeToCurrent(sqlite3_context *context, DateTime *p){ |
339 | p->iJD = sqlite3StmtCurrentTime(context); |
340 | if( p->iJD>0 ){ |
341 | p->validJD = 1; |
342 | return 0; |
343 | }else{ |
344 | return 1; |
345 | } |
346 | } |
347 | |
348 | /* |
349 | ** Input "r" is a numeric quantity which might be a julian day number, |
350 | ** or the number of seconds since 1970. If the value if r is within |
351 | ** range of a julian day number, install it as such and set validJD. |
352 | ** If the value is a valid unix timestamp, put it in p->s and set p->rawS. |
353 | */ |
354 | static void setRawDateNumber(DateTime *p, double r){ |
355 | p->s = r; |
356 | p->rawS = 1; |
357 | if( r>=0.0 && r<5373484.5 ){ |
358 | p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5); |
359 | p->validJD = 1; |
360 | } |
361 | } |
362 | |
363 | /* |
364 | ** Attempt to parse the given string into a julian day number. Return |
365 | ** the number of errors. |
366 | ** |
367 | ** The following are acceptable forms for the input string: |
368 | ** |
369 | ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM |
370 | ** DDDD.DD |
371 | ** now |
372 | ** |
373 | ** In the first form, the +/-HH:MM is always optional. The fractional |
374 | ** seconds extension (the ".FFF") is optional. The seconds portion |
375 | ** (":SS.FFF") is option. The year and date can be omitted as long |
376 | ** as there is a time string. The time string can be omitted as long |
377 | ** as there is a year and date. |
378 | */ |
379 | static int parseDateOrTime( |
380 | sqlite3_context *context, |
381 | const char *zDate, |
382 | DateTime *p |
383 | ){ |
384 | double r; |
385 | if( parseYyyyMmDd(zDate,p)==0 ){ |
386 | return 0; |
387 | }else if( parseHhMmSs(zDate, p)==0 ){ |
388 | return 0; |
389 | }else if( sqlite3StrICmp(zDate,"now" )==0 && sqlite3NotPureFunc(context) ){ |
390 | return setDateTimeToCurrent(context, p); |
391 | }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8)>0 ){ |
392 | setRawDateNumber(p, r); |
393 | return 0; |
394 | } |
395 | return 1; |
396 | } |
397 | |
398 | /* The julian day number for 9999-12-31 23:59:59.999 is 5373484.4999999. |
399 | ** Multiplying this by 86400000 gives 464269060799999 as the maximum value |
400 | ** for DateTime.iJD. |
401 | ** |
402 | ** But some older compilers (ex: gcc 4.2.1 on older Macs) cannot deal with |
403 | ** such a large integer literal, so we have to encode it. |
404 | */ |
405 | #define INT_464269060799999 ((((i64)0x1a640)<<32)|0x1072fdff) |
406 | |
407 | /* |
408 | ** Return TRUE if the given julian day number is within range. |
409 | ** |
410 | ** The input is the JulianDay times 86400000. |
411 | */ |
412 | static int validJulianDay(sqlite3_int64 iJD){ |
413 | return iJD>=0 && iJD<=INT_464269060799999; |
414 | } |
415 | |
416 | /* |
417 | ** Compute the Year, Month, and Day from the julian day number. |
418 | */ |
419 | static void computeYMD(DateTime *p){ |
420 | int Z, A, B, C, D, E, X1; |
421 | if( p->validYMD ) return; |
422 | if( !p->validJD ){ |
423 | p->Y = 2000; |
424 | p->M = 1; |
425 | p->D = 1; |
426 | }else if( !validJulianDay(p->iJD) ){ |
427 | datetimeError(p); |
428 | return; |
429 | }else{ |
430 | Z = (int)((p->iJD + 43200000)/86400000); |
431 | A = (int)((Z - 1867216.25)/36524.25); |
432 | A = Z + 1 + A - (A/4); |
433 | B = A + 1524; |
434 | C = (int)((B - 122.1)/365.25); |
435 | D = (36525*(C&32767))/100; |
436 | E = (int)((B-D)/30.6001); |
437 | X1 = (int)(30.6001*E); |
438 | p->D = B - D - X1; |
439 | p->M = E<14 ? E-1 : E-13; |
440 | p->Y = p->M>2 ? C - 4716 : C - 4715; |
441 | } |
442 | p->validYMD = 1; |
443 | } |
444 | |
445 | /* |
446 | ** Compute the Hour, Minute, and Seconds from the julian day number. |
447 | */ |
448 | static void computeHMS(DateTime *p){ |
449 | int s; |
450 | if( p->validHMS ) return; |
451 | computeJD(p); |
452 | s = (int)((p->iJD + 43200000) % 86400000); |
453 | p->s = s/1000.0; |
454 | s = (int)p->s; |
455 | p->s -= s; |
456 | p->h = s/3600; |
457 | s -= p->h*3600; |
458 | p->m = s/60; |
459 | p->s += s - p->m*60; |
460 | p->rawS = 0; |
461 | p->validHMS = 1; |
462 | } |
463 | |
464 | /* |
465 | ** Compute both YMD and HMS |
466 | */ |
467 | static void computeYMD_HMS(DateTime *p){ |
468 | computeYMD(p); |
469 | computeHMS(p); |
470 | } |
471 | |
472 | /* |
473 | ** Clear the YMD and HMS and the TZ |
474 | */ |
475 | static void clearYMD_HMS_TZ(DateTime *p){ |
476 | p->validYMD = 0; |
477 | p->validHMS = 0; |
478 | p->validTZ = 0; |
479 | } |
480 | |
481 | #ifndef SQLITE_OMIT_LOCALTIME |
482 | /* |
483 | ** On recent Windows platforms, the localtime_s() function is available |
484 | ** as part of the "Secure CRT". It is essentially equivalent to |
485 | ** localtime_r() available under most POSIX platforms, except that the |
486 | ** order of the parameters is reversed. |
487 | ** |
488 | ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx. |
489 | ** |
490 | ** If the user has not indicated to use localtime_r() or localtime_s() |
491 | ** already, check for an MSVC build environment that provides |
492 | ** localtime_s(). |
493 | */ |
494 | #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \ |
495 | && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE) |
496 | #undef HAVE_LOCALTIME_S |
497 | #define HAVE_LOCALTIME_S 1 |
498 | #endif |
499 | |
500 | /* |
501 | ** The following routine implements the rough equivalent of localtime_r() |
502 | ** using whatever operating-system specific localtime facility that |
503 | ** is available. This routine returns 0 on success and |
504 | ** non-zero on any kind of error. |
505 | ** |
506 | ** If the sqlite3GlobalConfig.bLocaltimeFault variable is non-zero then this |
507 | ** routine will always fail. If bLocaltimeFault is nonzero and |
508 | ** sqlite3GlobalConfig.xAltLocaltime is not NULL, then xAltLocaltime() is |
509 | ** invoked in place of the OS-defined localtime() function. |
510 | ** |
511 | ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C |
512 | ** library function localtime_r() is used to assist in the calculation of |
513 | ** local time. |
514 | */ |
515 | static int osLocaltime(time_t *t, struct tm *pTm){ |
516 | int rc; |
517 | #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S |
518 | struct tm *pX; |
519 | #if SQLITE_THREADSAFE>0 |
520 | sqlite3_mutex *mutex = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN); |
521 | #endif |
522 | sqlite3_mutex_enter(mutex); |
523 | pX = localtime(t); |
524 | #ifndef SQLITE_UNTESTABLE |
525 | if( sqlite3GlobalConfig.bLocaltimeFault ){ |
526 | if( sqlite3GlobalConfig.xAltLocaltime!=0 |
527 | && 0==sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm) |
528 | ){ |
529 | pX = pTm; |
530 | }else{ |
531 | pX = 0; |
532 | } |
533 | } |
534 | #endif |
535 | if( pX ) *pTm = *pX; |
536 | #if SQLITE_THREADSAFE>0 |
537 | sqlite3_mutex_leave(mutex); |
538 | #endif |
539 | rc = pX==0; |
540 | #else |
541 | #ifndef SQLITE_UNTESTABLE |
542 | if( sqlite3GlobalConfig.bLocaltimeFault ){ |
543 | if( sqlite3GlobalConfig.xAltLocaltime!=0 ){ |
544 | return sqlite3GlobalConfig.xAltLocaltime((const void*)t,(void*)pTm); |
545 | }else{ |
546 | return 1; |
547 | } |
548 | } |
549 | #endif |
550 | #if HAVE_LOCALTIME_R |
551 | rc = localtime_r(t, pTm)==0; |
552 | #else |
553 | rc = localtime_s(pTm, t); |
554 | #endif /* HAVE_LOCALTIME_R */ |
555 | #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */ |
556 | return rc; |
557 | } |
558 | #endif /* SQLITE_OMIT_LOCALTIME */ |
559 | |
560 | |
561 | #ifndef SQLITE_OMIT_LOCALTIME |
562 | /* |
563 | ** Assuming the input DateTime is UTC, move it to its localtime equivalent. |
564 | */ |
565 | static int toLocaltime( |
566 | DateTime *p, /* Date at which to calculate offset */ |
567 | sqlite3_context *pCtx /* Write error here if one occurs */ |
568 | ){ |
569 | time_t t; |
570 | struct tm sLocal; |
571 | int iYearDiff; |
572 | |
573 | /* Initialize the contents of sLocal to avoid a compiler warning. */ |
574 | memset(&sLocal, 0, sizeof(sLocal)); |
575 | |
576 | computeJD(p); |
577 | if( p->iJD<2108667600*(i64)100000 /* 1970-01-01 */ |
578 | || p->iJD>2130141456*(i64)100000 /* 2038-01-18 */ |
579 | ){ |
580 | /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only |
581 | ** works for years between 1970 and 2037. For dates outside this range, |
582 | ** SQLite attempts to map the year into an equivalent year within this |
583 | ** range, do the calculation, then map the year back. |
584 | */ |
585 | DateTime x = *p; |
586 | computeYMD_HMS(&x); |
587 | iYearDiff = (2000 + x.Y%4) - x.Y; |
588 | x.Y += iYearDiff; |
589 | x.validJD = 0; |
590 | computeJD(&x); |
591 | t = (time_t)(x.iJD/1000 - 21086676*(i64)10000); |
592 | }else{ |
593 | iYearDiff = 0; |
594 | t = (time_t)(p->iJD/1000 - 21086676*(i64)10000); |
595 | } |
596 | if( osLocaltime(&t, &sLocal) ){ |
597 | sqlite3_result_error(pCtx, "local time unavailable" , -1); |
598 | return SQLITE_ERROR; |
599 | } |
600 | p->Y = sLocal.tm_year + 1900 - iYearDiff; |
601 | p->M = sLocal.tm_mon + 1; |
602 | p->D = sLocal.tm_mday; |
603 | p->h = sLocal.tm_hour; |
604 | p->m = sLocal.tm_min; |
605 | p->s = sLocal.tm_sec + (p->iJD%1000)*0.001; |
606 | p->validYMD = 1; |
607 | p->validHMS = 1; |
608 | p->validJD = 0; |
609 | p->rawS = 0; |
610 | p->validTZ = 0; |
611 | p->isError = 0; |
612 | return SQLITE_OK; |
613 | } |
614 | #endif /* SQLITE_OMIT_LOCALTIME */ |
615 | |
616 | /* |
617 | ** The following table defines various date transformations of the form |
618 | ** |
619 | ** 'NNN days' |
620 | ** |
621 | ** Where NNN is an arbitrary floating-point number and "days" can be one |
622 | ** of several units of time. |
623 | */ |
624 | static const struct { |
625 | u8 nName; /* Length of the name */ |
626 | char zName[7]; /* Name of the transformation */ |
627 | float rLimit; /* Maximum NNN value for this transform */ |
628 | float rXform; /* Constant used for this transform */ |
629 | } aXformType[] = { |
630 | { 6, "second" , 4.6427e+14, 1.0 }, |
631 | { 6, "minute" , 7.7379e+12, 60.0 }, |
632 | { 4, "hour" , 1.2897e+11, 3600.0 }, |
633 | { 3, "day" , 5373485.0, 86400.0 }, |
634 | { 5, "month" , 176546.0, 2592000.0 }, |
635 | { 4, "year" , 14713.0, 31536000.0 }, |
636 | }; |
637 | |
638 | /* |
639 | ** Process a modifier to a date-time stamp. The modifiers are |
640 | ** as follows: |
641 | ** |
642 | ** NNN days |
643 | ** NNN hours |
644 | ** NNN minutes |
645 | ** NNN.NNNN seconds |
646 | ** NNN months |
647 | ** NNN years |
648 | ** start of month |
649 | ** start of year |
650 | ** start of week |
651 | ** start of day |
652 | ** weekday N |
653 | ** unixepoch |
654 | ** localtime |
655 | ** utc |
656 | ** |
657 | ** Return 0 on success and 1 if there is any kind of error. If the error |
658 | ** is in a system call (i.e. localtime()), then an error message is written |
659 | ** to context pCtx. If the error is an unrecognized modifier, no error is |
660 | ** written to pCtx. |
661 | */ |
662 | static int parseModifier( |
663 | sqlite3_context *pCtx, /* Function context */ |
664 | const char *z, /* The text of the modifier */ |
665 | int n, /* Length of zMod in bytes */ |
666 | DateTime *p, /* The date/time value to be modified */ |
667 | int idx /* Parameter index of the modifier */ |
668 | ){ |
669 | int rc = 1; |
670 | double r; |
671 | switch(sqlite3UpperToLower[(u8)z[0]] ){ |
672 | case 'a': { |
673 | /* |
674 | ** auto |
675 | ** |
676 | ** If rawS is available, then interpret as a julian day number, or |
677 | ** a unix timestamp, depending on its magnitude. |
678 | */ |
679 | if( sqlite3_stricmp(z, "auto" )==0 ){ |
680 | if( idx>1 ) return 1; /* IMP: R-33611-57934 */ |
681 | if( !p->rawS || p->validJD ){ |
682 | rc = 0; |
683 | p->rawS = 0; |
684 | }else if( p->s>=-21086676*(i64)10000 /* -4713-11-24 12:00:00 */ |
685 | && p->s<=(25340230*(i64)10000)+799 /* 9999-12-31 23:59:59 */ |
686 | ){ |
687 | r = p->s*1000.0 + 210866760000000.0; |
688 | clearYMD_HMS_TZ(p); |
689 | p->iJD = (sqlite3_int64)(r + 0.5); |
690 | p->validJD = 1; |
691 | p->rawS = 0; |
692 | rc = 0; |
693 | } |
694 | } |
695 | break; |
696 | } |
697 | case 'j': { |
698 | /* |
699 | ** julianday |
700 | ** |
701 | ** Always interpret the prior number as a julian-day value. If this |
702 | ** is not the first modifier, or if the prior argument is not a numeric |
703 | ** value in the allowed range of julian day numbers understood by |
704 | ** SQLite (0..5373484.5) then the result will be NULL. |
705 | */ |
706 | if( sqlite3_stricmp(z, "julianday" )==0 ){ |
707 | if( idx>1 ) return 1; /* IMP: R-31176-64601 */ |
708 | if( p->validJD && p->rawS ){ |
709 | rc = 0; |
710 | p->rawS = 0; |
711 | } |
712 | } |
713 | break; |
714 | } |
715 | #ifndef SQLITE_OMIT_LOCALTIME |
716 | case 'l': { |
717 | /* localtime |
718 | ** |
719 | ** Assuming the current time value is UTC (a.k.a. GMT), shift it to |
720 | ** show local time. |
721 | */ |
722 | if( sqlite3_stricmp(z, "localtime" )==0 && sqlite3NotPureFunc(pCtx) ){ |
723 | rc = toLocaltime(p, pCtx); |
724 | } |
725 | break; |
726 | } |
727 | #endif |
728 | case 'u': { |
729 | /* |
730 | ** unixepoch |
731 | ** |
732 | ** Treat the current value of p->s as the number of |
733 | ** seconds since 1970. Convert to a real julian day number. |
734 | */ |
735 | if( sqlite3_stricmp(z, "unixepoch" )==0 && p->rawS ){ |
736 | if( idx>1 ) return 1; /* IMP: R-49255-55373 */ |
737 | r = p->s*1000.0 + 210866760000000.0; |
738 | if( r>=0.0 && r<464269060800000.0 ){ |
739 | clearYMD_HMS_TZ(p); |
740 | p->iJD = (sqlite3_int64)(r + 0.5); |
741 | p->validJD = 1; |
742 | p->rawS = 0; |
743 | rc = 0; |
744 | } |
745 | } |
746 | #ifndef SQLITE_OMIT_LOCALTIME |
747 | else if( sqlite3_stricmp(z, "utc" )==0 && sqlite3NotPureFunc(pCtx) ){ |
748 | if( p->tzSet==0 ){ |
749 | i64 iOrigJD; /* Original localtime */ |
750 | i64 iGuess; /* Guess at the corresponding utc time */ |
751 | int cnt = 0; /* Safety to prevent infinite loop */ |
752 | int iErr; /* Guess is off by this much */ |
753 | |
754 | computeJD(p); |
755 | iGuess = iOrigJD = p->iJD; |
756 | iErr = 0; |
757 | do{ |
758 | DateTime new; |
759 | memset(&new, 0, sizeof(new)); |
760 | iGuess -= iErr; |
761 | new.iJD = iGuess; |
762 | new.validJD = 1; |
763 | rc = toLocaltime(&new, pCtx); |
764 | if( rc ) return rc; |
765 | computeJD(&new); |
766 | iErr = new.iJD - iOrigJD; |
767 | }while( iErr && cnt++<3 ); |
768 | memset(p, 0, sizeof(*p)); |
769 | p->iJD = iGuess; |
770 | p->validJD = 1; |
771 | p->tzSet = 1; |
772 | } |
773 | rc = SQLITE_OK; |
774 | } |
775 | #endif |
776 | break; |
777 | } |
778 | case 'w': { |
779 | /* |
780 | ** weekday N |
781 | ** |
782 | ** Move the date to the same time on the next occurrence of |
783 | ** weekday N where 0==Sunday, 1==Monday, and so forth. If the |
784 | ** date is already on the appropriate weekday, this is a no-op. |
785 | */ |
786 | if( sqlite3_strnicmp(z, "weekday " , 8)==0 |
787 | && sqlite3AtoF(&z[8], &r, sqlite3Strlen30(&z[8]), SQLITE_UTF8)>0 |
788 | && r>=0.0 && r<7.0 && (n=(int)r)==r ){ |
789 | sqlite3_int64 Z; |
790 | computeYMD_HMS(p); |
791 | p->validTZ = 0; |
792 | p->validJD = 0; |
793 | computeJD(p); |
794 | Z = ((p->iJD + 129600000)/86400000) % 7; |
795 | if( Z>n ) Z -= 7; |
796 | p->iJD += (n - Z)*86400000; |
797 | clearYMD_HMS_TZ(p); |
798 | rc = 0; |
799 | } |
800 | break; |
801 | } |
802 | case 's': { |
803 | /* |
804 | ** start of TTTTT |
805 | ** |
806 | ** Move the date backwards to the beginning of the current day, |
807 | ** or month or year. |
808 | */ |
809 | if( sqlite3_strnicmp(z, "start of " , 9)!=0 ) break; |
810 | if( !p->validJD && !p->validYMD && !p->validHMS ) break; |
811 | z += 9; |
812 | computeYMD(p); |
813 | p->validHMS = 1; |
814 | p->h = p->m = 0; |
815 | p->s = 0.0; |
816 | p->rawS = 0; |
817 | p->validTZ = 0; |
818 | p->validJD = 0; |
819 | if( sqlite3_stricmp(z,"month" )==0 ){ |
820 | p->D = 1; |
821 | rc = 0; |
822 | }else if( sqlite3_stricmp(z,"year" )==0 ){ |
823 | p->M = 1; |
824 | p->D = 1; |
825 | rc = 0; |
826 | }else if( sqlite3_stricmp(z,"day" )==0 ){ |
827 | rc = 0; |
828 | } |
829 | break; |
830 | } |
831 | case '+': |
832 | case '-': |
833 | case '0': |
834 | case '1': |
835 | case '2': |
836 | case '3': |
837 | case '4': |
838 | case '5': |
839 | case '6': |
840 | case '7': |
841 | case '8': |
842 | case '9': { |
843 | double rRounder; |
844 | int i; |
845 | for(n=1; z[n] && z[n]!=':' && !sqlite3Isspace(z[n]); n++){} |
846 | if( sqlite3AtoF(z, &r, n, SQLITE_UTF8)<=0 ){ |
847 | rc = 1; |
848 | break; |
849 | } |
850 | if( z[n]==':' ){ |
851 | /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the |
852 | ** specified number of hours, minutes, seconds, and fractional seconds |
853 | ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be |
854 | ** omitted. |
855 | */ |
856 | const char *z2 = z; |
857 | DateTime tx; |
858 | sqlite3_int64 day; |
859 | if( !sqlite3Isdigit(*z2) ) z2++; |
860 | memset(&tx, 0, sizeof(tx)); |
861 | if( parseHhMmSs(z2, &tx) ) break; |
862 | computeJD(&tx); |
863 | tx.iJD -= 43200000; |
864 | day = tx.iJD/86400000; |
865 | tx.iJD -= day*86400000; |
866 | if( z[0]=='-' ) tx.iJD = -tx.iJD; |
867 | computeJD(p); |
868 | clearYMD_HMS_TZ(p); |
869 | p->iJD += tx.iJD; |
870 | rc = 0; |
871 | break; |
872 | } |
873 | |
874 | /* If control reaches this point, it means the transformation is |
875 | ** one of the forms like "+NNN days". */ |
876 | z += n; |
877 | while( sqlite3Isspace(*z) ) z++; |
878 | n = sqlite3Strlen30(z); |
879 | if( n>10 || n<3 ) break; |
880 | if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--; |
881 | computeJD(p); |
882 | rc = 1; |
883 | rRounder = r<0 ? -0.5 : +0.5; |
884 | for(i=0; i<ArraySize(aXformType); i++){ |
885 | if( aXformType[i].nName==n |
886 | && sqlite3_strnicmp(aXformType[i].zName, z, n)==0 |
887 | && r>-aXformType[i].rLimit && r<aXformType[i].rLimit |
888 | ){ |
889 | switch( i ){ |
890 | case 4: { /* Special processing to add months */ |
891 | int x; |
892 | assert( strcmp(aXformType[i].zName,"month" )==0 ); |
893 | computeYMD_HMS(p); |
894 | p->M += (int)r; |
895 | x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; |
896 | p->Y += x; |
897 | p->M -= x*12; |
898 | p->validJD = 0; |
899 | r -= (int)r; |
900 | break; |
901 | } |
902 | case 5: { /* Special processing to add years */ |
903 | int y = (int)r; |
904 | assert( strcmp(aXformType[i].zName,"year" )==0 ); |
905 | computeYMD_HMS(p); |
906 | p->Y += y; |
907 | p->validJD = 0; |
908 | r -= (int)r; |
909 | break; |
910 | } |
911 | } |
912 | computeJD(p); |
913 | p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder); |
914 | rc = 0; |
915 | break; |
916 | } |
917 | } |
918 | clearYMD_HMS_TZ(p); |
919 | break; |
920 | } |
921 | default: { |
922 | break; |
923 | } |
924 | } |
925 | return rc; |
926 | } |
927 | |
928 | /* |
929 | ** Process time function arguments. argv[0] is a date-time stamp. |
930 | ** argv[1] and following are modifiers. Parse them all and write |
931 | ** the resulting time into the DateTime structure p. Return 0 |
932 | ** on success and 1 if there are any errors. |
933 | ** |
934 | ** If there are zero parameters (if even argv[0] is undefined) |
935 | ** then assume a default value of "now" for argv[0]. |
936 | */ |
937 | static int isDate( |
938 | sqlite3_context *context, |
939 | int argc, |
940 | sqlite3_value **argv, |
941 | DateTime *p |
942 | ){ |
943 | int i, n; |
944 | const unsigned char *z; |
945 | int eType; |
946 | memset(p, 0, sizeof(*p)); |
947 | if( argc==0 ){ |
948 | if( !sqlite3NotPureFunc(context) ) return 1; |
949 | return setDateTimeToCurrent(context, p); |
950 | } |
951 | if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT |
952 | || eType==SQLITE_INTEGER ){ |
953 | setRawDateNumber(p, sqlite3_value_double(argv[0])); |
954 | }else{ |
955 | z = sqlite3_value_text(argv[0]); |
956 | if( !z || parseDateOrTime(context, (char*)z, p) ){ |
957 | return 1; |
958 | } |
959 | } |
960 | for(i=1; i<argc; i++){ |
961 | z = sqlite3_value_text(argv[i]); |
962 | n = sqlite3_value_bytes(argv[i]); |
963 | if( z==0 || parseModifier(context, (char*)z, n, p, i) ) return 1; |
964 | } |
965 | computeJD(p); |
966 | if( p->isError || !validJulianDay(p->iJD) ) return 1; |
967 | return 0; |
968 | } |
969 | |
970 | |
971 | /* |
972 | ** The following routines implement the various date and time functions |
973 | ** of SQLite. |
974 | */ |
975 | |
976 | /* |
977 | ** julianday( TIMESTRING, MOD, MOD, ...) |
978 | ** |
979 | ** Return the julian day number of the date specified in the arguments |
980 | */ |
981 | static void juliandayFunc( |
982 | sqlite3_context *context, |
983 | int argc, |
984 | sqlite3_value **argv |
985 | ){ |
986 | DateTime x; |
987 | if( isDate(context, argc, argv, &x)==0 ){ |
988 | computeJD(&x); |
989 | sqlite3_result_double(context, x.iJD/86400000.0); |
990 | } |
991 | } |
992 | |
993 | /* |
994 | ** unixepoch( TIMESTRING, MOD, MOD, ...) |
995 | ** |
996 | ** Return the number of seconds (including fractional seconds) since |
997 | ** the unix epoch of 1970-01-01 00:00:00 GMT. |
998 | */ |
999 | static void unixepochFunc( |
1000 | sqlite3_context *context, |
1001 | int argc, |
1002 | sqlite3_value **argv |
1003 | ){ |
1004 | DateTime x; |
1005 | if( isDate(context, argc, argv, &x)==0 ){ |
1006 | computeJD(&x); |
1007 | sqlite3_result_int64(context, x.iJD/1000 - 21086676*(i64)10000); |
1008 | } |
1009 | } |
1010 | |
1011 | /* |
1012 | ** datetime( TIMESTRING, MOD, MOD, ...) |
1013 | ** |
1014 | ** Return YYYY-MM-DD HH:MM:SS |
1015 | */ |
1016 | static void datetimeFunc( |
1017 | sqlite3_context *context, |
1018 | int argc, |
1019 | sqlite3_value **argv |
1020 | ){ |
1021 | DateTime x; |
1022 | if( isDate(context, argc, argv, &x)==0 ){ |
1023 | int Y, s; |
1024 | char zBuf[24]; |
1025 | computeYMD_HMS(&x); |
1026 | Y = x.Y; |
1027 | if( Y<0 ) Y = -Y; |
1028 | zBuf[1] = '0' + (Y/1000)%10; |
1029 | zBuf[2] = '0' + (Y/100)%10; |
1030 | zBuf[3] = '0' + (Y/10)%10; |
1031 | zBuf[4] = '0' + (Y)%10; |
1032 | zBuf[5] = '-'; |
1033 | zBuf[6] = '0' + (x.M/10)%10; |
1034 | zBuf[7] = '0' + (x.M)%10; |
1035 | zBuf[8] = '-'; |
1036 | zBuf[9] = '0' + (x.D/10)%10; |
1037 | zBuf[10] = '0' + (x.D)%10; |
1038 | zBuf[11] = ' '; |
1039 | zBuf[12] = '0' + (x.h/10)%10; |
1040 | zBuf[13] = '0' + (x.h)%10; |
1041 | zBuf[14] = ':'; |
1042 | zBuf[15] = '0' + (x.m/10)%10; |
1043 | zBuf[16] = '0' + (x.m)%10; |
1044 | zBuf[17] = ':'; |
1045 | s = (int)x.s; |
1046 | zBuf[18] = '0' + (s/10)%10; |
1047 | zBuf[19] = '0' + (s)%10; |
1048 | zBuf[20] = 0; |
1049 | if( x.Y<0 ){ |
1050 | zBuf[0] = '-'; |
1051 | sqlite3_result_text(context, zBuf, 20, SQLITE_TRANSIENT); |
1052 | }else{ |
1053 | sqlite3_result_text(context, &zBuf[1], 19, SQLITE_TRANSIENT); |
1054 | } |
1055 | } |
1056 | } |
1057 | |
1058 | /* |
1059 | ** time( TIMESTRING, MOD, MOD, ...) |
1060 | ** |
1061 | ** Return HH:MM:SS |
1062 | */ |
1063 | static void timeFunc( |
1064 | sqlite3_context *context, |
1065 | int argc, |
1066 | sqlite3_value **argv |
1067 | ){ |
1068 | DateTime x; |
1069 | if( isDate(context, argc, argv, &x)==0 ){ |
1070 | int s; |
1071 | char zBuf[16]; |
1072 | computeHMS(&x); |
1073 | zBuf[0] = '0' + (x.h/10)%10; |
1074 | zBuf[1] = '0' + (x.h)%10; |
1075 | zBuf[2] = ':'; |
1076 | zBuf[3] = '0' + (x.m/10)%10; |
1077 | zBuf[4] = '0' + (x.m)%10; |
1078 | zBuf[5] = ':'; |
1079 | s = (int)x.s; |
1080 | zBuf[6] = '0' + (s/10)%10; |
1081 | zBuf[7] = '0' + (s)%10; |
1082 | zBuf[8] = 0; |
1083 | sqlite3_result_text(context, zBuf, 8, SQLITE_TRANSIENT); |
1084 | } |
1085 | } |
1086 | |
1087 | /* |
1088 | ** date( TIMESTRING, MOD, MOD, ...) |
1089 | ** |
1090 | ** Return YYYY-MM-DD |
1091 | */ |
1092 | static void dateFunc( |
1093 | sqlite3_context *context, |
1094 | int argc, |
1095 | sqlite3_value **argv |
1096 | ){ |
1097 | DateTime x; |
1098 | if( isDate(context, argc, argv, &x)==0 ){ |
1099 | int Y; |
1100 | char zBuf[16]; |
1101 | computeYMD(&x); |
1102 | Y = x.Y; |
1103 | if( Y<0 ) Y = -Y; |
1104 | zBuf[1] = '0' + (Y/1000)%10; |
1105 | zBuf[2] = '0' + (Y/100)%10; |
1106 | zBuf[3] = '0' + (Y/10)%10; |
1107 | zBuf[4] = '0' + (Y)%10; |
1108 | zBuf[5] = '-'; |
1109 | zBuf[6] = '0' + (x.M/10)%10; |
1110 | zBuf[7] = '0' + (x.M)%10; |
1111 | zBuf[8] = '-'; |
1112 | zBuf[9] = '0' + (x.D/10)%10; |
1113 | zBuf[10] = '0' + (x.D)%10; |
1114 | zBuf[11] = 0; |
1115 | if( x.Y<0 ){ |
1116 | zBuf[0] = '-'; |
1117 | sqlite3_result_text(context, zBuf, 11, SQLITE_TRANSIENT); |
1118 | }else{ |
1119 | sqlite3_result_text(context, &zBuf[1], 10, SQLITE_TRANSIENT); |
1120 | } |
1121 | } |
1122 | } |
1123 | |
1124 | /* |
1125 | ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...) |
1126 | ** |
1127 | ** Return a string described by FORMAT. Conversions as follows: |
1128 | ** |
1129 | ** %d day of month |
1130 | ** %f ** fractional seconds SS.SSS |
1131 | ** %H hour 00-24 |
1132 | ** %j day of year 000-366 |
1133 | ** %J ** julian day number |
1134 | ** %m month 01-12 |
1135 | ** %M minute 00-59 |
1136 | ** %s seconds since 1970-01-01 |
1137 | ** %S seconds 00-59 |
1138 | ** %w day of week 0-6 sunday==0 |
1139 | ** %W week of year 00-53 |
1140 | ** %Y year 0000-9999 |
1141 | ** %% % |
1142 | */ |
1143 | static void strftimeFunc( |
1144 | sqlite3_context *context, |
1145 | int argc, |
1146 | sqlite3_value **argv |
1147 | ){ |
1148 | DateTime x; |
1149 | size_t i,j; |
1150 | sqlite3 *db; |
1151 | const char *zFmt; |
1152 | sqlite3_str sRes; |
1153 | |
1154 | |
1155 | if( argc==0 ) return; |
1156 | zFmt = (const char*)sqlite3_value_text(argv[0]); |
1157 | if( zFmt==0 || isDate(context, argc-1, argv+1, &x) ) return; |
1158 | db = sqlite3_context_db_handle(context); |
1159 | sqlite3StrAccumInit(&sRes, 0, 0, 0, db->aLimit[SQLITE_LIMIT_LENGTH]); |
1160 | |
1161 | computeJD(&x); |
1162 | computeYMD_HMS(&x); |
1163 | for(i=j=0; zFmt[i]; i++){ |
1164 | if( zFmt[i]!='%' ) continue; |
1165 | if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j)); |
1166 | i++; |
1167 | j = i + 1; |
1168 | switch( zFmt[i] ){ |
1169 | case 'd': { |
1170 | sqlite3_str_appendf(&sRes, "%02d" , x.D); |
1171 | break; |
1172 | } |
1173 | case 'f': { |
1174 | double s = x.s; |
1175 | if( s>59.999 ) s = 59.999; |
1176 | sqlite3_str_appendf(&sRes, "%06.3f" , s); |
1177 | break; |
1178 | } |
1179 | case 'H': { |
1180 | sqlite3_str_appendf(&sRes, "%02d" , x.h); |
1181 | break; |
1182 | } |
1183 | case 'W': /* Fall thru */ |
1184 | case 'j': { |
1185 | int nDay; /* Number of days since 1st day of year */ |
1186 | DateTime y = x; |
1187 | y.validJD = 0; |
1188 | y.M = 1; |
1189 | y.D = 1; |
1190 | computeJD(&y); |
1191 | nDay = (int)((x.iJD-y.iJD+43200000)/86400000); |
1192 | if( zFmt[i]=='W' ){ |
1193 | int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */ |
1194 | wd = (int)(((x.iJD+43200000)/86400000)%7); |
1195 | sqlite3_str_appendf(&sRes,"%02d" ,(nDay+7-wd)/7); |
1196 | }else{ |
1197 | sqlite3_str_appendf(&sRes,"%03d" ,nDay+1); |
1198 | } |
1199 | break; |
1200 | } |
1201 | case 'J': { |
1202 | sqlite3_str_appendf(&sRes,"%.16g" ,x.iJD/86400000.0); |
1203 | break; |
1204 | } |
1205 | case 'm': { |
1206 | sqlite3_str_appendf(&sRes,"%02d" ,x.M); |
1207 | break; |
1208 | } |
1209 | case 'M': { |
1210 | sqlite3_str_appendf(&sRes,"%02d" ,x.m); |
1211 | break; |
1212 | } |
1213 | case 's': { |
1214 | i64 iS = (i64)(x.iJD/1000 - 21086676*(i64)10000); |
1215 | sqlite3_str_appendf(&sRes,"%lld" ,iS); |
1216 | break; |
1217 | } |
1218 | case 'S': { |
1219 | sqlite3_str_appendf(&sRes,"%02d" ,(int)x.s); |
1220 | break; |
1221 | } |
1222 | case 'w': { |
1223 | sqlite3_str_appendchar(&sRes, 1, |
1224 | (char)(((x.iJD+129600000)/86400000) % 7) + '0'); |
1225 | break; |
1226 | } |
1227 | case 'Y': { |
1228 | sqlite3_str_appendf(&sRes,"%04d" ,x.Y); |
1229 | break; |
1230 | } |
1231 | case '%': { |
1232 | sqlite3_str_appendchar(&sRes, 1, '%'); |
1233 | break; |
1234 | } |
1235 | default: { |
1236 | sqlite3_str_reset(&sRes); |
1237 | return; |
1238 | } |
1239 | } |
1240 | } |
1241 | if( j<i ) sqlite3_str_append(&sRes, zFmt+j, (int)(i-j)); |
1242 | sqlite3ResultStrAccum(context, &sRes); |
1243 | } |
1244 | |
1245 | /* |
1246 | ** current_time() |
1247 | ** |
1248 | ** This function returns the same value as time('now'). |
1249 | */ |
1250 | static void ctimeFunc( |
1251 | sqlite3_context *context, |
1252 | int NotUsed, |
1253 | sqlite3_value **NotUsed2 |
1254 | ){ |
1255 | UNUSED_PARAMETER2(NotUsed, NotUsed2); |
1256 | timeFunc(context, 0, 0); |
1257 | } |
1258 | |
1259 | /* |
1260 | ** current_date() |
1261 | ** |
1262 | ** This function returns the same value as date('now'). |
1263 | */ |
1264 | static void cdateFunc( |
1265 | sqlite3_context *context, |
1266 | int NotUsed, |
1267 | sqlite3_value **NotUsed2 |
1268 | ){ |
1269 | UNUSED_PARAMETER2(NotUsed, NotUsed2); |
1270 | dateFunc(context, 0, 0); |
1271 | } |
1272 | |
1273 | /* |
1274 | ** current_timestamp() |
1275 | ** |
1276 | ** This function returns the same value as datetime('now'). |
1277 | */ |
1278 | static void ctimestampFunc( |
1279 | sqlite3_context *context, |
1280 | int NotUsed, |
1281 | sqlite3_value **NotUsed2 |
1282 | ){ |
1283 | UNUSED_PARAMETER2(NotUsed, NotUsed2); |
1284 | datetimeFunc(context, 0, 0); |
1285 | } |
1286 | #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */ |
1287 | |
1288 | #ifdef SQLITE_OMIT_DATETIME_FUNCS |
1289 | /* |
1290 | ** If the library is compiled to omit the full-scale date and time |
1291 | ** handling (to get a smaller binary), the following minimal version |
1292 | ** of the functions current_time(), current_date() and current_timestamp() |
1293 | ** are included instead. This is to support column declarations that |
1294 | ** include "DEFAULT CURRENT_TIME" etc. |
1295 | ** |
1296 | ** This function uses the C-library functions time(), gmtime() |
1297 | ** and strftime(). The format string to pass to strftime() is supplied |
1298 | ** as the user-data for the function. |
1299 | */ |
1300 | static void currentTimeFunc( |
1301 | sqlite3_context *context, |
1302 | int argc, |
1303 | sqlite3_value **argv |
1304 | ){ |
1305 | time_t t; |
1306 | char *zFormat = (char *)sqlite3_user_data(context); |
1307 | sqlite3_int64 iT; |
1308 | struct tm *pTm; |
1309 | struct tm sNow; |
1310 | char zBuf[20]; |
1311 | |
1312 | UNUSED_PARAMETER(argc); |
1313 | UNUSED_PARAMETER(argv); |
1314 | |
1315 | iT = sqlite3StmtCurrentTime(context); |
1316 | if( iT<=0 ) return; |
1317 | t = iT/1000 - 10000*(sqlite3_int64)21086676; |
1318 | #if HAVE_GMTIME_R |
1319 | pTm = gmtime_r(&t, &sNow); |
1320 | #else |
1321 | sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN)); |
1322 | pTm = gmtime(&t); |
1323 | if( pTm ) memcpy(&sNow, pTm, sizeof(sNow)); |
1324 | sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MAIN)); |
1325 | #endif |
1326 | if( pTm ){ |
1327 | strftime(zBuf, 20, zFormat, &sNow); |
1328 | sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); |
1329 | } |
1330 | } |
1331 | #endif |
1332 | |
1333 | /* |
1334 | ** This function registered all of the above C functions as SQL |
1335 | ** functions. This should be the only routine in this file with |
1336 | ** external linkage. |
1337 | */ |
1338 | void sqlite3RegisterDateTimeFunctions(void){ |
1339 | static FuncDef aDateTimeFuncs[] = { |
1340 | #ifndef SQLITE_OMIT_DATETIME_FUNCS |
1341 | PURE_DATE(julianday, -1, 0, 0, juliandayFunc ), |
1342 | PURE_DATE(unixepoch, -1, 0, 0, unixepochFunc ), |
1343 | PURE_DATE(date, -1, 0, 0, dateFunc ), |
1344 | PURE_DATE(time, -1, 0, 0, timeFunc ), |
1345 | PURE_DATE(datetime, -1, 0, 0, datetimeFunc ), |
1346 | PURE_DATE(strftime, -1, 0, 0, strftimeFunc ), |
1347 | DFUNCTION(current_time, 0, 0, 0, ctimeFunc ), |
1348 | DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc), |
1349 | DFUNCTION(current_date, 0, 0, 0, cdateFunc ), |
1350 | #else |
1351 | STR_FUNCTION(current_time, 0, "%H:%M:%S" , 0, currentTimeFunc), |
1352 | STR_FUNCTION(current_date, 0, "%Y-%m-%d" , 0, currentTimeFunc), |
1353 | STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S" , 0, currentTimeFunc), |
1354 | #endif |
1355 | }; |
1356 | sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs)); |
1357 | } |
1358 | |