4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
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.
11 *************************************************************************
12 ** This file contains the C functions that implement date and time
13 ** functions for SQLite.
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.
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
24 ** 1970-01-01 00:00:00 is JD 2440587.5
25 ** 2000-01-01 00:00:00 is JD 2451544.5
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
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.
37 ** The conversion algorithms are implemented based on descriptions
38 ** in the following text:
41 ** Astronomical Algorithms, 2nd Edition, 1998
44 ** Richmond, Virginia (USA)
46 #include "sqliteInt.h"
51 #ifndef SQLITE_OMIT_DATETIME_FUNCS
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".
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 *);
64 ** A structure for holding a single date and time.
66 typedef struct DateTime 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 validYMD
; /* True (1) if Y,M,D are valid */
74 char validHMS
; /* True (1) if h,m,s are valid */
75 char validJD
; /* True (1) if iJD is valid */
76 char validTZ
; /* True (1) if tz is valid */
77 char tzSet
; /* Timezone was set explicitly */
82 ** Convert zDate into one or more integers according to the conversion
85 ** zFormat[] contains 4 characters for each integer converted, except for
86 ** the last integer which is specified by three characters. The meaning
87 ** of a four-character format specifiers ABCD is:
89 ** A: number of digits to convert. Always "2" or "4".
90 ** B: minimum value. Always "0" or "1".
91 ** C: maximum value, decoded as:
98 ** D: the separator character, or \000 to indicate this is the
99 ** last number to convert.
101 ** Example: To translate an ISO-8601 date YYYY-MM-DD, the format would
102 ** be "40f-21a-20c". The "40f-" indicates the 4-digit year followed by "-".
103 ** The "21a-" indicates the 2-digit month followed by "-". The "20c" indicates
104 ** the 2-digit day which is the last integer in the set.
106 ** The function returns the number of successful conversions.
108 static int getDigits(const char *zDate
, const char *zFormat
, ...){
109 /* The aMx[] array translates the 3rd character of each format
110 ** spec into a max size: a b c d e f */
111 static const u16 aMx
[] = { 12, 14, 24, 31, 59, 9999 };
115 va_start(ap
, zFormat
);
117 char N
= zFormat
[0] - '0';
118 char min
= zFormat
[1] - '0';
122 assert( zFormat
[2]>='a' && zFormat
[2]<='f' );
123 max
= aMx
[zFormat
[2] - 'a'];
127 if( !sqlite3Isdigit(*zDate
) ){
130 val
= val
*10 + *zDate
- '0';
133 if( val
<(int)min
|| val
>(int)max
|| (nextC
!=0 && nextC
!=*zDate
) ){
136 *va_arg(ap
,int*) = val
;
147 ** Parse a timezone extension on the end of a date-time.
148 ** The extension is of the form:
152 ** Or the "zulu" notation:
156 ** If the parse is successful, write the number of minutes
157 ** of change in p->tz and return 0. If a parser error occurs,
160 ** A missing specifier is not considered an error.
162 static int parseTimezone(const char *zDate
, DateTime
*p
){
166 while( sqlite3Isspace(*zDate
) ){ zDate
++; }
173 }else if( c
=='Z' || c
=='z' ){
180 if( getDigits(zDate
, "20b:20e", &nHr
, &nMn
)!=2 ){
184 p
->tz
= sgn
*(nMn
+ nHr
*60);
186 while( sqlite3Isspace(*zDate
) ){ zDate
++; }
192 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
193 ** The HH, MM, and SS must each be exactly 2 digits. The
194 ** fractional seconds FFFF can be one or more digits.
196 ** Return 1 if there is a parsing error and 0 on success.
198 static int parseHhMmSs(const char *zDate
, DateTime
*p
){
201 if( getDigits(zDate
, "20c:20e", &h
, &m
)!=2 ){
207 if( getDigits(zDate
, "20e", &s
)!=1 ){
211 if( *zDate
=='.' && sqlite3Isdigit(zDate
[1]) ){
214 while( sqlite3Isdigit(*zDate
) ){
215 ms
= ms
*10.0 + *zDate
- '0';
229 if( parseTimezone(zDate
, p
) ) return 1;
230 p
->validTZ
= (p
->tz
!=0)?1:0;
235 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume
236 ** that the YYYY-MM-DD is according to the Gregorian calendar.
238 ** Reference: Meeus page 61
240 static void computeJD(DateTime
*p
){
241 int Y
, M
, D
, A
, B
, X1
, X2
;
243 if( p
->validJD
) return;
249 Y
= 2000; /* If no YMD specified, assume 2000-Jan-01 */
259 X1
= 36525*(Y
+4716)/100;
260 X2
= 306001*(M
+1)/10000;
261 p
->iJD
= (sqlite3_int64
)((X1
+ X2
+ D
+ B
- 1524.5 ) * 86400000);
264 p
->iJD
+= p
->h
*3600000 + p
->m
*60000 + (sqlite3_int64
)(p
->s
*1000);
266 p
->iJD
-= p
->tz
*60000;
275 ** Parse dates of the form
277 ** YYYY-MM-DD HH:MM:SS.FFF
278 ** YYYY-MM-DD HH:MM:SS
282 ** Write the result into the DateTime structure and return 0
283 ** on success and 1 if the input string is not a well-formed
286 static int parseYyyyMmDd(const char *zDate
, DateTime
*p
){
295 if( getDigits(zDate
, "40f-21a-21d", &Y
, &M
, &D
)!=3 ){
299 while( sqlite3Isspace(*zDate
) || 'T'==*(u8
*)zDate
){ zDate
++; }
300 if( parseHhMmSs(zDate
, p
)==0 ){
301 /* We got the time */
302 }else if( *zDate
==0 ){
319 ** Set the time to the current time reported by the VFS.
321 ** Return the number of errors.
323 static int setDateTimeToCurrent(sqlite3_context
*context
, DateTime
*p
){
324 p
->iJD
= sqlite3StmtCurrentTime(context
);
334 ** Attempt to parse the given string into a julian day number. Return
335 ** the number of errors.
337 ** The following are acceptable forms for the input string:
339 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM
343 ** In the first form, the +/-HH:MM is always optional. The fractional
344 ** seconds extension (the ".FFF") is optional. The seconds portion
345 ** (":SS.FFF") is option. The year and date can be omitted as long
346 ** as there is a time string. The time string can be omitted as long
347 ** as there is a year and date.
349 static int parseDateOrTime(
350 sqlite3_context
*context
,
355 if( parseYyyyMmDd(zDate
,p
)==0 ){
357 }else if( parseHhMmSs(zDate
, p
)==0 ){
359 }else if( sqlite3StrICmp(zDate
,"now")==0){
360 return setDateTimeToCurrent(context
, p
);
361 }else if( sqlite3AtoF(zDate
, &r
, sqlite3Strlen30(zDate
), SQLITE_UTF8
) ){
362 p
->iJD
= (sqlite3_int64
)(r
*86400000.0 + 0.5);
370 ** Compute the Year, Month, and Day from the julian day number.
372 static void computeYMD(DateTime
*p
){
373 int Z
, A
, B
, C
, D
, E
, X1
;
374 if( p
->validYMD
) return;
380 Z
= (int)((p
->iJD
+ 43200000)/86400000);
381 A
= (int)((Z
- 1867216.25)/36524.25);
382 A
= Z
+ 1 + A
- (A
/4);
384 C
= (int)((B
- 122.1)/365.25);
385 D
= (36525*(C
&32767))/100;
386 E
= (int)((B
-D
)/30.6001);
387 X1
= (int)(30.6001*E
);
389 p
->M
= E
<14 ? E
-1 : E
-13;
390 p
->Y
= p
->M
>2 ? C
- 4716 : C
- 4715;
396 ** Compute the Hour, Minute, and Seconds from the julian day number.
398 static void computeHMS(DateTime
*p
){
400 if( p
->validHMS
) return;
402 s
= (int)((p
->iJD
+ 43200000) % 86400000);
414 ** Compute both YMD and HMS
416 static void computeYMD_HMS(DateTime
*p
){
422 ** Clear the YMD and HMS and the TZ
424 static void clearYMD_HMS_TZ(DateTime
*p
){
430 #ifndef SQLITE_OMIT_LOCALTIME
432 ** On recent Windows platforms, the localtime_s() function is available
433 ** as part of the "Secure CRT". It is essentially equivalent to
434 ** localtime_r() available under most POSIX platforms, except that the
435 ** order of the parameters is reversed.
437 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
439 ** If the user has not indicated to use localtime_r() or localtime_s()
440 ** already, check for an MSVC build environment that provides
443 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S \
444 && defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
445 #undef HAVE_LOCALTIME_S
446 #define HAVE_LOCALTIME_S 1
450 ** The following routine implements the rough equivalent of localtime_r()
451 ** using whatever operating-system specific localtime facility that
452 ** is available. This routine returns 0 on success and
453 ** non-zero on any kind of error.
455 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is true then this
456 ** routine will always fail.
458 ** EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
459 ** library function localtime_r() is used to assist in the calculation of
462 static int osLocaltime(time_t *t
, struct tm
*pTm
){
464 #if !HAVE_LOCALTIME_R && !HAVE_LOCALTIME_S
466 #if SQLITE_THREADSAFE>0
467 sqlite3_mutex
*mutex
= sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER
);
469 sqlite3_mutex_enter(mutex
);
471 #ifndef SQLITE_OMIT_BUILTIN_TEST
472 if( sqlite3GlobalConfig
.bLocaltimeFault
) pX
= 0;
475 sqlite3_mutex_leave(mutex
);
478 #ifndef SQLITE_OMIT_BUILTIN_TEST
479 if( sqlite3GlobalConfig
.bLocaltimeFault
) return 1;
482 rc
= localtime_r(t
, pTm
)==0;
484 rc
= localtime_s(pTm
, t
);
485 #endif /* HAVE_LOCALTIME_R */
486 #endif /* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
489 #endif /* SQLITE_OMIT_LOCALTIME */
492 #ifndef SQLITE_OMIT_LOCALTIME
494 ** Compute the difference (in milliseconds) between localtime and UTC
495 ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs,
496 ** return this value and set *pRc to SQLITE_OK.
498 ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value
499 ** is undefined in this case.
501 static sqlite3_int64
localtimeOffset(
502 DateTime
*p
, /* Date at which to calculate offset */
503 sqlite3_context
*pCtx
, /* Write error here if one occurs */
504 int *pRc
/* OUT: Error code. SQLITE_OK or ERROR */
510 /* Initialize the contents of sLocal to avoid a compiler warning. */
511 memset(&sLocal
, 0, sizeof(sLocal
));
515 if( x
.Y
<1971 || x
.Y
>=2038 ){
516 /* EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
517 ** works for years between 1970 and 2037. For dates outside this range,
518 ** SQLite attempts to map the year into an equivalent year within this
519 ** range, do the calculation, then map the year back.
528 int s
= (int)(x
.s
+ 0.5);
534 t
= (time_t)(x
.iJD
/1000 - 21086676*(i64
)10000);
535 if( osLocaltime(&t
, &sLocal
) ){
536 sqlite3_result_error(pCtx
, "local time unavailable", -1);
540 y
.Y
= sLocal
.tm_year
+ 1900;
541 y
.M
= sLocal
.tm_mon
+ 1;
542 y
.D
= sLocal
.tm_mday
;
543 y
.h
= sLocal
.tm_hour
;
552 return y
.iJD
- x
.iJD
;
554 #endif /* SQLITE_OMIT_LOCALTIME */
557 ** Process a modifier to a date-time stamp. The modifiers are
575 ** Return 0 on success and 1 if there is any kind of error. If the error
576 ** is in a system call (i.e. localtime()), then an error message is written
577 ** to context pCtx. If the error is an unrecognized modifier, no error is
580 static int parseModifier(sqlite3_context
*pCtx
, const char *zMod
, DateTime
*p
){
586 for(n
=0; n
<ArraySize(zBuf
)-1 && zMod
[n
]; n
++){
587 z
[n
] = (char)sqlite3UpperToLower
[(u8
)zMod
[n
]];
591 #ifndef SQLITE_OMIT_LOCALTIME
595 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
598 if( strcmp(z
, "localtime")==0 ){
600 p
->iJD
+= localtimeOffset(p
, pCtx
, &rc
);
610 ** Treat the current value of p->iJD as the number of
611 ** seconds since 1970. Convert to a real julian day number.
613 if( strcmp(z
, "unixepoch")==0 && p
->validJD
){
614 p
->iJD
= (p
->iJD
+ 43200)/86400 + 21086676*(i64
)10000000;
618 #ifndef SQLITE_OMIT_LOCALTIME
619 else if( strcmp(z
, "utc")==0 ){
623 c1
= localtimeOffset(p
, pCtx
, &rc
);
627 p
->iJD
+= c1
- localtimeOffset(p
, pCtx
, &rc
);
641 ** Move the date to the same time on the next occurrence of
642 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the
643 ** date is already on the appropriate weekday, this is a no-op.
645 if( strncmp(z
, "weekday ", 8)==0
646 && sqlite3AtoF(&z
[8], &r
, sqlite3Strlen30(&z
[8]), SQLITE_UTF8
)
647 && (n
=(int)r
)==r
&& n
>=0 && r
<7 ){
653 Z
= ((p
->iJD
+ 129600000)/86400000) % 7;
655 p
->iJD
+= (n
- Z
)*86400000;
665 ** Move the date backwards to the beginning of the current day,
668 if( strncmp(z
, "start of ", 9)!=0 ) break;
676 if( strcmp(z
,"month")==0 ){
679 }else if( strcmp(z
,"year")==0 ){
684 }else if( strcmp(z
,"day")==0 ){
702 for(n
=1; z
[n
] && z
[n
]!=':' && !sqlite3Isspace(z
[n
]); n
++){}
703 if( !sqlite3AtoF(z
, &r
, n
, SQLITE_UTF8
) ){
708 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
709 ** specified number of hours, minutes, seconds, and fractional seconds
710 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be
716 if( !sqlite3Isdigit(*z2
) ) z2
++;
717 memset(&tx
, 0, sizeof(tx
));
718 if( parseHhMmSs(z2
, &tx
) ) break;
721 day
= tx
.iJD
/86400000;
722 tx
.iJD
-= day
*86400000;
723 if( z
[0]=='-' ) tx
.iJD
= -tx
.iJD
;
731 while( sqlite3Isspace(*z
) ) z
++;
732 n
= sqlite3Strlen30(z
);
733 if( n
>10 || n
<3 ) break;
734 if( z
[n
-1]=='s' ){ z
[n
-1] = 0; n
--; }
737 rRounder
= r
<0 ? -0.5 : +0.5;
738 if( n
==3 && strcmp(z
,"day")==0 ){
739 p
->iJD
+= (sqlite3_int64
)(r
*86400000.0 + rRounder
);
740 }else if( n
==4 && strcmp(z
,"hour")==0 ){
741 p
->iJD
+= (sqlite3_int64
)(r
*(86400000.0/24.0) + rRounder
);
742 }else if( n
==6 && strcmp(z
,"minute")==0 ){
743 p
->iJD
+= (sqlite3_int64
)(r
*(86400000.0/(24.0*60.0)) + rRounder
);
744 }else if( n
==6 && strcmp(z
,"second")==0 ){
745 p
->iJD
+= (sqlite3_int64
)(r
*(86400000.0/(24.0*60.0*60.0)) + rRounder
);
746 }else if( n
==5 && strcmp(z
,"month")==0 ){
750 x
= p
->M
>0 ? (p
->M
-1)/12 : (p
->M
-12)/12;
757 p
->iJD
+= (sqlite3_int64
)((r
- y
)*30.0*86400000.0 + rRounder
);
759 }else if( n
==4 && strcmp(z
,"year")==0 ){
766 p
->iJD
+= (sqlite3_int64
)((r
- y
)*365.0*86400000.0 + rRounder
);
782 ** Process time function arguments. argv[0] is a date-time stamp.
783 ** argv[1] and following are modifiers. Parse them all and write
784 ** the resulting time into the DateTime structure p. Return 0
785 ** on success and 1 if there are any errors.
787 ** If there are zero parameters (if even argv[0] is undefined)
788 ** then assume a default value of "now" for argv[0].
791 sqlite3_context
*context
,
793 sqlite3_value
**argv
,
797 const unsigned char *z
;
799 memset(p
, 0, sizeof(*p
));
801 return setDateTimeToCurrent(context
, p
);
803 if( (eType
= sqlite3_value_type(argv
[0]))==SQLITE_FLOAT
804 || eType
==SQLITE_INTEGER
){
805 p
->iJD
= (sqlite3_int64
)(sqlite3_value_double(argv
[0])*86400000.0 + 0.5);
808 z
= sqlite3_value_text(argv
[0]);
809 if( !z
|| parseDateOrTime(context
, (char*)z
, p
) ){
813 for(i
=1; i
<argc
; i
++){
814 z
= sqlite3_value_text(argv
[i
]);
815 if( z
==0 || parseModifier(context
, (char*)z
, p
) ) return 1;
822 ** The following routines implement the various date and time functions
827 ** julianday( TIMESTRING, MOD, MOD, ...)
829 ** Return the julian day number of the date specified in the arguments
831 static void juliandayFunc(
832 sqlite3_context
*context
,
837 if( isDate(context
, argc
, argv
, &x
)==0 ){
839 sqlite3_result_double(context
, x
.iJD
/86400000.0);
844 ** datetime( TIMESTRING, MOD, MOD, ...)
846 ** Return YYYY-MM-DD HH:MM:SS
848 static void datetimeFunc(
849 sqlite3_context
*context
,
854 if( isDate(context
, argc
, argv
, &x
)==0 ){
857 sqlite3_snprintf(sizeof(zBuf
), zBuf
, "%04d-%02d-%02d %02d:%02d:%02d",
858 x
.Y
, x
.M
, x
.D
, x
.h
, x
.m
, (int)(x
.s
));
859 sqlite3_result_text(context
, zBuf
, -1, SQLITE_TRANSIENT
);
864 ** time( TIMESTRING, MOD, MOD, ...)
868 static void timeFunc(
869 sqlite3_context
*context
,
874 if( isDate(context
, argc
, argv
, &x
)==0 ){
877 sqlite3_snprintf(sizeof(zBuf
), zBuf
, "%02d:%02d:%02d", x
.h
, x
.m
, (int)x
.s
);
878 sqlite3_result_text(context
, zBuf
, -1, SQLITE_TRANSIENT
);
883 ** date( TIMESTRING, MOD, MOD, ...)
887 static void dateFunc(
888 sqlite3_context
*context
,
893 if( isDate(context
, argc
, argv
, &x
)==0 ){
896 sqlite3_snprintf(sizeof(zBuf
), zBuf
, "%04d-%02d-%02d", x
.Y
, x
.M
, x
.D
);
897 sqlite3_result_text(context
, zBuf
, -1, SQLITE_TRANSIENT
);
902 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
904 ** Return a string described by FORMAT. Conversions as follows:
907 ** %f ** fractional seconds SS.SSS
909 ** %j day of year 000-366
910 ** %J ** julian day number
913 ** %s seconds since 1970-01-01
915 ** %w day of week 0-6 sunday==0
916 ** %W week of year 00-53
920 static void strftimeFunc(
921 sqlite3_context
*context
,
932 if( argc
==0 ) return;
933 zFmt
= (const char*)sqlite3_value_text(argv
[0]);
934 if( zFmt
==0 || isDate(context
, argc
-1, argv
+1, &x
) ) return;
935 db
= sqlite3_context_db_handle(context
);
936 for(i
=0, n
=1; zFmt
[i
]; i
++, n
++){
964 return; /* ERROR. return a NULL */
969 testcase( n
==sizeof(zBuf
)-1 );
970 testcase( n
==sizeof(zBuf
) );
971 testcase( n
==(u64
)db
->aLimit
[SQLITE_LIMIT_LENGTH
]+1 );
972 testcase( n
==(u64
)db
->aLimit
[SQLITE_LIMIT_LENGTH
] );
973 if( n
<sizeof(zBuf
) ){
975 }else if( n
>(u64
)db
->aLimit
[SQLITE_LIMIT_LENGTH
] ){
976 sqlite3_result_error_toobig(context
);
979 z
= sqlite3DbMallocRawNN(db
, (int)n
);
981 sqlite3_result_error_nomem(context
);
987 for(i
=j
=0; zFmt
[i
]; i
++){
993 case 'd': sqlite3_snprintf(3, &z
[j
],"%02d",x
.D
); j
+=2; break;
996 if( s
>59.999 ) s
= 59.999;
997 sqlite3_snprintf(7, &z
[j
],"%06.3f", s
);
998 j
+= sqlite3Strlen30(&z
[j
]);
1001 case 'H': sqlite3_snprintf(3, &z
[j
],"%02d",x
.h
); j
+=2; break;
1002 case 'W': /* Fall thru */
1004 int nDay
; /* Number of days since 1st day of year */
1010 nDay
= (int)((x
.iJD
-y
.iJD
+43200000)/86400000);
1012 int wd
; /* 0=Monday, 1=Tuesday, ... 6=Sunday */
1013 wd
= (int)(((x
.iJD
+43200000)/86400000)%7);
1014 sqlite3_snprintf(3, &z
[j
],"%02d",(nDay
+7-wd
)/7);
1017 sqlite3_snprintf(4, &z
[j
],"%03d",nDay
+1);
1023 sqlite3_snprintf(20, &z
[j
],"%.16g",x
.iJD
/86400000.0);
1024 j
+=sqlite3Strlen30(&z
[j
]);
1027 case 'm': sqlite3_snprintf(3, &z
[j
],"%02d",x
.M
); j
+=2; break;
1028 case 'M': sqlite3_snprintf(3, &z
[j
],"%02d",x
.m
); j
+=2; break;
1030 sqlite3_snprintf(30,&z
[j
],"%lld",
1031 (i64
)(x
.iJD
/1000 - 21086676*(i64
)10000));
1032 j
+= sqlite3Strlen30(&z
[j
]);
1035 case 'S': sqlite3_snprintf(3,&z
[j
],"%02d",(int)x
.s
); j
+=2; break;
1037 z
[j
++] = (char)(((x
.iJD
+129600000)/86400000) % 7) + '0';
1041 sqlite3_snprintf(5,&z
[j
],"%04d",x
.Y
); j
+=sqlite3Strlen30(&z
[j
]);
1044 default: z
[j
++] = '%'; break;
1049 sqlite3_result_text(context
, z
, -1,
1050 z
==zBuf
? SQLITE_TRANSIENT
: SQLITE_DYNAMIC
);
1056 ** This function returns the same value as time('now').
1058 static void ctimeFunc(
1059 sqlite3_context
*context
,
1061 sqlite3_value
**NotUsed2
1063 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
1064 timeFunc(context
, 0, 0);
1070 ** This function returns the same value as date('now').
1072 static void cdateFunc(
1073 sqlite3_context
*context
,
1075 sqlite3_value
**NotUsed2
1077 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
1078 dateFunc(context
, 0, 0);
1082 ** current_timestamp()
1084 ** This function returns the same value as datetime('now').
1086 static void ctimestampFunc(
1087 sqlite3_context
*context
,
1089 sqlite3_value
**NotUsed2
1091 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
1092 datetimeFunc(context
, 0, 0);
1094 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */
1096 #ifdef SQLITE_OMIT_DATETIME_FUNCS
1098 ** If the library is compiled to omit the full-scale date and time
1099 ** handling (to get a smaller binary), the following minimal version
1100 ** of the functions current_time(), current_date() and current_timestamp()
1101 ** are included instead. This is to support column declarations that
1102 ** include "DEFAULT CURRENT_TIME" etc.
1104 ** This function uses the C-library functions time(), gmtime()
1105 ** and strftime(). The format string to pass to strftime() is supplied
1106 ** as the user-data for the function.
1108 static void currentTimeFunc(
1109 sqlite3_context
*context
,
1111 sqlite3_value
**argv
1114 char *zFormat
= (char *)sqlite3_user_data(context
);
1120 UNUSED_PARAMETER(argc
);
1121 UNUSED_PARAMETER(argv
);
1123 iT
= sqlite3StmtCurrentTime(context
);
1125 t
= iT
/1000 - 10000*(sqlite3_int64
)21086676;
1127 pTm
= gmtime_r(&t
, &sNow
);
1129 sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER
));
1131 if( pTm
) memcpy(&sNow
, pTm
, sizeof(sNow
));
1132 sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER
));
1135 strftime(zBuf
, 20, zFormat
, &sNow
);
1136 sqlite3_result_text(context
, zBuf
, -1, SQLITE_TRANSIENT
);
1142 ** This function registered all of the above C functions as SQL
1143 ** functions. This should be the only routine in this file with
1144 ** external linkage.
1146 void sqlite3RegisterDateTimeFunctions(void){
1147 static FuncDef aDateTimeFuncs
[] = {
1148 #ifndef SQLITE_OMIT_DATETIME_FUNCS
1149 DFUNCTION(julianday
, -1, 0, 0, juliandayFunc
),
1150 DFUNCTION(date
, -1, 0, 0, dateFunc
),
1151 DFUNCTION(time
, -1, 0, 0, timeFunc
),
1152 DFUNCTION(datetime
, -1, 0, 0, datetimeFunc
),
1153 DFUNCTION(strftime
, -1, 0, 0, strftimeFunc
),
1154 DFUNCTION(current_time
, 0, 0, 0, ctimeFunc
),
1155 DFUNCTION(current_timestamp
, 0, 0, 0, ctimestampFunc
),
1156 DFUNCTION(current_date
, 0, 0, 0, cdateFunc
),
1158 STR_FUNCTION(current_time
, 0, "%H:%M:%S", 0, currentTimeFunc
),
1159 STR_FUNCTION(current_date
, 0, "%Y-%m-%d", 0, currentTimeFunc
),
1160 STR_FUNCTION(current_timestamp
, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc
),
1163 sqlite3InsertBuiltinFuncs(aDateTimeFuncs
, ArraySize(aDateTimeFuncs
));