dt.c

关系型数据库 Postgresql 6.5.2

date2j(int y, int m, int d)
{
	int			m12 = (m - 14) / 12;

	return ((1461 * (y + 4800 + m12)) / 4 + (367 * (m - 2 - 12 * (m12))) / 12
			- (3 * ((y + 4900 + m12) / 100)) / 4 + d - 32075);
}	/* date2j() */

void
j2date(int jd, int *year, int *month, int *day)
{
	int			j,
				y,
				m,
				d;

	int			i,
				l,
				n;

	l = jd + 68569;
	n = (4 * l) / 146097;
	l -= (146097 * n + 3) / 4;
	i = (4000 * (l + 1)) / 1461001;
	l += 31 - (1461 * i) / 4;
	j = (80 * l) / 2447;
	d = l - (2447 * j) / 80;
	l = j / 11;
	m = (j + 2) - (12 * l);
	y = 100 * (n - 49) + i + l;

	*year = y;
	*month = m;
	*day = d;
	return;
}	/* j2date() */

static int
j2day(int date)
{
	int			day;

	day = (date + 1) % 7;

	return day;
}	/* j2day() */


/* datetime2tm()
 * Convert datetime data type to POSIX time structure.
 * Note that year is _not_ 1900-based, but is an explicit full value.
 * Also, month is one-based, _not_ zero-based.
 * Returns:
 *	 0 on success
 *	-1 on out of range
 *
 * For dates within the system-supported time_t range, convert to the
 *	local time zone. If out of this range, leave as GMT. - tgl 97/05/27
 */
