Hi Ashutosh,
I ended up doing some more work on this today. All of the major
features should be implemented now. Below are what I think are the
outstanding TODOs:
- Clean up error messages and error codes
- Figure out how to correctly implement interval_part for infinite
intervals. For now I pretty much copied the implementation of
timestamp_part, but I'm not convinced that's correct.
- Fix horology tests.
- Test consolidation. After looking through the interval tests, I
realized that I may have duplicated some test cases. It would probably
be best to remove those duplicate tests.
- General cleanup, remove TODOs.
Attached is my most recent patch.
- Joe Koshakow
From 380cde4061afd6eed4cde938a4c668a2c96bb58f Mon Sep 17 00:00:00 2001
From: Joseph Koshakow <kosh...@gmail.com>
Date: Sat, 17 Dec 2022 14:21:26 -0500
Subject: [PATCH] This is WIP.
Following things are supported
1. Accepts '+/-infinity' as a valid string input for interval type.
2. Support interval_pl, interval_div
3. Tests in interval.sql for comparison operators working fine.
TODOs
1. Various TODOs in code
2. interval_pl: how to handle infinite values with opposite signs
3. timestamp, timestamptz, date and time arithmetic
4. Fix horology test.
Ashutosh Bapat
---
src/backend/utils/adt/date.c | 20 +
src/backend/utils/adt/datetime.c | 2 +
src/backend/utils/adt/timestamp.c | 330 ++++++++-
src/include/datatype/timestamp.h | 22 +
src/test/regress/expected/interval.out | 953 ++++++++++++++++++++++++-
src/test/regress/sql/interval.sql | 182 ++++-
6 files changed, 1442 insertions(+), 67 deletions(-)
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 1cf7c7652d..a2c9214bcf 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -2073,6 +2073,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("TODO")));
+
result = time + span->time;
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
@@ -2091,6 +2096,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("TODO")));
+
result = time - span->time;
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
@@ -2605,6 +2615,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("TODO")));
+
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
result->time = time->time + span->time;
@@ -2627,6 +2642,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("TODO")));
+
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
result->time = time->time - span->time;
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index b5b117a8ca..1e98c6dc78 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -3634,6 +3634,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/timestamp.c b/src/backend/utils/adt/timestamp.c
index 3f2508c0c4..d108057ce5 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -79,6 +79,8 @@ static bool AdjustIntervalForTypmod(Interval *interval, int32 typmod,
static TimestampTz timestamp2timestamptz(Timestamp timestamp);
static Timestamp timestamptz2timestamp(TimestampTz timestamp);
+static void EncodeSpecialInterval(Interval *interval, char *str);
+static void negate_interval(Interval *interval, Interval *result);
/* common code for timestamptypmodin and timestamptztypmodin */
static int32
@@ -943,6 +945,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);
@@ -965,8 +975,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);
@@ -1352,6 +1367,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.
@@ -1545,6 +1567,17 @@ EncodeSpecialTimestamp(Timestamp dt, char *str)
elog(ERROR, "invalid argument for EncodeSpecialTimestamp");
}
+static 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)
{
@@ -2033,6 +2066,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;
}
@@ -2050,6 +2085,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;
}
@@ -2083,7 +2120,8 @@ 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));
}
@@ -2775,6 +2813,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))
@@ -2850,6 +2891,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,
@@ -2888,6 +2932,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))
@@ -2926,7 +2973,25 @@ 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.
+ */
+ // TODO this logic can probably be combined and cleaned up.
+ if (INTERVAL_IS_NOBEGIN(span) && TIMESTAMP_IS_NOBEGIN(timestamp))
+ TIMESTAMP_NOBEGIN(result);
+ else if (INTERVAL_IS_NOEND(span) && TIMESTAMP_IS_NOEND(timestamp))
+ TIMESTAMP_NOEND(result);
+ else if (INTERVAL_NOT_FINITE(span) && TIMESTAMP_NOT_FINITE(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+ else if (INTERVAL_IS_NOBEGIN(span))
+ TIMESTAMP_NOBEGIN(result);
+ else if (INTERVAL_IS_NOEND(span))
+ TIMESTAMP_NOEND(result);
+ else if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
else
{
@@ -3005,9 +3070,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;
+ negate_interval(span, &tspan);
return DirectFunctionCall2(timestamp_pl_interval,
TimestampGetDatum(timestamp),
@@ -3032,7 +3095,25 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
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.
+ */
+ // TODO this logic can probably be combined and cleaned up.
+ if (INTERVAL_IS_NOBEGIN(span) && TIMESTAMP_IS_NOBEGIN(timestamp))
+ TIMESTAMP_NOBEGIN(result);
+ else if (INTERVAL_IS_NOEND(span) && TIMESTAMP_IS_NOEND(timestamp))
+ TIMESTAMP_NOEND(result);
+ else if (INTERVAL_NOT_FINITE(span) && TIMESTAMP_NOT_FINITE(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+ else if (INTERVAL_IS_NOBEGIN(span))
+ TIMESTAMP_NOBEGIN(result);
+ else if (INTERVAL_IS_NOEND(span))
+ TIMESTAMP_NOEND(result);
+ else if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
else
{
@@ -3115,9 +3196,7 @@ timestamptz_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;
+ negate_interval(span, &tspan);
return DirectFunctionCall2(timestamptz_pl_interval,
TimestampGetDatum(timestamp),
@@ -3132,23 +3211,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")));
+ negate_interval(interval, result);
PG_RETURN_INTERVAL_P(result);
}
@@ -3192,6 +3255,38 @@ interval_pl(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ /*
+ * 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) && INTERVAL_IS_NOBEGIN(span2))
+ {
+ INTERVAL_NOBEGIN(result);
+ PG_RETURN_INTERVAL_P(result);
+ }
+ else if (INTERVAL_IS_NOEND(span1) && INTERVAL_IS_NOEND(span2))
+ {
+ INTERVAL_NOEND(result);
+ PG_RETURN_INTERVAL_P(result);
+ }
+ else if (INTERVAL_NOT_FINITE(span1) && INTERVAL_NOT_FINITE(span2))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+ }
+ else if (INTERVAL_NOT_FINITE(span1))
+ {
+ memcpy(result, span1, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+ else if (INTERVAL_NOT_FINITE(span2))
+ {
+ memcpy(result, span2, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result->month = span1->month + span2->month;
/* overflow check copied from int4pl */
if (SAMESIGN(span1->month, span2->month) &&
@@ -3226,6 +3321,39 @@ interval_mi(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ /*
+ * 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) && INTERVAL_IS_NOEND(span2))
+ {
+ INTERVAL_NOBEGIN(result);
+ PG_RETURN_INTERVAL_P(result);
+ }
+ else if (INTERVAL_IS_NOEND(span1) && INTERVAL_IS_NOBEGIN(span2))
+ {
+ INTERVAL_NOEND(result);
+ PG_RETURN_INTERVAL_P(result);
+ }
+ else if (INTERVAL_NOT_FINITE(span1) && INTERVAL_NOT_FINITE(span2))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+ }
+ else if (INTERVAL_NOT_FINITE(span1))
+ {
+ memcpy(result, span1, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+ else if (INTERVAL_NOT_FINITE(span2))
+ {
+ memcpy(result, span2, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result->month = span1->month - span2->month;
/* overflow check copied from int4mi */
if (!SAMESIGN(span1->month, span2->month) &&
@@ -3271,6 +3399,19 @@ interval_mul(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ /*
+ * Multiplying infinite interval by finite number keeps it infinite but may change
+ * the sign.
+ */
+ if (INTERVAL_NOT_FINITE(span))
+ {
+ if (factor < 0.0)
+ negate_interval(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)
@@ -3362,6 +3503,19 @@ interval_div(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
+ /*
+ * Dividing infinite interval by finite number keeps it infinite but may change
+ * the sign.
+ */
+ if (INTERVAL_NOT_FINITE(span))
+ {
+ if (factor < 0.0)
+ negate_interval(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);
@@ -3916,6 +4070,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("TODO")));
+
if (stride->month != 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4371,6 +4530,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);
@@ -5230,6 +5395,62 @@ extract_timestamptz(PG_FUNCTION_ARGS)
}
+/*
+ * 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.
+ */
+// TODO I don't actaully know if this is correct.
+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))));
+
+ // TODO: Maybe everything should be returning inf/-inf?
+ switch (unit)
+ {
+ /* Oscillating units */
+ case DTK_MICROSEC:
+ case DTK_MILLISEC:
+ case DTK_SECOND:
+ case DTK_MINUTE:
+ case DTK_HOUR:
+ case DTK_DAY:
+ case DTK_MONTH:
+ case DTK_QUARTER:
+ return 0.0;
+
+ /* Monotonically-increasing units */
+ 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.
*/
@@ -5253,6 +5474,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 = NonFiniteTimestampTzPart(type, val, lowunits,
+ INTERVAL_IS_NOBEGIN(interval),
+ false);
+
+ if (r)
+ {
+ 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);
@@ -5955,3 +6204,28 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * TODO: possibly we should move these to a place along with other interval_*
+ * functions.
+ */
+
+/* Negates the given interval */
+static void
+negate_interval(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")));
+ else
+ {
+ result->time = -interval->time;
+ result->day = -interval->day;
+ result->month = -interval->month;
+ }
+}
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index d155f1b03b..4c281c9112 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -160,6 +160,28 @@ struct pg_itm_in
#define TIMESTAMP_NOT_FINITE(j) (TIMESTAMP_IS_NOBEGIN(j) || TIMESTAMP_IS_NOEND(j))
+// TODO: Should we make custom NOBEGIN and NOEND constants for Interval?
+#define INTERVAL_NOBEGIN(i) \
+ do { \
+ (i->time) = DT_NOBEGIN; \
+ (i->day) = PG_INT32_MIN; \
+ (i->month) = PG_INT32_MIN; \
+ } while (0)
+
+#define INTERVAL_IS_NOBEGIN(i) \
+ ((i->time) == DT_NOBEGIN && (i->day) == PG_INT32_MIN && (i->month) == PG_INT32_MIN)
+
+#define INTERVAL_NOEND(i) \
+ do { \
+ (i->time) = DT_NOEND; \
+ (i->day) = PG_INT32_MAX; \
+ (i->month) = PG_INT32_MAX; \
+ } while (0)
+
+#define INTERVAL_IS_NOEND(i) \
+ ((i->time) == DT_NOEND && (i->day) == PG_INT32_MAX && (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/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 579e92e7b3..7567d51661 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -52,6 +52,19 @@ 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)
+
+--TODO: Add tests for operators etc. by looking at the other tests below
CREATE TABLE INTERVAL_TBL (f1 interval);
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
@@ -63,6 +76,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"
@@ -72,6 +87,10 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
ERROR: invalid input syntax for type interval: "@ 30 eons ago"
LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
^
+INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity');
+ERROR: invalid input syntax for type interval: "+infinity"
+LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity');
+ ^
-- Test non-error-throwing API
SELECT pg_input_is_valid('1.5 weeks', 'interval');
pg_input_is_valid
@@ -117,7 +136,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 +153,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 +164,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 +174,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 +193,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 +209,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 +218,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 +254,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 +263,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,7 +273,18 @@ 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);
@@ -386,12 +434,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
@@ -820,8 +870,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 +882,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;
@@ -1578,31 +1630,31 @@ 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';
+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
@@ -1681,19 +1733,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
+(12 rows)
SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
ERROR: unit "fortnight" not recognized for type interval
@@ -1767,7 +1821,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');
@@ -1776,3 +1832,832 @@ 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...
+ ^
+SELECT isfinite(interval 'infinity');
+ isfinite
+----------
+ f
+(1 row)
+
+SELECT isfinite(interval '-infinity');
+ isfinite
+----------
+ f
+(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 '1995-08-06' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date 'infinity' + interval '-infinity';
+ERROR: TODO
+SELECT date '-infinity' + interval 'infinity';
+ERROR: TODO
+SELECT date '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date 'infinity' - interval 'infinity';
+ERROR: TODO
+SELECT date 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date '-infinity' - interval '-infinity';
+ERROR: TODO
+SELECT interval 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' + interval '-infinity';
+ERROR: TODO
+SELECT interval '-infinity' + interval 'infinity';
+ERROR: TODO
+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 'infinity' - interval 'infinity';
+ERROR: TODO
+SELECT interval 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' - interval '-infinity';
+ERROR: TODO
+SELECT interval 'infinity' - interval '10 days';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' - interval '10 days';
+ ?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 '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: TODO
+SELECT timestamp '-infinity' + interval 'infinity';
+ERROR: TODO
+SELECT timestamp '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp 'infinity' - interval 'infinity';
+ERROR: TODO
+SELECT timestamp 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '-infinity' - interval '-infinity';
+ERROR: TODO
+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: TODO
+SELECT timestamptz '-infinity' + interval 'infinity';
+ERROR: TODO
+SELECT timestamptz '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz 'infinity' - interval 'infinity';
+ERROR: TODO
+SELECT timestamptz 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - interval '-infinity';
+ERROR: TODO
+SELECT time '11:27:42' + interval 'infinity';
+ERROR: TODO
+SELECT time '11:27:42' + interval '-infinity';
+ERROR: TODO
+SELECT time '11:27:42' - interval 'infinity';
+ERROR: TODO
+SELECT time '11:27:42' - interval '-infinity';
+ERROR: TODO
+SELECT timetz '11:27:42' + interval 'infinity';
+ERROR: TODO
+SELECT timetz '11:27:42' + interval '-infinity';
+ERROR: TODO
+SELECT timetz '11:27:42' - interval 'infinity';
+ERROR: TODO
+SELECT timetz '11:27:42' - interval '-infinity';
+ERROR: TODO
+SELECT interval 'infinity' < interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval 'infinity' < interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' < interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' < interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval 'infinity' <= interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' <= interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' <= interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' <= interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' > interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval 'infinity' > interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' > interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' > interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval 'infinity' >= interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' >= interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' >= interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' >= interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' = interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' = interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' = interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' = interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' <> interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval 'infinity' <> interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' <> interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' <> interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT -interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT -interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' * 2;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' * -2;
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' * 2;
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' * -2;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' / 3;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' / -3;
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' / 3;
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' / -3;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date_bin('infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+ERROR: TODO
+SELECT date_bin('-infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+ERROR: TODO
+SELECT date_trunc('hour', interval 'infinity');
+ date_trunc
+------------
+ infinity
+(1 row)
+
+SELECT date_trunc('hour', interval '-infinity');
+ date_trunc
+------------
+ -infinity
+(1 row)
+
+SELECT date_part('us', interval 'infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('us', interval '-infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('ms', interval 'infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('ms', interval '-infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('second', interval 'infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('second', interval '-infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('minute', interval 'infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('minute', interval '-infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('hour', interval 'infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('hour', interval '-infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('day', interval 'infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('day', interval '-infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('month', interval 'infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('month', interval '-infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('quarter', interval 'infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('quarter', interval '-infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('year', interval 'infinity');
+ date_part
+-----------
+ Infinity
+(1 row)
+
+SELECT date_part('year', interval '-infinity');
+ date_part
+-----------
+ -Infinity
+(1 row)
+
+SELECT date_part('decade', interval 'infinity');
+ date_part
+-----------
+ Infinity
+(1 row)
+
+SELECT date_part('decade', interval '-infinity');
+ date_part
+-----------
+ -Infinity
+(1 row)
+
+SELECT date_part('century', interval 'infinity');
+ date_part
+-----------
+ Infinity
+(1 row)
+
+SELECT date_part('century', interval '-infinity');
+ date_part
+-----------
+ -Infinity
+(1 row)
+
+SELECT date_part('millennium', interval 'infinity');
+ date_part
+-----------
+ Infinity
+(1 row)
+
+SELECT date_part('millennium', interval '-infinity');
+ date_part
+-----------
+ -Infinity
+(1 row)
+
+SELECT date_part('epoch', interval 'infinity');
+ date_part
+-----------
+ Infinity
+(1 row)
+
+SELECT date_part('epoch', interval '-infinity');
+ date_part
+-----------
+ -Infinity
+(1 row)
+
+SELECT extract(us from interval 'infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(us from interval '-infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(ms from interval 'infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(ms from interval '-infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(second from interval 'infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(second from interval '-infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(minute from interval 'infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(minute from interval '-infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(hour from interval 'infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(hour from interval '-infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(day from interval 'infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(day from interval '-infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(month from interval 'infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(month from interval '-infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(quarter from interval 'infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(quarter from interval '-infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(year from interval 'infinity');
+ extract
+----------
+ Infinity
+(1 row)
+
+SELECT extract(year from interval '-infinity');
+ extract
+-----------
+ -Infinity
+(1 row)
+
+SELECT extract(decade from interval 'infinity');
+ extract
+----------
+ Infinity
+(1 row)
+
+SELECT extract(decade from interval '-infinity');
+ extract
+-----------
+ -Infinity
+(1 row)
+
+SELECT extract(century from interval 'infinity');
+ extract
+----------
+ Infinity
+(1 row)
+
+SELECT extract(century from interval '-infinity');
+ extract
+-----------
+ -Infinity
+(1 row)
+
+SELECT extract(millennium from interval 'infinity');
+ extract
+----------
+ Infinity
+(1 row)
+
+SELECT extract(millennium from interval '-infinity');
+ extract
+-----------
+ -Infinity
+(1 row)
+
+SELECT extract(epoch from interval 'infinity');
+ extract
+----------
+ Infinity
+(1 row)
+
+SELECT extract(epoch from interval '-infinity');
+ extract
+-----------
+ -Infinity
+(1 row)
+
+SELECT justify_days(interval 'infinity');
+ justify_days
+--------------
+ infinity
+(1 row)
+
+SELECT justify_days(interval '-infinity');
+ justify_days
+--------------
+ -infinity
+(1 row)
+
+SELECT justify_hours(interval 'infinity');
+ justify_hours
+---------------
+ infinity
+(1 row)
+
+SELECT justify_hours(interval '-infinity');
+ justify_hours
+---------------
+ -infinity
+(1 row)
+
+SELECT justify_interval(interval 'infinity');
+ justify_interval
+------------------
+ infinity
+(1 row)
+
+SELECT justify_interval(interval '-infinity');
+ justify_interval
+------------------
+ -infinity
+(1 row)
+
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 0517b5b82b..e58135f3cb 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -14,6 +14,10 @@ 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";
+
+--TODO: Add tests for operators etc. by looking at the other tests below
CREATE TABLE INTERVAL_TBL (f1 interval);
@@ -27,10 +31,13 @@ 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');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity');
-- Test non-error-throwing API
SELECT pg_input_is_valid('1.5 weeks', 'interval');
@@ -140,7 +147,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;
@@ -509,13 +516,13 @@ select interval '-2147483648 months -2147483648 days -9223372036854775808 micros
-- 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;
@@ -578,3 +585,168 @@ 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';
+
+SELECT isfinite(interval 'infinity');
+SELECT isfinite(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 '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 '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 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 interval '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 '-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 '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 '-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 'infinity';
+SELECT interval 'infinity' <> interval '-infinity';
+SELECT interval '-infinity' <> interval 'infinity';
+SELECT interval '-infinity' <> interval '-infinity';
+
+SELECT -interval 'infinity';
+SELECT -interval '-infinity';
+SELECT interval 'infinity' * 2;
+SELECT interval 'infinity' * -2;
+SELECT interval '-infinity' * 2;
+SELECT interval '-infinity' * -2;
+SELECT interval 'infinity' / 3;
+SELECT interval 'infinity' / -3;
+SELECT interval '-infinity' / 3;
+SELECT interval '-infinity' / -3;
+
+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 date_trunc('hour', interval 'infinity');
+SELECT date_trunc('hour', interval '-infinity');
+
+SELECT date_part('us', interval 'infinity');
+SELECT date_part('us', interval '-infinity');
+SELECT date_part('ms', interval 'infinity');
+SELECT date_part('ms', interval '-infinity');
+SELECT date_part('second', interval 'infinity');
+SELECT date_part('second', interval '-infinity');
+SELECT date_part('minute', interval 'infinity');
+SELECT date_part('minute', interval '-infinity');
+SELECT date_part('hour', interval 'infinity');
+SELECT date_part('hour', interval '-infinity');
+SELECT date_part('day', interval 'infinity');
+SELECT date_part('day', interval '-infinity');
+SELECT date_part('month', interval 'infinity');
+SELECT date_part('month', interval '-infinity');
+SELECT date_part('quarter', interval 'infinity');
+SELECT date_part('quarter', interval '-infinity');
+SELECT date_part('year', interval 'infinity');
+SELECT date_part('year', interval '-infinity');
+SELECT date_part('decade', interval 'infinity');
+SELECT date_part('decade', interval '-infinity');
+SELECT date_part('century', interval 'infinity');
+SELECT date_part('century', interval '-infinity');
+SELECT date_part('millennium', interval 'infinity');
+SELECT date_part('millennium', interval '-infinity');
+SELECT date_part('epoch', interval 'infinity');
+SELECT date_part('epoch', interval '-infinity');
+SELECT extract(us from interval 'infinity');
+SELECT extract(us from interval '-infinity');
+SELECT extract(ms from interval 'infinity');
+SELECT extract(ms from interval '-infinity');
+SELECT extract(second from interval 'infinity');
+SELECT extract(second from interval '-infinity');
+SELECT extract(minute from interval 'infinity');
+SELECT extract(minute from interval '-infinity');
+SELECT extract(hour from interval 'infinity');
+SELECT extract(hour from interval '-infinity');
+SELECT extract(day from interval 'infinity');
+SELECT extract(day from interval '-infinity');
+SELECT extract(month from interval 'infinity');
+SELECT extract(month from interval '-infinity');
+SELECT extract(quarter from interval 'infinity');
+SELECT extract(quarter from interval '-infinity');
+SELECT extract(year from interval 'infinity');
+SELECT extract(year from interval '-infinity');
+SELECT extract(decade from interval 'infinity');
+SELECT extract(decade from interval '-infinity');
+SELECT extract(century from interval 'infinity');
+SELECT extract(century from interval '-infinity');
+SELECT extract(millennium from interval 'infinity');
+SELECT extract(millennium from interval '-infinity');
+SELECT extract(epoch from interval 'infinity');
+SELECT extract(epoch from interval '-infinity');
+
+SELECT justify_days(interval 'infinity');
+SELECT justify_days(interval '-infinity');
+SELECT justify_hours(interval 'infinity');
+SELECT justify_hours(interval '-infinity');
+SELECT justify_interval(interval 'infinity');
+SELECT justify_interval(interval '-infinity');
--
2.34.1
