sourcemod-central: extensions/sqlite/sqlite-source/date.c@bee90c8ef6c3 (annotated)

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

author	Asher Baker <asherkin@gmail.com>
	Thu Apr 26 16:58:18 2012 +0100 (2012-04-26)
changeset 3488	bee90c8ef6c3
parent 1952	b7aa2b626807
permissions	-rw-r--r--