int
datetime2tm(DateTime dt, int *tzp, struct tm * tm, double *fsec, char **tzn)
{
	double		date,
				date0,
				time,
				sec;
	time_t		utime;

#ifdef USE_POSIX_TIME
	struct tm  *tx;

#endif

	date0 = date2j(2000, 1, 1);

	time = dt;
	TMODULO(time, date, 86400e0);

	if (time < 0)
	{
		time += 86400;
		date -= 1;
	}

	/* Julian day routine does not work for negative Julian days */
	if (date < -date0)
		return -1;

	/* add offset to go from J2000 back to standard Julian date */
	date += date0;

#ifdef DATEDEBUG
	printf("datetime2tm- date is %f (%f %f)\n", dt, date, time);
#endif

	j2date((int) date, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
	dt2time(time, &tm->tm_hour, &tm->tm_min, &sec);

#ifdef DATEDEBUG
	printf("datetime2tm- date is %d.%02d.%02d\n", tm->tm_year, tm->tm_mon, tm->tm_mday);
	printf("datetime2tm- time is %02d:%02d:%02.0f\n", tm->tm_hour, tm->tm_min, sec);
#endif

	*fsec = JROUND(sec);
	TMODULO(*fsec, tm->tm_sec, 1e0);

#ifdef DATEDEBUG
	printf("datetime2tm- time is %02d:%02d:%02d %.7f\n", tm->tm_hour, tm->tm_min, tm->tm_sec, *fsec);
#endif

	if (tzp != NULL)
	{
		if (IS_VALID_UTIME(tm->tm_year, tm->tm_mon, tm->tm_mday))
		{
			utime = (dt + (date0 - date2j(1970, 1, 1)) * 86400);

#ifdef USE_POSIX_TIME
			tx = localtime(&utime);
#ifdef DATEDEBUG
#if defined(HAVE_TM_ZONE)
			printf("datetime2tm- (localtime) %d.%02d.%02d %02d:%02d:%02.0f %s dst=%d\n",
				   tx->tm_year, tx->tm_mon, tx->tm_mday, tx->tm_hour, tx->tm_min, sec,
				   tx->tm_zone, tx->tm_isdst);
#elif defined(HAVE_INT_TIMEZONE)
			printf("datetime2tm- (localtime) %d.%02d.%02d %02d:%02d:%02.0f %s %s dst=%d\n",
				   tx->tm_year, tx->tm_mon, tx->tm_mday, tx->tm_hour, tx->tm_min, sec,
				   tzname[0], tzname[1], tx->tm_isdst);
#else
#error USE_POSIX_TIME is defined but neither HAVE_TM_ZONE or HAVE_INT_TIMEZONE are defined
#endif
#endif
			tm->tm_year = tx->tm_year + 1900;
			tm->tm_mon = tx->tm_mon + 1;
			tm->tm_mday = tx->tm_mday;
			tm->tm_hour = tx->tm_hour;
			tm->tm_min = tx->tm_min;
#if NOT_USED
/* XXX HACK
 * Argh! My Linux box puts in a 1 second offset for dates less than 1970
 *	but only if the seconds field was non-zero. So, don't copy the seconds
 *	field and instead carry forward from the original - tgl 97/06/18
 * Note that GNU/Linux uses the standard freeware zic package as do
 *	many other platforms so this may not be GNU/Linux/ix86-specific.
 */
			tm->tm_sec = tx->tm_sec;
#endif
			tm->tm_isdst = tx->tm_isdst;

#if defined(HAVE_TM_ZONE)
			tm->tm_gmtoff = tx->tm_gmtoff;
			tm->tm_zone = tx->tm_zone;

			*tzp = -(tm->tm_gmtoff);	/* tm_gmtoff is Sun/DEC-ism */
			if (tzn != NULL)
				*tzn = (char *) tm->tm_zone;
#elif defined(HAVE_INT_TIMEZONE)
#ifdef __CYGWIN__
			*tzp = (tm->tm_isdst ? (_timezone - 3600) : _timezone);
#else
			*tzp = (tm->tm_isdst ? (timezone - 3600) : timezone);
#endif
			if (tzn != NULL)
				*tzn = tzname[(tm->tm_isdst > 0)];
#else
#error USE_POSIX_TIME is defined but neither HAVE_TM_ZONE or HAVE_INT_TIMEZONE are defined
#endif

#else							/* !USE_POSIX_TIME */
			*tzp = CTimeZone;	/* V7 conventions; don't know timezone? */
			if (tzn != NULL)
				*tzn = CTZName;
#endif

		}
		else
		{
			*tzp = 0;
			tm->tm_isdst = 0;
			if (tzn != NULL)
				*tzn = NULL;
		}

		dt = dt2local(dt, *tzp);

	}
	else
	{
		tm->tm_isdst = 0;
		if (tzn != NULL)
			*tzn = NULL;
	}

#ifdef DATEDEBUG
	printf("datetime2tm- date is %d.%02d.%02d\n", tm->tm_year, tm->tm_mon, tm->tm_mday);
	printf("datetime2tm- time is %02d:%02d:%02d %.7f\n", tm->tm_hour, tm->tm_min, tm->tm_sec, *fsec);
#endif

#ifdef DATEDEBUG
#ifdef USE_POSIX_TIME
#if defined(HAVE_TM_ZONE)
	printf("datetime2tm- timezone is %s; offset is %d\n",
		   tm->tm_zone, ((tzp != NULL) ? *tzp : 0));
#elif defined(HAVE_INT_TIMEZONE)
	printf("datetime2tm- timezone is %s; offset is %d (%d); daylight is %d\n",
		   tzname[tm->tm_isdst != 0], ((tzp != NULL) ? *tzp : 0), CTimeZone, CDayLight);
#endif
#endif
#endif

	return 0;
}	/* datetime2tm() */


/* tm2datetime()
 * Convert a tm structure to a datetime data type.
 * Note that year is _not_ 1900-based, but is an explicit full value.
 * Also, month is one-based, _not_ zero-based.
 */
int
tm2datetime(struct tm * tm, double fsec, int *tzp, DateTime *result)
{

	double		date,
				time;

	/* Julian day routines are not correct for negative Julian days */
	if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
		return -1;

	date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(2000, 1, 1);
	time = time2t(tm->tm_hour, tm->tm_min, (tm->tm_sec + fsec));
	*result = (date * 86400 + time);
#ifdef DATEDEBUG
	printf("tm2datetime- date is %f (%f %f %d)\n", *result, date, time, (((tm->tm_hour * 60) + tm->tm_min) * 60 + tm->tm_sec));
	printf("tm2datetime- time is %f %02d:%02d:%02d %f\n", time, tm->tm_hour, tm->tm_min, tm->tm_sec, fsec);
#endif
	if (tzp != NULL)
		*result = dt2local(*result, -(*tzp));

	return 0;
}	/* tm2datetime() */


/* timespan2tm()
 * Convert a timespan data type to a tm structure.
 */
static int
timespan2tm(TimeSpan span, struct tm * tm, float8 *fsec)
{
	double		time;

	if (span.month != 0)
	{
		tm->tm_year = span.month / 12;
		tm->tm_mon = span.month % 12;

	}
	else
	{
		tm->tm_year = 0;
		tm->tm_mon = 0;
	}

#ifdef ROUND_ALL
	time = JROUND(span.time);
#else
	time = span.time;
#endif

	TMODULO(time, tm->tm_mday, 86400e0);
	TMODULO(time, tm->tm_hour, 3600e0);
	TMODULO(time, tm->tm_min, 60e0);
	TMODULO(time, tm->tm_sec, 1e0);
	*fsec = time;

#ifdef DATEDEBUG
	printf("timespan2tm- %d %f = %04d-%02d-%02d %02d:%02d:%02d %.2f\n", span.month, span.time,
		   tm->tm_year, tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min, tm->tm_sec, *fsec);
#endif

	return 0;
}	/* timespan2tm() */

static int
tm2timespan(struct tm * tm, double fsec, TimeSpan *span)
{
	span->month = ((tm->tm_year * 12) + tm->tm_mon);
	span->time = ((((((tm->tm_mday * 24) + tm->tm_hour) * 60) + tm->tm_min) * 60) + tm->tm_sec);
	span->time = JROUND(span->time + fsec);

#ifdef DATEDEBUG
	printf("tm2timespan- %d %f = %04d-%02d-%02d %02d:%02d:%02d %.2f\n", span->month, span->time,
		   tm->tm_year, tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min, tm->tm_sec, fsec);
#endif

	return 0;
}	/* tm2timespan() */


static DateTime
dt2local(DateTime dt, int tz)
{
	dt -= tz;
	dt = JROUND(dt);
	return dt;
}	/* dt2local() */

static double
time2t(const int hour, const int min, const double sec)
{
	return (((hour * 60) + min) * 60) + sec;
}	/* time2t() */

static void
dt2time(DateTime jd, int *hour, int *min, double *sec)
{
	double		time;

	time = jd;

	*hour = (time / 3600);
	time -= ((*hour) * 3600);
	*min = (time / 60);
	time -= ((*min) * 60);
	*sec = JROUND(time);

	return;
}	/* dt2time() */


/*
 * parse and convert date in timestr (the normal interface)
 *
 * Returns the number of seconds since epoch (J2000)
 */

/* ParseDateTime()
 * Break string into tokens based on a date/time context.
 */
int
ParseDateTime(char *timestr, char *lowstr,
			  char **field, int *ftype, int maxfields, int *numfields)
{
	int			nf = 0;
	char	   *cp = timestr;
	char	   *lp = lowstr;

#ifdef DATEDEBUG
	printf("ParseDateTime- input string is %s\n", timestr);
#endif
	/* outer loop through fields */
	while (*cp != '\0')
	{
		field[nf] = lp;

		/* leading digit? then date or time */
		if (isdigit(*cp) || (*cp == '.'))
		{
			*lp++ = *cp++;
			while (isdigit(*cp))
				*lp++ = *cp++;
			/* time field? */
			if (*cp == ':')
			{
				ftype[nf] = DTK_TIME;
				while (isdigit(*cp) || (*cp == ':') || (*cp == '.'))
					*lp++ = *cp++;

			}
			/* date field? allow embedded text month */
			else if ((*cp == '-') || (*cp == '/') || (*cp == '.'))
			{
				ftype[nf] = DTK_DATE;
				while (isalnum(*cp) || (*cp == '-') || (*cp == '/') || (*cp == '.'))
					*lp++ = tolower(*cp++);

			}

			/*
			 * otherwise, number only and will determine year, month, or
			 * day later
			 */
			else
				ftype[nf] = DTK_NUMBER;

		}

		/*
		 * text? then date string, month, day of week, special, or
		 * timezone
		 */
		else if (isalpha(*cp))
		{
			ftype[nf] = DTK_STRING;
			*lp++ = tolower(*cp++);
			while (isalpha(*cp))
				*lp++ = tolower(*cp++);

			/* full date string with leading text month? */
			if ((*cp == '-') || (*cp == '/') || (*cp == '.'))
			{
				ftype[nf] = DTK_DATE;
				while (isdigit(*cp) || (*cp == '-') || (*cp == '/') || (*cp == '.'))
					*lp++ = tolower(*cp++);
			}

			/* skip leading spaces */
		}
		else if (isspace(*cp))
		{
			cp++;
			continue;

			/* sign? then special or numeric timezone */
		}
		else if ((*cp == '+') || (*cp == '-'))
		{
			*lp++ = *cp++;
			/* soak up leading whitespace */
			while (isspace(*cp))
				cp++;
			/* numeric timezone? */
			if (isdigit(*cp))
			{
				ftype[nf] = DTK_TZ;
				*lp++ = *cp++;
				while (isdigit(*cp) || (*cp == ':'))
					*lp++ = *cp++;

				/* special? */
			}
			else if (isalpha(*cp))
			{
				ftype[nf] = DTK_SPECIAL;
				*lp++ = tolower(*cp++);
				while (isalpha(*cp))
					*lp++ = tolower(*cp++);

				/* otherwise something wrong... */
			}
			else
				return -1;

			/* ignore punctuation but use as delimiter */
		}
		else if (ispunct(*cp))
		{
			cp++;
			continue;

		}
		else
			return -1;

		/* force in a delimiter */
		*lp++ = '\0';
		nf++;
		if (nf > MAXDATEFIELDS)
			return -1;
#ifdef DATEDEBUG
		printf("ParseDateTime- set field[%d] to %s type %d\n", (nf - 1), field[nf - 1], ftype[nf - 1]);
#endif
	}

	*numfields = nf;

	return 0;
}	/* ParseDateTime() */


/* DecodeDateTime()
 * Interpret previously parsed fields for general date and time.
 * Return 0 if full date, 1 if only time, and -1 if problems.
 *		External format(s):
 *				"<weekday> <month>-<day>-<year> <hour>:<minute>:<second>"
 *				"Fri Feb-7-1997 15:23:27"
 *				"Feb-7-1997 15:23:27"
 *				"2-7-1997 15:23:27"
 *				"1997-2-7 15:23:27"
 *				"1997.038 15:23:27"		(day of year 1-366)
 *		Also supports input in compact time:
 *				"970207 152327"
 *				"97038 152327"
 *
 * Use the system-provided functions to get the current time zone
 *	if not specified in the input string.
 * If the date is outside the time_t system-supported time range,
 *	then assume GMT time zone. - tgl 97/05/27
 */
int
DecodeDateTime(char **field, int *ftype, int nf,
			   int *dtype, struct tm * tm, double *fsec, int *tzp)
{
	int			fmask = 0,
				tmask,
				type;
	int			i;
	int			flen,
				val;
	int			mer = HR24;
	int			haveTextMonth = FALSE;
	int			is2digits = FALSE;
	int			bc = FALSE;

	*dtype = DTK_DATE;
	tm->tm_hour = 0;
	tm->tm_min = 0;
	tm->tm_sec = 0;
	*fsec = 0;
	tm->tm_isdst = -1;			/* don't know daylight savings time status
								 * apriori */
	if (tzp != NULL)
		*tzp = 0;

	for (i = 0; i < nf; i++)
	{
#ifdef DATEDEBUG
		printf("DecodeDateTime- field[%d] is %s (type %d)\n", i, field[i], ftype[i]);
#endif
		switch (ftype[i])
		{
			case DTK_DATE:
				if (DecodeDate(field[i], fmask, &tmask, tm) != 0)
					return -1;
				break;

			case DTK_TIME:
				if (DecodeTime(field[i], fmask, &tmask, tm, fsec) != 0)
					return -1;

				/*
				 * check upper limit on hours; other limits checked in
				 * DecodeTime()
				 */
				if (tm->tm_hour > 23)
					return -1;
				break;

			case DTK_TZ:
				if (tzp == NULL)
					return -1;
				if (DecodeTimezone(field[i], tzp) != 0)
					return -1;
				tmask = DTK_M(TZ);
				break;

			case DTK_NUMBER:
				flen = strlen(field[i]);

				/*
				 * long numeric string and either no date or no time read
				 * yet? then interpret as a concatenated date or time...
				 */
				if ((flen > 4) && !((fmask & DTK_DATE_M) && (fmask & DTK_TIME_M)))
				{
					if (DecodeNumberField(flen, field[i], fmask, &tmask, tm, fsec, &is2digits) != 0)
						return -1;

				}
				/* otherwise it is a single date/time field... */
				else
				{
					if (DecodeNumber(flen, field[i], fmask, &tmask, tm, fsec, &is2digits) != 0)
						return -1;
				}
				break;

			c