Hi All,
Sorry for the delay in the new patch, I've attached my most recent
patch to this email. I ended up reworking a good portion of my previous
patch so below I've included some reasons why, notes on my current
approach, and some pro/cons to the approach.
* The main reason for the rework had to do with double conversions and
shared code.
* The existing code for rounding had a lot of int to double
casting and vice versa. I *think* that doubles are able to completely
represent the range of ints. However doubles are not able to represent
the full range of int64. After making the change I started noticing
a lot of lossy behavior. One thought I had was to change the doubles
to long doubles, but I wasn't able to figure out if long doubles could
completely represent the range of int64. Especially since their size
varies depending on the architecture. Does anyone know the answer to
this?
* I ended up creating two intermediate data structures for Intervals.
One for decoding and one for everything else. I'll go into more detail
below.
* One common benefit was that they both contain a usec field which
means that the Interval methods no longer need to carry around a
separate fsec argument.
* The obvious con here is that Intervals require two unique
intermediate data structures, while all other date/time types
can share a single intermediate data structure. I find this to
be a bit clunky.
* pg_itm_in is the struct used for Interval decoding. It's very similar
to pg_tm, except all of the time related fields are collapsed into a
single `int64 usec` field.
* The biggest benefit of this was that all int64-double conversions
are limited to a single function, AdjustFractMicroseconds. Instead
of fractional units flowing down over every single time field, they
only need to flow down into the single `int64 usec` field.
* Overflows are caught much earlier in the decoding process which
helps avoid wasted work.
* I found that the decoding code became simpler for time fields,
though this is a bit subjective.
* pg_itm is the struct used for all other Interval functionality. It's
very similar to pg_tm, except the tm_hour field is converted from int
to int64 and an `int tm_usec` field was added.
* When encoding and working with Intervals, we almost always want
to break the time field out into hours, min, sec, usec. So it's
helpful to have a common place to do this, instead of every
function duplicating this code.
* When breaking the time fields out, a single field will never
contain a value greater than could have fit in the next unit
higher. Meaning that minutes will never be greater than 60, seconds
will be never greater than 60, and usec will never be greater than
1,000. So hours is actually the only field that needs to be int64
and the rest can be an int.
* This also helps limit the impact to shared code (see below).
* There's some shared code between Intervals and other date/time types.
Specifically the DecodeTime function and the datetime_to_char_body
function. These functions take in a `struct pg_tm` and a `fsec_t fsec`
(fsec_t is just an alias for int32) which allows them to be re-used by
all date/time types. The only difference now between pg_tm and pg_itm
is the tm_hour field size (the tm_usec field in pg_itm can be used as
the fsec). So to get around this I changed the function signatures to
take a `struct pg_tm`, `fsec_t fsec`, and an `int64 hour` argument.
It's up to the caller to provide to correct hour field. Intervals can
easily convert pg_itm to a pg_tm, fsec, and hour. It's honestly a bit
error-prone since those functions have to explicitly ignore the
pg_tm->tm_hour field and use the provided hour argument instead, but I
couldn't think of a better less intrusive solution. If anyone has a
better idea, please don't hesitate to bring it up.
* This partly existed in the previous patch, but I just wanted to
restate it. All modifications to pg_itm_in during decoding is done via
helper functions that check for overflow. All invocations of these
functions either return an error on overflow or explicitly state why an
overflow is impossible.
* I completely rewrote the ParseISO8601Number function to try and avoid
double to int64 conversions. I tried to model it after the parsing done
in DecodeInterval, though I would appreciate extra scrutiny here.
- Joe Koshakow
From a2afce720fb65b87638a634078067a796a639ddc Mon Sep 17 00:00:00 2001
From: Joseph Koshakow <kosh...@gmail.com>
Date: Mon, 28 Feb 2022 22:52:55 -0500
Subject: [PATCH] Rework Interval encoding and decoding
The current Interval encoding and decoding has the following issues:
* The decoding functions have many uncaught errors that allow the
Interval value to overflow/underflow.
* Both the decoding and encoding functions do not protect against
taking the absolute value or negating INT_MIN which leads to
undefined behavior (usually it just leaves the value unchanged at
INT_MIN, which is not the desired result).
* The encoding and decoding process arbitrarily limits the range of
Interval values by what can fit into the intermediate data
structures, and not by what can fit into the Interval data
structure.
This commit attempts to solve all of those issues by creating new
Interval specific intermediate data structures and explicitly check for
overflow, underflow, and undefined behavior.
---
src/backend/utils/adt/datetime.c | 602 ++++++++++++++----------
src/backend/utils/adt/formatting.c | 39 +-
src/backend/utils/adt/numutils.c | 12 +
src/backend/utils/adt/timestamp.c | 323 ++++++-------
src/include/datatype/timestamp.h | 9 +-
src/include/pgtime.h | 25 +
src/include/utils/builtins.h | 1 +
src/include/utils/datetime.h | 8 +-
src/include/utils/timestamp.h | 5 +-
src/test/regress/expected/interval.out | 611 +++++++++++++++++++++++++
src/test/regress/sql/interval.sql | 184 ++++++++
11 files changed, 1410 insertions(+), 409 deletions(-)
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 7926258c06..3690fbbc63 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -21,6 +21,7 @@
#include "access/htup_details.h"
#include "access/xact.h"
#include "catalog/pg_type.h"
+#include "common/int.h"
#include "common/string.h"
#include "funcapi.h"
#include "miscadmin.h"
@@ -38,16 +39,20 @@ static int DecodeNumberField(int len, char *str,
int fmask, int *tmask,
struct pg_tm *tm, fsec_t *fsec, bool *is2digits);
static int DecodeTime(char *str, int fmask, int range,
- int *tmask, struct pg_tm *tm, fsec_t *fsec);
+ int *tmask, struct pg_tm *tm, int64 *hour, fsec_t *fsec);
+static int DecodeTimeForInterval(char *str, int fmask, int range,
+ int *tmask, struct pg_itm_in *itm_in);
static const datetkn *datebsearch(const char *key, const datetkn *base, int nel);
static int DecodeDate(char *str, int fmask, int *tmask, bool *is2digits,
struct pg_tm *tm);
-static char *AppendSeconds(char *cp, int sec, fsec_t fsec,
+static char *AppendSeconds(char *cp, int sec, int64 fsec,
int precision, bool fillzeros);
-static void AdjustFractSeconds(double frac, struct pg_tm *tm, fsec_t *fsec,
- int scale);
-static void AdjustFractDays(double frac, struct pg_tm *tm, fsec_t *fsec,
- int scale);
+static bool AdjustFractMicroseconds(long double frac, struct pg_itm_in *itm_in, int64 scale);
+static bool AdjustFractDays(double frac, struct pg_itm_in *pg_itm_in, int scale);
+static bool AdjustFractMonths(double frac, struct pg_itm_in *itm_in, int scale);
+static bool AdjustMicroseconds(int64 val, struct pg_itm_in *itm_in, int64 multiplier, double fval);
+static bool AdjustDays(int val, struct pg_itm_in *itm_in, int multiplier);
+static bool AdjustYears(int val, struct pg_itm_in *itm_in, int multiplier);
static int DetermineTimeZoneOffsetInternal(struct pg_tm *tm, pg_tz *tzp,
pg_time_t *tp);
static bool DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t,
@@ -428,7 +433,7 @@ GetCurrentTimeUsec(struct pg_tm *tm, fsec_t *fsec, int *tzp)
* Note that any sign is stripped from the input seconds values.
*/
static char *
-AppendSeconds(char *cp, int sec, fsec_t fsec, int precision, bool fillzeros)
+AppendSeconds(char *cp, int sec, int64 fsec, int precision, bool fillzeros)
{
Assert(precision >= 0);
@@ -437,10 +442,9 @@ AppendSeconds(char *cp, int sec, fsec_t fsec, int precision, bool fillzeros)
else
cp = pg_ultostr(cp, Abs(sec));
- /* fsec_t is just an int32 */
if (fsec != 0)
{
- int32 value = Abs(fsec);
+ int64 value = Abs(fsec);
char *end = &cp[precision + 1];
bool gotnonzero = false;
@@ -453,8 +457,8 @@ AppendSeconds(char *cp, int sec, fsec_t fsec, int precision, bool fillzeros)
*/
while (precision--)
{
- int32 oldval = value;
- int32 remainder;
+ int64 oldval = value;
+ int64 remainder;
value /= 10;
remainder = oldval - value * 10;
@@ -475,7 +479,7 @@ AppendSeconds(char *cp, int sec, fsec_t fsec, int precision, bool fillzeros)
* which will generate a correct answer in the minimum valid width.
*/
if (value)
- return pg_ultostr(cp, Abs(fsec));
+ return pg_ulltostr(cp, Abs(fsec));
return end;
}
@@ -497,36 +501,96 @@ AppendTimestampSeconds(char *cp, struct pg_tm *tm, fsec_t fsec)
}
/*
- * Multiply frac by scale (to produce seconds) and add to *tm & *fsec.
- * We assume the input frac is less than 1 so overflow is not an issue.
+ * Multiply frac by scale (to produce microseconds) and add to *itm.
+ * We assume the input frac is less than 1 so overflow of frac is not an issue.
*/
-static void
-AdjustFractSeconds(double frac, struct pg_tm *tm, fsec_t *fsec, int scale)
+static bool
+AdjustFractMicroseconds(long double frac, struct pg_itm_in *itm_in, int64 scale)
{
- int sec;
+ int64 usec;
+ int64 round = 0;
if (frac == 0)
- return;
+ return true;
frac *= scale;
- sec = (int) frac;
- tm->tm_sec += sec;
- frac -= sec;
- *fsec += rint(frac * 1000000);
+ usec = (int64) frac;
+ if (pg_add_s64_overflow(itm_in->tm_usec, usec, &itm_in->tm_usec))
+ return false;
+
+ frac = frac - usec;
+ if (frac > 0.5)
+ round = 1;
+ else if (frac < -0.5)
+ round = -1;
+
+ return !pg_add_s64_overflow(itm_in->tm_usec, round, &itm_in->tm_usec);
}
/* As above, but initial scale produces days */
-static void
-AdjustFractDays(double frac, struct pg_tm *tm, fsec_t *fsec, int scale)
+static bool
+AdjustFractDays(double frac, struct pg_itm_in *itm_in, int scale)
{
int extra_days;
if (frac == 0)
- return;
+ return true;
frac *= scale;
extra_days = (int) frac;
- tm->tm_mday += extra_days;
+ if (pg_add_s32_overflow(itm_in->tm_mday, extra_days, &itm_in->tm_mday))
+ return false;
frac -= extra_days;
- AdjustFractSeconds(frac, tm, fsec, SECS_PER_DAY);
+ return AdjustFractMicroseconds(frac, itm_in, USECS_PER_DAY);
+}
+
+/* As above, but initial scale produces months */
+static bool
+AdjustFractMonths(double frac, struct pg_itm_in *itm_in, int scale)
+{
+ int extra_months = rint(frac * MONTHS_PER_YEAR * scale);
+ return !pg_add_s32_overflow(itm_in->tm_mon, extra_months, &itm_in->tm_mon);
+}
+
+/*
+ * Multiply val by multiplier (to produce microseconds) and add to *itm.
+ * Returns true if successful, false if tm overflows.
+ */
+static bool
+AdjustMicroseconds(int64 val, struct pg_itm_in *itm_in, int64 multiplier, double fval)
+{
+ int64 usecs;
+ if (pg_mul_s64_overflow(val, multiplier, &usecs) ||
+ pg_add_s64_overflow(itm_in->tm_usec, usecs, &itm_in->tm_usec))
+ return false;
+
+ return AdjustFractMicroseconds(fval, itm_in, multiplier);
+}
+
+/*
+ * Multiply val by multiplier (to produce days) and add to *itm.
+ * Returns true if successful, false if tm overflows.
+ */
+static bool
+AdjustDays(int val, struct pg_itm_in *itm_in, int multiplier)
+{
+ int extra_days;
+ return !pg_mul_s32_overflow(val, multiplier, &extra_days) &&
+ !pg_add_s32_overflow(itm_in->tm_mday, extra_days, &itm_in->tm_mday);
+}
+
+/* As above, but initial val produces months */
+static bool
+AdjustMonths(int val, struct pg_itm_in *itm_in)
+{
+ return !pg_add_s32_overflow(itm_in->tm_mon, val, &itm_in->tm_mon);
+}
+
+/* As above, but initial val produces years */
+static bool
+AdjustYears(int val, struct pg_itm_in *itm_in, int multiplier)
+{
+ int years;
+ return !pg_mul_s32_overflow(val, multiplier, &years) &&
+ !pg_add_s32_overflow(itm_in->tm_year, years, &itm_in->tm_year);
}
/* Fetch a fractional-second value with suitable error checking */
@@ -965,7 +1029,8 @@ DecodeDateTime(char **field, int *ftype, int nf,
break;
case DTK_TIME:
-
+ {
+ int64 hour;
/*
* This might be an ISO time following a "t" field.
*/
@@ -977,16 +1042,19 @@ DecodeDateTime(char **field, int *ftype, int nf,
ptype = 0;
}
dterr = DecodeTime(field[i], fmask, INTERVAL_FULL_RANGE,
- &tmask, tm, fsec);
+ &tmask, tm, &hour, fsec);
if (dterr)
return dterr;
+ if (hour > INT_MAX || hour < INT_MIN)
+ return DTERR_FIELD_OVERFLOW;
+ tm->tm_hour = (int) hour;
/* check for time overflow */
if (time_overflows(tm->tm_hour, tm->tm_min, tm->tm_sec,
*fsec))
return DTERR_FIELD_OVERFLOW;
break;
-
+ }
case DTK_TZ:
{
int tz;
@@ -1866,13 +1934,18 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
break;
case DTK_TIME:
+ {
+ int64 hour;
dterr = DecodeTime(field[i], (fmask | DTK_DATE_M),
INTERVAL_FULL_RANGE,
- &tmask, tm, fsec);
+ &tmask, tm, &hour, fsec);
if (dterr)
return dterr;
+ if (hour > INT_MAX || hour < INT_MIN)
+ return DTERR_FIELD_OVERFLOW;
+ tm->tm_hour = (int) hour;
break;
-
+ }
case DTK_TZ:
{
int tz;
@@ -2554,10 +2627,13 @@ ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
*
* Only check the lower limit on hours, since this same code can be
* used to represent time spans.
+ *
+ * Different consumers of this function have different requirements on the size
+ * of hours. So we take in an *int64 hour and let the consumer check the result.
*/
static int
DecodeTime(char *str, int fmask, int range,
- int *tmask, struct pg_tm *tm, fsec_t *fsec)
+ int *tmask, struct pg_tm *tm, int64 *hour, fsec_t *fsec)
{
char *cp;
int dterr;
@@ -2565,7 +2641,7 @@ DecodeTime(char *str, int fmask, int range,
*tmask = DTK_TIME_M;
errno = 0;
- tm->tm_hour = strtoint(str, &cp, 10);
+ *hour = strtoi64(str, &cp, 10);
if (errno == ERANGE)
return DTERR_FIELD_OVERFLOW;
if (*cp != ':')
@@ -2581,9 +2657,11 @@ DecodeTime(char *str, int fmask, int range,
/* If it's a MINUTE TO SECOND interval, take 2 fields as being mm:ss */
if (range == (INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND)))
{
+ if (*hour > INT_MAX || *hour < INT_MIN)
+ return DTERR_FIELD_OVERFLOW;
tm->tm_sec = tm->tm_min;
- tm->tm_min = tm->tm_hour;
- tm->tm_hour = 0;
+ tm->tm_min = (int) *hour;
+ *hour = 0;
}
}
else if (*cp == '.')
@@ -2592,9 +2670,11 @@ DecodeTime(char *str, int fmask, int range,
dterr = ParseFractionalSecond(cp, fsec);
if (dterr)
return dterr;
+ if (*hour > INT_MAX || *hour < INT_MIN)
+ return DTERR_FIELD_OVERFLOW;
tm->tm_sec = tm->tm_min;
- tm->tm_min = tm->tm_hour;
- tm->tm_hour = 0;
+ tm->tm_min = (int) *hour;
+ *hour = 0;
}
else if (*cp == ':')
{
@@ -2617,7 +2697,7 @@ DecodeTime(char *str, int fmask, int range,
return DTERR_BAD_FORMAT;
/* do a sanity check */
- if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > MINS_PER_HOUR - 1 ||
+ if (*hour < 0 || tm->tm_min < 0 || tm->tm_min > MINS_PER_HOUR - 1 ||
tm->tm_sec < 0 || tm->tm_sec > SECS_PER_MINUTE ||
*fsec < INT64CONST(0) ||
*fsec > USECS_PER_SEC)
@@ -2626,6 +2706,30 @@ DecodeTime(char *str, int fmask, int range,
return 0;
}
+/* DecodeTimeForInterval()
+ * Decode time string which includes delimiters for Interval decoding.
+ * Return 0 if okay, a DTERR code if not.
+ */
+static int
+DecodeTimeForInterval(char *str, int fmask, int range,
+ int *tmask, struct pg_itm_in *itm_in)
+{
+ int dterr;
+ int64 hour;
+ struct pg_tm tt,
+ *tm = &tt;
+ dterr = DecodeTime(str, fmask, range,
+ tmask, tm, &hour, (fsec_t *)&itm_in->tm_usec);
+ if (dterr)
+ return dterr;
+
+ if (!AdjustMicroseconds(hour, itm_in, USECS_PER_HOUR, 0) ||
+ !AdjustMicroseconds(tm->tm_min, itm_in, USECS_PER_MINUTE, 0) ||
+ !AdjustMicroseconds(tm->tm_sec, itm_in, USECS_PER_SEC, 0))
+ return DTERR_FIELD_OVERFLOW;
+
+ return 0;
+}
/* DecodeNumber()
* Interpret plain numeric field as a date value in context.
@@ -3063,28 +3167,24 @@ DecodeSpecial(int field, char *lowtoken, int *val)
return type;
}
-
-/* ClearPgTm
+/* ClearPgItmIn
*
- * Zero out a pg_tm and associated fsec_t
+ * Zero out a pg_itm_in
*/
static inline void
-ClearPgTm(struct pg_tm *tm, fsec_t *fsec)
+ClearPgItmIn(struct pg_itm_in *itm_in)
{
- tm->tm_year = 0;
- tm->tm_mon = 0;
- tm->tm_mday = 0;
- tm->tm_hour = 0;
- tm->tm_min = 0;
- tm->tm_sec = 0;
- *fsec = 0;
+ itm_in->tm_year = 0;
+ itm_in->tm_mon = 0;
+ itm_in->tm_mday = 0;
+ itm_in->tm_usec = 0;
}
/* DecodeInterval()
* Interpret previously parsed fields for general time interval.
* Returns 0 if successful, DTERR code if bogus input detected.
- * dtype, tm, fsec are output parameters.
+ * dtype and itm_in are output parameters.
*
* Allow "date" field DTK_DATE since this could be just
* an unsigned floating point number. - thomas 1997-11-16
@@ -3094,21 +3194,22 @@ ClearPgTm(struct pg_tm *tm, fsec_t *fsec)
*/
int
DecodeInterval(char **field, int *ftype, int nf, int range,
- int *dtype, struct pg_tm *tm, fsec_t *fsec)
+ int *dtype, struct pg_itm_in *itm_in)
{
bool is_before = false;
char *cp;
int fmask = 0,
tmask,
- type;
+ type,
+ tval;
int i;
int dterr;
- int val;
+ int64 val;
double fval;
*dtype = DTK_DELTA;
type = IGNORE_DTF;
- ClearPgTm(tm, fsec);
+ ClearPgItmIn(itm_in);
/* read through list backwards to pick up units before values */
for (i = nf - 1; i >= 0; i--)
@@ -3116,8 +3217,8 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
switch (ftype[i])
{
case DTK_TIME:
- dterr = DecodeTime(field[i], fmask, range,
- &tmask, tm, fsec);
+ dterr = DecodeTimeForInterval(field[i], fmask, range,
+ &tmask, itm_in);
if (dterr)
return dterr;
type = DTK_DAY;
@@ -3137,16 +3238,15 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
* like DTK_TIME case above, plus handling the sign.
*/
if (strchr(field[i] + 1, ':') != NULL &&
- DecodeTime(field[i] + 1, fmask, range,
- &tmask, tm, fsec) == 0)
+ DecodeTimeForInterval(field[i] + 1, fmask, range,
+ &tmask, itm_in) == 0)
{
if (*field[i] == '-')
{
- /* flip the sign on all fields */
- tm->tm_hour = -tm->tm_hour;
- tm->tm_min = -tm->tm_min;
- tm->tm_sec = -tm->tm_sec;
- *fsec = -(*fsec);
+ /* flip the sign on time field */
+ if (itm_in->tm_usec == PG_INT64_MIN)
+ return DTERR_FIELD_OVERFLOW;
+ itm_in->tm_usec = -itm_in->tm_usec;
}
/*
@@ -3204,7 +3304,7 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
}
errno = 0;
- val = strtoint(field[i], &cp, 10);
+ val = strtoi64(field[i], &cp, 10);
if (errno == ERANGE)
return DTERR_FIELD_OVERFLOW;
@@ -3221,8 +3321,8 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
type = DTK_MONTH;
if (*field[i] == '-')
val2 = -val2;
- if (((double) val * MONTHS_PER_YEAR + val2) > INT_MAX ||
- ((double) val * MONTHS_PER_YEAR + val2) < INT_MIN)
+ if ((val * MONTHS_PER_YEAR + val2) > INT_MAX ||
+ (val * MONTHS_PER_YEAR + val2) < INT_MIN)
return DTERR_FIELD_OVERFLOW;
val = val * MONTHS_PER_YEAR + val2;
fval = 0;
@@ -3247,21 +3347,20 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
switch (type)
{
case DTK_MICROSEC:
- *fsec += rint(val + fval);
+ if (!AdjustMicroseconds(val, itm_in, 1, fval))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(MICROSECOND);
break;
case DTK_MILLISEC:
- /* avoid overflowing the fsec field */
- tm->tm_sec += val / 1000;
- val -= (val / 1000) * 1000;
- *fsec += rint((val + fval) * 1000);
+ if (!AdjustMicroseconds(val, itm_in, USECS_PER_MSEC, fval))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(MILLISECOND);
break;
case DTK_SECOND:
- tm->tm_sec += val;
- *fsec += rint(fval * 1000000);
+ if (!AdjustMicroseconds(val, itm_in, USECS_PER_SEC, fval))
+ return DTERR_FIELD_OVERFLOW;
/*
* If any subseconds were specified, consider this
@@ -3274,57 +3373,71 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
break;
case DTK_MINUTE:
- tm->tm_min += val;
- AdjustFractSeconds(fval, tm, fsec, SECS_PER_MINUTE);
+ if (!AdjustMicroseconds(val, itm_in, USECS_PER_MINUTE, fval))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(MINUTE);
break;
case DTK_HOUR:
- tm->tm_hour += val;
- AdjustFractSeconds(fval, tm, fsec, SECS_PER_HOUR);
+ if (!AdjustMicroseconds(val, itm_in, USECS_PER_HOUR, fval))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(HOUR);
type = DTK_DAY; /* set for next field */
break;
case DTK_DAY:
- tm->tm_mday += val;
- AdjustFractSeconds(fval, tm, fsec, SECS_PER_DAY);
+ if (val < INT_MIN || val > INT_MAX ||
+ !AdjustDays((int) val, itm_in, 1) ||
+ !AdjustFractMicroseconds(fval, itm_in, USECS_PER_DAY))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(DAY);
break;
case DTK_WEEK:
- tm->tm_mday += val * 7;
- AdjustFractDays(fval, tm, fsec, 7);
+ if (val < INT_MIN || val > INT_MAX ||
+ !AdjustDays((int) val, itm_in, 7) ||
+ !AdjustFractDays(fval, itm_in, 7))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(WEEK);
break;
case DTK_MONTH:
- tm->tm_mon += val;
- AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH);
+ if (val < INT_MIN || val > INT_MAX ||
+ !AdjustMonths((int) val, itm_in) ||
+ !AdjustFractDays(fval, itm_in, DAYS_PER_MONTH))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(MONTH);
break;
case DTK_YEAR:
- tm->tm_year += val;
- tm->tm_mon += rint(fval * MONTHS_PER_YEAR);
+ if (val < INT_MIN || val > INT_MAX ||
+ !AdjustYears((int) val, itm_in, 1) ||
+ !AdjustFractMonths(fval, itm_in, 1))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(YEAR);
break;
case DTK_DECADE:
- tm->tm_year += val * 10;
- tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 10);
+ if (val < INT_MIN || val > INT_MAX ||
+ !AdjustYears((int) val, itm_in, YEARS_PER_DECADE) ||
+ !AdjustFractMonths(fval, itm_in, YEARS_PER_DECADE))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(DECADE);
break;
case DTK_CENTURY:
- tm->tm_year += val * 100;
- tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 100);
+ if (val < INT_MIN || val > INT_MAX ||
+ !AdjustYears((int) val, itm_in, YEARS_PER_CENTURY) ||
+ !AdjustFractMonths(fval, itm_in, YEARS_PER_CENTURY))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(CENTURY);
break;
case DTK_MILLENNIUM:
- tm->tm_year += val * 1000;
- tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 1000);
+ if (val < INT_MIN || val > INT_MAX ||
+ !AdjustYears((int) val, itm_in, YEARS_PER_MILLENNIUM) ||
+ !AdjustFractMonths(fval, itm_in, YEARS_PER_MILLENNIUM))
+ return DTERR_FIELD_OVERFLOW;
tmask = DTK_M(MILLENNIUM);
break;
@@ -3335,7 +3448,7 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
case DTK_STRING:
case DTK_SPECIAL:
- type = DecodeUnits(i, field[i], &val);
+ type = DecodeUnits(i, field[i], &tval);
if (type == IGNORE_DTF)
continue;
@@ -3343,17 +3456,17 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
switch (type)
{
case UNITS:
- type = val;
+ type = tval;
break;
case AGO:
is_before = true;
- type = val;
+ type = tval;
break;
case RESERV:
tmask = (DTK_DATE_M | DTK_TIME_M);
- *dtype = val;
+ *dtype = tval;
break;
default:
@@ -3374,16 +3487,6 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
if (fmask == 0)
return DTERR_BAD_FORMAT;
- /* ensure fractional seconds are fractional */
- if (*fsec != 0)
- {
- int sec;
-
- sec = *fsec / USECS_PER_SEC;
- *fsec -= sec * USECS_PER_SEC;
- tm->tm_sec += sec;
- }
-
/*----------
* The SQL standard defines the interval literal
* '-1 1:00:00'
@@ -3420,33 +3523,30 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
* Rather than re-determining which field was field[0], just force
* 'em all negative.
*/
- if (*fsec > 0)
- *fsec = -(*fsec);
- if (tm->tm_sec > 0)
- tm->tm_sec = -tm->tm_sec;
- if (tm->tm_min > 0)
- tm->tm_min = -tm->tm_min;
- if (tm->tm_hour > 0)
- tm->tm_hour = -tm->tm_hour;
- if (tm->tm_mday > 0)
- tm->tm_mday = -tm->tm_mday;
- if (tm->tm_mon > 0)
- tm->tm_mon = -tm->tm_mon;
- if (tm->tm_year > 0)
- tm->tm_year = -tm->tm_year;
+ if (itm_in->tm_usec > 0)
+ itm_in->tm_usec = -itm_in->tm_usec;
+ if (itm_in->tm_mday > 0)
+ itm_in->tm_mday = -itm_in->tm_mday;
+ if (itm_in->tm_mon > 0)
+ itm_in->tm_mon = -itm_in->tm_mon;
+ if (itm_in->tm_year > 0)
+ itm_in->tm_year = -itm_in->tm_year;
}
}
/* finally, AGO negates everything */
if (is_before)
{
- *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 (itm_in->tm_usec == PG_INT64_MIN ||
+ itm_in->tm_mday == INT_MIN ||
+ itm_in->tm_mon == INT_MIN ||
+ itm_in->tm_year == INT_MIN)
+ return DTERR_FIELD_OVERFLOW;
+
+ itm_in->tm_usec = -itm_in->tm_usec;
+ itm_in->tm_mday = -itm_in->tm_mday;
+ itm_in->tm_mon = -itm_in->tm_mon;
+ itm_in->tm_year = -itm_in->tm_year;
}
return 0;
@@ -3460,26 +3560,37 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
* Returns 0 or DTERR code.
*/
static int
-ParseISO8601Number(char *str, char **endptr, int *ipart, double *fpart)
+ParseISO8601Number(char *str, char **endptr, int64 *ipart, double *fpart)
{
- double val;
+ int sign = 1;
+ *ipart = 0;
+ *fpart = 0.0;
if (!(isdigit((unsigned char) *str) || *str == '-' || *str == '.'))
return DTERR_BAD_FORMAT;
errno = 0;
- val = strtod(str, endptr);
+
+ /* Parse sign if there is any */
+ if (*str == '-')
+ {
+ sign = -1;
+ str++;
+ *endptr = str;
+ }
+
+ /* Parse int64 part if there is any */
+ if (isdigit((unsigned char) **endptr))
+ *ipart = strtoi64(*endptr, endptr, 10) * sign;
+
+ /* Parse decimal part if there is any */
+ if (**endptr == '.') {
+ *fpart = strtod(*endptr, endptr) * sign;
+ }
+
/* did we not see anything that looks like a double? */
if (*endptr == str || errno != 0)
return DTERR_BAD_FORMAT;
- /* watch out for overflow */
- if (val < INT_MIN || val > INT_MAX)
- return DTERR_FIELD_OVERFLOW;
- /* be very sure we truncate towards zero (cf dtrunc()) */
- if (val >= 0)
- *ipart = (int) floor(val);
- else
- *ipart = (int) -floor(-val);
- *fpart = val - *ipart;
+
return 0;
}
@@ -3508,21 +3619,20 @@ ISO8601IntegerWidth(char *fieldstart)
* Returns 0 if successful, DTERR code if bogus input detected.
* Note: error code should be DTERR_BAD_FORMAT if input doesn't look like
* ISO8601, otherwise this could cause unexpected error messages.
- * dtype, tm, fsec are output parameters.
+ * dtype and itm_in are output parameters.
*
* A couple exceptions from the spec:
* - a week field ('W') may coexist with other units
* - allows decimals in fields other than the least significant unit.
*/
int
-DecodeISO8601Interval(char *str,
- int *dtype, struct pg_tm *tm, fsec_t *fsec)
+DecodeISO8601Interval(char *str, int *dtype, struct pg_itm_in *itm_in)
{
bool datepart = true;
bool havefield = false;
*dtype = DTK_DELTA;
- ClearPgTm(tm, fsec);
+ ClearPgItmIn(itm_in);
if (strlen(str) < 2 || str[0] != 'P')
return DTERR_BAD_FORMAT;
@@ -3531,7 +3641,7 @@ DecodeISO8601Interval(char *str,
while (*str)
{
char *fieldstart;
- int val;
+ int64 val;
double fval;
char unit;
int dterr;
@@ -3557,32 +3667,50 @@ DecodeISO8601Interval(char *str,
if (datepart)
{
+ /* Date parts cannot be bigger than int */
+ if (val < INT_MIN || val > INT_MAX)
+ return DTERR_FIELD_OVERFLOW;
switch (unit) /* before T: Y M W D */
{
case 'Y':
- tm->tm_year += val;
- tm->tm_mon += rint(fval * MONTHS_PER_YEAR);
+ /*
+ * Not possible to overflow years in this format since
+ * there's no year aliases and can't have fractional
+ * years
+ */
+ (void) AdjustYears((int) val, itm_in, 1);
+ if (!AdjustFractMonths(fval, itm_in, 1))
+ return DTERR_FIELD_OVERFLOW;
break;
case 'M':
- tm->tm_mon += val;
- AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH);
+ if (!AdjustMonths((int) val, itm_in) ||
+ !AdjustFractDays(fval, itm_in, DAYS_PER_MONTH))
+ return DTERR_FIELD_OVERFLOW;
break;
case 'W':
- tm->tm_mday += val * 7;
- AdjustFractDays(fval, tm, fsec, 7);
+ if (!AdjustDays((int) val, itm_in, 7) ||
+ !AdjustFractDays(fval, itm_in, 7))
+ return DTERR_FIELD_OVERFLOW;
break;
case 'D':
- tm->tm_mday += val;
- AdjustFractSeconds(fval, tm, fsec, SECS_PER_DAY);
+ if (!AdjustDays((int) val, itm_in, 1) ||
+ !AdjustFractMicroseconds(fval, itm_in, USECS_PER_DAY))
+ return DTERR_FIELD_OVERFLOW;
break;
case 'T': /* ISO 8601 4.4.3.3 Alternative Format / Basic */
case '\0':
if (ISO8601IntegerWidth(fieldstart) == 8 && !havefield)
{
- tm->tm_year += val / 10000;
- tm->tm_mon += (val / 100) % 100;
- tm->tm_mday += val % 100;
- AdjustFractSeconds(fval, tm, fsec, SECS_PER_DAY);
+ /* None of the date fields can overflow because val is
+ * within the bounds of an int from the check above
+ */
+ (void) AdjustYears((int) val / 10000, itm_in, 1);
+ (void) AdjustMonths((int) (val / 100) % 100, itm_in);
+ (void) AdjustDays((int) val % 100, itm_in, 1);
+ /* Can't overflow because date fields must come before
+ * time fields
+ */
+ (void) AdjustFractMicroseconds(fval, itm_in, USECS_PER_DAY);
if (unit == '\0')
return 0;
datepart = false;
@@ -3596,8 +3724,14 @@ DecodeISO8601Interval(char *str,
if (havefield)
return DTERR_BAD_FORMAT;
- tm->tm_year += val;
- tm->tm_mon += rint(fval * MONTHS_PER_YEAR);
+ /*
+ * Not possible to overflow years in this format since
+ * there's no year aliases and can't have fractional
+ * years
+ */
+ (void) AdjustYears((int) val, itm_in, 1);
+ /* Can't overflow because years must come before months */
+ (void) AdjustFractMonths(fval, itm_in, 1);
if (unit == '\0')
return 0;
if (unit == 'T')
@@ -3610,8 +3744,10 @@ DecodeISO8601Interval(char *str,
dterr = ParseISO8601Number(str, &str, &val, &fval);
if (dterr)
return dterr;
- tm->tm_mon += val;
- AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH);
+ if (!AdjustMonths((int) val, itm_in))
+ return DTERR_FIELD_OVERFLOW;
+ /* Can't overflow because months must come before days */
+ (void) AdjustFractDays(fval, itm_in, DAYS_PER_MONTH);
if (*str == '\0')
return 0;
if (*str == 'T')
@@ -3627,8 +3763,10 @@ DecodeISO8601Interval(char *str,
dterr = ParseISO8601Number(str, &str, &val, &fval);
if (dterr)
return dterr;
- tm->tm_mday += val;
- AdjustFractSeconds(fval, tm, fsec, SECS_PER_DAY);
+ if (!AdjustDays((int) val, itm_in, 1))
+ return DTERR_FIELD_OVERFLOW;
+ /* Can't overflow because days must come before time fields */
+ (void) AdjustFractMicroseconds(fval, itm_in, USECS_PER_DAY);
if (*str == '\0')
return 0;
if (*str == 'T')
@@ -3648,24 +3786,25 @@ DecodeISO8601Interval(char *str,
switch (unit) /* after T: H M S */
{
case 'H':
- tm->tm_hour += val;
- AdjustFractSeconds(fval, tm, fsec, SECS_PER_HOUR);
+ if (!AdjustMicroseconds(val, itm_in, USECS_PER_HOUR, fval))
+ return DTERR_FIELD_OVERFLOW;
break;
case 'M':
- tm->tm_min += val;
- AdjustFractSeconds(fval, tm, fsec, SECS_PER_MINUTE);
+ if (!AdjustMicroseconds(val, itm_in, USECS_PER_MINUTE, fval))
+ return DTERR_FIELD_OVERFLOW;
break;
case 'S':
- tm->tm_sec += val;
- AdjustFractSeconds(fval, tm, fsec, 1);
+ if (!AdjustMicroseconds(val, itm_in, USECS_PER_SEC, fval))
+ return DTERR_FIELD_OVERFLOW;
break;
case '\0': /* ISO 8601 4.4.3.3 Alternative Format */
if (ISO8601IntegerWidth(fieldstart) == 6 && !havefield)
{
- tm->tm_hour += val / 10000;
- tm->tm_min += (val / 100) % 100;
- tm->tm_sec += val % 100;
- AdjustFractSeconds(fval, tm, fsec, 1);
+ if (!AdjustMicroseconds(val / 10000, itm_in, USECS_PER_HOUR, 0) ||
+ !AdjustMicroseconds((val / 100) % 100, itm_in, USECS_PER_MINUTE, 0) ||
+ !AdjustMicroseconds(val % 100, itm_in, USECS_PER_SEC, 0) ||
+ !AdjustFractMicroseconds(fval, itm_in, 1))
+ return DTERR_FIELD_OVERFLOW;
return 0;
}
/* Else fall through to extended alternative format */
@@ -3675,16 +3814,16 @@ DecodeISO8601Interval(char *str,
if (havefield)
return DTERR_BAD_FORMAT;
- tm->tm_hour += val;
- AdjustFractSeconds(fval, tm, fsec, SECS_PER_HOUR);
+ if (!AdjustMicroseconds(val, itm_in, USECS_PER_HOUR, fval))
+ return DTERR_FIELD_OVERFLOW;
if (unit == '\0')
return 0;
dterr = ParseISO8601Number(str, &str, &val, &fval);
if (dterr)
return dterr;
- tm->tm_min += val;
- AdjustFractSeconds(fval, tm, fsec, SECS_PER_MINUTE);
+ if (!AdjustMicroseconds(val, itm_in, USECS_PER_MINUTE, fval))
+ return DTERR_FIELD_OVERFLOW;
if (*str == '\0')
return 0;
if (*str != ':')
@@ -3694,8 +3833,8 @@ DecodeISO8601Interval(char *str,
dterr = ParseISO8601Number(str, &str, &val, &fval);
if (dterr)
return dterr;
- tm->tm_sec += val;
- AdjustFractSeconds(fval, tm, fsec, 1);
+ if (!AdjustMicroseconds(val, itm_in, USECS_PER_SEC, fval))
+ return DTERR_FIELD_OVERFLOW;
if (*str == '\0')
return 0;
return DTERR_BAD_FORMAT;
@@ -4166,25 +4305,25 @@ EncodeDateTime(struct pg_tm *tm, fsec_t fsec, bool print_tz, int tz, const char
/* Append an ISO-8601-style interval field, but only if value isn't zero */
static char *
-AddISO8601IntPart(char *cp, int value, char units)
+AddISO8601IntPart(char *cp, int64 value, char units)
{
if (value == 0)
return cp;
- sprintf(cp, "%d%c", value, units);
+ sprintf(cp, "%lld%c", (long long) value, units);
return cp + strlen(cp);
}
/* Append a postgres-style interval field, but only if value isn't zero */
static char *
-AddPostgresIntPart(char *cp, int value, const char *units,
+AddPostgresIntPart(char *cp, int64 value, const char *units,
bool *is_zero, bool *is_before)
{
if (value == 0)
return cp;
- sprintf(cp, "%s%s%d %s%s",
+ sprintf(cp, "%s%s%lld %s%s",
(!*is_zero) ? " " : "",
(*is_before && value > 0) ? "+" : "",
- value,
+ (long long) value,
units,
(value != 1) ? "s" : "");
@@ -4199,7 +4338,7 @@ AddPostgresIntPart(char *cp, int value, const char *units,
/* Append a verbose-style interval field, but only if value isn't zero */
static char *
-AddVerboseIntPart(char *cp, int value, const char *units,
+AddVerboseIntPart(char *cp, int64 value, const char *units,
bool *is_zero, bool *is_before)
{
if (value == 0)
@@ -4208,11 +4347,11 @@ AddVerboseIntPart(char *cp, int value, const char *units,
if (*is_zero)
{
*is_before = (value < 0);
- value = abs(value);
+ value = Abs(value);
}
else if (*is_before)
value = -value;
- sprintf(cp, " %d %s%s", value, units, (value == 1) ? "" : "s");
+ sprintf(cp, " %lld %s%s", (long long) value, units, (value == 1) ? "" : "s");
*is_zero = false;
return cp + strlen(cp);
}
@@ -4238,15 +4377,16 @@ AddVerboseIntPart(char *cp, int value, const char *units,
* "day-time literal"s (that look like ('4 5:6:7')
*/
void
-EncodeInterval(struct pg_tm *tm, fsec_t fsec, int style, char *str)
+EncodeInterval(struct pg_itm *itm, int style, char *str)
{
char *cp = str;
- int year = tm->tm_year;
- int mon = tm->tm_mon;
- int mday = tm->tm_mday;
- int hour = tm->tm_hour;
- int min = tm->tm_min;
- int sec = tm->tm_sec;
+ int64 year = (int64) itm->tm_year;
+ int64 mon = (int64) itm->tm_mon;
+ int64 mday = (int64) itm->tm_mday;
+ int64 hour = itm->tm_hour;
+ int min = itm->tm_min;
+ int sec = itm->tm_sec;
+ int usec = itm->tm_usec;
bool is_before = false;
bool is_zero = true;
@@ -4263,13 +4403,13 @@ EncodeInterval(struct pg_tm *tm, fsec_t fsec, int style, char *str)
{
bool has_negative = year < 0 || mon < 0 ||
mday < 0 || hour < 0 ||
- min < 0 || sec < 0 || fsec < 0;
+ min < 0 || sec < 0 || usec < 0;
bool has_positive = year > 0 || mon > 0 ||
mday > 0 || hour > 0 ||
- min > 0 || sec > 0 || fsec > 0;
+ min > 0 || sec > 0 || usec > 0;
bool has_year_month = year != 0 || mon != 0;
bool has_day_time = mday != 0 || hour != 0 ||
- min != 0 || sec != 0 || fsec != 0;
+ min != 0 || sec != 0 || usec != 0;
bool has_day = mday != 0;
bool sql_standard_value = !(has_negative && has_positive) &&
!(has_year_month && has_day_time);
@@ -4287,7 +4427,7 @@ EncodeInterval(struct pg_tm *tm, fsec_t fsec, int style, char *str)
hour = -hour;
min = -min;
sec = -sec;
- fsec = -fsec;
+ usec = -usec;
}
if (!has_negative && !has_positive)
@@ -4304,32 +4444,34 @@ EncodeInterval(struct pg_tm *tm, fsec_t fsec, int style, char *str)
char year_sign = (year < 0 || mon < 0) ? '-' : '+';
char day_sign = (mday < 0) ? '-' : '+';
char sec_sign = (hour < 0 || min < 0 ||
- sec < 0 || fsec < 0) ? '-' : '+';
+ sec < 0 || usec < 0) ? '-' : '+';
- sprintf(cp, "%c%d-%d %c%d %c%d:%02d:",
- year_sign, abs(year), abs(mon),
- day_sign, abs(mday),
- sec_sign, abs(hour), abs(min));
+ sprintf(cp, "%c%lld-%lld %c%lld %c%lld:%02d:",
+ year_sign, (long long) Abs(year), (long long) Abs(mon),
+ day_sign, (long long) Abs(mday),
+ sec_sign, (long long) Abs(hour), abs(min));
cp += strlen(cp);
- cp = AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, true);
+ cp = AppendSeconds(cp, sec, usec, MAX_INTERVAL_PRECISION, true);
*cp = '\0';
}
else if (has_year_month)
{
- sprintf(cp, "%d-%d", year, mon);
+ sprintf(cp, "%lld-%lld",
+ (long long) year, (long long) mon);
}
else if (has_day)
{
- sprintf(cp, "%d %d:%02d:", mday, hour, min);
+ sprintf(cp, "%lld %lld:%02d:",
+ (long long) mday, (long long) hour, min);
cp += strlen(cp);
- cp = AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, true);
+ cp = AppendSeconds(cp, sec, usec, MAX_INTERVAL_PRECISION, true);
*cp = '\0';
}
else
{
- sprintf(cp, "%d:%02d:", hour, min);
+ sprintf(cp, "%lld:%02d:", (long long) hour, min);
cp += strlen(cp);
- cp = AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, true);
+ cp = AppendSeconds(cp, sec, usec, MAX_INTERVAL_PRECISION, true);
*cp = '\0';
}
}
@@ -4339,7 +4481,7 @@ EncodeInterval(struct pg_tm *tm, fsec_t fsec, int style, char *str)
case INTSTYLE_ISO_8601:
/* special-case zero to avoid printing nothing */
if (year == 0 && mon == 0 && mday == 0 &&
- hour == 0 && min == 0 && sec == 0 && fsec == 0)
+ hour == 0 && min == 0 && sec == 0 && usec == 0)
{
sprintf(cp, "PT0S");
break;
@@ -4348,15 +4490,15 @@ EncodeInterval(struct pg_tm *tm, fsec_t fsec, int style, char *str)
cp = AddISO8601IntPart(cp, year, 'Y');
cp = AddISO8601IntPart(cp, mon, 'M');
cp = AddISO8601IntPart(cp, mday, 'D');
- if (hour != 0 || min != 0 || sec != 0 || fsec != 0)
+ if (hour != 0 || min != 0 || sec != 0 || usec != 0)
*cp++ = 'T';
cp = AddISO8601IntPart(cp, hour, 'H');
cp = AddISO8601IntPart(cp, min, 'M');
- if (sec != 0 || fsec != 0)
+ if (sec != 0 || usec != 0)
{
- if (sec < 0 || fsec < 0)
+ if (sec < 0 || usec < 0)
*cp++ = '-';
- cp = AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, false);
+ cp = AppendSeconds(cp, sec, usec, MAX_INTERVAL_PRECISION, false);
*cp++ = 'S';
*cp++ = '\0';
}
@@ -4373,16 +4515,16 @@ EncodeInterval(struct pg_tm *tm, fsec_t fsec, int style, char *str)
*/
cp = AddPostgresIntPart(cp, mon, "mon", &is_zero, &is_before);
cp = AddPostgresIntPart(cp, mday, "day", &is_zero, &is_before);
- if (is_zero || hour != 0 || min != 0 || sec != 0 || fsec != 0)
+ if (is_zero || hour != 0 || min != 0 || sec != 0 || usec != 0)
{
- bool minus = (hour < 0 || min < 0 || sec < 0 || fsec < 0);
+ bool minus = (hour < 0 || min < 0 || sec < 0 || usec < 0);
- sprintf(cp, "%s%s%02d:%02d:",
+ sprintf(cp, "%s%s%02lld:%02d:",
is_zero ? "" : " ",
(minus ? "-" : (is_before ? "+" : "")),
- abs(hour), abs(min));
+ (long long) Abs(hour), abs(min));
cp += strlen(cp);
- cp = AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, true);
+ cp = AppendSeconds(cp, sec, usec, MAX_INTERVAL_PRECISION, true);
*cp = '\0';
}
break;
@@ -4397,10 +4539,10 @@ EncodeInterval(struct pg_tm *tm, fsec_t fsec, int style, char *str)
cp = AddVerboseIntPart(cp, mday, "day", &is_zero, &is_before);
cp = AddVerboseIntPart(cp, hour, "hour", &is_zero, &is_before);
cp = AddVerboseIntPart(cp, min, "min", &is_zero, &is_before);
- if (sec != 0 || fsec != 0)
+ if (sec != 0 || usec != 0)
{
*cp++ = ' ';
- if (sec < 0 || (sec == 0 && fsec < 0))
+ if (sec < 0 || (sec == 0 && usec < 0))
{
if (is_zero)
is_before = true;
@@ -4409,10 +4551,10 @@ EncodeInterval(struct pg_tm *tm, fsec_t fsec, int style, char *str)
}
else if (is_before)
*cp++ = '-';
- cp = AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, false);
+ cp = AppendSeconds(cp, sec, usec, MAX_INTERVAL_PRECISION, false);
/* We output "ago", not negatives, so use abs(). */
sprintf(cp, " sec%s",
- (abs(sec) != 1 || fsec != 0) ? "s" : "");
+ (abs(sec) != 1 || usec != 0) ? "s" : "");
is_zero = false;
}
/* identically zero? then put in a unitless zero... */
@@ -4668,7 +4810,7 @@ pg_timezone_abbrevs(PG_FUNCTION_ARGS)
int gmtoffset;
bool is_dst;
unsigned char *p;
- struct pg_tm tm;
+ struct pg_itm_in itm_in;
Interval *resInterval;
/* stuff done only on the first call of the function */
@@ -4762,10 +4904,10 @@ pg_timezone_abbrevs(PG_FUNCTION_ARGS)
values[0] = CStringGetTextDatum(buffer);
/* Convert offset (in seconds) to an interval */
- MemSet(&tm, 0, sizeof(struct pg_tm));
- tm.tm_sec = gmtoffset;
+ MemSet(&itm_in, 0, sizeof(struct pg_itm_in));
+ itm_in.tm_usec = (int64) gmtoffset * USECS_PER_SEC;
resInterval = (Interval *) palloc(sizeof(Interval));
- tm2interval(&tm, 0, resInterval);
+ itmin2interval(&itm_in, resInterval);
values[1] = IntervalPGetDatum(resInterval);
values[2] = BoolGetDatum(is_dst);
@@ -4798,7 +4940,7 @@ pg_timezone_names(PG_FUNCTION_ARGS)
fsec_t fsec;
const char *tzn;
Interval *resInterval;
- struct pg_tm itm;
+ struct pg_itm_in itm_in;
MemoryContext oldcontext;
/* check to see if caller supports us returning a tuplestore */
@@ -4857,10 +4999,10 @@ pg_timezone_names(PG_FUNCTION_ARGS)
values[0] = CStringGetTextDatum(pg_get_timezone_name(tz));
values[1] = CStringGetTextDatum(tzn ? tzn : "");
- MemSet(&itm, 0, sizeof(struct pg_tm));
- itm.tm_sec = -tzoff;
+ MemSet(&itm_in, 0, sizeof(struct pg_itm_in));
+ itm_in.tm_usec = (int64) -tzoff * USECS_PER_SEC;
resInterval = (Interval *) palloc(sizeof(Interval));
- tm2interval(&itm, 0, resInterval);
+ itmin2interval(&itm_in, resInterval);
values[2] = IntervalPGetDatum(resInterval);
values[3] = BoolGetDatum(tm.tm_isdst > 0);
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index d4c2e7b069..71d820f7ed 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -496,6 +496,10 @@ typedef struct
typedef struct TmToChar
{
struct pg_tm tm; /* classic 'tm' struct */
+ /* Different date/time types have different requirements on the size of the
+ * hour field. So we take in a separate int64 hour field.
+ */
+ int64 tm_hour; /* hours */
fsec_t fsec; /* fractional seconds */
const char *tzn; /* timezone */
} TmToChar;
@@ -2649,6 +2653,7 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
FormatNode *n;
char *s;
struct pg_tm *tm = &in->tm;
+ int64 tm_hour = in->tm_hour;
int i;
/* cache localized days and months */
@@ -2668,25 +2673,25 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
{
case DCH_A_M:
case DCH_P_M:
- strcpy(s, (tm->tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
+ strcpy(s, (tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
? P_M_STR : A_M_STR);
s += strlen(s);
break;
case DCH_AM:
case DCH_PM:
- strcpy(s, (tm->tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
+ strcpy(s, (tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
? PM_STR : AM_STR);
s += strlen(s);
break;
case DCH_a_m:
case DCH_p_m:
- strcpy(s, (tm->tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
+ strcpy(s, (tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
? p_m_STR : a_m_STR);
s += strlen(s);
break;
case DCH_am:
case DCH_pm:
- strcpy(s, (tm->tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
+ strcpy(s, (tm_hour % HOURS_PER_DAY >= HOURS_PER_DAY / 2)
? pm_STR : am_STR);
s += strlen(s);
break;
@@ -2697,16 +2702,16 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
* display time as shown on a 12-hour clock, even for
* intervals
*/
- sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : (tm->tm_hour >= 0) ? 2 : 3,
- tm->tm_hour % (HOURS_PER_DAY / 2) == 0 ? HOURS_PER_DAY / 2 :
- tm->tm_hour % (HOURS_PER_DAY / 2));
+ sprintf(s, "%0*lld", S_FM(n->suffix) ? 0 : (tm_hour >= 0) ? 2 : 3,
+ tm_hour % (HOURS_PER_DAY / 2) == 0 ? (long long) HOURS_PER_DAY / 2 :
+ (long long) tm_hour % (HOURS_PER_DAY / 2));
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
break;
case DCH_HH24:
- sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : (tm->tm_hour >= 0) ? 2 : 3,
- tm->tm_hour);
+ sprintf(s, "%0*lld", S_FM(n->suffix) ? 0 : (tm_hour >= 0) ? 2 : 3,
+ (long long) tm_hour);
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
@@ -2754,7 +2759,7 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
break;
#undef DCH_to_char_fsec
case DCH_SSSS:
- sprintf(s, "%d", tm->tm_hour * SECS_PER_HOUR +
+ sprintf(s, "%lld", (long long) tm_hour * SECS_PER_HOUR +
tm->tm_min * SECS_PER_MINUTE +
tm->tm_sec);
if (S_THth(n->suffix))
@@ -4100,6 +4105,7 @@ timestamp_to_char(PG_FUNCTION_ARGS)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ tmtc.tm_hour = (int64) tm->tm_hour;
thisdate = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
tm->tm_wday = (thisdate + 1) % 7;
@@ -4132,6 +4138,7 @@ timestamptz_to_char(PG_FUNCTION_ARGS)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ tmtc.tm_hour = (int64) tm->tm_hour;
thisdate = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
tm->tm_wday = (thisdate + 1) % 7;
@@ -4156,6 +4163,8 @@ interval_to_char(PG_FUNCTION_ARGS)
*res;
TmToChar tmtc;
struct pg_tm *tm;
+ struct pg_itm tt,
+ *itm = &tt;
if (VARSIZE_ANY_EXHDR(fmt) <= 0)
PG_RETURN_NULL();
@@ -4163,8 +4172,16 @@ interval_to_char(PG_FUNCTION_ARGS)
ZERO_tmtc(&tmtc);
tm = tmtcTm(&tmtc);
- if (interval2tm(*it, tm, &tmtcFsec(&tmtc)) != 0)
+ if (interval2itm(*it, itm))
PG_RETURN_NULL();
+ tmtc.fsec = itm->tm_usec;
+ tmtc.tm_hour = itm->tm_hour;
+ tm->tm_sec = itm->tm_sec;
+ tm->tm_min = itm->tm_min;
+ tm->tm_mday = itm->tm_mday;
+ tm->tm_mon = itm->tm_mon;
+ tm->tm_year = itm->tm_year;
+ tm->tm_yday = itm->tm_yday;
/* wday is meaningless, yday approximates the total span in days */
tm->tm_yday = (tm->tm_year * MONTHS_PER_YEAR + tm->tm_mon) * DAYS_PER_MONTH + tm->tm_mday;
diff --git a/src/backend/utils/adt/numutils.c b/src/backend/utils/adt/numutils.c
index cc3f95d399..8bdcdee328 100644
--- a/src/backend/utils/adt/numutils.c
+++ b/src/backend/utils/adt/numutils.c
@@ -602,3 +602,15 @@ pg_ultostr(char *str, uint32 value)
return str + len;
}
+
+/*
+ * pg_ulltostr
+ * See above
+ */
+char *
+pg_ulltostr(char *str, uint64 value)
+{
+ int len = pg_ulltoa_n(value, str);
+
+ return str + len;
+}
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index ae36ff3328..77cc730b9d 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -888,9 +888,8 @@ interval_in(PG_FUNCTION_ARGS)
#endif
int32 typmod = PG_GETARG_INT32(2);
Interval *result;
- fsec_t fsec;
- struct pg_tm tt,
- *tm = &tt;
+ struct pg_itm_in tt,
+ *itm_in = &tt;
int dtype;
int nf;
int range;
@@ -899,13 +898,10 @@ interval_in(PG_FUNCTION_ARGS)
int ftype[MAXDATEFIELDS];
char workbuf[256];
- tm->tm_year = 0;
- tm->tm_mon = 0;
- tm->tm_mday = 0;
- tm->tm_hour = 0;
- tm->tm_min = 0;
- tm->tm_sec = 0;
- fsec = 0;
+ itm_in->tm_year = 0;
+ itm_in->tm_mon = 0;
+ itm_in->tm_mday = 0;
+ itm_in->tm_usec = 0;
if (typmod >= 0)
range = INTERVAL_RANGE(typmod);
@@ -916,12 +912,12 @@ interval_in(PG_FUNCTION_ARGS)
ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
dterr = DecodeInterval(field, ftype, nf, range,
- &dtype, tm, &fsec);
+ &dtype, itm_in);
/* if those functions think it's a bad format, try ISO8601 style */
if (dterr == DTERR_BAD_FORMAT)
dterr = DecodeISO8601Interval(str,
- &dtype, tm, &fsec);
+ &dtype, itm_in);
if (dterr != 0)
{
@@ -935,7 +931,7 @@ interval_in(PG_FUNCTION_ARGS)
switch (dtype)
{
case DTK_DELTA:
- if (tm2interval(tm, fsec, result) != 0)
+ if (itmin2interval(itm_in, result) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("interval out of range")));
@@ -959,15 +955,14 @@ interval_out(PG_FUNCTION_ARGS)
{
Interval *span = PG_GETARG_INTERVAL_P(0);
char *result;
- struct pg_tm tt,
- *tm = &tt;
- fsec_t fsec;
+ struct pg_itm tt,
+ *itm = &tt;
char buf[MAXDATELEN + 1];
- if (interval2tm(*span, tm, &fsec) != 0)
+ if (interval2itm(*span, itm) != 0)
elog(ERROR, "could not convert interval to tm");
- EncodeInterval(tm, fsec, IntervalStyle, buf);
+ EncodeInterval(itm, IntervalStyle, buf);
result = pstrdup(buf);
PG_RETURN_CSTRING(result);
@@ -1963,45 +1958,59 @@ tm2timestamp(struct pg_tm *tm, fsec_t fsec, int *tzp, Timestamp *result)
* Convert an interval data type to a tm structure.
*/
int
-interval2tm(Interval span, struct pg_tm *tm, fsec_t *fsec)
+interval2itm(Interval span, struct pg_itm *itm)
{
TimeOffset time;
TimeOffset tfrac;
- tm->tm_year = span.month / MONTHS_PER_YEAR;
- tm->tm_mon = span.month % MONTHS_PER_YEAR;
- tm->tm_mday = span.day;
+ itm->tm_year = span.month / MONTHS_PER_YEAR;
+ itm->tm_mon = span.month % MONTHS_PER_YEAR;
+ itm->tm_mday = span.day;
time = span.time;
tfrac = time / USECS_PER_HOUR;
time -= tfrac * USECS_PER_HOUR;
- tm->tm_hour = tfrac;
- if (!SAMESIGN(tm->tm_hour, tfrac))
+ itm->tm_hour = tfrac;
+ if (!SAMESIGN(itm->tm_hour, tfrac))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("interval out of range")));
tfrac = time / USECS_PER_MINUTE;
time -= tfrac * USECS_PER_MINUTE;
- tm->tm_min = tfrac;
+ itm->tm_min = tfrac;
tfrac = time / USECS_PER_SEC;
- *fsec = time - (tfrac * USECS_PER_SEC);
- tm->tm_sec = tfrac;
+ itm->tm_usec = time - (tfrac * USECS_PER_SEC);
+ itm->tm_sec = tfrac;
+
+ return 0;
+}
+
+int
+itm2interval(struct pg_itm *itm, Interval *span)
+{
+ int64 total_months = (int64) itm->tm_year * MONTHS_PER_YEAR + itm->tm_mon;
+
+ if (total_months > INT_MAX || total_months < INT_MIN)
+ return -1;
+ span->month = (int) total_months;
+ span->day = itm->tm_mday;
+ span->time = (((((itm->tm_hour * INT64CONST(60)) +
+ itm->tm_min) * INT64CONST(60)) +
+ itm->tm_sec) * USECS_PER_SEC) + itm->tm_usec;
return 0;
}
int
-tm2interval(struct pg_tm *tm, fsec_t fsec, Interval *span)
+itmin2interval(struct pg_itm_in *itm_in, Interval *span)
{
- double total_months = (double) tm->tm_year * MONTHS_PER_YEAR + tm->tm_mon;
+ double total_months = (double) itm_in->tm_year * MONTHS_PER_YEAR + itm_in->tm_mon;
if (total_months > INT_MAX || total_months < INT_MIN)
return -1;
span->month = total_months;
- span->day = tm->tm_mday;
- span->time = (((((tm->tm_hour * INT64CONST(60)) +
- tm->tm_min) * INT64CONST(60)) +
- tm->tm_sec) * USECS_PER_SEC) + fsec;
+ span->day = itm_in->tm_mday;
+ span->time = itm_in->tm_usec;
return 0;
}
@@ -3601,11 +3610,10 @@ timestamp_age(PG_FUNCTION_ARGS)
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
Interval *result;
- fsec_t fsec,
- fsec1,
+ fsec_t fsec1,
fsec2;
- struct pg_tm tt,
- *tm = &tt;
+ struct pg_itm tt,
+ *itm = &tt;
struct pg_tm tt1,
*tm1 = &tt1;
struct pg_tm tt2,
@@ -3617,84 +3625,84 @@ timestamp_age(PG_FUNCTION_ARGS)
timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
{
/* form the symbolic difference */
- fsec = fsec1 - fsec2;
- tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
- 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;
+ itm->tm_usec = fsec1 - fsec2;
+ itm->tm_sec = tm1->tm_sec - tm2->tm_sec;
+ itm->tm_min = tm1->tm_min - tm2->tm_min;
+ itm->tm_hour = tm1->tm_hour - tm2->tm_hour;
+ itm->tm_mday = tm1->tm_mday - tm2->tm_mday;
+ itm->tm_mon = tm1->tm_mon - tm2->tm_mon;
+ itm->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;
+ itm->tm_usec = -itm->tm_usec;
+ itm->tm_sec = -itm->tm_sec;
+ itm->tm_min = -itm->tm_min;
+ itm->tm_hour = -itm->tm_hour;
+ itm->tm_mday = -itm->tm_mday;
+ itm->tm_mon = -itm->tm_mon;
+ itm->tm_year = -itm->tm_year;
}
/* propagate any negative fields into the next higher field */
- while (fsec < 0)
+ while (itm->tm_usec < 0)
{
- fsec += USECS_PER_SEC;
- tm->tm_sec--;
+ itm->tm_usec += USECS_PER_SEC;
+ itm->tm_sec--;
}
- while (tm->tm_sec < 0)
+ while (itm->tm_sec < 0)
{
- tm->tm_sec += SECS_PER_MINUTE;
- tm->tm_min--;
+ itm->tm_sec += SECS_PER_MINUTE;
+ itm->tm_min--;
}
- while (tm->tm_min < 0)
+ while (itm->tm_min < 0)
{
- tm->tm_min += MINS_PER_HOUR;
- tm->tm_hour--;
+ itm->tm_min += MINS_PER_HOUR;
+ itm->tm_hour--;
}
- while (tm->tm_hour < 0)
+ while (itm->tm_hour < 0)
{
- tm->tm_hour += HOURS_PER_DAY;
- tm->tm_mday--;
+ itm->tm_hour += HOURS_PER_DAY;
+ itm->tm_mday--;
}
- while (tm->tm_mday < 0)
+ while (itm->tm_mday < 0)
{
if (dt1 < dt2)
{
- tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
- tm->tm_mon--;
+ itm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
+ itm->tm_mon--;
}
else
{
- tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
- tm->tm_mon--;
+ itm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
+ itm->tm_mon--;
}
}
- while (tm->tm_mon < 0)
+ while (itm->tm_mon < 0)
{
- tm->tm_mon += MONTHS_PER_YEAR;
- tm->tm_year--;
+ itm->tm_mon += MONTHS_PER_YEAR;
+ itm->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;
+ itm->tm_usec = -itm->tm_usec;
+ itm->tm_sec = -itm->tm_sec;
+ itm->tm_min = -itm->tm_min;
+ itm->tm_hour = -itm->tm_hour;
+ itm->tm_mday = -itm->tm_mday;
+ itm->tm_mon = -itm->tm_mon;
+ itm->tm_year = -itm->tm_year;
}
- if (tm2interval(tm, fsec, result) != 0)
+ if (itm2interval(itm, result) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("interval out of range")));
@@ -3720,11 +3728,10 @@ timestamptz_age(PG_FUNCTION_ARGS)
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
Interval *result;
- fsec_t fsec,
- fsec1,
+ fsec_t fsec1,
fsec2;
- struct pg_tm tt,
- *tm = &tt;
+ struct pg_itm tt,
+ *itm = &tt;
struct pg_tm tt1,
*tm1 = &tt1;
struct pg_tm tt2,
@@ -3738,69 +3745,69 @@ timestamptz_age(PG_FUNCTION_ARGS)
timestamp2tm(dt2, &tz2, tm2, &fsec2, NULL, NULL) == 0)
{
/* form the symbolic difference */
- fsec = fsec1 - fsec2;
- tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
- 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;
+ itm->tm_usec = fsec1 - fsec2;
+ itm->tm_sec = tm1->tm_sec - tm2->tm_sec;
+ itm->tm_min = tm1->tm_min - tm2->tm_min;
+ itm->tm_hour = tm1->tm_hour - tm2->tm_hour;
+ itm->tm_mday = tm1->tm_mday - tm2->tm_mday;
+ itm->tm_mon = tm1->tm_mon - tm2->tm_mon;
+ itm->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;
+ itm->tm_usec = -itm->tm_usec;
+ itm->tm_sec = -itm->tm_sec;
+ itm->tm_min = -itm->tm_min;
+ itm->tm_hour = -itm->tm_hour;
+ itm->tm_mday = -itm->tm_mday;
+ itm->tm_mon = -itm->tm_mon;
+ itm->tm_year = -itm->tm_year;
}
/* propagate any negative fields into the next higher field */
- while (fsec < 0)
+ while (itm->tm_usec < 0)
{
- fsec += USECS_PER_SEC;
- tm->tm_sec--;
+ itm->tm_usec += USECS_PER_SEC;
+ itm->tm_sec--;
}
- while (tm->tm_sec < 0)
+ while (itm->tm_sec < 0)
{
- tm->tm_sec += SECS_PER_MINUTE;
- tm->tm_min--;
+ itm->tm_sec += SECS_PER_MINUTE;
+ itm->tm_min--;
}
- while (tm->tm_min < 0)
+ while (itm->tm_min < 0)
{
- tm->tm_min += MINS_PER_HOUR;
- tm->tm_hour--;
+ itm->tm_min += MINS_PER_HOUR;
+ itm->tm_hour--;
}
- while (tm->tm_hour < 0)
+ while (itm->tm_hour < 0)
{
- tm->tm_hour += HOURS_PER_DAY;
- tm->tm_mday--;
+ itm->tm_hour += HOURS_PER_DAY;
+ itm->tm_mday--;
}
- while (tm->tm_mday < 0)
+ while (itm->tm_mday < 0)
{
if (dt1 < dt2)
{
- tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
- tm->tm_mon--;
+ itm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
+ itm->tm_mon--;
}
else
{
- tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
- tm->tm_mon--;
+ itm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
+ itm->tm_mon--;
}
}
- while (tm->tm_mon < 0)
+ while (itm->tm_mon < 0)
{
- tm->tm_mon += MONTHS_PER_YEAR;
- tm->tm_year--;
+ itm->tm_mon += MONTHS_PER_YEAR;
+ itm->tm_year--;
}
/*
@@ -3810,16 +3817,16 @@ timestamptz_age(PG_FUNCTION_ARGS)
/* 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;
+ itm->tm_usec = -itm->tm_usec;
+ itm->tm_sec = -itm->tm_sec;
+ itm->tm_min = -itm->tm_min;
+ itm->tm_hour = -itm->tm_hour;
+ itm->tm_mday = -itm->tm_mday;
+ itm->tm_mon = -itm->tm_mon;
+ itm->tm_year = -itm->tm_year;
}
- if (tm2interval(tm, fsec, result) != 0)
+ if (itm2interval(itm, result) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("interval out of range")));
@@ -4306,9 +4313,8 @@ interval_trunc(PG_FUNCTION_ARGS)
int type,
val;
char *lowunits;
- fsec_t fsec;
- struct pg_tm tt,
- *tm = &tt;
+ struct pg_itm tt,
+ *itm = &tt;
result = (Interval *) palloc(sizeof(Interval));
@@ -4320,45 +4326,45 @@ interval_trunc(PG_FUNCTION_ARGS)
if (type == UNITS)
{
- if (interval2tm(*interval, tm, &fsec) == 0)
+ if (interval2itm(*interval, itm) == 0)
{
switch (val)
{
case DTK_MILLENNIUM:
/* caution: C division may have negative remainder */
- tm->tm_year = (tm->tm_year / 1000) * 1000;
+ itm->tm_year = (itm->tm_year / 1000) * 1000;
/* FALL THRU */
case DTK_CENTURY:
/* caution: C division may have negative remainder */
- tm->tm_year = (tm->tm_year / 100) * 100;
+ itm->tm_year = (itm->tm_year / 100) * 100;
/* FALL THRU */
case DTK_DECADE:
/* caution: C division may have negative remainder */
- tm->tm_year = (tm->tm_year / 10) * 10;
+ itm->tm_year = (itm->tm_year / 10) * 10;
/* FALL THRU */
case DTK_YEAR:
- tm->tm_mon = 0;
+ itm->tm_mon = 0;
/* FALL THRU */
case DTK_QUARTER:
- tm->tm_mon = 3 * (tm->tm_mon / 3);
+ itm->tm_mon = 3 * (itm->tm_mon / 3);
/* FALL THRU */
case DTK_MONTH:
- tm->tm_mday = 0;
+ itm->tm_mday = 0;
/* FALL THRU */
case DTK_DAY:
- tm->tm_hour = 0;
+ itm->tm_hour = 0;
/* FALL THRU */
case DTK_HOUR:
- tm->tm_min = 0;
+ itm->tm_min = 0;
/* FALL THRU */
case DTK_MINUTE:
- tm->tm_sec = 0;
+ itm->tm_sec = 0;
/* FALL THRU */
case DTK_SECOND:
- fsec = 0;
+ itm->tm_usec = 0;
break;
case DTK_MILLISEC:
- fsec = (fsec / 1000) * 1000;
+ itm->tm_usec = (itm->tm_usec / 1000) * 1000;
break;
case DTK_MICROSEC:
break;
@@ -4371,7 +4377,7 @@ interval_trunc(PG_FUNCTION_ARGS)
(val == DTK_WEEK) ? errdetail("Months usually have fractional weeks.") : 0));
}
- if (tm2interval(tm, fsec, result) != 0)
+ if (itm2interval(itm, result) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("interval out of range")));
@@ -5189,9 +5195,8 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
int type,
val;
char *lowunits;
- fsec_t fsec;
- struct pg_tm tt,
- *tm = &tt;
+ struct pg_itm tt,
+ *itm = &tt;
lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
VARSIZE_ANY_EXHDR(units),
@@ -5203,12 +5208,12 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
if (type == UNITS)
{
- if (interval2tm(*interval, tm, &fsec) == 0)
+ if (interval2itm(*interval, itm) == 0)
{
switch (val)
{
case DTK_MICROSEC:
- intresult = tm->tm_sec * INT64CONST(1000000) + fsec;
+ intresult = itm->tm_sec * INT64CONST(1000000) + itm->tm_usec;
break;
case DTK_MILLISEC:
@@ -5217,9 +5222,9 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
* tm->tm_sec * 1000 + fsec / 1000
* = (tm->tm_sec * 1'000'000 + fsec) / 1000
*/
- PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 3));
+ PG_RETURN_NUMERIC(int64_div_fast_to_numeric(itm->tm_sec * INT64CONST(1000000) + + itm->tm_usec, 3));
else
- PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0);
+ PG_RETURN_FLOAT8(itm->tm_sec * 1000.0 + itm->tm_usec / 1000.0);
break;
case DTK_SECOND:
@@ -5228,48 +5233,48 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
* tm->tm_sec + fsec / 1'000'000
* = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000
*/
- PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 6));
+ PG_RETURN_NUMERIC(int64_div_fast_to_numeric(itm->tm_sec * INT64CONST(1000000) + + itm->tm_usec, 6));
else
- PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0);
+ PG_RETURN_FLOAT8(itm->tm_sec + itm->tm_usec / 1000000.0);
break;
case DTK_MINUTE:
- intresult = tm->tm_min;
+ intresult = itm->tm_min;
break;
case DTK_HOUR:
- intresult = tm->tm_hour;
+ intresult = itm->tm_hour;
break;
case DTK_DAY:
- intresult = tm->tm_mday;
+ intresult = itm->tm_mday;
break;
case DTK_MONTH:
- intresult = tm->tm_mon;
+ intresult = itm->tm_mon;
break;
case DTK_QUARTER:
- intresult = (tm->tm_mon / 3) + 1;
+ intresult = (itm->tm_mon / 3) + 1;
break;
case DTK_YEAR:
- intresult = tm->tm_year;
+ intresult = itm->tm_year;
break;
case DTK_DECADE:
/* caution: C division may have negative remainder */
- intresult = tm->tm_year / 10;
+ intresult = itm->tm_year / 10;
break;
case DTK_CENTURY:
/* caution: C division may have negative remainder */
- intresult = tm->tm_year / 100;
+ intresult = itm->tm_year / 100;
break;
case DTK_MILLENNIUM:
/* caution: C division may have negative remainder */
- intresult = tm->tm_year / 1000;
+ intresult = itm->tm_year / 1000;
break;
default:
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index 5fa38d20d8..ba918a2b22 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -64,9 +64,11 @@ typedef struct
/*
* Assorted constants for datetime-related calculations
*/
-
-#define DAYS_PER_YEAR 365.25 /* assumes leap year every four years */
-#define MONTHS_PER_YEAR 12
+#define YEARS_PER_MILLENNIUM 1000
+#define YEARS_PER_CENTURY 100
+#define YEARS_PER_DECADE 10
+#define DAYS_PER_YEAR 365.25 /* assumes leap year every four years */
+#define MONTHS_PER_YEAR 12
/*
* DAYS_PER_MONTH is very imprecise. The more accurate value is
* 365.2425/12 = 30.436875, or '30 days 10:29:06'. Right now we only
@@ -92,6 +94,7 @@ typedef struct
#define USECS_PER_HOUR INT64CONST(3600000000)
#define USECS_PER_MINUTE INT64CONST(60000000)
#define USECS_PER_SEC INT64CONST(1000000)
+#define USECS_PER_MSEC INT64CONST(1000)
/*
* We allow numeric timezone offsets up to 15:59:59 either way from Greenwich.
diff --git a/src/include/pgtime.h b/src/include/pgtime.h
index 2977b13aab..708d1e0cc9 100644
--- a/src/include/pgtime.h
+++ b/src/include/pgtime.h
@@ -44,6 +44,31 @@ struct pg_tm
const char *tm_zone;
};
+/* data structure to help decode intervals */
+struct pg_itm_in
+{
+ int64 tm_usec;
+ int tm_mday;
+ int tm_mon; /* see above */
+ int tm_year; /* see above */
+};
+
+/* data structure to help encode and manipulate intervals */
+struct pg_itm
+{
+ /* time units smaller than hours only have values less than 1 hour and can
+ * fit into an int
+ */
+ int tm_usec;
+ int tm_sec;
+ int tm_min;
+ int64 tm_hour;
+ int tm_mday;
+ int tm_mon; /* see above */
+ int tm_year; /* see above */
+ int tm_yday;
+};
+
typedef struct pg_tz pg_tz;
typedef struct pg_tzenum pg_tzenum;
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 666e545496..bf3e2a9336 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -53,6 +53,7 @@ extern int pg_ltoa(int32 l, char *a);
extern int pg_lltoa(int64 ll, char *a);
extern char *pg_ultostr_zeropad(char *str, uint32 value, int32 minwidth);
extern char *pg_ultostr(char *str, uint32 value);
+extern char *pg_ulltostr(char *str, uint64 value);
/* oid.c */
extern oidvector *buildoidvector(const Oid *oids, int n);
diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h
index 0d158f3e4b..d4d699c8f7 100644
--- a/src/include/utils/datetime.h
+++ b/src/include/utils/datetime.h
@@ -300,9 +300,9 @@ extern int DecodeTimeOnly(char **field, int *ftype,
int nf, int *dtype,
struct pg_tm *tm, fsec_t *fsec, int *tzp);
extern int DecodeInterval(char **field, int *ftype, int nf, int range,
- int *dtype, struct pg_tm *tm, fsec_t *fsec);
-extern int DecodeISO8601Interval(char *str,
- int *dtype, struct pg_tm *tm, fsec_t *fsec);
+ int *dtype, struct pg_itm_in *tm);
+extern int DecodeISO8601Interval(char *str, int *dtype,
+ struct pg_itm_in *itm_in);
extern void DateTimeParseError(int dterr, const char *str,
const char *datatype) pg_attribute_noreturn();
@@ -315,7 +315,7 @@ extern int DetermineTimeZoneAbbrevOffsetTS(TimestampTz ts, const char *abbr,
extern void EncodeDateOnly(struct pg_tm *tm, int style, char *str);
extern void EncodeTimeOnly(struct pg_tm *tm, fsec_t fsec, bool print_tz, int tz, int style, char *str);
extern void EncodeDateTime(struct pg_tm *tm, fsec_t fsec, bool print_tz, int tz, const char *tzn, int style, char *str);
-extern void EncodeInterval(struct pg_tm *tm, fsec_t fsec, int style, char *str);
+extern void EncodeInterval(struct pg_itm *itm, int style, char *str);
extern void EncodeSpecialTimestamp(Timestamp dt, char *str);
extern int ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index c1a74f8e2b..eea429dc5f 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -88,8 +88,9 @@ extern int timestamp2tm(Timestamp dt, int *tzp, struct pg_tm *tm,
fsec_t *fsec, const char **tzn, pg_tz *attimezone);
extern void dt2time(Timestamp dt, int *hour, int *min, int *sec, fsec_t *fsec);
-extern int interval2tm(Interval span, struct pg_tm *tm, fsec_t *fsec);
-extern int tm2interval(struct pg_tm *tm, fsec_t fsec, Interval *span);
+extern int interval2itm(Interval span, struct pg_itm *itm);
+extern int itm2interval(struct pg_itm *itm, Interval *span);
+extern int itmin2interval(struct pg_itm_in *itm_in, Interval *span);
extern Timestamp SetEpochTimestamp(void);
extern void GetEpochTime(struct pg_tm *tm);
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 146f7c55d0..00ffe0e2be 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -1079,3 +1079,614 @@ SELECT extract(epoch from interval '1000000000 days');
86400000000000.000000
(1 row)
+-- test time fields using entire 64 bit microseconds range
+SELECT INTERVAL '2562047788.01521550194 hours';
+ interval
+-----------------------------------
+ @ 2562047788 hours 54.775807 secs
+(1 row)
+
+SELECT INTERVAL '-2562047788.01521550222 hours';
+ interval
+---------------------------------------
+ @ 2562047788 hours 54.775808 secs ago
+(1 row)
+
+SELECT INTERVAL '153722867280.912930117 minutes';
+ interval
+-----------------------------------
+ @ 2562047788 hours 54.775807 secs
+(1 row)
+
+SELECT INTERVAL '-153722867280.912930133 minutes';
+ interval
+---------------------------------------
+ @ 2562047788 hours 54.775808 secs ago
+(1 row)
+
+SELECT INTERVAL '9223372036854.775807 seconds';
+ interval
+-----------------------------------
+ @ 2562047788 hours 54.775807 secs
+(1 row)
+
+SELECT INTERVAL '-9223372036854.775808 seconds';
+ interval
+---------------------------------------
+ @ 2562047788 hours 54.775808 secs ago
+(1 row)
+
+SELECT INTERVAL '9223372036854775.807 milliseconds';
+ interval
+-----------------------------------
+ @ 2562047788 hours 54.775807 secs
+(1 row)
+
+SELECT INTERVAL '-9223372036854775.808 milliseconds';
+ interval
+---------------------------------------
+ @ 2562047788 hours 54.775808 secs ago
+(1 row)
+
+SELECT INTERVAL '9223372036854775807 microseconds';
+ interval
+-----------------------------------
+ @ 2562047788 hours 54.775807 secs
+(1 row)
+
+SELECT INTERVAL '-9223372036854775808 microseconds';
+ interval
+---------------------------------------
+ @ 2562047788 hours 54.775808 secs ago
+(1 row)
+
+SELECT INTERVAL 'PT2562047788H54.775807S';
+ interval
+-----------------------------------
+ @ 2562047788 hours 54.775807 secs
+(1 row)
+
+SELECT INTERVAL 'PT-2562047788H-54.775808S';
+ interval
+---------------------------------------
+ @ 2562047788 hours 54.775808 secs ago
+(1 row)
+
+SELECT INTERVAL 'PT2562047788:00:54.775807';
+ interval
+-----------------------------------
+ @ 2562047788 hours 54.775807 secs
+(1 row)
+
+SELECT INTERVAL 'PT2562047788.0152155019444';
+ interval
+-----------------------------------
+ @ 2562047788 hours 54.775807 secs
+(1 row)
+
+SELECT INTERVAL 'PT-2562047788.0152155022222';
+ interval
+---------------------------------------
+ @ 2562047788 hours 54.775808 secs ago
+(1 row)
+
+-- overflow each date/time field
+SELECT INTERVAL '2147483648 years';
+ERROR: interval field value out of range: "2147483648 years"
+LINE 1: SELECT INTERVAL '2147483648 years';
+ ^
+SELECT INTERVAL '-2147483649 years';
+ERROR: interval field value out of range: "-2147483649 years"
+LINE 1: SELECT INTERVAL '-2147483649 years';
+ ^
+SELECT INTERVAL '2147483648 months';
+ERROR: interval field value out of range: "2147483648 months"
+LINE 1: SELECT INTERVAL '2147483648 months';
+ ^
+SELECT INTERVAL '-2147483649 months';
+ERROR: interval field value out of range: "-2147483649 months"
+LINE 1: SELECT INTERVAL '-2147483649 months';
+ ^
+SELECT INTERVAL '2147483648 days';
+ERROR: interval field value out of range: "2147483648 days"
+LINE 1: SELECT INTERVAL '2147483648 days';
+ ^
+SELECT INTERVAL '-2147483649 days';
+ERROR: interval field value out of range: "-2147483649 days"
+LINE 1: SELECT INTERVAL '-2147483649 days';
+ ^
+SELECT INTERVAL '2562047789 hours';
+ERROR: interval field value out of range: "2562047789 hours"
+LINE 1: SELECT INTERVAL '2562047789 hours';
+ ^
+SELECT INTERVAL '-2562047789 hours';
+ERROR: interval field value out of range: "-2562047789 hours"
+LINE 1: SELECT INTERVAL '-2562047789 hours';
+ ^
+SELECT INTERVAL '153722867281 minutes';
+ERROR: interval field value out of range: "153722867281 minutes"
+LINE 1: SELECT INTERVAL '153722867281 minutes';
+ ^
+SELECT INTERVAL '-153722867281 minutes';
+ERROR: interval field value out of range: "-153722867281 minutes"
+LINE 1: SELECT INTERVAL '-153722867281 minutes';
+ ^
+SELECT INTERVAL '9223372036855 seconds';
+ERROR: interval field value out of range: "9223372036855 seconds"
+LINE 1: SELECT INTERVAL '9223372036855 seconds';
+ ^
+SELECT INTERVAL '-9223372036855 seconds';
+ERROR: interval field value out of range: "-9223372036855 seconds"
+LINE 1: SELECT INTERVAL '-9223372036855 seconds';
+ ^
+SELECT INTERVAL '9223372036854777 millisecond';
+ERROR: interval field value out of range: "9223372036854777 millisecond"
+LINE 1: SELECT INTERVAL '9223372036854777 millisecond';
+ ^
+SELECT INTERVAL '-9223372036854777 millisecond';
+ERROR: interval field value out of range: "-9223372036854777 millisecond"
+LINE 1: SELECT INTERVAL '-9223372036854777 millisecond';
+ ^
+SELECT INTERVAL '9223372036854775808 microsecond';
+ERROR: interval field value out of range: "9223372036854775808 microsecond"
+LINE 1: SELECT INTERVAL '9223372036854775808 microsecond';
+ ^
+SELECT INTERVAL '-9223372036854775809 microsecond';
+ERROR: interval field value out of range: "-9223372036854775809 microsecond"
+LINE 1: SELECT INTERVAL '-9223372036854775809 microsecond';
+ ^
+SELECT INTERVAL 'P2147483648';
+ERROR: interval field value out of range: "P2147483648"
+LINE 1: SELECT INTERVAL 'P2147483648';
+ ^
+SELECT INTERVAL 'P-2147483649';
+ERROR: interval field value out of range: "P-2147483649"
+LINE 1: SELECT INTERVAL 'P-2147483649';
+ ^
+SELECT INTERVAL 'P1-2147483647-2147483647';
+ERROR: interval out of range
+LINE 1: SELECT INTERVAL 'P1-2147483647-2147483647';
+ ^
+SELECT INTERVAL 'PT2562047789';
+ERROR: interval field value out of range: "PT2562047789"
+LINE 1: SELECT INTERVAL 'PT2562047789';
+ ^
+SELECT INTERVAL 'PT-2562047789';
+ERROR: interval field value out of range: "PT-2562047789"
+LINE 1: SELECT INTERVAL 'PT-2562047789';
+ ^
+-- overflow with date/time unit aliases
+SELECT INTERVAL '2147483647 weeks';
+ERROR: interval field value out of range: "2147483647 weeks"
+LINE 1: SELECT INTERVAL '2147483647 weeks';
+ ^
+SELECT INTERVAL '-2147483648 weeks';
+ERROR: interval field value out of range: "-2147483648 weeks"
+LINE 1: SELECT INTERVAL '-2147483648 weeks';
+ ^
+SELECT INTERVAL '2147483647 decades';
+ERROR: interval field value out of range: "2147483647 decades"
+LINE 1: SELECT INTERVAL '2147483647 decades';
+ ^
+SELECT INTERVAL '-2147483648 decades';
+ERROR: interval field value out of range: "-2147483648 decades"
+LINE 1: SELECT INTERVAL '-2147483648 decades';
+ ^
+SELECT INTERVAL '2147483647 centuries';
+ERROR: interval field value out of range: "2147483647 centuries"
+LINE 1: SELECT INTERVAL '2147483647 centuries';
+ ^
+SELECT INTERVAL '-2147483648 centuries';
+ERROR: interval field value out of range: "-2147483648 centuries"
+LINE 1: SELECT INTERVAL '-2147483648 centuries';
+ ^
+SELECT INTERVAL '2147483647 millennium';
+ERROR: interval field value out of range: "2147483647 millennium"
+LINE 1: SELECT INTERVAL '2147483647 millennium';
+ ^
+SELECT INTERVAL '-2147483648 millennium';
+ERROR: interval field value out of range: "-2147483648 millennium"
+LINE 1: SELECT INTERVAL '-2147483648 millennium';
+ ^
+SELECT INTERVAL '1 week 2147483647 days';
+ERROR: interval field value out of range: "1 week 2147483647 days"
+LINE 1: SELECT INTERVAL '1 week 2147483647 days';
+ ^
+SELECT INTERVAL '-1 week -2147483648 days';
+ERROR: interval field value out of range: "-1 week -2147483648 days"
+LINE 1: SELECT INTERVAL '-1 week -2147483648 days';
+ ^
+SELECT INTERVAL '2147483647 days 1 week';
+ERROR: interval field value out of range: "2147483647 days 1 week"
+LINE 1: SELECT INTERVAL '2147483647 days 1 week';
+ ^
+SELECT INTERVAL '-2147483648 days -1 week';
+ERROR: interval field value out of range: "-2147483648 days -1 week"
+LINE 1: SELECT INTERVAL '-2147483648 days -1 week';
+ ^
+SELECT INTERVAL 'P1W2147483647D';
+ERROR: interval field value out of range: "P1W2147483647D"
+LINE 1: SELECT INTERVAL 'P1W2147483647D';
+ ^
+SELECT INTERVAL 'P-1W-2147483648D';
+ERROR: interval field value out of range: "P-1W-2147483648D"
+LINE 1: SELECT INTERVAL 'P-1W-2147483648D';
+ ^
+SELECT INTERVAL 'P2147483647D1W';
+ERROR: interval field value out of range: "P2147483647D1W"
+LINE 1: SELECT INTERVAL 'P2147483647D1W';
+ ^
+SELECT INTERVAL 'P-2147483648D-1W';
+ERROR: interval field value out of range: "P-2147483648D-1W"
+LINE 1: SELECT INTERVAL 'P-2147483648D-1W';
+ ^
+SELECT INTERVAL '1 decade 2147483647 years';
+ERROR: interval field value out of range: "1 decade 2147483647 years"
+LINE 1: SELECT INTERVAL '1 decade 2147483647 years';
+ ^
+SELECT INTERVAL '1 century 2147483647 years';
+ERROR: interval field value out of range: "1 century 2147483647 years"
+LINE 1: SELECT INTERVAL '1 century 2147483647 years';
+ ^
+SELECT INTERVAL '1 millennium 2147483647 years';
+ERROR: interval field value out of range: "1 millennium 2147483647 years"
+LINE 1: SELECT INTERVAL '1 millennium 2147483647 years';
+ ^
+SELECT INTERVAL '-1 decade -2147483648 years';
+ERROR: interval field value out of range: "-1 decade -2147483648 years"
+LINE 1: SELECT INTERVAL '-1 decade -2147483648 years';
+ ^
+SELECT INTERVAL '-1 century -2147483648 years';
+ERROR: interval field value out of range: "-1 century -2147483648 years"
+LINE 1: SELECT INTERVAL '-1 century -2147483648 years';
+ ^
+SELECT INTERVAL '-1 millennium -2147483648 years';
+ERROR: interval field value out of range: "-1 millennium -2147483648 years"
+LINE 1: SELECT INTERVAL '-1 millennium -2147483648 years';
+ ^
+SELECT INTERVAL '2147483647 years 1 decade';
+ERROR: interval field value out of range: "2147483647 years 1 decade"
+LINE 1: SELECT INTERVAL '2147483647 years 1 decade';
+ ^
+SELECT INTERVAL '2147483647 years 1 century';
+ERROR: interval field value out of range: "2147483647 years 1 century"
+LINE 1: SELECT INTERVAL '2147483647 years 1 century';
+ ^
+SELECT INTERVAL '2147483647 years 1 millennium';
+ERROR: interval field value out of range: "2147483647 years 1 millennium"
+LINE 1: SELECT INTERVAL '2147483647 years 1 millennium';
+ ^
+SELECT INTERVAL '-2147483648 years -1 decade';
+ERROR: interval field value out of range: "-2147483648 years -1 decade"
+LINE 1: SELECT INTERVAL '-2147483648 years -1 decade';
+ ^
+SELECT INTERVAL '-2147483648 years -1 century';
+ERROR: interval field value out of range: "-2147483648 years -1 century"
+LINE 1: SELECT INTERVAL '-2147483648 years -1 century';
+ ^
+SELECT INTERVAL '-2147483648 years -1 millennium';
+ERROR: interval field value out of range: "-2147483648 years -1 millennium"
+LINE 1: SELECT INTERVAL '-2147483648 years -1 millennium';
+ ^
+-- overflowing with fractional fields - postgres format
+SELECT INTERVAL '0.1 millennium 2147483647 months';
+ERROR: interval field value out of range: "0.1 millennium 2147483647 months"
+LINE 1: SELECT INTERVAL '0.1 millennium 2147483647 months';
+ ^
+SELECT INTERVAL '0.1 centuries 2147483647 months';
+ERROR: interval field value out of range: "0.1 centuries 2147483647 months"
+LINE 1: SELECT INTERVAL '0.1 centuries 2147483647 months';
+ ^
+SELECT INTERVAL '0.1 decades 2147483647 months';
+ERROR: interval field value out of range: "0.1 decades 2147483647 months"
+LINE 1: SELECT INTERVAL '0.1 decades 2147483647 months';
+ ^
+SELECT INTERVAL '0.1 yrs 2147483647 months';
+ERROR: interval field value out of range: "0.1 yrs 2147483647 months"
+LINE 1: SELECT INTERVAL '0.1 yrs 2147483647 months';
+ ^
+SELECT INTERVAL '-0.1 millennium -2147483648 months';
+ERROR: interval field value out of range: "-0.1 millennium -2147483648 months"
+LINE 1: SELECT INTERVAL '-0.1 millennium -2147483648 months';
+ ^
+SELECT INTERVAL '-0.1 centuries -2147483648 months';
+ERROR: interval field value out of range: "-0.1 centuries -2147483648 months"
+LINE 1: SELECT INTERVAL '-0.1 centuries -2147483648 months';
+ ^
+SELECT INTERVAL '-0.1 decades -2147483648 months';
+ERROR: interval field value out of range: "-0.1 decades -2147483648 months"
+LINE 1: SELECT INTERVAL '-0.1 decades -2147483648 months';
+ ^
+SELECT INTERVAL '-0.1 yrs -2147483648 months';
+ERROR: interval field value out of range: "-0.1 yrs -2147483648 months"
+LINE 1: SELECT INTERVAL '-0.1 yrs -2147483648 months';
+ ^
+SELECT INTERVAL '2147483647 months 0.1 millennium';
+ERROR: interval field value out of range: "2147483647 months 0.1 millennium"
+LINE 1: SELECT INTERVAL '2147483647 months 0.1 millennium';
+ ^
+SELECT INTERVAL '2147483647 months 0.1 centuries';
+ERROR: interval field value out of range: "2147483647 months 0.1 centuries"
+LINE 1: SELECT INTERVAL '2147483647 months 0.1 centuries';
+ ^
+SELECT INTERVAL '2147483647 months 0.1 decades';
+ERROR: interval field value out of range: "2147483647 months 0.1 decades"
+LINE 1: SELECT INTERVAL '2147483647 months 0.1 decades';
+ ^
+SELECT INTERVAL '2147483647 months 0.1 yrs';
+ERROR: interval field value out of range: "2147483647 months 0.1 yrs"
+LINE 1: SELECT INTERVAL '2147483647 months 0.1 yrs';
+ ^
+SELECT INTERVAL '-2147483648 months -0.1 millennium';
+ERROR: interval field value out of range: "-2147483648 months -0.1 millennium"
+LINE 1: SELECT INTERVAL '-2147483648 months -0.1 millennium';
+ ^
+SELECT INTERVAL '-2147483648 months -0.1 centuries';
+ERROR: interval field value out of range: "-2147483648 months -0.1 centuries"
+LINE 1: SELECT INTERVAL '-2147483648 months -0.1 centuries';
+ ^
+SELECT INTERVAL '-2147483648 months -0.1 decades';
+ERROR: interval field value out of range: "-2147483648 months -0.1 decades"
+LINE 1: SELECT INTERVAL '-2147483648 months -0.1 decades';
+ ^
+SELECT INTERVAL '-2147483648 months -0.1 yrs';
+ERROR: interval field value out of range: "-2147483648 months -0.1 yrs"
+LINE 1: SELECT INTERVAL '-2147483648 months -0.1 yrs';
+ ^
+SELECT INTERVAL '0.1 months 2147483647 days';
+ERROR: interval field value out of range: "0.1 months 2147483647 days"
+LINE 1: SELECT INTERVAL '0.1 months 2147483647 days';
+ ^
+SELECT INTERVAL '-0.1 months -2147483648 days';
+ERROR: interval field value out of range: "-0.1 months -2147483648 days"
+LINE 1: SELECT INTERVAL '-0.1 months -2147483648 days';
+ ^
+SELECT INTERVAL '2147483647 days 0.1 months';
+ERROR: interval field value out of range: "2147483647 days 0.1 months"
+LINE 1: SELECT INTERVAL '2147483647 days 0.1 months';
+ ^
+SELECT INTERVAL '-2147483648 days -0.1 months';
+ERROR: interval field value out of range: "-2147483648 days -0.1 months"
+LINE 1: SELECT INTERVAL '-2147483648 days -0.1 months';
+ ^
+SELECT INTERVAL '0.5 weeks 2147483647 days';
+ERROR: interval field value out of range: "0.5 weeks 2147483647 days"
+LINE 1: SELECT INTERVAL '0.5 weeks 2147483647 days';
+ ^
+SELECT INTERVAL '-0.5 weeks -2147483648 days';
+ERROR: interval field value out of range: "-0.5 weeks -2147483648 days"
+LINE 1: SELECT INTERVAL '-0.5 weeks -2147483648 days';
+ ^
+SELECT INTERVAL '2147483647 days 0.5 weeks';
+ERROR: interval field value out of range: "2147483647 days 0.5 weeks"
+LINE 1: SELECT INTERVAL '2147483647 days 0.5 weeks';
+ ^
+SELECT INTERVAL '-2147483648 days -0.5 weeks';
+ERROR: interval field value out of range: "-2147483648 days -0.5 weeks"
+LINE 1: SELECT INTERVAL '-2147483648 days -0.5 weeks';
+ ^
+SELECT INTERVAL '0.01 months 9223372036854775807 microseconds';
+ERROR: interval field value out of range: "0.01 months 9223372036854775807 microseconds"
+LINE 1: SELECT INTERVAL '0.01 months 9223372036854775807 microsecond...
+ ^
+SELECT INTERVAL '-0.01 months -9223372036854775808 microseconds';
+ERROR: interval field value out of range: "-0.01 months -9223372036854775808 microseconds"
+LINE 1: SELECT INTERVAL '-0.01 months -9223372036854775808 microseco...
+ ^
+SELECT INTERVAL '9223372036854775807 microseconds 0.01 months';
+ERROR: interval field value out of range: "9223372036854775807 microseconds 0.01 months"
+LINE 1: SELECT INTERVAL '9223372036854775807 microseconds 0.01 month...
+ ^
+SELECT INTERVAL '-9223372036854775808 microseconds -0.01 months';
+ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.01 months"
+LINE 1: SELECT INTERVAL '-9223372036854775808 microseconds -0.01 mon...
+ ^
+SELECT INTERVAL '0.1 weeks 9223372036854775807 microseconds';
+ERROR: interval field value out of range: "0.1 weeks 9223372036854775807 microseconds"
+LINE 1: SELECT INTERVAL '0.1 weeks 9223372036854775807 microseconds'...
+ ^
+SELECT INTERVAL '-0.1 weeks -9223372036854775808 microseconds';
+ERROR: interval field value out of range: "-0.1 weeks -9223372036854775808 microseconds"
+LINE 1: SELECT INTERVAL '-0.1 weeks -9223372036854775808 microsecond...
+ ^
+SELECT INTERVAL '9223372036854775807 microseconds 0.1 weeks';
+ERROR: interval field value out of range: "9223372036854775807 microseconds 0.1 weeks"
+LINE 1: SELECT INTERVAL '9223372036854775807 microseconds 0.1 weeks'...
+ ^
+SELECT INTERVAL '-9223372036854775808 microseconds -0.1 weeks';
+ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.1 weeks"
+LINE 1: SELECT INTERVAL '-9223372036854775808 microseconds -0.1 week...
+ ^
+SELECT INTERVAL '0.1 days 9223372036854775807 microseconds';
+ERROR: interval field value out of range: "0.1 days 9223372036854775807 microseconds"
+LINE 1: SELECT INTERVAL '0.1 days 9223372036854775807 microseconds';
+ ^
+SELECT INTERVAL '-0.1 days -9223372036854775808 microseconds';
+ERROR: interval field value out of range: "-0.1 days -9223372036854775808 microseconds"
+LINE 1: SELECT INTERVAL '-0.1 days -9223372036854775808 microseconds...
+ ^
+SELECT INTERVAL '9223372036854775807 microseconds 0.1 days';
+ERROR: interval field value out of range: "9223372036854775807 microseconds 0.1 days"
+LINE 1: SELECT INTERVAL '9223372036854775807 microseconds 0.1 days';
+ ^
+SELECT INTERVAL '-9223372036854775808 microseconds -0.1 days';
+ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.1 days"
+LINE 1: SELECT INTERVAL '-9223372036854775808 microseconds -0.1 days...
+ ^
+-- overflowing with fractional fields - ISO8601 format
+SELECT INTERVAL 'P0.1Y2147483647M';
+ERROR: interval field value out of range: "P0.1Y2147483647M"
+LINE 1: SELECT INTERVAL 'P0.1Y2147483647M';
+ ^
+SELECT INTERVAL 'P-0.1Y-2147483648M';
+ERROR: interval field value out of range: "P-0.1Y-2147483648M"
+LINE 1: SELECT INTERVAL 'P-0.1Y-2147483648M';
+ ^
+SELECT INTERVAL 'P2147483647M0.1Y';
+ERROR: interval field value out of range: "P2147483647M0.1Y"
+LINE 1: SELECT INTERVAL 'P2147483647M0.1Y';
+ ^
+SELECT INTERVAL 'P-2147483648M-0.1Y';
+ERROR: interval field value out of range: "P-2147483648M-0.1Y"
+LINE 1: SELECT INTERVAL 'P-2147483648M-0.1Y';
+ ^
+SELECT INTERVAL 'P0.1M2147483647D';
+ERROR: interval field value out of range: "P0.1M2147483647D"
+LINE 1: SELECT INTERVAL 'P0.1M2147483647D';
+ ^
+SELECT INTERVAL 'P-0.1M-2147483648D';
+ERROR: interval field value out of range: "P-0.1M-2147483648D"
+LINE 1: SELECT INTERVAL 'P-0.1M-2147483648D';
+ ^
+SELECT INTERVAL 'P2147483647D0.1M';
+ERROR: interval field value out of range: "P2147483647D0.1M"
+LINE 1: SELECT INTERVAL 'P2147483647D0.1M';
+ ^
+SELECT INTERVAL 'P-2147483648D-0.1M';
+ERROR: interval field value out of range: "P-2147483648D-0.1M"
+LINE 1: SELECT INTERVAL 'P-2147483648D-0.1M';
+ ^
+SELECT INTERVAL 'P0.5W2147483647D';
+ERROR: interval field value out of range: "P0.5W2147483647D"
+LINE 1: SELECT INTERVAL 'P0.5W2147483647D';
+ ^
+SELECT INTERVAL 'P-0.5W-2147483648D';
+ERROR: interval field value out of range: "P-0.5W-2147483648D"
+LINE 1: SELECT INTERVAL 'P-0.5W-2147483648D';
+ ^
+SELECT INTERVAL 'P2147483647D0.5W';
+ERROR: interval field value out of range: "P2147483647D0.5W"
+LINE 1: SELECT INTERVAL 'P2147483647D0.5W';
+ ^
+SELECT INTERVAL 'P-2147483648D-0.5W';
+ERROR: interval field value out of range: "P-2147483648D-0.5W"
+LINE 1: SELECT INTERVAL 'P-2147483648D-0.5W';
+ ^
+SELECT INTERVAL 'P0.01MT2562047788H54.775807S';
+ERROR: interval field value out of range: "P0.01MT2562047788H54.775807S"
+LINE 1: SELECT INTERVAL 'P0.01MT2562047788H54.775807S';
+ ^
+SELECT INTERVAL 'P-0.01MT-2562047788H-54.775808S';
+ERROR: interval field value out of range: "P-0.01MT-2562047788H-54.775808S"
+LINE 1: SELECT INTERVAL 'P-0.01MT-2562047788H-54.775808S';
+ ^
+SELECT INTERVAL 'P0.1DT2562047788H54.775807S';
+ERROR: interval field value out of range: "P0.1DT2562047788H54.775807S"
+LINE 1: SELECT INTERVAL 'P0.1DT2562047788H54.775807S';
+ ^
+SELECT INTERVAL 'P-0.1DT-2562047788H-54.775808S';
+ERROR: interval field value out of range: "P-0.1DT-2562047788H-54.775808S"
+LINE 1: SELECT INTERVAL 'P-0.1DT-2562047788H-54.775808S';
+ ^
+SELECT INTERVAL 'PT2562047788.1H54.775807S';
+ERROR: interval field value out of range: "PT2562047788.1H54.775807S"
+LINE 1: SELECT INTERVAL 'PT2562047788.1H54.775807S';
+ ^
+SELECT INTERVAL 'PT-2562047788.1H-54.775808S';
+ERROR: interval field value out of range: "PT-2562047788.1H-54.775808S"
+LINE 1: SELECT INTERVAL 'PT-2562047788.1H-54.775808S';
+ ^
+SELECT INTERVAL 'PT2562047788H0.1M54.775807S';
+ERROR: interval field value out of range: "PT2562047788H0.1M54.775807S"
+LINE 1: SELECT INTERVAL 'PT2562047788H0.1M54.775807S';
+ ^
+SELECT INTERVAL 'PT-2562047788H-0.1M-54.775808S';
+ERROR: interval field value out of range: "PT-2562047788H-0.1M-54.775808S"
+LINE 1: SELECT INTERVAL 'PT-2562047788H-0.1M-54.775808S';
+ ^
+-- overflowing with fractional fields - ISO8601 alternative format
+SELECT INTERVAL 'P0.1-2147483647-00';
+ERROR: interval field value out of range: "P0.1-2147483647-00"
+LINE 1: SELECT INTERVAL 'P0.1-2147483647-00';
+ ^
+SELECT INTERVAL 'P00-0.1-2147483647';
+ERROR: interval field value out of range: "P00-0.1-2147483647"
+LINE 1: SELECT INTERVAL 'P00-0.1-2147483647';
+ ^
+SELECT INTERVAL 'P00-0.01-00T2562047788:00:54.775807';
+ERROR: interval field value out of range: "P00-0.01-00T2562047788:00:54.775807"
+LINE 1: SELECT INTERVAL 'P00-0.01-00T2562047788:00:54.775807';
+ ^
+SELECT INTERVAL 'P00-00-0.1T2562047788:00:54.775807';
+ERROR: interval field value out of range: "P00-00-0.1T2562047788:00:54.775807"
+LINE 1: SELECT INTERVAL 'P00-00-0.1T2562047788:00:54.775807';
+ ^
+SELECT INTERVAL 'PT2562047788.1:00:54.775807';
+ERROR: interval field value out of range: "PT2562047788.1:00:54.775807"
+LINE 1: SELECT INTERVAL 'PT2562047788.1:00:54.775807';
+ ^
+SELECT INTERVAL 'PT2562047788:01.:54.775807';
+ERROR: interval field value out of range: "PT2562047788:01.:54.775807"
+LINE 1: SELECT INTERVAL 'PT2562047788:01.:54.775807';
+ ^
+-- overflowing with fractional fields - SQL standard format
+SELECT INTERVAL '0.1 2562047788:0:54.775807';
+ERROR: interval field value out of range: "0.1 2562047788:0:54.775807"
+LINE 1: SELECT INTERVAL '0.1 2562047788:0:54.775807';
+ ^
+SELECT INTERVAL '0.1 2562047788:0:54.775808 ago';
+ERROR: interval field value out of range: "0.1 2562047788:0:54.775808 ago"
+LINE 1: SELECT INTERVAL '0.1 2562047788:0:54.775808 ago';
+ ^
+SELECT INTERVAL '2562047788.1:0:54.775807';
+ERROR: interval field value out of range: "2562047788.1:0:54.775807"
+LINE 1: SELECT INTERVAL '2562047788.1:0:54.775807';
+ ^
+SELECT INTERVAL '2562047788.1:0:54.775808 ago';
+ERROR: interval field value out of range: "2562047788.1:0:54.775808 ago"
+LINE 1: SELECT INTERVAL '2562047788.1:0:54.775808 ago';
+ ^
+SELECT INTERVAL '2562047788:0.1:54.775807';
+ERROR: invalid input syntax for type interval: "2562047788:0.1:54.775807"
+LINE 1: SELECT INTERVAL '2562047788:0.1:54.775807';
+ ^
+SELECT INTERVAL '2562047788:0.1:54.775808 ago';
+ERROR: invalid input syntax for type interval: "2562047788:0.1:54.775808 ago"
+LINE 1: SELECT INTERVAL '2562047788:0.1:54.775808 ago';
+ ^
+-- overflowing using AGO with INT_MIN
+SELECT INTERVAL '-2147483648 months ago';
+ERROR: interval field value out of range: "-2147483648 months ago"
+LINE 1: SELECT INTERVAL '-2147483648 months ago';
+ ^
+SELECT INTERVAL '-2147483648 days ago';
+ERROR: interval field value out of range: "-2147483648 days ago"
+LINE 1: SELECT INTERVAL '-2147483648 days ago';
+ ^
+SELECT INTERVAL '-9223372036854775808 microseconds ago';
+ERROR: interval field value out of range: "-9223372036854775808 microseconds ago"
+LINE 1: SELECT INTERVAL '-9223372036854775808 microseconds ago';
+ ^
+SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 microseconds ago';
+ERROR: interval field value out of range: "-2147483648 months -2147483648 days -9223372036854775808 microseconds ago"
+LINE 1: SELECT INTERVAL '-2147483648 months -2147483648 days -922337...
+ ^
+-- test that INT_MIN number is formatted properly
+SET IntervalStyle to postgres;
+SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 us';
+ interval
+--------------------------------------------------------------------
+ -178956970 years -8 mons -2147483648 days -2562047788:00:54.775808
+(1 row)
+
+SET IntervalStyle to postgres_verbose;
+SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 us';
+ interval
+------------------------------------------------------------------------------
+ @ 178956970 years 8 mons 2147483648 days 2562047788 hours 54.775808 secs ago
+(1 row)
+
+SET IntervalStyle TO sql_standard;
+SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 us';
+ interval
+---------------------------------------------------
+ -178956970-8 -2147483648 -2562047788:00:54.775808
+(1 row)
+
+SET IntervalStyle to iso_8601;
+SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 us';
+ interval
+-----------------------------------------------------
+ P-178956970Y-8M-2147483648DT-2562047788H-54.775808S
+(1 row)
+
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index c31f0eec05..fc924d5bde 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -367,3 +367,187 @@ SELECT f1,
-- internal overflow test case
SELECT extract(epoch from interval '1000000000 days');
+
+-- test time fields using entire 64 bit microseconds range
+SELECT INTERVAL '2562047788.01521550194 hours';
+SELECT INTERVAL '-2562047788.01521550222 hours';
+SELECT INTERVAL '153722867280.912930117 minutes';
+SELECT INTERVAL '-153722867280.912930133 minutes';
+SELECT INTERVAL '9223372036854.775807 seconds';
+SELECT INTERVAL '-9223372036854.775808 seconds';
+SELECT INTERVAL '9223372036854775.807 milliseconds';
+SELECT INTERVAL '-9223372036854775.808 milliseconds';
+SELECT INTERVAL '9223372036854775807 microseconds';
+SELECT INTERVAL '-9223372036854775808 microseconds';
+
+SELECT INTERVAL 'PT2562047788H54.775807S';
+SELECT INTERVAL 'PT-2562047788H-54.775808S';
+
+SELECT INTERVAL 'PT2562047788:00:54.775807';
+
+SELECT INTERVAL 'PT2562047788.0152155019444';
+SELECT INTERVAL 'PT-2562047788.0152155022222';
+
+-- overflow each date/time field
+SELECT INTERVAL '2147483648 years';
+SELECT INTERVAL '-2147483649 years';
+SELECT INTERVAL '2147483648 months';
+SELECT INTERVAL '-2147483649 months';
+SELECT INTERVAL '2147483648 days';
+SELECT INTERVAL '-2147483649 days';
+SELECT INTERVAL '2562047789 hours';
+SELECT INTERVAL '-2562047789 hours';
+SELECT INTERVAL '153722867281 minutes';
+SELECT INTERVAL '-153722867281 minutes';
+SELECT INTERVAL '9223372036855 seconds';
+SELECT INTERVAL '-9223372036855 seconds';
+SELECT INTERVAL '9223372036854777 millisecond';
+SELECT INTERVAL '-9223372036854777 millisecond';
+SELECT INTERVAL '9223372036854775808 microsecond';
+SELECT INTERVAL '-9223372036854775809 microsecond';
+
+SELECT INTERVAL 'P2147483648';
+SELECT INTERVAL 'P-2147483649';
+SELECT INTERVAL 'P1-2147483647-2147483647';
+SELECT INTERVAL 'PT2562047789';
+SELECT INTERVAL 'PT-2562047789';
+
+-- overflow with date/time unit aliases
+SELECT INTERVAL '2147483647 weeks';
+SELECT INTERVAL '-2147483648 weeks';
+SELECT INTERVAL '2147483647 decades';
+SELECT INTERVAL '-2147483648 decades';
+SELECT INTERVAL '2147483647 centuries';
+SELECT INTERVAL '-2147483648 centuries';
+SELECT INTERVAL '2147483647 millennium';
+SELECT INTERVAL '-2147483648 millennium';
+
+SELECT INTERVAL '1 week 2147483647 days';
+SELECT INTERVAL '-1 week -2147483648 days';
+SELECT INTERVAL '2147483647 days 1 week';
+SELECT INTERVAL '-2147483648 days -1 week';
+
+SELECT INTERVAL 'P1W2147483647D';
+SELECT INTERVAL 'P-1W-2147483648D';
+SELECT INTERVAL 'P2147483647D1W';
+SELECT INTERVAL 'P-2147483648D-1W';
+
+SELECT INTERVAL '1 decade 2147483647 years';
+SELECT INTERVAL '1 century 2147483647 years';
+SELECT INTERVAL '1 millennium 2147483647 years';
+SELECT INTERVAL '-1 decade -2147483648 years';
+SELECT INTERVAL '-1 century -2147483648 years';
+SELECT INTERVAL '-1 millennium -2147483648 years';
+
+SELECT INTERVAL '2147483647 years 1 decade';
+SELECT INTERVAL '2147483647 years 1 century';
+SELECT INTERVAL '2147483647 years 1 millennium';
+SELECT INTERVAL '-2147483648 years -1 decade';
+SELECT INTERVAL '-2147483648 years -1 century';
+SELECT INTERVAL '-2147483648 years -1 millennium';
+
+-- overflowing with fractional fields - postgres format
+SELECT INTERVAL '0.1 millennium 2147483647 months';
+SELECT INTERVAL '0.1 centuries 2147483647 months';
+SELECT INTERVAL '0.1 decades 2147483647 months';
+SELECT INTERVAL '0.1 yrs 2147483647 months';
+SELECT INTERVAL '-0.1 millennium -2147483648 months';
+SELECT INTERVAL '-0.1 centuries -2147483648 months';
+SELECT INTERVAL '-0.1 decades -2147483648 months';
+SELECT INTERVAL '-0.1 yrs -2147483648 months';
+
+SELECT INTERVAL '2147483647 months 0.1 millennium';
+SELECT INTERVAL '2147483647 months 0.1 centuries';
+SELECT INTERVAL '2147483647 months 0.1 decades';
+SELECT INTERVAL '2147483647 months 0.1 yrs';
+SELECT INTERVAL '-2147483648 months -0.1 millennium';
+SELECT INTERVAL '-2147483648 months -0.1 centuries';
+SELECT INTERVAL '-2147483648 months -0.1 decades';
+SELECT INTERVAL '-2147483648 months -0.1 yrs';
+
+SELECT INTERVAL '0.1 months 2147483647 days';
+SELECT INTERVAL '-0.1 months -2147483648 days';
+SELECT INTERVAL '2147483647 days 0.1 months';
+SELECT INTERVAL '-2147483648 days -0.1 months';
+
+SELECT INTERVAL '0.5 weeks 2147483647 days';
+SELECT INTERVAL '-0.5 weeks -2147483648 days';
+SELECT INTERVAL '2147483647 days 0.5 weeks';
+SELECT INTERVAL '-2147483648 days -0.5 weeks';
+
+SELECT INTERVAL '0.01 months 9223372036854775807 microseconds';
+SELECT INTERVAL '-0.01 months -9223372036854775808 microseconds';
+SELECT INTERVAL '9223372036854775807 microseconds 0.01 months';
+SELECT INTERVAL '-9223372036854775808 microseconds -0.01 months';
+
+SELECT INTERVAL '0.1 weeks 9223372036854775807 microseconds';
+SELECT INTERVAL '-0.1 weeks -9223372036854775808 microseconds';
+SELECT INTERVAL '9223372036854775807 microseconds 0.1 weeks';
+SELECT INTERVAL '-9223372036854775808 microseconds -0.1 weeks';
+
+SELECT INTERVAL '0.1 days 9223372036854775807 microseconds';
+SELECT INTERVAL '-0.1 days -9223372036854775808 microseconds';
+SELECT INTERVAL '9223372036854775807 microseconds 0.1 days';
+SELECT INTERVAL '-9223372036854775808 microseconds -0.1 days';
+
+-- overflowing with fractional fields - ISO8601 format
+SELECT INTERVAL 'P0.1Y2147483647M';
+SELECT INTERVAL 'P-0.1Y-2147483648M';
+SELECT INTERVAL 'P2147483647M0.1Y';
+SELECT INTERVAL 'P-2147483648M-0.1Y';
+
+SELECT INTERVAL 'P0.1M2147483647D';
+SELECT INTERVAL 'P-0.1M-2147483648D';
+SELECT INTERVAL 'P2147483647D0.1M';
+SELECT INTERVAL 'P-2147483648D-0.1M';
+
+SELECT INTERVAL 'P0.5W2147483647D';
+SELECT INTERVAL 'P-0.5W-2147483648D';
+SELECT INTERVAL 'P2147483647D0.5W';
+SELECT INTERVAL 'P-2147483648D-0.5W';
+
+SELECT INTERVAL 'P0.01MT2562047788H54.775807S';
+SELECT INTERVAL 'P-0.01MT-2562047788H-54.775808S';
+
+SELECT INTERVAL 'P0.1DT2562047788H54.775807S';
+SELECT INTERVAL 'P-0.1DT-2562047788H-54.775808S';
+
+SELECT INTERVAL 'PT2562047788.1H54.775807S';
+SELECT INTERVAL 'PT-2562047788.1H-54.775808S';
+
+SELECT INTERVAL 'PT2562047788H0.1M54.775807S';
+SELECT INTERVAL 'PT-2562047788H-0.1M-54.775808S';
+
+-- overflowing with fractional fields - ISO8601 alternative format
+SELECT INTERVAL 'P0.1-2147483647-00';
+SELECT INTERVAL 'P00-0.1-2147483647';
+SELECT INTERVAL 'P00-0.01-00T2562047788:00:54.775807';
+SELECT INTERVAL 'P00-00-0.1T2562047788:00:54.775807';
+SELECT INTERVAL 'PT2562047788.1:00:54.775807';
+SELECT INTERVAL 'PT2562047788:01.:54.775807';
+
+-- overflowing with fractional fields - SQL standard format
+SELECT INTERVAL '0.1 2562047788:0:54.775807';
+SELECT INTERVAL '0.1 2562047788:0:54.775808 ago';
+
+SELECT INTERVAL '2562047788.1:0:54.775807';
+SELECT INTERVAL '2562047788.1:0:54.775808 ago';
+
+SELECT INTERVAL '2562047788:0.1:54.775807';
+SELECT INTERVAL '2562047788:0.1:54.775808 ago';
+
+-- overflowing using AGO with INT_MIN
+SELECT INTERVAL '-2147483648 months ago';
+SELECT INTERVAL '-2147483648 days ago';
+SELECT INTERVAL '-9223372036854775808 microseconds ago';
+SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 microseconds ago';
+
+-- test that INT_MIN number is formatted properly
+SET IntervalStyle to postgres;
+SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 us';
+SET IntervalStyle to postgres_verbose;
+SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 us';
+SET IntervalStyle TO sql_standard;
+SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 us';
+SET IntervalStyle to iso_8601;
+SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 us';
--
2.25.1
