monotone

monotone Mtn Source Tree

Root/sqlite/date.c

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

Archive Download this file

Branches

Tags

Quick Links:     www.monotone.ca    -     Downloads    -     Documentation    -     Wiki    -     Code Forge    -     Build Status