dt.c

关系型数据库 Postgresql 6.5.2

		tm->tm_min = (tm1->tm_min - tm2->tm_min);
		tm->tm_hour = (tm1->tm_hour - tm2->tm_hour);
		tm->tm_mday = (tm1->tm_mday - tm2->tm_mday);
		tm->tm_mon = (tm1->tm_mon - tm2->tm_mon);
		tm->tm_year = (tm1->tm_year - tm2->tm_year);

		/* flip sign if necessary... */
		if (dt1 < dt2)
		{
			fsec = -fsec;
			tm->tm_sec = -tm->tm_sec;
			tm->tm_min = -tm->tm_min;
			tm->tm_hour = -tm->tm_hour;
			tm->tm_mday = -tm->tm_mday;
			tm->tm_mon = -tm->tm_mon;
			tm->tm_year = -tm->tm_year;
		}

		if (tm->tm_sec < 0)
		{
			tm->tm_sec += 60;
			tm->tm_min--;
		}

		if (tm->tm_min < 0)
		{
			tm->tm_min += 60;
			tm->tm_hour--;
		}

		if (tm->tm_hour < 0)
		{
			tm->tm_hour += 24;
			tm->tm_mday--;
		}

		if (tm->tm_mday < 0)
		{
			if (dt1 < dt2)
			{
				tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
				tm->tm_mon--;
			}
			else
			{
				tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
				tm->tm_mon--;
			}
		}

		if (tm->tm_mon < 0)
		{
			tm->tm_mon += 12;
			tm->tm_year--;
		}

		/* recover sign if necessary... */
		if (dt1 < dt2)
		{
			fsec = -fsec;
			tm->tm_sec = -tm->tm_sec;
			tm->tm_min = -tm->tm_min;
			tm->tm_hour = -tm->tm_hour;
			tm->tm_mday = -tm->tm_mday;
			tm->tm_mon = -tm->tm_mon;
			tm->tm_year = -tm->tm_year;
		}

		if (tm2timespan(tm, fsec, result) != 0)
			elog(ERROR, "Unable to decode datetime", NULL);

	}
	else
		elog(ERROR, "Unable to decode datetime", NULL);

	return result;
}	/* datetime_age() */


/*----------------------------------------------------------
 *	Conversion operators.
 *---------------------------------------------------------*/


/* datetime_text()
 * Convert datetime to text data type.
 */
text *
datetime_text(DateTime *datetime)
{
	text	   *result;
	char	   *str;
	int			len;

	if (!PointerIsValid(datetime))
		return NULL;

	str = datetime_out(datetime);

	if (!PointerIsValid(str))
		return NULL;

	len = (strlen(str) + VARHDRSZ);

	result = palloc(len);

	VARSIZE(result) = len;
	memmove(VARDATA(result), str, (len - VARHDRSZ));

	pfree(str);

	return result;
}	/* datetime_text() */


/* text_datetime()
 * Convert text string to datetime.
 * Text type is not null terminated, so use temporary string
 *	then call the standard input routine.
 */
DateTime   *
text_datetime(text *str)
{
	DateTime   *result;
	int			i;
	char	   *sp,
			   *dp,
				dstr[MAXDATELEN + 1];

	if (!PointerIsValid(str))
		return NULL;

	sp = VARDATA(str);
	dp = dstr;
	for (i = 0; i < (VARSIZE(str) - VARHDRSZ); i++)
		*dp++ = *sp++;
	*dp = '\0';

	result = datetime_in(dstr);

	return result;
}	/* text_datetime() */


/* timespan_text()
 * Convert timespan to text data type.
 */
text *
timespan_text(TimeSpan *timespan)
{
	text	   *result;
	char	   *str;
	int			len;

	if (!PointerIsValid(timespan))
		return NULL;

	str = timespan_out(timespan);

	if (!PointerIsValid(str))
		return NULL;

	len = (strlen(str) + VARHDRSZ);

	result = palloc(len);

	VARSIZE(result) = len;
	memmove(VARDATA(result), str, (len - VARHDRSZ));

	pfree(str);

	return result;
}	/* timespan_text() */


/* text_timespan()
 * Convert text string to timespan.
 * Text type may not be null terminated, so copy to temporary string
 *	then call the standard input routine.
 */
TimeSpan   *
text_timespan(text *str)
{
	TimeSpan   *result;
	int			i;
	char	   *sp,
			   *dp,
				dstr[MAXDATELEN + 1];

	if (!PointerIsValid(str))
		return NULL;

	sp = VARDATA(str);
	dp = dstr;
	for (i = 0; i < (VARSIZE(str) - VARHDRSZ); i++)
		*dp++ = *sp++;
	*dp = '\0';

	result = timespan_in(dstr);

	return result;
}	/* text_timespan() */

/* datetime_trunc()
 * Extract specified field from datetime.
 */
