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)
60struct tm *__cdecl localtime(const time_t *);
61#endif
62
63/*
64** A structure for holding a single date and time.
65*/
66typedef struct DateTime DateTime;
67struct 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*/
110static 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 );
143end_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*/
164static 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);
187zulu_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*/
200static 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*/
240static 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*/
251static 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*/
301static 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*/
338static 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*/
354static 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*/
379static 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*/
412static 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*/
419static 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*/
448static 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*/
467static void computeYMD_HMS(DateTime *p){
468 computeYMD(p);
469 computeHMS(p);
470}
471
472/*
473** Clear the YMD and HMS and the TZ
474*/
475static 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*/
515static 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*/
565static 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*/
624static 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*/
662static 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*/
937static 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*/
981static 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*/
999static 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*/
1016static 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*/
1063static 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*/
1092static 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*/
1143static 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*/
1250static 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*/
1264static 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*/
1278static 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*/
1300static 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*/
1338void 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