On Sun, Mar 19, 2023 at 5:13 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> Did you actually write "if TIMESTAMP_IS_NOBEGIN(dt2)" and not
> "if (TIMESTAMP_IS_NOBEGIN(dt2))"? If the former, I'm not surprised
> that pgindent gets confused. The parentheses are required by the
> C standard. Your code might accidentally work because the macro
> has parentheses internally, but call sites have no business
> knowing that. For example, it would be completely legit to change
> TIMESTAMP_IS_NOBEGIN to be a plain function, and then this would be
> syntactically incorrect.
Oh duh. I've been doing too much Rust development and did this without
thinking. I've attached a patch with a fix.
- Joe Koshakow
From d3543e7c410f83cbe3f3f3df9715025bc767fc5f Mon Sep 17 00:00:00 2001
From: Joseph Koshakow <kosh...@gmail.com>
Date: Sat, 18 Mar 2023 13:59:34 -0400
Subject: [PATCH 3/3] Add infinite interval values
This commit adds positive and negative infinite values to the interval
data type. The entire range of intervals with INT_MAX months or INT_MIN
months are reserved for infinite values. This makes checking finiteness
much simpler.
Ashutosh Bapat and Joe Koshakow and Jian He
---
doc/src/sgml/datatype.sgml | 2 +-
doc/src/sgml/func.sgml | 5 +-
src/backend/utils/adt/date.c | 32 +
src/backend/utils/adt/datetime.c | 2 +
src/backend/utils/adt/formatting.c | 2 +-
src/backend/utils/adt/selfuncs.c | 12 +-
src/backend/utils/adt/timestamp.c | 679 ++++++++++++++++++----
src/include/datatype/timestamp.h | 19 +
src/include/utils/timestamp.h | 3 +
src/test/regress/expected/horology.out | 6 +-
src/test/regress/expected/interval.out | 559 ++++++++++++++++--
src/test/regress/expected/timestamp.out | 62 ++
src/test/regress/expected/timestamptz.out | 62 ++
src/test/regress/sql/horology.sql | 6 +-
src/test/regress/sql/interval.sql | 170 +++++-
src/test/regress/sql/timestamp.sql | 19 +
src/test/regress/sql/timestamptz.sql | 18 +
17 files changed, 1454 insertions(+), 204 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index faf0d74104..694af4000d 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2321,7 +2321,7 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
</row>
<row>
<entry><literal>infinity</literal></entry>
- <entry><type>date</type>, <type>timestamp</type></entry>
+ <entry><type>date</type>, <type>timestamp</type>, <type>interval</type></entry>
<entry>later than all other time stamps</entry>
</row>
<row>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a3a13b895f..33fa3e6670 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9472,7 +9472,7 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
<returnvalue>boolean</returnvalue>
</para>
<para>
- Test for finite interval (currently always true)
+ Test for finite interval (not +/-infinity)
</para>
<para>
<literal>isfinite(interval '4 hours')</literal>
@@ -10369,7 +10369,8 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
When the input value is +/-Infinity, <function>extract</function> returns
+/-Infinity for monotonically-increasing fields (<literal>epoch</literal>,
<literal>julian</literal>, <literal>year</literal>, <literal>isoyear</literal>,
- <literal>decade</literal>, <literal>century</literal>, and <literal>millennium</literal>).
+ <literal>decade</literal>, <literal>century</literal>, and <literal>millennium</literal>
+ for all types and <literal>hour</literal> and <literal>day</literal> just for <type>interval</type>).
For other fields, NULL is returned. <productname>PostgreSQL</productname>
versions before 9.6 returned zero for all cases of infinite input.
</para>
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index a163fbb4ab..5b4ba76eed 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -2023,6 +2023,11 @@ interval_time(PG_FUNCTION_ARGS)
TimeADT result;
int64 days;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("time out of range")));
+
result = span->time;
if (result >= USECS_PER_DAY)
{
@@ -2067,6 +2072,11 @@ time_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeADT result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite interval to time")));
+
result = time + span->time;
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
@@ -2085,6 +2095,11 @@ time_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeADT result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot subtract infinite interval from time")));
+
result = time - span->time;
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
@@ -2599,6 +2614,11 @@ timetz_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeTzADT *result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite interval to time")));
+
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
result->time = time->time + span->time;
@@ -2621,6 +2641,11 @@ timetz_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeTzADT *result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot subtract infinite interval from time")));
+
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
result->time = time->time - span->time;
@@ -3113,6 +3138,13 @@ timetz_izone(PG_FUNCTION_ARGS)
TimeTzADT *result;
int tz;
+ if (INTERVAL_NOT_FINITE(zone))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("interval time zone \"%s\" must be finite",
+ DatumGetCString(DirectFunctionCall1(interval_out,
+ PointerGetDatum(zone))))));
+
if (zone->month != 0 || zone->day != 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 64f28a85b0..f77bf1d5eb 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -3550,6 +3550,8 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
case DTK_STRING:
case DTK_SPECIAL:
type = DecodeUnits(i, field[i], &uval);
+ if (type == UNKNOWN_FIELD)
+ type = DecodeSpecial(i, field[i], &uval);
if (type == IGNORE_DTF)
continue;
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index e6246dc44b..8c739833dc 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -4145,7 +4145,7 @@ interval_to_char(PG_FUNCTION_ARGS)
struct pg_itm tt,
*itm = &tt;
- if (VARSIZE_ANY_EXHDR(fmt) <= 0)
+ if (VARSIZE_ANY_EXHDR(fmt) <= 0 || INTERVAL_NOT_FINITE(it))
PG_RETURN_NULL();
ZERO_tmtc(&tmtc);
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index fe37e65af0..e2cc6049f2 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -4795,17 +4795,7 @@ convert_timevalue_to_scalar(Datum value, Oid typid, bool *failure)
case DATEOID:
return date2timestamp_no_overflow(DatumGetDateADT(value));
case INTERVALOID:
- {
- Interval *interval = DatumGetIntervalP(value);
-
- /*
- * Convert the month part of Interval to days using assumed
- * average month length of 365.25/12.0 days. Not too
- * accurate, but plenty good enough for our purposes.
- */
- return interval->time + interval->day * (double) USECS_PER_DAY +
- interval->month * ((DAYS_PER_YEAR / (double) MONTHS_PER_YEAR) * USECS_PER_DAY);
- }
+ return interval2timestamp_no_overflow(DatumGetIntervalP(value));
case TIMEOID:
return DatumGetTimeADT(value);
case TIMETZOID:
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index b79af28ae3..c006e27dc0 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -18,6 +18,7 @@
#include <ctype.h>
#include <math.h>
#include <limits.h>
+#include <float.h>
#include <sys/time.h>
#include "access/xact.h"
@@ -80,6 +81,7 @@ static bool AdjustIntervalForTypmod(Interval *interval, int32 typmod,
static TimestampTz timestamp2timestamptz(Timestamp timestamp);
static Timestamp timestamptz2timestamp(TimestampTz timestamp);
+static void interval_um_internal(Interval *interval, Interval *result);
/* common code for timestamptypmodin and timestamptztypmodin */
static int32
@@ -940,6 +942,14 @@ interval_in(PG_FUNCTION_ARGS)
errmsg("interval out of range")));
break;
+ case DTK_LATE:
+ INTERVAL_NOEND(result);
+ break;
+
+ case DTK_EARLY:
+ INTERVAL_NOBEGIN(result);
+ break;
+
default:
elog(ERROR, "unexpected dtype %d while parsing interval \"%s\"",
dtype, str);
@@ -962,8 +972,13 @@ interval_out(PG_FUNCTION_ARGS)
*itm = &tt;
char buf[MAXDATELEN + 1];
- interval2itm(*span, itm);
- EncodeInterval(itm, IntervalStyle, buf);
+ if (INTERVAL_NOT_FINITE(span))
+ EncodeSpecialInterval(span, buf);
+ else
+ {
+ interval2itm(*span, itm);
+ EncodeInterval(itm, IntervalStyle, buf);
+ }
result = pstrdup(buf);
PG_RETURN_CSTRING(result);
@@ -1349,6 +1364,13 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod,
INT64CONST(0)
};
+ /*
+ * Infinite interval after being subjected to typmod conversion remains
+ * infinite.
+ */
+ if (INTERVAL_NOT_FINITE(interval))
+ return true;
+
/*
* Unspecified range and precision? Then not necessary to adjust. Setting
* typmod to -1 is the convention for all data types.
@@ -1522,6 +1544,7 @@ make_interval(PG_FUNCTION_ARGS)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("interval out of range")));
+
result->day = days;
if (pg_mul_add_s32_overflow(weeks, 7, &result->day))
ereport(ERROR,
@@ -1539,6 +1562,11 @@ make_interval(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("interval out of range")));
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
PG_RETURN_INTERVAL_P(result);
}
@@ -1556,6 +1584,17 @@ EncodeSpecialTimestamp(Timestamp dt, char *str)
elog(ERROR, "invalid argument for EncodeSpecialTimestamp");
}
+void
+EncodeSpecialInterval(Interval *interval, char *str)
+{
+ if (INTERVAL_IS_NOBEGIN(interval))
+ strcpy(str, EARLY);
+ else if (INTERVAL_IS_NOEND(interval))
+ strcpy(str, LATE);
+ else /* shouldn't happen */
+ elog(ERROR, "invalid argument for EncodeSpecialInterval");
+}
+
Datum
now(PG_FUNCTION_ARGS)
{
@@ -2043,6 +2082,8 @@ itm2interval(struct pg_itm *itm, Interval *span)
if (pg_add_s64_overflow(span->time, itm->tm_usec,
&span->time))
return -1;
+ if (INTERVAL_NOT_FINITE(span))
+ return -1;
return 0;
}
@@ -2060,6 +2101,8 @@ itmin2interval(struct pg_itm_in *itm_in, Interval *span)
span->month = (int32) total_months;
span->day = itm_in->tm_mday;
span->time = itm_in->tm_usec;
+ if (INTERVAL_NOT_FINITE(span))
+ return -1;
return 0;
}
@@ -2093,7 +2136,9 @@ timestamp_finite(PG_FUNCTION_ARGS)
Datum
interval_finite(PG_FUNCTION_ARGS)
{
- PG_RETURN_BOOL(true);
+ Interval *interval = PG_GETARG_INTERVAL_P(0);
+
+ PG_RETURN_BOOL(!INTERVAL_NOT_FINITE(interval));
}
@@ -2224,6 +2269,29 @@ timestamp_fastcmp(Datum x, Datum y, SortSupport ssup)
}
#endif
+double
+interval2timestamp_no_overflow(Interval *interval)
+{
+ double result;
+
+ if (INTERVAL_IS_NOBEGIN(interval))
+ result = -DBL_MAX;
+ else if (INTERVAL_IS_NOEND(interval))
+ result = DBL_MAX;
+ else
+ {
+ /*
+ * Convert the month part of Interval to days using assumed average
+ * month length of 365.25/12.0 days. Not too accurate, but plenty
+ * good enough for our purposes.
+ */
+ return interval->time + interval->day * (double) USECS_PER_DAY +
+ interval->month * ((DAYS_PER_YEAR / (double) MONTHS_PER_YEAR) * USECS_PER_DAY);
+ }
+
+ return result;
+}
+
Datum
timestamp_sortsupport(PG_FUNCTION_ARGS)
{
@@ -2719,46 +2787,71 @@ timestamp_mi(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
- if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("cannot subtract infinite timestamps")));
-
- if (unlikely(pg_sub_s64_overflow(dt1, dt2, &result->time)))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ /*
+ * Subtracting two infinite timestamps with different signs results in an
+ * infinite interval with the same sign as the left operand. Subtracting
+ * two infinte timestamps with the same sign results in an error.
+ */
+ if (TIMESTAMP_IS_NOBEGIN(dt1))
+ {
+ if (TIMESTAMP_IS_NOBEGIN(dt2))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+ else
+ INTERVAL_NOBEGIN(result);
+ }
+ else if (TIMESTAMP_IS_NOEND(dt1))
+ {
+ if (TIMESTAMP_IS_NOEND(dt2))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+ else
+ INTERVAL_NOEND(result);
+ }
+ else if (TIMESTAMP_IS_NOBEGIN(dt2))
+ INTERVAL_NOEND(result);
+ else if (TIMESTAMP_IS_NOEND(dt2))
+ INTERVAL_NOBEGIN(result);
+ else
+ {
+ if (unlikely(pg_sub_s64_overflow(dt1, dt2, &result->time)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->month = 0;
- result->day = 0;
+ result->month = 0;
+ result->day = 0;
- /*----------
- * This is wrong, but removing it breaks a lot of regression tests.
- * For example:
- *
- * test=> SET timezone = 'EST5EDT';
- * test=> SELECT
- * test-> ('2005-10-30 13:22:00-05'::timestamptz -
- * test(> '2005-10-29 13:22:00-04'::timestamptz);
- * ?column?
- * ----------------
- * 1 day 01:00:00
- * (1 row)
- *
- * so adding that to the first timestamp gets:
- *
- * test=> SELECT
- * test-> ('2005-10-29 13:22:00-04'::timestamptz +
- * test(> ('2005-10-30 13:22:00-05'::timestamptz -
- * test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
- * timezone
- * --------------------
- * 2005-10-30 14:22:00
- * (1 row)
- *----------
- */
- result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours,
- IntervalPGetDatum(result)));
+ /*----------
+ * This is wrong, but removing it breaks a lot of regression tests.
+ * For example:
+ *
+ * test=> SET timezone = 'EST5EDT';
+ * test=> SELECT
+ * test-> ('2005-10-30 13:22:00-05'::timestamptz -
+ * test(> '2005-10-29 13:22:00-04'::timestamptz);
+ * ?column?
+ * ----------------
+ * 1 day 01:00:00
+ * (1 row)
+ *
+ * so adding that to the first timestamp gets:
+ *
+ * test=> SELECT
+ * test-> ('2005-10-29 13:22:00-04'::timestamptz +
+ * test(> ('2005-10-30 13:22:00-05'::timestamptz -
+ * test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
+ * timezone
+ * --------------------
+ * 2005-10-30 14:22:00
+ * (1 row)
+ *----------
+ */
+ result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours,
+ IntervalPGetDatum(result)));
+ }
PG_RETURN_INTERVAL_P(result);
}
@@ -2788,6 +2881,9 @@ interval_justify_interval(PG_FUNCTION_ARGS)
result->day = span->day;
result->time = span->time;
+ if (INTERVAL_NOT_FINITE(result))
+ PG_RETURN_INTERVAL_P(result);
+
/* pre-justify days if it might prevent overflow */
if ((result->day > 0 && result->time > 0) ||
(result->day < 0 && result->time < 0))
@@ -2840,6 +2936,11 @@ interval_justify_interval(PG_FUNCTION_ARGS)
result->day++;
}
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
PG_RETURN_INTERVAL_P(result);
}
@@ -2863,6 +2964,9 @@ interval_justify_hours(PG_FUNCTION_ARGS)
result->day = span->day;
result->time = span->time;
+ if (INTERVAL_NOT_FINITE(result))
+ PG_RETURN_INTERVAL_P(result);
+
TMODULO(result->time, wholeday, USECS_PER_DAY);
if (pg_add_s32_overflow(result->day, wholeday, &result->day))
ereport(ERROR,
@@ -2901,6 +3005,9 @@ interval_justify_days(PG_FUNCTION_ARGS)
result->day = span->day;
result->time = span->time;
+ if (INTERVAL_NOT_FINITE(result))
+ PG_RETURN_INTERVAL_P(result);
+
wholemonth = result->day / DAYS_PER_MONTH;
result->day -= wholemonth * DAYS_PER_MONTH;
if (pg_add_s32_overflow(result->month, wholemonth, &result->month))
@@ -2919,6 +3026,11 @@ interval_justify_days(PG_FUNCTION_ARGS)
result->month++;
}
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
PG_RETURN_INTERVAL_P(result);
}
@@ -2939,7 +3051,30 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
Timestamp result;
- if (TIMESTAMP_NOT_FINITE(timestamp))
+ /*
+ * Adding two infinites with the same sign results in an infinite
+ * timestamp with the same sign. Adding two infintes with different signs
+ * results in an error.
+ */
+ if (INTERVAL_IS_NOBEGIN(span))
+ {
+ if (TIMESTAMP_IS_NOEND(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ else
+ TIMESTAMP_NOBEGIN(result);
+ }
+ else if (INTERVAL_IS_NOEND(span))
+ {
+ if (TIMESTAMP_IS_NOBEGIN(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ else
+ TIMESTAMP_NOEND(result);
+ }
+ else if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
else
{
@@ -3018,9 +3153,7 @@ timestamp_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
Interval tspan;
- tspan.month = -span->month;
- tspan.day = -span->day;
- tspan.time = -span->time;
+ interval_um_internal(span, &tspan);
return DirectFunctionCall2(timestamp_pl_interval,
TimestampGetDatum(timestamp),
@@ -3047,7 +3180,30 @@ timestamptz_pl_interval_internal(TimestampTz timestamp,
TimestampTz result;
int tz;
- if (TIMESTAMP_NOT_FINITE(timestamp))
+ /*
+ * Adding two infinites with the same sign results in an infinite
+ * timestamp with the same sign. Adding two infintes with different signs
+ * results in an error.
+ */
+ if (INTERVAL_IS_NOBEGIN(span))
+ {
+ if (TIMESTAMP_IS_NOEND(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ else
+ TIMESTAMP_NOBEGIN(result);
+ }
+ else if (INTERVAL_IS_NOEND(span))
+ {
+ if (TIMESTAMP_IS_NOBEGIN(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ else
+ TIMESTAMP_NOEND(result);
+ }
+ else if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
else
{
@@ -3137,9 +3293,7 @@ timestamptz_mi_interval_internal(TimestampTz timestamp,
{
Interval tspan;
- tspan.month = -span->month;
- tspan.day = -span->day;
- tspan.time = -span->time;
+ interval_um_internal(span, &tspan);
return timestamptz_pl_interval_internal(timestamp, &tspan, attimezone);
}
@@ -3190,6 +3344,33 @@ timestamptz_mi_interval_at_zone(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMP(timestamptz_mi_interval_internal(timestamp, span, attimezone));
}
+/* Negates the given interval */
+static void
+interval_um_internal(Interval *interval, Interval *result)
+{
+ if (INTERVAL_IS_NOBEGIN(interval))
+ INTERVAL_NOEND(result);
+ else if (INTERVAL_IS_NOEND(interval))
+ INTERVAL_NOBEGIN(result);
+ else if (interval->time == PG_INT64_MIN || interval->day == PG_INT32_MIN || interval->month == PG_INT32_MIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range"),
+ errcontext("while negating an interval")));
+ else
+ {
+ result->time = -interval->time;
+ result->day = -interval->day;
+ result->month = -interval->month;
+
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range"),
+ errcontext("while negating an interval")));
+ }
+}
+
Datum
interval_um(PG_FUNCTION_ARGS)
{
@@ -3197,23 +3378,7 @@ interval_um(PG_FUNCTION_ARGS)
Interval *result;
result = (Interval *) palloc(sizeof(Interval));
-
- result->time = -interval->time;
- /* overflow check copied from int4um */
- if (interval->time != 0 && SAMESIGN(result->time, interval->time))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
- result->day = -interval->day;
- if (interval->day != 0 && SAMESIGN(result->day, interval->day))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
- result->month = -interval->month;
- if (interval->month != 0 && SAMESIGN(result->month, interval->month))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ interval_um_internal(interval, result);
PG_RETURN_INTERVAL_P(result);
}
@@ -3257,27 +3422,60 @@ interval_pl(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
- result->month = span1->month + span2->month;
- /* overflow check copied from int4pl */
- if (SAMESIGN(span1->month, span2->month) &&
- !SAMESIGN(result->month, span1->month))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ /*
+ * Adding two infinite intervals with the same signs results in an
+ * infinite interval with the same sign. Adding two infinte intervals with
+ * different signs results in an error.
+ */
+ if (INTERVAL_IS_NOBEGIN(span1))
+ {
+ if (INTERVAL_IS_NOEND(span2))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ else
+ INTERVAL_NOBEGIN(result);
+ }
+ else if (INTERVAL_IS_NOEND(span1))
+ {
+ if (INTERVAL_IS_NOBEGIN(span2))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ else
+ INTERVAL_NOEND(result);
+ }
+ else if (INTERVAL_NOT_FINITE(span2))
+ memcpy(result, span2, sizeof(Interval));
+ else
+ {
+ result->month = span1->month + span2->month;
+ /* overflow check copied from int4pl */
+ if (SAMESIGN(span1->month, span2->month) &&
+ !SAMESIGN(result->month, span1->month))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->day = span1->day + span2->day;
- if (SAMESIGN(span1->day, span2->day) &&
- !SAMESIGN(result->day, span1->day))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ result->day = span1->day + span2->day;
+ if (SAMESIGN(span1->day, span2->day) &&
+ !SAMESIGN(result->day, span1->day))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->time = span1->time + span2->time;
- if (SAMESIGN(span1->time, span2->time) &&
- !SAMESIGN(result->time, span1->time))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ result->time = span1->time + span2->time;
+ if (SAMESIGN(span1->time, span2->time) &&
+ !SAMESIGN(result->time, span1->time))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ }
PG_RETURN_INTERVAL_P(result);
}
@@ -3291,27 +3489,62 @@ interval_mi(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
- result->month = span1->month - span2->month;
- /* overflow check copied from int4mi */
- if (!SAMESIGN(span1->month, span2->month) &&
- !SAMESIGN(result->month, span1->month))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ /*
+ * Subtracting two infinite intervals with different signs results in an
+ * infinite interval with the same sign as the left operand. Subtracting
+ * two infinte intervals with the same sign results in an error.
+ */
+ if (INTERVAL_IS_NOBEGIN(span1))
+ {
+ if (INTERVAL_IS_NOBEGIN(span2))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ else
+ INTERVAL_NOBEGIN(result);
+ }
+ else if (INTERVAL_IS_NOEND(span1))
+ {
+ if (INTERVAL_IS_NOEND(span2))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ else
+ INTERVAL_NOEND(result);
+ }
+ else if (INTERVAL_IS_NOBEGIN(span2))
+ INTERVAL_NOEND(result);
+ else if (INTERVAL_IS_NOEND(span2))
+ INTERVAL_NOBEGIN(result);
+ else
+ {
+ result->month = span1->month - span2->month;
+ /* overflow check copied from int4mi */
+ if (!SAMESIGN(span1->month, span2->month) &&
+ !SAMESIGN(result->month, span1->month))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->day = span1->day - span2->day;
- if (!SAMESIGN(span1->day, span2->day) &&
- !SAMESIGN(result->day, span1->day))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ result->day = span1->day - span2->day;
+ if (!SAMESIGN(span1->day, span2->day) &&
+ !SAMESIGN(result->day, span1->day))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->time = span1->time - span2->time;
- if (!SAMESIGN(span1->time, span2->time) &&
- !SAMESIGN(result->time, span1->time))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ result->time = span1->time - span2->time;
+ if (!SAMESIGN(span1->time, span2->time) &&
+ !SAMESIGN(result->time, span1->time))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ }
PG_RETURN_INTERVAL_P(result);
}
@@ -3336,6 +3569,24 @@ interval_mul(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ if (isnan(factor))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ /*
+ * Multiplying infinite interval by finite number keeps it infinite but
+ * may change the sign.
+ */
+ if (INTERVAL_NOT_FINITE(span))
+ {
+ if (factor < 0.0)
+ interval_um_internal(span, result);
+ else
+ memcpy(result, span, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result_double = span->month * factor;
if (isnan(result_double) ||
result_double > INT_MAX || result_double < INT_MIN)
@@ -3396,6 +3647,11 @@ interval_mul(PG_FUNCTION_ARGS)
errmsg("interval out of range")));
result->time = (int64) result_double;
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
PG_RETURN_INTERVAL_P(result);
}
@@ -3427,6 +3683,29 @@ interval_div(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
+ if (isnan(factor))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ /*
+ * Dividing infinite interval by finite number keeps it infinite but may
+ * change the sign.
+ */
+ if (INTERVAL_NOT_FINITE(span))
+ {
+ if (isinf(factor))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ if (factor < 0.0)
+ interval_um_internal(span, result);
+ else
+ memcpy(result, span, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result->month = (int32) (span->month / factor);
result->day = (int32) (span->day / factor);
@@ -3448,6 +3727,11 @@ interval_div(PG_FUNCTION_ARGS)
result->day += (int32) month_remainder_days;
result->time = rint(span->time / factor + sec_remainder * USECS_PER_SEC);
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
PG_RETURN_INTERVAL_P(result);
}
@@ -3731,8 +4015,35 @@ timestamp_age(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
- if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 &&
- timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
+ /*
+ * Subtracting two infinite timestamps with different signs results in an
+ * infinite interval with the same sign as the left operand. Subtracting
+ * two infinte timestamps with the same sign results in an error.
+ */
+ if (TIMESTAMP_IS_NOBEGIN(dt1))
+ {
+ if (TIMESTAMP_IS_NOBEGIN(dt2))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+ else
+ INTERVAL_NOBEGIN(result);
+ }
+ else if (TIMESTAMP_IS_NOEND(dt1))
+ {
+ if (TIMESTAMP_IS_NOEND(dt2))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+ else
+ INTERVAL_NOEND(result);
+ }
+ else if (TIMESTAMP_IS_NOBEGIN(dt2))
+ INTERVAL_NOEND(result);
+ else if (TIMESTAMP_IS_NOEND(dt2))
+ INTERVAL_NOBEGIN(result);
+ else if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 &&
+ timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
{
/* form the symbolic difference */
tm->tm_usec = fsec1 - fsec2;
@@ -3851,8 +4162,35 @@ timestamptz_age(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
- if (timestamp2tm(dt1, &tz1, tm1, &fsec1, NULL, NULL) == 0 &&
- timestamp2tm(dt2, &tz2, tm2, &fsec2, NULL, NULL) == 0)
+ /*
+ * Subtracting two infinite timestamps with different signs results in an
+ * infinite interval with the same sign as the left operand. Subtracting
+ * two infinte timestamps with the same sign results in an error.
+ */
+ if (TIMESTAMP_IS_NOBEGIN(dt1))
+ {
+ if (TIMESTAMP_IS_NOBEGIN(dt2))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+ else
+ INTERVAL_NOBEGIN(result);
+ }
+ else if (TIMESTAMP_IS_NOEND(dt1))
+ {
+ if (TIMESTAMP_IS_NOEND(dt2))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+ else
+ INTERVAL_NOEND(result);
+ }
+ else if (TIMESTAMP_IS_NOBEGIN(dt2))
+ INTERVAL_NOEND(result);
+ else if (TIMESTAMP_IS_NOEND(dt2))
+ INTERVAL_NOBEGIN(result);
+ else if (timestamp2tm(dt1, &tz1, tm1, &fsec1, NULL, NULL) == 0 &&
+ timestamp2tm(dt2, &tz2, tm2, &fsec2, NULL, NULL) == 0)
{
/* form the symbolic difference */
tm->tm_usec = fsec1 - fsec2;
@@ -3977,6 +4315,11 @@ timestamp_bin(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("origin out of range")));
+ if (INTERVAL_NOT_FINITE(stride))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamps cannot be binned into infinite intervals")));
+
if (stride->month != 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4160,6 +4503,11 @@ timestamptz_bin(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("origin out of range")));
+ if (INTERVAL_NOT_FINITE(stride))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamps cannot be binned into infinite intervals")));
+
if (stride->month != 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4398,6 +4746,12 @@ interval_trunc(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ if (INTERVAL_NOT_FINITE(interval))
+ {
+ memcpy(result, interval, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
VARSIZE_ANY_EXHDR(units),
false);
@@ -4742,7 +5096,7 @@ timestamp_part_common(PG_FUNCTION_ARGS, bool retnumeric)
TIMESTAMP_IS_NOBEGIN(timestamp),
false);
- if (r)
+ if (r != 0.0)
{
if (retnumeric)
{
@@ -5016,7 +5370,7 @@ timestamptz_part_common(PG_FUNCTION_ARGS, bool retnumeric)
TIMESTAMP_IS_NOBEGIN(timestamp),
true);
- if (r)
+ if (r != 0.0)
{
if (retnumeric)
{
@@ -5256,6 +5610,59 @@ extract_timestamptz(PG_FUNCTION_ARGS)
return timestamptz_part_common(fcinfo, true);
}
+/*
+ * NonFiniteIntervalPart
+ *
+ * Used by interval_part when extracting from infinite
+ * interval. Returns +/-Infinity if that is the appropriate result,
+ * otherwise returns zero (which should be taken as meaning to return NULL).
+ *
+ * Errors thrown here for invalid units should exactly match those that
+ * would be thrown in the calling functions, else there will be unexpected
+ * discrepancies between finite- and infinite-input cases.
+ */
+static float8
+NonFiniteIntervalPart(int type, int unit, char *lowunits,
+ bool isNegative, bool isTz)
+{
+ if ((type != UNITS) && (type != RESERV))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unit \"%s\" not recognized for type %s",
+ lowunits, format_type_be(INTERVALOID))));
+
+ switch (unit)
+ {
+ /* Oscillating units */
+ case DTK_MICROSEC:
+ case DTK_MILLISEC:
+ case DTK_SECOND:
+ case DTK_MINUTE:
+ case DTK_MONTH:
+ case DTK_QUARTER:
+ return 0.0;
+
+ /* Monotonically-increasing units */
+ case DTK_HOUR:
+ case DTK_DAY:
+ case DTK_YEAR:
+ case DTK_DECADE:
+ case DTK_CENTURY:
+ case DTK_MILLENNIUM:
+ case DTK_EPOCH:
+ if (isNegative)
+ return -get_float8_infinity();
+ else
+ return get_float8_infinity();
+
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("unit \"%s\" not supported for type %s",
+ lowunits, format_type_be(INTERVALOID))));
+ return 0.0; /* keep compiler quiet */
+ }
+}
/* interval_part() and extract_interval()
* Extract specified field from interval.
@@ -5280,6 +5687,34 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
if (type == UNKNOWN_FIELD)
type = DecodeSpecial(0, lowunits, &val);
+ if (INTERVAL_NOT_FINITE(interval))
+ {
+ double r = NonFiniteIntervalPart(type, val, lowunits,
+ INTERVAL_IS_NOBEGIN(interval),
+ false);
+
+ if (r != 0.0)
+ {
+ if (retnumeric)
+ {
+ if (r < 0)
+ return DirectFunctionCall3(numeric_in,
+ CStringGetDatum("-Infinity"),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1));
+ else if (r > 0)
+ return DirectFunctionCall3(numeric_in,
+ CStringGetDatum("Infinity"),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1));
+ }
+ else
+ PG_RETURN_FLOAT8(r);
+ }
+ else
+ PG_RETURN_NULL();
+ }
+
if (type == UNITS)
{
interval2itm(*interval, tm);
@@ -5522,6 +5957,13 @@ timestamp_izone(PG_FUNCTION_ARGS)
if (TIMESTAMP_NOT_FINITE(timestamp))
PG_RETURN_TIMESTAMPTZ(timestamp);
+ if (INTERVAL_NOT_FINITE(zone))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("interval time zone \"%s\" must be finite",
+ DatumGetCString(DirectFunctionCall1(interval_out,
+ PointerGetDatum(zone))))));
+
if (zone->month != 0 || zone->day != 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -5752,6 +6194,13 @@ timestamptz_izone(PG_FUNCTION_ARGS)
if (TIMESTAMP_NOT_FINITE(timestamp))
PG_RETURN_TIMESTAMP(timestamp);
+ if (INTERVAL_NOT_FINITE(zone))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("interval time zone \"%s\" must be finite",
+ DatumGetCString(DirectFunctionCall1(interval_out,
+ PointerGetDatum(zone))))));
+
if (zone->month != 0 || zone->day != 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -5818,6 +6267,11 @@ generate_series_timestamp(PG_FUNCTION_ARGS)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("step size cannot equal zero")));
+ if (INTERVAL_NOT_FINITE((&fctx->step)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot be infinite")));
+
funcctx->user_fctx = fctx;
MemoryContextSwitchTo(oldcontext);
}
@@ -5900,6 +6354,11 @@ generate_series_timestamptz_internal(FunctionCallInfo fcinfo)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("step size cannot equal zero")));
+ if (INTERVAL_NOT_FINITE((&fctx->step)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot be infinite")));
+
funcctx->user_fctx = fctx;
MemoryContextSwitchTo(oldcontext);
}
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index ab8ccf89ca..5caf5dcfbe 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -167,6 +167,25 @@ struct pg_itm_in
#define TIMESTAMP_NOT_FINITE(j) (TIMESTAMP_IS_NOBEGIN(j) || TIMESTAMP_IS_NOEND(j))
+#define INTERVAL_NOBEGIN(i) \
+ do { \
+ (i->time) = PG_INT64_MIN; \
+ (i->day) = PG_INT32_MIN; \
+ (i->month) = PG_INT32_MIN; \
+ } while (0)
+
+#define INTERVAL_IS_NOBEGIN(i) ((i->month) == PG_INT32_MIN)
+
+#define INTERVAL_NOEND(i) \
+ do { \
+ (i->time) = PG_INT64_MAX; \
+ (i->day) = PG_INT32_MAX; \
+ (i->month) = PG_INT32_MAX; \
+ } while (0)
+
+#define INTERVAL_IS_NOEND(i) ((i->month) == PG_INT32_MAX)
+
+#define INTERVAL_NOT_FINITE(i) (INTERVAL_IS_NOBEGIN(i) || INTERVAL_IS_NOEND(i))
/*
* Julian date support.
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index edd59dc432..bcb54a7238 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -114,6 +114,7 @@ extern int timestamp2tm(Timestamp dt, int *tzp, struct pg_tm *tm,
fsec_t *fsec, const char **tzn, pg_tz *attimezone);
extern void dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec);
+extern void EncodeSpecialInterval(Interval *interval, char *str);
extern void 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);
@@ -123,6 +124,8 @@ extern void GetEpochTime(struct pg_tm *tm);
extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2);
+extern double interval2timestamp_no_overflow(Interval *interval);
+
/* timestamp comparison works for timestamptz also */
#define timestamptz_cmp_internal(dt1,dt2) timestamp_cmp_internal(dt1, dt2)
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index e63e5b30fe..33842f9fbe 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -1048,6 +1048,7 @@ SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract"
FROM TIMESTAMP_TBL t, INTERVAL_TBL i
WHERE t.d1 BETWEEN '1990-01-01' AND '2001-01-01'
AND i.f1 BETWEEN '00:00' AND '23:00'
+ AND isfinite(i.f1)
ORDER BY 1,2;
t | i | add | subtract
----------------------------+-----------+----------------------------+----------------------------
@@ -1159,6 +1160,7 @@ SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract"
SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
FROM TIME_TBL t, INTERVAL_TBL i
+ WHERE isfinite(i.f1)
ORDER BY 1,2;
t | i | add | subtract
-------------+-------------------------------+-------------+-------------
@@ -1266,6 +1268,7 @@ SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
FROM TIMETZ_TBL t, INTERVAL_TBL i
+ WHERE isfinite(i.f1)
ORDER BY 1,2;
t | i | add | subtract
----------------+-------------------------------+----------------+----------------
@@ -1541,6 +1544,7 @@ SELECT f1 AS "timestamp"
SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus
FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
+ WHERE isfinite(t.f1)
ORDER BY plus, "timestamp", "interval";
timestamp | interval | plus
------------------------------+-------------------------------+------------------------------
@@ -1708,7 +1712,7 @@ SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus
SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 - t.f1 AS minus
FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
- WHERE isfinite(d.f1)
+ WHERE isfinite(t.f1)
ORDER BY minus, "timestamp", "interval";
timestamp | interval | minus
------------------------------+-------------------------------+------------------------------
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 27bfb8ba9b..0adfe5f9fb 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -52,6 +52,18 @@ SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
9 years 1 mon -12 days +13:14:00
(1 row)
+SELECT INTERVAL 'infinity' AS "eternity";
+ eternity
+----------
+ infinity
+(1 row)
+
+SELECT INTERVAL '-infinity' AS "beginning of time";
+ beginning of time
+-------------------
+ -infinity
+(1 row)
+
CREATE TABLE INTERVAL_TBL (f1 interval);
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
@@ -63,6 +75,8 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity');
-- badly formatted interval
INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
ERROR: invalid input syntax for type interval: "badly formatted interval"
@@ -117,7 +131,9 @@ SELECT * FROM INTERVAL_TBL;
6 years
5 mons
5 mons 12:00:00
-(10 rows)
+ infinity
+ -infinity
+(12 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
@@ -132,7 +148,9 @@ SELECT * FROM INTERVAL_TBL
6 years
5 mons
5 mons 12:00:00
-(9 rows)
+ infinity
+ -infinity
+(11 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours';
@@ -141,7 +159,8 @@ SELECT * FROM INTERVAL_TBL
00:01:00
05:00:00
-00:00:14
-(3 rows)
+ -infinity
+(4 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 < interval '@ 1 day';
@@ -150,7 +169,8 @@ SELECT * FROM INTERVAL_TBL
00:01:00
05:00:00
-00:00:14
-(3 rows)
+ -infinity
+(4 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 = interval '@ 34 years';
@@ -168,7 +188,8 @@ SELECT * FROM INTERVAL_TBL
6 years
5 mons
5 mons 12:00:00
-(5 rows)
+ infinity
+(6 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago';
@@ -183,7 +204,8 @@ SELECT * FROM INTERVAL_TBL
6 years
5 mons
5 mons 12:00:00
-(9 rows)
+ infinity
+(10 rows)
SELECT r1.*, r2.*
FROM INTERVAL_TBL r1, INTERVAL_TBL r2
@@ -191,27 +213,35 @@ SELECT r1.*, r2.*
ORDER BY r1.f1, r2.f1;
f1 | f1
-----------------+-----------------
+ -00:00:14 | -infinity
+ 00:01:00 | -infinity
00:01:00 | -00:00:14
+ 05:00:00 | -infinity
05:00:00 | -00:00:14
05:00:00 | 00:01:00
+ 1 day 02:03:04 | -infinity
1 day 02:03:04 | -00:00:14
1 day 02:03:04 | 00:01:00
1 day 02:03:04 | 05:00:00
+ 10 days | -infinity
10 days | -00:00:14
10 days | 00:01:00
10 days | 05:00:00
10 days | 1 day 02:03:04
+ 3 mons | -infinity
3 mons | -00:00:14
3 mons | 00:01:00
3 mons | 05:00:00
3 mons | 1 day 02:03:04
3 mons | 10 days
+ 5 mons | -infinity
5 mons | -00:00:14
5 mons | 00:01:00
5 mons | 05:00:00
5 mons | 1 day 02:03:04
5 mons | 10 days
5 mons | 3 mons
+ 5 mons 12:00:00 | -infinity
5 mons 12:00:00 | -00:00:14
5 mons 12:00:00 | 00:01:00
5 mons 12:00:00 | 05:00:00
@@ -219,6 +249,7 @@ SELECT r1.*, r2.*
5 mons 12:00:00 | 10 days
5 mons 12:00:00 | 3 mons
5 mons 12:00:00 | 5 mons
+ 6 years | -infinity
6 years | -00:00:14
6 years | 00:01:00
6 years | 05:00:00
@@ -227,6 +258,7 @@ SELECT r1.*, r2.*
6 years | 3 mons
6 years | 5 mons
6 years | 5 mons 12:00:00
+ 34 years | -infinity
34 years | -00:00:14
34 years | 00:01:00
34 years | 05:00:00
@@ -236,16 +268,27 @@ SELECT r1.*, r2.*
34 years | 5 mons
34 years | 5 mons 12:00:00
34 years | 6 years
-(45 rows)
+ infinity | -infinity
+ infinity | -00:00:14
+ infinity | 00:01:00
+ infinity | 05:00:00
+ infinity | 1 day 02:03:04
+ infinity | 10 days
+ infinity | 3 mons
+ infinity | 5 mons
+ infinity | 5 mons 12:00:00
+ infinity | 6 years
+ infinity | 34 years
+(66 rows)
-- Test intervals that are large enough to overflow 64 bits in comparisons
CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval);
INSERT INTO INTERVAL_TBL_OF (f1) VALUES
- ('2147483647 days 2147483647 months'),
- ('2147483647 days -2147483648 months'),
+ ('2147483647 days 2147483646 months'),
+ ('2147483647 days -2147483647 months'),
('1 year'),
- ('-2147483648 days 2147483647 months'),
- ('-2147483648 days -2147483648 months');
+ ('-2147483648 days 2147483646 months'),
+ ('-2147483648 days -2147483647 months');
-- these should fail as out-of-range
INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days');
ERROR: interval field value out of range: "2147483648 days"
@@ -272,16 +315,16 @@ SELECT r1.*, r2.*
ORDER BY r1.f1, r2.f1;
f1 | f1
-------------------------------------------+-------------------------------------------
- -178956970 years -8 mons +2147483647 days | -178956970 years -8 mons -2147483648 days
- 1 year | -178956970 years -8 mons -2147483648 days
- 1 year | -178956970 years -8 mons +2147483647 days
- 178956970 years 7 mons -2147483648 days | -178956970 years -8 mons -2147483648 days
- 178956970 years 7 mons -2147483648 days | -178956970 years -8 mons +2147483647 days
- 178956970 years 7 mons -2147483648 days | 1 year
- 178956970 years 7 mons 2147483647 days | -178956970 years -8 mons -2147483648 days
- 178956970 years 7 mons 2147483647 days | -178956970 years -8 mons +2147483647 days
- 178956970 years 7 mons 2147483647 days | 1 year
- 178956970 years 7 mons 2147483647 days | 178956970 years 7 mons -2147483648 days
+ -178956970 years -7 mons +2147483647 days | -178956970 years -7 mons -2147483648 days
+ 1 year | -178956970 years -7 mons -2147483648 days
+ 1 year | -178956970 years -7 mons +2147483647 days
+ 178956970 years 6 mons -2147483648 days | -178956970 years -7 mons -2147483648 days
+ 178956970 years 6 mons -2147483648 days | -178956970 years -7 mons +2147483647 days
+ 178956970 years 6 mons -2147483648 days | 1 year
+ 178956970 years 6 mons 2147483647 days | -178956970 years -7 mons -2147483648 days
+ 178956970 years 6 mons 2147483647 days | -178956970 years -7 mons +2147483647 days
+ 178956970 years 6 mons 2147483647 days | 1 year
+ 178956970 years 6 mons 2147483647 days | 178956970 years 6 mons -2147483648 days
(10 rows)
CREATE INDEX ON INTERVAL_TBL_OF USING btree (f1);
@@ -296,11 +339,11 @@ SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
f1
-------------------------------------------
- -178956970 years -8 mons -2147483648 days
- -178956970 years -8 mons +2147483647 days
+ -178956970 years -7 mons -2147483648 days
+ -178956970 years -7 mons +2147483647 days
1 year
- 178956970 years 7 mons -2147483648 days
- 178956970 years 7 mons 2147483647 days
+ 178956970 years 6 mons -2147483648 days
+ 178956970 years 6 mons 2147483647 days
(5 rows)
RESET enable_seqscan;
@@ -386,12 +429,14 @@ SELECT * FROM INTERVAL_TBL;
@ 6 years
@ 5 mons
@ 5 mons 12 hours
-(10 rows)
+ infinity
+ -infinity
+(12 rows)
-- test avg(interval), which is somewhat fragile since people have been
-- known to change the allowed input syntax for type interval without
-- updating pg_aggregate.agginitval
-select avg(f1) from interval_tbl;
+select avg(f1) from interval_tbl where isfinite(f1);
avg
-------------------------------------------------
@ 4 years 1 mon 10 days 4 hours 18 mins 23 secs
@@ -429,7 +474,9 @@ SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds') as
SELECT justify_hours(interval '2147483647 days 24 hrs');
ERROR: interval out of range
-SELECT justify_days(interval '2147483647 months 30 days');
+SELECT justify_days(interval '2147483646 months 30 days');
+ERROR: interval out of range
+SELECT justify_days(interval '2147483646 months 60 days');
ERROR: interval out of range
-- test justify_interval()
SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
@@ -450,25 +497,29 @@ SELECT justify_interval(interval '-2147483648 days -24 hrs');
@ 5965232 years 4 mons 9 days ago
(1 row)
-SELECT justify_interval(interval '2147483647 months 30 days');
+SELECT justify_interval(interval '2147483646 months 30 days');
+ERROR: interval out of range
+SELECT justify_interval(interval '2147483646 months 60 days');
ERROR: interval out of range
-SELECT justify_interval(interval '-2147483648 months -30 days');
+SELECT justify_interval(interval '-2147483647 months -30 days');
ERROR: interval out of range
-SELECT justify_interval(interval '2147483647 months 30 days -24 hrs');
+SELECT justify_interval(interval '-2147483647 months -60 days');
+ERROR: interval out of range
+SELECT justify_interval(interval '2147483646 months 30 days -24 hrs');
justify_interval
----------------------------------
- @ 178956970 years 7 mons 29 days
+ @ 178956970 years 6 mons 29 days
(1 row)
-SELECT justify_interval(interval '-2147483648 months -30 days 24 hrs');
+SELECT justify_interval(interval '-2147483647 months -30 days 24 hrs');
justify_interval
--------------------------------------
- @ 178956970 years 8 mons 29 days ago
+ @ 178956970 years 7 mons 29 days ago
(1 row)
-SELECT justify_interval(interval '2147483647 months -30 days 1440 hrs');
+SELECT justify_interval(interval '2147483646 months -30 days 1440 hrs');
ERROR: interval out of range
-SELECT justify_interval(interval '-2147483648 months 30 days -1440 hrs');
+SELECT justify_interval(interval '-2147483647 months 30 days -1440 hrs');
ERROR: interval out of range
-- test fractional second input, and detection of duplicate units
SET DATESTYLE = 'ISO';
@@ -820,8 +871,8 @@ SELECT interval '1 2:03:04.5678' minute to second(2);
SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
(f1 + INTERVAL '1 month')::INTERVAL MONTH::INTERVAL YEAR AS "years"
FROM interval_tbl;
- f1 | minutes | years
------------------+-----------------+----------
+ f1 | minutes | years
+-----------------+-----------------+-----------
00:01:00 | 00:01:00 | 00:00:00
05:00:00 | 05:00:00 | 00:00:00
10 days | 10 days | 00:00:00
@@ -832,7 +883,9 @@ SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
6 years | 6 years | 6 years
5 mons | 5 mons | 00:00:00
5 mons 12:00:00 | 5 mons 12:00:00 | 00:00:00
-(10 rows)
+ infinity | infinity | infinity
+ -infinity | -infinity | -infinity
+(12 rows)
-- test inputting and outputting SQL standard interval literals
SET IntervalStyle TO sql_standard;
@@ -1594,31 +1647,31 @@ select make_interval(-1, -2147483648, -2147483648, -1, -1, -1, -9223372036854.77
ERROR: interval out of range
-- test that INT_MIN number is formatted properly
SET IntervalStyle to postgres;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
interval
--------------------------------------------------------------------
- -178956970 years -8 mons -2147483648 days -2562047788:00:54.775808
+ -178956970 years -7 mons -2147483648 days -2562047788:00:54.775808
(1 row)
SET IntervalStyle to sql_standard;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
interval
---------------------------------------------------
- -178956970-8 -2147483648 -2562047788:00:54.775808
+ -178956970-7 -2147483648 -2562047788:00:54.775808
(1 row)
SET IntervalStyle to iso_8601;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
interval
-----------------------------------------------------
- P-178956970Y-8M-2147483648DT-2562047788H-54.775808S
+ P-178956970Y-7M-2147483648DT-2562047788H-54.775808S
(1 row)
SET IntervalStyle to postgres_verbose;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
interval
------------------------------------------------------------------------------
- @ 178956970 years 8 mons 2147483648 days 2562047788 hours 54.775808 secs ago
+ @ 178956970 years 7 mons 2147483648 days 2562047788 hours 54.775808 secs ago
(1 row)
-- check that '30 days' equals '1 month' according to the hash function
@@ -1697,19 +1750,21 @@ SELECT f1,
EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
EXTRACT(EPOCH FROM f1) AS EPOCH
FROM INTERVAL_TBL;
- f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year | decade | century | millennium | epoch
--------------------------------+-------------+-------------+------------+--------+------+-----+-------+---------+------+--------+---------+------------+-------------------
- @ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60.000000
- @ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000.000000
- @ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000.000000
- @ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400.000000
- @ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000.000000
- @ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14.000000
- @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784.000000
- @ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600.000000
- @ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000.000000
- @ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200.000000
-(10 rows)
+ f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year | decade | century | millennium | epoch
+-------------------------------+-------------+-------------+------------+--------+-----------+-----------+-------+---------+-----------+-----------+-----------+------------+-------------------
+ @ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60.000000
+ @ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000.000000
+ @ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000.000000
+ @ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400.000000
+ @ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000.000000
+ @ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14.000000
+ @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784.000000
+ @ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600.000000
+ @ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000.000000
+ @ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200.000000
+ infinity | | | | | Infinity | Infinity | | | Infinity | Infinity | Infinity | Infinity | Infinity
+ -infinity | | | | | -Infinity | -Infinity | | | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
+(12 rows)
SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
ERROR: unit "fortnight" not recognized for type interval
@@ -1783,7 +1838,9 @@ SELECT f1,
@ 6 years | 0 | 0 | 0 | 189345600
@ 5 mons | 0 | 0 | 0 | 12960000
@ 5 mons 12 hours | 0 | 0 | 0 | 13003200
-(10 rows)
+ infinity | | | | Infinity
+ -infinity | | | | -Infinity
+(12 rows)
-- internal overflow test case
SELECT extract(epoch from interval '1000000000 days');
@@ -1792,3 +1849,383 @@ SELECT extract(epoch from interval '1000000000 days');
86400000000000.000000
(1 row)
+-- infinite intervals
+SELECT interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+ERROR: interval out of range
+LINE 1: SELECT interval '-2147483648 months -2147483648 days -922337...
+ ^
+SELECT interval '2147483647 months 2147483647 days 9223372036854775807 us';
+ERROR: interval out of range
+LINE 1: SELECT interval '2147483647 months 2147483647 days 922337203...
+ ^
+CREATE TABLE INFINITE_INTERVAL_TBL (i interval);
+INSERT INTO INFINITE_INTERVAL_TBL VALUES ('infinity'), ('-infinity'), ('1 year 2 days 3 hours');
+SELECT i, isfinite(i) FROM INFINITE_INTERVAL_TBL;
+ i | isfinite
+-------------------------+----------
+ infinity | f
+ -infinity | f
+ @ 1 year 2 days 3 hours | t
+(3 rows)
+
+SELECT date '1995-08-06' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date '1995-08-06' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date '1995-08-06' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date '1995-08-06' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date 'infinity' + interval '-infinity';
+ERROR: interval out of range
+SELECT date '-infinity' + interval 'infinity';
+ERROR: interval out of range
+SELECT date '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date 'infinity' - interval 'infinity';
+ERROR: interval out of range
+SELECT date 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date '-infinity' - interval '-infinity';
+ERROR: interval out of range
+SELECT interval 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' + interval '-infinity';
+ERROR: interval out of range
+SELECT interval '-infinity' + interval 'infinity';
+ERROR: interval out of range
+SELECT interval '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval 'infinity' + interval '10 days';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' + interval '10 days';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' + interval '1 month 1 day 1 us';
+ERROR: interval out of range
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' + interval '-1 month -1 day -1 us';
+ERROR: interval out of range
+SELECT interval 'infinity' - interval 'infinity';
+ERROR: interval out of range
+SELECT interval 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' - interval '-infinity';
+ERROR: interval out of range
+SELECT interval 'infinity' - interval '10 days';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' - interval '10 days';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' - interval '-1 month -1 day -1 us';
+ERROR: interval out of range
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' - interval '1 month 1 day 1 us';
+ERROR: interval out of range
+SELECT timestamp '1995-08-06 12:30:15' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp '1995-08-06 12:30:15' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '1995-08-06 12:30:15' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '1995-08-06 12:30:15' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp 'infinity' + interval '-infinity';
+ERROR: interval out of range
+SELECT timestamp '-infinity' + interval 'infinity';
+ERROR: interval out of range
+SELECT timestamp '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp 'infinity' - interval 'infinity';
+ERROR: interval out of range
+SELECT timestamp 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '-infinity' - interval '-infinity';
+ERROR: interval out of range
+SELECT timestamptz '1995-08-06 12:30:15' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '1995-08-06 12:30:15' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz '1995-08-06 12:30:15' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz '1995-08-06 12:30:15' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz 'infinity' + interval '-infinity';
+ERROR: interval out of range
+SELECT timestamptz '-infinity' + interval 'infinity';
+ERROR: interval out of range
+SELECT timestamptz '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz 'infinity' - interval 'infinity';
+ERROR: interval out of range
+SELECT timestamptz 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - interval '-infinity';
+ERROR: interval out of range
+SELECT time '11:27:42' + interval 'infinity';
+ERROR: cannot add infinite interval to time
+SELECT time '11:27:42' + interval '-infinity';
+ERROR: cannot add infinite interval to time
+SELECT time '11:27:42' - interval 'infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT time '11:27:42' - interval '-infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT timetz '11:27:42' + interval 'infinity';
+ERROR: cannot add infinite interval to time
+SELECT timetz '11:27:42' + interval '-infinity';
+ERROR: cannot add infinite interval to time
+SELECT timetz '11:27:42' - interval 'infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT timetz '11:27:42' - interval '-infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT lhst.i lhs,
+ rhst.i rhs,
+ lhst.i < rhst.i AS lt,
+ lhst.i <= rhst.i AS le,
+ lhst.i = rhst.i AS eq,
+ lhst.i > rhst.i AS gt,
+ lhst.i >= rhst.i AS ge,
+ lhst.i <> rhst.i AS ne
+ FROM INFINITE_INTERVAL_TBL lhst CROSS JOIN INFINITE_INTERVAL_TBL rhst
+ WHERE NOT isfinite(lhst.i);
+ lhs | rhs | lt | le | eq | gt | ge | ne
+-----------+-------------------------+----+----+----+----+----+----
+ infinity | infinity | f | t | t | f | t | f
+ -infinity | infinity | t | t | f | f | f | t
+ infinity | -infinity | f | f | f | t | t | t
+ -infinity | -infinity | f | t | t | f | t | f
+ infinity | @ 1 year 2 days 3 hours | f | f | f | t | t | t
+ -infinity | @ 1 year 2 days 3 hours | t | t | f | f | f | t
+(6 rows)
+
+SELECT i AS interval,
+ -i AS um,
+ i * 2.0 AS mul,
+ i * -2.0 AS mul_neg,
+ i * 'infinity' AS mul_inf,
+ i * '-infinity' AS mul_inf_neg,
+ i / 3.0 AS div,
+ i / -3.0 AS div_neg
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+ interval | um | mul | mul_neg | mul_inf | mul_inf_neg | div | div_neg
+-----------+-----------+-----------+-----------+-----------+-------------+-----------+-----------
+ infinity | -infinity | infinity | -infinity | infinity | -infinity | infinity | -infinity
+ -infinity | infinity | -infinity | infinity | -infinity | infinity | -infinity | infinity
+(2 rows)
+
+SELECT -interval '-2147483647 months -2147483647 days -9223372036854775807 us';
+ERROR: interval out of range
+CONTEXT: while negating an interval
+SELECT interval 'infinity' * 'nan';
+ERROR: interval out of range
+SELECT interval '-infinity' * 'nan';
+ERROR: interval out of range
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' * 2;
+ERROR: interval out of range
+SELECT interval 'infinity' / 'infinity';
+ERROR: interval out of range
+SELECT interval 'infinity' / '-infinity';
+ERROR: interval out of range
+SELECT interval 'infinity' / 'nan';
+ERROR: interval out of range
+SELECT interval '-infinity' / 'infinity';
+ERROR: interval out of range
+SELECT interval '-infinity' / '-infinity';
+ERROR: interval out of range
+SELECT interval '-infinity' / 'nan';
+ERROR: interval out of range
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' / 0.5;
+ERROR: interval out of range
+SELECT date_bin('infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+ERROR: timestamps cannot be binned into infinite intervals
+SELECT date_bin('-infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+ERROR: timestamps cannot be binned into infinite intervals
+SELECT i AS interval, date_trunc('hour', i)
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+ interval | date_trunc
+-----------+------------
+ infinity | infinity
+ -infinity | -infinity
+(2 rows)
+
+SELECT i AS interval, justify_days(i), justify_hours(i), justify_interval(i)
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+ interval | justify_days | justify_hours | justify_interval
+-----------+--------------+---------------+------------------
+ infinity | infinity | infinity | infinity
+ -infinity | -infinity | -infinity | -infinity
+(2 rows)
+
+SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamp);
+ERROR: interval time zone "infinity" must be finite
+SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamp);
+ERROR: interval time zone "-infinity" must be finite
+SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamptz);
+ERROR: interval time zone "infinity" must be finite
+SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamptz);
+ERROR: interval time zone "-infinity" must be finite
+SELECT timezone('infinity'::interval, '12:12:12'::time);
+ERROR: interval time zone "infinity" must be finite
+SELECT timezone('-infinity'::interval, '12:12:12'::time);
+ERROR: interval time zone "-infinity" must be finite
+SELECT timezone('infinity'::interval, '12:12:12'::timetz);
+ERROR: interval time zone "infinity" must be finite
+SELECT timezone('-infinity'::interval, '12:12:12'::timetz);
+ERROR: interval time zone "-infinity" must be finite
+SELECT 'infinity'::interval::time;
+ERROR: time out of range
+SELECT '-infinity'::interval::time;
+ERROR: time out of range
+SELECT to_char('infinity'::interval, 'YYYY');
+ to_char
+---------
+
+(1 row)
+
+SELECT to_char('-infinity'::interval, 'YYYY');
+ to_char
+---------
+
+(1 row)
+
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index c64bcb7c12..53542e076b 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -2125,3 +2125,65 @@ select * from generate_series('2020-01-01 00:00'::timestamp,
'2020-01-02 03:00'::timestamp,
'0 hour'::interval);
ERROR: step size cannot equal zero
+select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval 'infinity');
+ERROR: step size cannot be infinite
+select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval '-infinity');
+ERROR: step size cannot be infinite
+-- test arithmetic with infinite timestamps
+select timestamp 'infinity' - timestamp 'infinity';
+ERROR: timestamp out of range
+select timestamp 'infinity' - timestamp '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+select timestamp '-infinity' - timestamp 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+select timestamp '-infinity' - timestamp '-infinity';
+ERROR: timestamp out of range
+select timestamp 'infinity' - timestamp '1995-08-06 12:12:12';
+ ?column?
+----------
+ infinity
+(1 row)
+
+select timestamp '-infinity' - timestamp '1995-08-06 12:12:12';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+-- test age() with infinite timestamps
+select age(timestamp 'infinity');
+ age
+-----------
+ -infinity
+(1 row)
+
+select age(timestamp '-infinity');
+ age
+----------
+ infinity
+(1 row)
+
+select age(timestamp 'infinity', timestamp 'infinity');
+ERROR: timestamp out of range
+select age(timestamp 'infinity', timestamp '-infinity');
+ age
+----------
+ infinity
+(1 row)
+
+select age(timestamp '-infinity', timestamp 'infinity');
+ age
+-----------
+ -infinity
+(1 row)
+
+select age(timestamp '-infinity', timestamp '-infinity');
+ERROR: timestamp out of range
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 0dd2fe2c82..825202d597 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2468,6 +2468,10 @@ select * from generate_series('2020-01-01 00:00'::timestamptz,
'2020-01-02 03:00'::timestamptz,
'0 hour'::interval);
ERROR: step size cannot equal zero
+select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval 'infinity');
+ERROR: step size cannot be infinite
+select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval '-infinity');
+ERROR: step size cannot be infinite
-- Interval crossing time shift for Europe/Warsaw timezone (with DST)
SET TimeZone to 'UTC';
SELECT date_add('2022-10-30 00:00:00+01'::timestamptz,
@@ -3154,3 +3158,61 @@ select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
Tue Jan 17 16:00:00 2017 PST
(1 row)
+-- test arithmetic with infinite timestamps
+SELECT timestamptz 'infinity' - timestamptz 'infinity';
+ERROR: timestamp out of range
+SELECT timestamptz 'infinity' - timestamptz '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - timestamptz 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - timestamptz '-infinity';
+ERROR: timestamp out of range
+SELECT timestamptz 'infinity' - timestamptz '1995-08-06 12:12:12';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - timestamptz '1995-08-06 12:12:12';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+-- test age() with infinite timestamps
+SELECT age(timestamptz 'infinity');
+ age
+-----------
+ -infinity
+(1 row)
+
+SELECT age(timestamptz '-infinity');
+ age
+----------
+ infinity
+(1 row)
+
+SELECT age(timestamptz 'infinity', timestamptz 'infinity');
+ERROR: timestamp out of range
+SELECT age(timestamptz 'infinity', timestamptz '-infinity');
+ age
+----------
+ infinity
+(1 row)
+
+SELECT age(timestamptz '-infinity', timestamptz 'infinity');
+ age
+-----------
+ -infinity
+(1 row)
+
+SELECT age(timestamptz '-infinity', timestamptz '-infinity');
+ERROR: timestamp out of range
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index f7f8c8d2dd..1d0ab322c0 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -207,14 +207,17 @@ SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract"
FROM TIMESTAMP_TBL t, INTERVAL_TBL i
WHERE t.d1 BETWEEN '1990-01-01' AND '2001-01-01'
AND i.f1 BETWEEN '00:00' AND '23:00'
+ AND isfinite(i.f1)
ORDER BY 1,2;
SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
FROM TIME_TBL t, INTERVAL_TBL i
+ WHERE isfinite(i.f1)
ORDER BY 1,2;
SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
FROM TIMETZ_TBL t, INTERVAL_TBL i
+ WHERE isfinite(i.f1)
ORDER BY 1,2;
-- SQL9x OVERLAPS operator
@@ -287,11 +290,12 @@ SELECT f1 AS "timestamp"
SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus
FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
+ WHERE isfinite(t.f1)
ORDER BY plus, "timestamp", "interval";
SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 - t.f1 AS minus
FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
- WHERE isfinite(d.f1)
+ WHERE isfinite(t.f1)
ORDER BY minus, "timestamp", "interval";
SELECT d.f1 AS "timestamp",
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index f1abf08501..1e1d8560bf 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -14,6 +14,8 @@ SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";
SELECT INTERVAL '1.5 months' AS "One month 15 days";
SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
+SELECT INTERVAL 'infinity' AS "eternity";
+SELECT INTERVAL '-infinity' AS "beginning of time";
CREATE TABLE INTERVAL_TBL (f1 interval);
@@ -27,6 +29,8 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity');
-- badly formatted interval
INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
@@ -69,11 +73,11 @@ SELECT r1.*, r2.*
-- Test intervals that are large enough to overflow 64 bits in comparisons
CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval);
INSERT INTO INTERVAL_TBL_OF (f1) VALUES
- ('2147483647 days 2147483647 months'),
- ('2147483647 days -2147483648 months'),
+ ('2147483647 days 2147483646 months'),
+ ('2147483647 days -2147483647 months'),
('1 year'),
- ('-2147483648 days 2147483647 months'),
- ('-2147483648 days -2147483648 months');
+ ('-2147483648 days 2147483646 months'),
+ ('-2147483648 days -2147483647 months');
-- these should fail as out-of-range
INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days');
INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days');
@@ -140,7 +144,7 @@ SELECT * FROM INTERVAL_TBL;
-- known to change the allowed input syntax for type interval without
-- updating pg_aggregate.agginitval
-select avg(f1) from interval_tbl;
+select avg(f1) from interval_tbl where isfinite(f1);
-- test long interval input
select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 seconds'::interval;
@@ -157,7 +161,8 @@ SELECT justify_hours(interval '6 months 3 days 52 hours 3 minutes 2 seconds') as
SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds') as "7 mons 6 days 5 hours 4 mins 3 seconds";
SELECT justify_hours(interval '2147483647 days 24 hrs');
-SELECT justify_days(interval '2147483647 months 30 days');
+SELECT justify_days(interval '2147483646 months 30 days');
+SELECT justify_days(interval '2147483646 months 60 days');
-- test justify_interval()
@@ -165,12 +170,14 @@ SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
SELECT justify_interval(interval '2147483647 days 24 hrs');
SELECT justify_interval(interval '-2147483648 days -24 hrs');
-SELECT justify_interval(interval '2147483647 months 30 days');
-SELECT justify_interval(interval '-2147483648 months -30 days');
-SELECT justify_interval(interval '2147483647 months 30 days -24 hrs');
-SELECT justify_interval(interval '-2147483648 months -30 days 24 hrs');
-SELECT justify_interval(interval '2147483647 months -30 days 1440 hrs');
-SELECT justify_interval(interval '-2147483648 months 30 days -1440 hrs');
+SELECT justify_interval(interval '2147483646 months 30 days');
+SELECT justify_interval(interval '2147483646 months 60 days');
+SELECT justify_interval(interval '-2147483647 months -30 days');
+SELECT justify_interval(interval '-2147483647 months -60 days');
+SELECT justify_interval(interval '2147483646 months 30 days -24 hrs');
+SELECT justify_interval(interval '-2147483647 months -30 days 24 hrs');
+SELECT justify_interval(interval '2147483646 months -30 days 1440 hrs');
+SELECT justify_interval(interval '-2147483647 months 30 days -1440 hrs');
-- test fractional second input, and detection of duplicate units
SET DATESTYLE = 'ISO';
@@ -517,13 +524,13 @@ select make_interval(-1, -2147483648, -2147483648, -1, -1, -1, -9223372036854.77
-- test that INT_MIN number is formatted properly
SET IntervalStyle to postgres;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
SET IntervalStyle to sql_standard;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
SET IntervalStyle to iso_8601;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
SET IntervalStyle to postgres_verbose;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
-- check that '30 days' equals '1 month' according to the hash function
select '30 days'::interval = '1 month'::interval as t;
@@ -586,3 +593,134 @@ SELECT f1,
-- internal overflow test case
SELECT extract(epoch from interval '1000000000 days');
+
+-- infinite intervals
+SELECT interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+SELECT interval '2147483647 months 2147483647 days 9223372036854775807 us';
+
+CREATE TABLE INFINITE_INTERVAL_TBL (i interval);
+INSERT INTO INFINITE_INTERVAL_TBL VALUES ('infinity'), ('-infinity'), ('1 year 2 days 3 hours');
+
+SELECT i, isfinite(i) FROM INFINITE_INTERVAL_TBL;
+
+SELECT date '1995-08-06' + interval 'infinity';
+SELECT date '1995-08-06' + interval '-infinity';
+SELECT date '1995-08-06' - interval 'infinity';
+SELECT date '1995-08-06' - interval '-infinity';
+SELECT date 'infinity' + interval 'infinity';
+SELECT date 'infinity' + interval '-infinity';
+SELECT date '-infinity' + interval 'infinity';
+SELECT date '-infinity' + interval '-infinity';
+SELECT date 'infinity' - interval 'infinity';
+SELECT date 'infinity' - interval '-infinity';
+SELECT date '-infinity' - interval 'infinity';
+SELECT date '-infinity' - interval '-infinity';
+SELECT interval 'infinity' + interval 'infinity';
+SELECT interval 'infinity' + interval '-infinity';
+SELECT interval '-infinity' + interval 'infinity';
+SELECT interval '-infinity' + interval '-infinity';
+SELECT interval 'infinity' + interval '10 days';
+SELECT interval '-infinity' + interval '10 days';
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' + interval '1 month 1 day 1 us';
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' + interval '-1 month -1 day -1 us';
+SELECT interval 'infinity' - interval 'infinity';
+SELECT interval 'infinity' - interval '-infinity';
+SELECT interval '-infinity' - interval 'infinity';
+SELECT interval '-infinity' - interval '-infinity';
+SELECT interval 'infinity' - interval '10 days';
+SELECT interval '-infinity' - interval '10 days';
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' - interval '-1 month -1 day -1 us';
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' - interval '1 month 1 day 1 us';
+SELECT timestamp '1995-08-06 12:30:15' + interval 'infinity';
+SELECT timestamp '1995-08-06 12:30:15' + interval '-infinity';
+SELECT timestamp '1995-08-06 12:30:15' - interval 'infinity';
+SELECT timestamp '1995-08-06 12:30:15' - interval '-infinity';
+SELECT timestamp 'infinity' + interval 'infinity';
+SELECT timestamp 'infinity' + interval '-infinity';
+SELECT timestamp '-infinity' + interval 'infinity';
+SELECT timestamp '-infinity' + interval '-infinity';
+SELECT timestamp 'infinity' - interval 'infinity';
+SELECT timestamp 'infinity' - interval '-infinity';
+SELECT timestamp '-infinity' - interval 'infinity';
+SELECT timestamp '-infinity' - interval '-infinity';
+SELECT timestamptz '1995-08-06 12:30:15' + interval 'infinity';
+SELECT timestamptz '1995-08-06 12:30:15' + interval '-infinity';
+SELECT timestamptz '1995-08-06 12:30:15' - interval 'infinity';
+SELECT timestamptz '1995-08-06 12:30:15' - interval '-infinity';
+SELECT timestamptz 'infinity' + interval 'infinity';
+SELECT timestamptz 'infinity' + interval '-infinity';
+SELECT timestamptz '-infinity' + interval 'infinity';
+SELECT timestamptz '-infinity' + interval '-infinity';
+SELECT timestamptz 'infinity' - interval 'infinity';
+SELECT timestamptz 'infinity' - interval '-infinity';
+SELECT timestamptz '-infinity' - interval 'infinity';
+SELECT timestamptz '-infinity' - interval '-infinity';
+SELECT time '11:27:42' + interval 'infinity';
+SELECT time '11:27:42' + interval '-infinity';
+SELECT time '11:27:42' - interval 'infinity';
+SELECT time '11:27:42' - interval '-infinity';
+SELECT timetz '11:27:42' + interval 'infinity';
+SELECT timetz '11:27:42' + interval '-infinity';
+SELECT timetz '11:27:42' - interval 'infinity';
+SELECT timetz '11:27:42' - interval '-infinity';
+
+SELECT lhst.i lhs,
+ rhst.i rhs,
+ lhst.i < rhst.i AS lt,
+ lhst.i <= rhst.i AS le,
+ lhst.i = rhst.i AS eq,
+ lhst.i > rhst.i AS gt,
+ lhst.i >= rhst.i AS ge,
+ lhst.i <> rhst.i AS ne
+ FROM INFINITE_INTERVAL_TBL lhst CROSS JOIN INFINITE_INTERVAL_TBL rhst
+ WHERE NOT isfinite(lhst.i);
+
+SELECT i AS interval,
+ -i AS um,
+ i * 2.0 AS mul,
+ i * -2.0 AS mul_neg,
+ i * 'infinity' AS mul_inf,
+ i * '-infinity' AS mul_inf_neg,
+ i / 3.0 AS div,
+ i / -3.0 AS div_neg
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+
+SELECT -interval '-2147483647 months -2147483647 days -9223372036854775807 us';
+SELECT interval 'infinity' * 'nan';
+SELECT interval '-infinity' * 'nan';
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' * 2;
+
+SELECT interval 'infinity' / 'infinity';
+SELECT interval 'infinity' / '-infinity';
+SELECT interval 'infinity' / 'nan';
+SELECT interval '-infinity' / 'infinity';
+SELECT interval '-infinity' / '-infinity';
+SELECT interval '-infinity' / 'nan';
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' / 0.5;
+
+SELECT date_bin('infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+SELECT date_bin('-infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+
+SELECT i AS interval, date_trunc('hour', i)
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+
+SELECT i AS interval, justify_days(i), justify_hours(i), justify_interval(i)
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+
+SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamp);
+SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamp);
+SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamptz);
+SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamptz);
+SELECT timezone('infinity'::interval, '12:12:12'::time);
+SELECT timezone('-infinity'::interval, '12:12:12'::time);
+SELECT timezone('infinity'::interval, '12:12:12'::timetz);
+SELECT timezone('-infinity'::interval, '12:12:12'::timetz);
+
+SELECT 'infinity'::interval::time;
+SELECT '-infinity'::interval::time;
+
+SELECT to_char('infinity'::interval, 'YYYY');
+SELECT to_char('-infinity'::interval, 'YYYY');
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index b9bcce9cfe..ea12ffd18d 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -397,3 +397,22 @@ select generate_series('2022-01-01 00:00'::timestamp,
select * from generate_series('2020-01-01 00:00'::timestamp,
'2020-01-02 03:00'::timestamp,
'0 hour'::interval);
+select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval 'infinity');
+select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval '-infinity');
+
+
+-- test arithmetic with infinite timestamps
+select timestamp 'infinity' - timestamp 'infinity';
+select timestamp 'infinity' - timestamp '-infinity';
+select timestamp '-infinity' - timestamp 'infinity';
+select timestamp '-infinity' - timestamp '-infinity';
+select timestamp 'infinity' - timestamp '1995-08-06 12:12:12';
+select timestamp '-infinity' - timestamp '1995-08-06 12:12:12';
+
+-- test age() with infinite timestamps
+select age(timestamp 'infinity');
+select age(timestamp '-infinity');
+select age(timestamp 'infinity', timestamp 'infinity');
+select age(timestamp 'infinity', timestamp '-infinity');
+select age(timestamp '-infinity', timestamp 'infinity');
+select age(timestamp '-infinity', timestamp '-infinity');
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 69b36d0420..7876225c25 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -458,6 +458,8 @@ select generate_series('2022-01-01 00:00'::timestamptz,
select * from generate_series('2020-01-01 00:00'::timestamptz,
'2020-01-02 03:00'::timestamptz,
'0 hour'::interval);
+select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval 'infinity');
+select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval '-infinity');
-- Interval crossing time shift for Europe/Warsaw timezone (with DST)
SET TimeZone to 'UTC';
@@ -619,3 +621,19 @@ insert into tmptz values ('2017-01-18 00:00+00');
explain (costs off)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
+
+-- test arithmetic with infinite timestamps
+SELECT timestamptz 'infinity' - timestamptz 'infinity';
+SELECT timestamptz 'infinity' - timestamptz '-infinity';
+SELECT timestamptz '-infinity' - timestamptz 'infinity';
+SELECT timestamptz '-infinity' - timestamptz '-infinity';
+SELECT timestamptz 'infinity' - timestamptz '1995-08-06 12:12:12';
+SELECT timestamptz '-infinity' - timestamptz '1995-08-06 12:12:12';
+
+-- test age() with infinite timestamps
+SELECT age(timestamptz 'infinity');
+SELECT age(timestamptz '-infinity');
+SELECT age(timestamptz 'infinity', timestamptz 'infinity');
+SELECT age(timestamptz 'infinity', timestamptz '-infinity');
+SELECT age(timestamptz '-infinity', timestamptz 'infinity');
+SELECT age(timestamptz '-infinity', timestamptz '-infinity');
--
2.34.1