DateTime   *
datetime_trunc(text *units, DateTime *datetime)
{
	DateTime   *result;

	DateTime	dt;
	int			tz;
	int			type,
				val;
	int			i;
	char	   *up,
			   *lp,
				lowunits[MAXDATELEN + 1];
	double		fsec;
	char	   *tzn;
	struct tm	tt,
			   *tm = &tt;

	if ((!PointerIsValid(units)) || (!PointerIsValid(datetime)))
		return NULL;

	result = palloc(sizeof(DateTime));

	up = VARDATA(units);
	lp = lowunits;
	for (i = 0; i < (VARSIZE(units) - VARHDRSZ); i++)
		*lp++ = tolower(*up++);
	*lp = '\0';

	type = DecodeUnits(0, lowunits, &val);

#ifdef DATEDEBUG
	if (type == IGNORE)
		strcpy(lowunits, "(unknown)");
	printf("datetime_trunc- units %s type=%d value=%d\n", lowunits, type, val);
#endif

	if (DATETIME_NOT_FINITE(*datetime))
	{
#if NOT_USED
/* should return null but Postgres doesn't like that currently. - tgl 97/06/12 */
		elog(ERROR, "Datetime is not finite", NULL);
#endif
		*result = 0;

	}
	else
	{
		dt = (DATETIME_IS_RELATIVE(*datetime) ? SetDateTime(*datetime) : *datetime);

		if ((type == UNITS) && (datetime2tm(dt, &tz, tm, &fsec, &tzn) == 0))
		{
			switch (val)
			{
				case DTK_MILLENIUM:
					tm->tm_year = (tm->tm_year / 1000) * 1000;
				case DTK_CENTURY:
					tm->tm_year = (tm->tm_year / 100) * 100;
				case DTK_DECADE:
					tm->tm_year = (tm->tm_year / 10) * 10;
				case DTK_YEAR:
					tm->tm_mon = 1;
				case DTK_QUARTER:
					tm->tm_mon = (3 * (tm->tm_mon / 4)) + 1;
				case DTK_MONTH:
					tm->tm_mday = 1;
				case DTK_DAY:
					tm->tm_hour = 0;
				case DTK_HOUR:
					tm->tm_min = 0;
				case DTK_MINUTE:
					tm->tm_sec = 0;
				case DTK_SECOND:
					fsec = 0;
					break;

				case DTK_MILLISEC:
					fsec = rint(fsec * 1000) / 1000;
					break;

				case DTK_MICROSEC:
					fsec = rint(fsec * 1000000) / 1000000;
					break;

				default:
					elog(ERROR, "Datetime units '%s' not supported", lowunits);
					result = NULL;
			}

			if (IS_VALID_UTIME(tm->tm_year, tm->tm_mon, tm->tm_mday))
			{
#ifdef USE_POSIX_TIME
				tm->tm_isdst = -1;
				tm->tm_year -= 1900;
				tm->tm_mon -= 1;
				tm->tm_isdst = -1;
				mktime(tm);
				tm->tm_year += 1900;
				tm->tm_mon += 1;

#if defined(HAVE_TM_ZONE)
				tz = -(tm->tm_gmtoff);	/* tm_gmtoff is Sun/DEC-ism */
#elif defined(HAVE_INT_TIMEZONE)

#ifdef __CYGWIN__
				tz = (tm->tm_isdst ? (_timezone - 3600) : _timezone);
#else
				tz = (tm->tm_isdst ? (timezone - 3600) : timezone);
#endif

#else
#error USE_POSIX_TIME is defined but neither HAVE_TM_ZONE or HAVE_INT_TIMEZONE are defined
#endif

#else							/* !USE_POSIX_TIME */
				tz = CTimeZone;
#endif
			}
			else
			{
				tm->tm_isdst = 0;
				tz = 0;
			}

			if (tm2datetime(tm, fsec, &tz, result) != 0)
				elog(ERROR, "Unable to truncate datetime to '%s'", lowunits);

#if NOT_USED
		}
		else if ((type == RESERV) && (val == DTK_EPOCH))
		{
			DATETIME_EPOCH(*result);
			*result = dt - SetDateTime(*result);
#endif

		}
		else
		{
			elog(ERROR, "Datetime units '%s' not recognized", lowunits);
			result = NULL;
		}
	}

	return result;
}	/* datetime_trunc() */

/* timespan_trunc()
 * Extract specified field from timespan.
 */
