Hi Ashutosh,
I've added tests for all the operators and functions involving
intervals and what I think the expected behaviors to be. The
formatting might be slightly off and I've left the contents of the
error messages as TODOs. Hopefully it's a good reference for the
implementation.
> Adding infinite interval to an infinite timestamp with opposite
> direction is not going to yield 0 but some infinity. Since we are adding
> interval to the timestamp the resultant timestamp is an infinity
> preserving the direction.
I think I disagree with this. Tom Lane in one of the previous threads
said:
> tl;dr: we should model it after the behavior of IEEE float infinities,
> except we'll want to throw errors where those produce NaNs.
and I agree with this opinion. I believe that means that adding an
infinite interval to an infinite timestamp with opposite directions
should yield an error instead of some infinity. Since with floats this
would yield a NaN.
> Dividing infinite interval by finite number keeps it infinite.
> TODO: Do we change the sign of infinity if factor is negative?
Again if we model this after the IEEE float behavior, then the answer
is yes, we do change the sign of infinity.
- Joe Koshakow
From 4c1be4e2aa7abd56967fdce14b100715f3a63fee 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/datetime.c | 2 +
src/backend/utils/adt/timestamp.c | 166 +++++++-
src/test/regress/expected/interval.out | 565 ++++++++++++++++++++++++-
src/test/regress/sql/interval.sql | 105 +++++
4 files changed, 824 insertions(+), 14 deletions(-)
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..0c7286b06e 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -79,6 +79,12 @@ 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 interval_noend(Interval *interval);
+static bool interval_is_noend(Interval *interval);
+static void interval_nobegin(Interval *interval);
+static bool interval_is_nobegin(Interval *interval);
+static bool interval_not_finite(Interval *interval);
/* common code for timestamptypmodin and timestamptztypmodin */
static int32
@@ -943,6 +949,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 +979,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 +1371,13 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod,
INT64CONST(0)
};
+ /*
+ * Infinite interval after being subjected to typmod conversion remains
+ * infinite.
+ */
+ if (interval_not_finite(interval))
+ return;
+
/*
* Unspecified range and precision? Then not necessary to adjust. Setting
* typmod to -1 is the convention for all data types.
@@ -1545,6 +1571,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)
{
@@ -2080,10 +2117,12 @@ timestamp_finite(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(!TIMESTAMP_NOT_FINITE(timestamp));
}
+/* TODO: modify this to check finite-ness */
Datum
interval_finite(PG_FUNCTION_ARGS)
{
- PG_RETURN_BOOL(true);
+ Interval *interval = PG_GETARG_INTERVAL_P(0);
+ PG_RETURN_BOOL(!interval_not_finite(interval));
}
@@ -2926,8 +2965,27 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
Timestamp result;
+ /*
+ * Adding finite interval to an infinite time is going to be infinite in
+ * the same direction. Adding infinte interval to infinite timestamp in the
+ * same direction results in an infinite timestamp in the same direction.
+ * Adding infinite interval to an infinite timestamp with opposite
+ * direction is not going to yield 0 but some infinity. Since we are adding
+ * interval to the timestamp the resultant timestamp is an infinity
+ * preserving the direction.
+ */
if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
+ else if (interval_not_finite(span))
+ {
+ if (interval_is_nobegin(span))
+ TIMESTAMP_NOBEGIN(result);
+ else
+ {
+ Assert(interval_is_noend(span));
+ TIMESTAMP_NOEND(result);
+ }
+ }
else
{
if (span->month != 0)
@@ -3032,8 +3090,27 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
TimestampTz result;
int tz;
+ /*
+ * Adding finite interval to an infinite time is going to be infinite in
+ * the same direction. Adding infinte interval to infinite timestamp in the
+ * same direction results in an infinite timestamp in the same direction.
+ * Adding infinite interval to an infinite timestamp with opposite
+ * direction is not going to yield 0 but some infinity. Since we are adding
+ * interval to the timestamp the resultant timestamp is an infinity
+ * preserving the direction.
+ */
if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
+ else if (interval_not_finite(span))
+ {
+ if (interval_is_nobegin(span))
+ TIMESTAMP_NOBEGIN(result);
+ else
+ {
+ Assert(interval_is_noend(span));
+ TIMESTAMP_NOEND(result);
+ }
+ }
else
{
if (span->month != 0)
@@ -3192,6 +3269,21 @@ interval_pl(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ /*
+ * TODO: What if both span1 and span2 are infinite and in oppposite
+ * direction?
+ */
+ 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) &&
@@ -3362,6 +3454,14 @@ interval_div(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
+ /* Dividing infinite interval by finite number keeps it infinite. */
+ /* TODO: Do we change the sign of infinity if factor is negative? */
+ if (interval_not_finite(span))
+ {
+ memcpy(result, span, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result->month = (int32) (span->month / factor);
result->day = (int32) (span->day / factor);
@@ -5955,3 +6055,63 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * TODO: possibly we should move these to a place along with other interval_*
+ * functions.
+ */
+
+/* Set the given interval to indicate infinite interval in the future. */
+static void
+interval_noend(Interval *interval)
+{
+ interval->time = DT_NOEND;
+ interval->day = PG_INT32_MAX;
+ interval->month = PG_INT32_MAX;
+}
+
+/* Does the given interval indicate infinite interval in the future? */
+static bool
+interval_is_noend(Interval *interval)
+{
+ /*
+ * TODO: Possibly it makes sense to just check one of the fields to reduce
+ * the number of instructions here. But it's safer to check all the three
+ * fields.
+ */
+ return interval->time == DT_NOEND &&
+ interval->day == PG_INT32_MAX &&
+ interval->month == PG_INT32_MAX;
+}
+
+/* Set the given interval to indicate infinite interval in the past. */
+static void
+interval_nobegin(Interval *interval)
+{
+ interval->time = DT_NOBEGIN;
+ interval->day = PG_INT32_MIN;
+ interval->month = PG_INT32_MIN;
+}
+
+/* Does the given interval indicate infinite interval in the past? */
+static bool
+interval_is_nobegin(Interval *interval)
+{
+ /*
+ * TODO: Possibly it makes sense to just check one of the fields to reduce
+ * the number of instructions here. But it's safer to check all the three
+ * fields.
+ */
+ return interval->time == DT_NOBEGIN &&
+ interval->day == PG_INT32_MIN &&
+ interval->month == PG_INT32_MIN;
+}
+
+/* Is the given interval infinite? */
+/* TODO: probably a material for a macro. */
+static bool
+interval_not_finite(Interval *interval)
+{
+ return interval_is_nobegin(interval) || interval_is_noend(interval);
+}
+
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 579e92e7b3..68d48a0b3e 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,20 @@ 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;
+ avg
+----------
+ infinity
+(1 row)
+
+select avg(f1) from interval_tbl where isfinite(f1);
avg
-------------------------------------------------
@ 4 years 1 mon 10 days 4 hours 18 mins 23 secs
@@ -820,8 +876,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 +888,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;
@@ -1776,3 +1834,488 @@ SELECT extract(epoch from interval '1000000000 days');
86400000000000.000000
(1 row)
+-- infinite intervals
+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 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 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';
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT -interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' * 2;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' * -2;
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' * 2;
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' * -2;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' / 3;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' / -3;
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' / 3;
+ interval
+-----------
+ -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_part('month', interval 'infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('month', interval '-infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_trunc('hour', interval 'infinity');
+ interval
+----------
+ infinity
+(1 row)
+
+SELECT date_trunc('hour', interval '-infinity');
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT extract(month from interval 'infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(month from interval '-infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT justify_days(interval 'infinity');
+ interval
+----------
+ infinity
+(1 row)
+
+SELECT justify_days(interval '-infinity');
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT justify_hours(interval 'infinity');
+ interval
+----------
+ infinity
+(1 row)
+
+SELECT justify_hours(interval '-infinity');
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT justify_interval(interval 'infinity');
+ interval
+----------
+ infinity
+(1 row)
+
+SELECT justify_interval(interval '-infinity');
+ interval
+-----------
+ -infinity
+(1 row)
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 0517b5b82b..03d50140a4 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');
@@ -141,6 +148,7 @@ SELECT * FROM INTERVAL_TBL;
-- 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;
@@ -578,3 +586,100 @@ SELECT f1,
-- internal overflow test case
SELECT extract(epoch from interval '1000000000 days');
+
+-- infinite intervals
+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 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 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_part('month', interval 'infinity');
+SELECT date_part('month', interval '-infinity');
+SELECT date_trunc('hour', interval 'infinity');
+SELECT date_trunc('hour', interval '-infinity');
+SELECT extract(month from interval 'infinity');
+SELECT extract(month 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
