Datum
ora_timestamptz_round(PG_FUNCTION_ARGS)
{
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
TimestampTz result;
text *fmt = PG_GETARG_TEXT_PP(1);
int tz;
fsec_t fsec;
struct pg_tm tt, *tm = &tt;
const char *tzn;
bool redotz = false;
if (TIMESTAMP_NOT_FINITE(timestamp))
PG_RETURN_TIMESTAMPTZ(timestamp);
if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
tm_round(tm, fmt, &redotz);
if (redotz)
tz = DetermineTimeZoneOffset(tm, get_session_timezone(fcinfo));
if (tm2timestamp(tm, fsec, &tz, &result) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
PG_RETURN_TIMESTAMPTZ(result);
}
/* timestamp_abstime()
* Convert timestamp to abstime.
*/
Datum
timestamp_abstime(PG_FUNCTION_ARGS)
{
Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
AbsoluteTime result;
fsec_t fsec;
int tz;
struct pg_tm tt,
*tm = &tt;
if (TIMESTAMP_IS_NOBEGIN(timestamp))
result = NOSTART_ABSTIME;
else if (TIMESTAMP_IS_NOEND(timestamp))
result = NOEND_ABSTIME;
else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
{
tz = DetermineTimeZoneOffset(tm, session_timezone);
result = tm2abstime(tm, tz);
}
else
{
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
result = INVALID_ABSTIME;
}
PG_RETURN_ABSOLUTETIME(result);
}
/* DecodeDateTime()
* Interpret previously parsed fields for general date and time.
* Return 0 if full date, 1 if only time, and negative DTERR code if problems.
* (Currently, all callers treat 1 as an error return too.)
*
* 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"
* "20011225T040506.789-07"
*
* Use the system-provided functions to get the current time zone
* if not specified in the input string.
*
* If the date is outside the range of pg_time_t (in practice that could only
* happen if pg_time_t is just 32 bits), then assume UTC time zone - thomas
* 1997-05-27
*/
int
DecodeDateTime(char **field, int *ftype, int nf, int *dtype, struct tm *tm, fsec_t *fsec, int *tzp)
{
int fmask = 0,
tmask,
type;
int ptype = 0; /* "prefix type" for ISO y2001m02d04 format */
int i;
int val;
int dterr;
int mer = HR24;
bool haveTextMonth = FALSE;
bool isjulian = FALSE;
bool is2digits = FALSE;
bool bc = FALSE;
pg_tz *namedTz = NULL;
struct tm cur_tm;
/*
* We'll insist on at least all of the date fields, but initialize the
* remaining fields in case they are not set later...
*/
*dtype = DTK_DATE;
tm->tm_hour = 0;
tm->tm_min = 0;
tm->tm_sec = 0;
*fsec = 0;
/* don't know daylight savings time status apriori */
tm->tm_isdst = -1;
if (tzp != NULL)
*tzp = 0;
for (i = 0; i < nf; i++)
{
switch (ftype[i])
{
case DTK_DATE:
/***
* Integral julian day with attached time zone?
* All other forms with JD will be separated into
* distinct fields, so we handle just this case here.
***/
if (ptype == DTK_JULIAN)
{
char *cp;
int val;
if (tzp == NULL)
return DTERR_BAD_FORMAT;
errno = 0;
val = strtoi(field[i], &cp, 10);
if (errno == ERANGE || val < 0)
return DTERR_FIELD_OVERFLOW;
j2date(val, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
isjulian = TRUE;
/* Get the time zone from the end of the string */
dterr = DecodeTimezone(cp, tzp);
if (dterr)
return dterr;
tmask = DTK_DATE_M | DTK_TIME_M | DTK_M(TZ);
ptype = 0;
break;
}
/***
* Already have a date? Then this might be a time zone name
* with embedded punctuation (e.g. "America/New_York") or a
* run-together time with trailing time zone (e.g. hhmmss-zz).
* - thomas 2001-12-25
*
* We consider it a time zone if we already have month & day.
* This is to allow the form "mmm dd hhmmss tz year", which
* we've historically accepted.
***/
else if (ptype != 0 ||
((fmask & (DTK_M(MONTH) | DTK_M(DAY))) ==
(DTK_M(MONTH) | DTK_M(DAY))))
{
/* No time zone accepted? Then quit... */
if (tzp == NULL)
return DTERR_BAD_FORMAT;
if (isdigit((unsigned char) *field[i]) || ptype != 0)
{
char *cp;
if (ptype != 0)
{
/* Sanity check; should not fail this test */
if (ptype != DTK_TIME)
return DTERR_BAD_FORMAT;
ptype = 0;
}
/*
* Starts with a digit but we already have a time
* field? Then we are in trouble with a date and time
* already...
*/
if ((fmask & DTK_TIME_M) == DTK_TIME_M)
return DTERR_BAD_FORMAT;
if ((cp = strchr(field[i], '-')) == NULL)
return DTERR_BAD_FORMAT;
/* Get the time zone from the end of the string */
dterr = DecodeTimezone(cp, tzp);
if (dterr)
return dterr;
*cp = '\0';
/*
* Then read the rest of the field as a concatenated
* time
*/
dterr = DecodeNumberField(strlen(field[i]), field[i],
fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr < 0)
return dterr;
/*
* modify tmask after returning from
* DecodeNumberField()
*/
tmask |= DTK_M(TZ);
}
else
{
namedTz = pg_tzset(field[i]);
if (!namedTz)
{
/*
* We should return an error code instead of
* ereport'ing directly, but then there is no way
* to report the bad time zone name.
*/
warnx("time zone \"%s\" not recognized",
field[i]);
}
/* we'll apply the zone setting below */
tmask = DTK_M(TZ);
}
}
else
{
dterr = DecodeDate(field[i], fmask,
&tmask, &is2digits, tm);
if (dterr)
return dterr;
}
break;
case DTK_TIME:
/*
* This might be an ISO time following a "t" field.
*/
if (ptype != 0)
{
/* Sanity check; should not fail this test */
if (ptype != DTK_TIME)
return DTERR_BAD_FORMAT;
ptype = 0;
}
dterr = DecodeTime(field[i], fmask, INTERVAL_FULL_RANGE,
&tmask, tm, fsec);
if (dterr)
return dterr;
/*
* Check upper limit on hours; other limits checked in
* DecodeTime()
*/
/* test for > 24:00:00 */
if (tm->tm_hour > HOURS_PER_DAY ||
(tm->tm_hour == HOURS_PER_DAY &&
(tm->tm_min > 0 || tm->tm_sec > 0 || *fsec > 0)))
return DTERR_FIELD_OVERFLOW;
break;
case DTK_TZ:
{
int tz;
if (tzp == NULL)
return DTERR_BAD_FORMAT;
dterr = DecodeTimezone(field[i], &tz);
if (dterr)
return dterr;
*tzp = tz;
tmask = DTK_M(TZ);
}
break;
case DTK_NUMBER:
/*
* Was this an "ISO date" with embedded field labels? An
* example is "y2001m02d04" - thomas 2001-02-04
*/
if (ptype != 0)
{
char *cp;
int val;
errno = 0;
val = strtoi(field[i], &cp, 10);
if (errno == ERANGE)
return DTERR_FIELD_OVERFLOW;
/*
* only a few kinds are allowed to have an embedded
* decimal
*/
if (*cp == '.')
switch (ptype)
{
case DTK_JULIAN:
case DTK_TIME:
case DTK_SECOND:
break;
default:
return DTERR_BAD_FORMAT;
break;
}
else if (*cp != '\0')
return DTERR_BAD_FORMAT;
switch (ptype)
{
case DTK_YEAR:
tm->tm_year = val;
tmask = DTK_M(YEAR);
break;
case DTK_MONTH:
/*
* already have a month and hour? then assume
* minutes
*/
if ((fmask & DTK_M(MONTH)) != 0 &&
(fmask & DTK_M(HOUR)) != 0)
{
tm->tm_min = val;
tmask = DTK_M(MINUTE);
}
else
{
tm->tm_mon = val;
tmask = DTK_M(MONTH);
}
break;
case DTK_DAY:
tm->tm_mday = val;
tmask = DTK_M(DAY);
break;
case DTK_HOUR:
tm->tm_hour = val;
tmask = DTK_M(HOUR);
break;
case DTK_MINUTE:
tm->tm_min = val;
tmask = DTK_M(MINUTE);
break;
case DTK_SECOND:
tm->tm_sec = val;
tmask = DTK_M(SECOND);
if (*cp == '.')
{
dterr = ParseFractionalSecond(cp, fsec);
if (dterr)
return dterr;
tmask = DTK_ALL_SECS_M;
}
break;
case DTK_TZ:
tmask = DTK_M(TZ);
dterr = DecodeTimezone(field[i], tzp);
if (dterr)
return dterr;
break;
case DTK_JULIAN:
/* previous field was a label for "julian date" */
if (val < 0)
return DTERR_FIELD_OVERFLOW;
tmask = DTK_DATE_M;
j2date(val, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
isjulian = TRUE;
/* fractional Julian Day? */
if (*cp == '.')
{
double time;
errno = 0;
time = strtod(cp, &cp);
if (*cp != '\0' || errno != 0)
return DTERR_BAD_FORMAT;
#ifdef HAVE_INT64_TIMESTAMP
time *= USECS_PER_DAY;
#else
time *= SECS_PER_DAY;
#endif
dt2time(time,
&tm->tm_hour, &tm->tm_min,
&tm->tm_sec, fsec);
tmask |= DTK_TIME_M;
}
break;
case DTK_TIME:
/* previous field was "t" for ISO time */
dterr = DecodeNumberField(strlen(field[i]), field[i],
(fmask | DTK_DATE_M),
&tmask, tm,
fsec, &is2digits);
if (dterr < 0)
return dterr;
if (tmask != DTK_TIME_M)
return DTERR_BAD_FORMAT;
break;
default:
return DTERR_BAD_FORMAT;
break;
}
ptype = 0;
*dtype = DTK_DATE;
}
else
{
char *cp;
int flen;
flen = strlen(field[i]);
cp = strchr(field[i], '.');
/* Embedded decimal and no date yet? */
if (cp != NULL && !(fmask & DTK_DATE_M))
{
dterr = DecodeDate(field[i], fmask,
&tmask, &is2digits, tm);
if (dterr)
return dterr;
}
/* embedded decimal and several digits before? */
else if (cp != NULL && flen - strlen(cp) > 2)
{
/*
* Interpret as a concatenated date or time Set the
* type field to allow decoding other fields later.
* Example: 20011223 or 040506
*/
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr < 0)
return dterr;
}
else if (flen > 4)
{
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr < 0)
return dterr;
}
/* otherwise it is a single date/time field... */
else
{
dterr = DecodeNumber(flen, field[i],
haveTextMonth, fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr)
return dterr;
}
}
break;
case DTK_STRING:
case DTK_SPECIAL:
type = DecodeSpecial(i, field[i], &val);
if (type == IGNORE_DTF)
continue;
tmask = DTK_M(type);
switch (type)
{
case RESERV:
switch (val)
{
case DTK_CURRENT:
warnx("date/time value \"current\" is no longer supported");
return DTERR_BAD_FORMAT;
break;
case DTK_NOW:
tmask = (DTK_DATE_M | DTK_TIME_M | DTK_M(TZ));
*dtype = DTK_DATE;
GetCurrentTimeUsec(tm, fsec, tzp);
break;
case DTK_YESTERDAY:
tmask = DTK_DATE_M;
*dtype = DTK_DATE;
GetCurrentDateTime(&cur_tm);
j2date(date2j(cur_tm.tm_year, cur_tm.tm_mon, cur_tm.tm_mday) - 1,
&tm->tm_year, &tm->tm_mon, &tm->tm_mday);
break;
case DTK_TODAY:
tmask = DTK_DATE_M;
*dtype = DTK_DATE;
GetCurrentDateTime(&cur_tm);
tm->tm_year = cur_tm.tm_year;
tm->tm_mon = cur_tm.tm_mon;
tm->tm_mday = cur_tm.tm_mday;
break;
case DTK_TOMORROW:
tmask = DTK_DATE_M;
*dtype = DTK_DATE;
GetCurrentDateTime(&cur_tm);
j2date(date2j(cur_tm.tm_year, cur_tm.tm_mon, cur_tm.tm_mday) + 1,
&tm->tm_year, &tm->tm_mon, &tm->tm_mday);
break;
case DTK_ZULU:
tmask = (DTK_TIME_M | DTK_M(TZ));
*dtype = DTK_DATE;
tm->tm_hour = 0;
tm->tm_min = 0;
tm->tm_sec = 0;
if (tzp != NULL)
*tzp = 0;
break;
default:
*dtype = val;
}
break;
case MONTH:
/*
* already have a (numeric) month? then see if we can
* substitute...
*/
if ((fmask & DTK_M(MONTH)) && !haveTextMonth &&
!(fmask & DTK_M(DAY)) && tm->tm_mon >= 1 &&
tm->tm_mon <= 31)
{
tm->tm_mday = tm->tm_mon;
tmask = DTK_M(DAY);
}
haveTextMonth = TRUE;
tm->tm_mon = val;
break;
case DTZMOD:
/*
* daylight savings time modifier (solves "MET DST"
* syntax)
*/
tmask |= DTK_M(DTZ);
tm->tm_isdst = 1;
if (tzp == NULL)
return DTERR_BAD_FORMAT;
*tzp += val * MINS_PER_HOUR;
break;
case DTZ:
/*
* set mask for TZ here _or_ check for DTZ later when
* getting default timezone
*/
tmask |= DTK_M(TZ);
tm->tm_isdst = 1;
if (tzp == NULL)
return DTERR_BAD_FORMAT;
*tzp = val * MINS_PER_HOUR;
break;
case TZ:
tm->tm_isdst = 0;
if (tzp == NULL)
return DTERR_BAD_FORMAT;
*tzp = val * MINS_PER_HOUR;
break;
case IGNORE_DTF:
break;
case AMPM:
mer = val;
break;
case ADBC:
bc = (val == BC);
break;
case DOW:
tm->tm_wday = val;
break;
case UNITS:
tmask = 0;
ptype = val;
break;
case ISOTIME:
/*
* This is a filler field "t" indicating that the next
* field is time. Try to verify that this is sensible.
*/
tmask = 0;
/* No preceding date? Then quit... */
if ((fmask & DTK_DATE_M) != DTK_DATE_M)
return DTERR_BAD_FORMAT;
/***
* We will need one of the following fields:
* DTK_NUMBER should be hhmmss.fff
* DTK_TIME should be hh:mm:ss.fff
* DTK_DATE should be hhmmss-zz
***/
if (i >= nf - 1 ||
(ftype[i + 1] != DTK_NUMBER &&
ftype[i + 1] != DTK_TIME &&
ftype[i + 1] != DTK_DATE))
return DTERR_BAD_FORMAT;
ptype = val;
break;
case UNKNOWN_FIELD:
/*
* Before giving up and declaring error, check to see
* if it is an all-alpha timezone name.
*/
namedTz = pg_tzset(field[i]);
if (!namedTz)
return DTERR_BAD_FORMAT;
/* we'll apply the zone setting below */
tmask = DTK_M(TZ);
break;
default:
return DTERR_BAD_FORMAT;
}
break;
default:
return DTERR_BAD_FORMAT;
}
if (tmask & fmask)
return DTERR_BAD_FORMAT;
fmask |= tmask;
} /* end loop over fields */
/* do final checking/adjustment of Y/M/D fields */
dterr = ValidateDate(fmask, isjulian, is2digits, bc, tm);
if (dterr)
return dterr;
/* handle AM/PM */
if (mer != HR24 && tm->tm_hour > HOURS_PER_DAY / 2)
return DTERR_FIELD_OVERFLOW;
if (mer == AM && tm->tm_hour == HOURS_PER_DAY / 2)
tm->tm_hour = 0;
else if (mer == PM && tm->tm_hour != HOURS_PER_DAY / 2)
tm->tm_hour += HOURS_PER_DAY / 2;
/* do additional checking for full date specs... */
if (*dtype == DTK_DATE)
{
if ((fmask & DTK_DATE_M) != DTK_DATE_M)
{
if ((fmask & DTK_TIME_M) == DTK_TIME_M)
return 1;
return DTERR_BAD_FORMAT;
}
/*
* If we had a full timezone spec, compute the offset (we could not do
* it before, because we need the date to resolve DST status).
*/
if (namedTz != NULL)
{
/* daylight savings time modifier disallowed with full TZ */
if (fmask & DTK_M(DTZMOD))
return DTERR_BAD_FORMAT;
*tzp = DetermineTimeZoneOffset(tm, namedTz);
}
/* timezone not specified? then find local timezone if possible */
if (tzp != NULL && !(fmask & DTK_M(TZ)))
{
/*
* daylight savings time modifier but no standard timezone? then
* error
*/
if (fmask & DTK_M(DTZMOD))
return DTERR_BAD_FORMAT;
*tzp = DetermineTimeZoneOffset(tm, session_timezone);
}
}
return 0;
}
/* timestamptz_pl_interval()
* Add a interval to a timestamp with time zone data type.
* Note that interval has provisions for qualitative year/month
* units, so try to do the right thing with them.
* To add a month, increment the month, and use the same day of month.
* Then, if the next month has fewer days, set the day of month
* to the last day of month.
* Lastly, add in the "quantitative time".
*/
TimestampTz
timestamptz_pl_interval(TimestampTz timestamp, Interval *span)
{
TimestampTz result;
int tz;
if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
else
{
if (span->month != 0)
{
struct tm tt,
*tm = &tt;
fsec_t fsec;
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
warnx("timestamp out of range");
tm->tm_mon += span->month;
if (tm->tm_mon > MONTHS_PER_YEAR)
{
tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
}
else if (tm->tm_mon < 1)
{
tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR + MONTHS_PER_YEAR;
}
/* adjust for end of month boundary problems... */
if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
tz = DetermineTimeZoneOffset(tm, session_timezone);
if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
warnx("timestamp out of range");
}
if (span->day != 0)
{
struct tm tt,
*tm = &tt;
fsec_t fsec;
int julian;
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
warnx("timestamp out of range");
/* Add days by converting to and from julian */
julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
tz = DetermineTimeZoneOffset(tm, session_timezone);
if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
warnx("timestamp out of range");
}
timestamp += span->time;
result = timestamp;
}
return result;
}