TimeSpan   *
timespan_trunc(text *units, TimeSpan *timespan)
{
	TimeSpan   *result;

	int			type,
				val;
	int			i;
	char	   *up,
			   *lp,
				lowunits[MAXDATELEN + 1];
	double		fsec;
	struct tm	tt,
			   *tm = &tt;

	if ((!PointerIsValid(units)) || (!PointerIsValid(timespan)))
		return NULL;

	result = palloc(sizeof(TimeSpan));

	up = VARDATA(units);
	lp = lowunits;
	for (i = 0; i < (VARSIZE(units) - VARHDRSZ); i++)
		*lp++ = tolower(*up++);
	*lp = '\0';

	type = DecodeUnits(0, lowunits, &val);

#ifdef DATEDEBUG
	if (type == IGNORE)
		strcpy(lowunits, "(unknown)");
	printf("timespan_trunc- units %s type=%d value=%d\n", lowunits, type, val);
#endif

	if (TIMESPAN_IS_INVALID(*timespan))
	{
#if NOT_USED
		elog(ERROR, "Timespan is not finite", NULL);
#endif
		result = NULL;

	}
	else if (type == UNITS)
	{

		if (timespan2tm(*timespan, tm, &fsec) == 0)
		{
			switch (val)
			{
				case DTK_MILLENIUM:
					tm->tm_year = (tm->tm_year / 1000) * 1000;
				case DTK_CENTURY:
					tm->tm_year = (tm->tm_year / 100) * 100;
				case DTK_DECADE:
					tm->tm_year = (tm->tm_year / 10) * 10;
				case DTK_YEAR:
					tm->tm_mon = 0;
				case DTK_QUARTER:
					tm->tm_mon = (3 * (tm->tm_mon / 4));
				case DTK_MONTH:
					tm->tm_mday = 0;
				case DTK_DAY:
					tm->tm_hour = 0;
				case DTK_HOUR:
					tm->tm_min = 0;
				case DTK_MINUTE:
					tm->tm_sec = 0;
				case DTK_SECOND:
					fsec = 0;
					break;

				case DTK_MILLISEC:
					fsec = rint(fsec * 1000) / 1000;
					break;

				case DTK_MICROSEC:
					fsec = rint(fsec * 1000000) / 1000000;
					break;

				default:
					elog(ERROR, "Timespan units '%s' not supported", lowunits);
					result = NULL;
			}

			if (tm2timespan(tm, fsec, result) != 0)
				elog(ERROR, "Unable to truncate timespan to '%s'", lowunits);

		}
		else
		{
			elog(NOTICE, "Timespan out of range", NULL);
			result = NULL;
		}

#if NOT_USED
	}
	else if ((type == RESERV) && (val == DTK_EPOCH))
	{
		*result = timespan->time;
		if (timespan->month != 0)
		{
			*result += ((365.25 * 86400) * (timespan->month / 12));
			*result += ((30 * 86400) * (timespan->month % 12));
		}
#endif

	}
	else
	{
		elog(ERROR, "Timespan units '%s' not recognized", units);
		result = NULL;
	}

	return result;
}	/* timespan_trunc() */


/* datetime_part()
 * Extract specified field from datetime.
 */
float64
datetime_part(text *units, DateTime *datetime)
{
	float64		result;

	DateTime	dt;
	int			tz;
	int			type,
				val;
	int			i;
	char	   *up,
			   *lp,
				lowunits[MAXDATELEN + 1];
	double		dummy;
	double		fsec;
	char	   *tzn;
	struct tm	tt,
			   *tm = &tt;

	if ((!PointerIsValid(units)) || (!PointerIsValid(datetime)))
		return NULL;

	result = palloc(sizeof(float64data));

	up = VARDATA(units);
	lp = lowunits;
	for (i = 0; i < (VARSIZE(units) - VARHDRSZ); i++)
		*lp++ = tolower(*up++);
	*lp = '\0';

	type = DecodeUnits(0, lowunits, &val);
	if (type == IGNORE)
		type = DecodeSpecial(0, lowunits, &val);

#ifdef DATEDEBUG
	if (type == IGNORE)
		strcpy(lowunits, "(unknown)");
	printf("datetime_part- units %s type=%d value=%d\n", lowunits, type, val);
#endif

	if (DATETIME_NOT_FINITE(*datetime))
	{
#if NOT_USED
/* should return null but Postgres doesn't like that currently. - tgl 97/06/12 */
		elog(ERROR, "Datetime is not finite", NULL);
#endif
		*result = 0;

	}
	else
	{
		dt = (DATETIME_IS_RELATIVE(*datetime) ? SetDateTime(*datetime) : *datetime);

		if ((type == UNITS) && (datetime2tm(dt, &tz, tm, &fsec, &tzn) == 0))
		{
			switch (val)
			{
				case DTK_TZ:
					*result = tz;
					break;

				case DTK_TZ_MINUTE:
					*result = tz / 60;
					TMODULO(*result, dummy, 60e0);
					break;

				case DTK_TZ_HOUR:
					dummy = tz;
					TMODULO(dummy, *result, 3600e0);
					break;

				case DTK_MICROSEC:
					*result = (fsec * 1000000);
					break;

				case DTK_MILLISEC:
					*result = (fsec * 1000);
					break;

				case DTK_SECOND:
					*result = (tm->tm_sec + fsec);
					break;

				case DTK_MINUTE:
					*result = tm->tm_min;
					break;

				case DTK_HOUR:
					*result = tm->tm_hour;
					break;

				case DTK_DAY:
					*result = tm->tm_mday;
					break;

				case DTK_MONTH: