Subject: Infinite Interval

From: Joseph Koshakow <[email-masked]>
Date: Sat, 10 Dec 2022 14:21:12 -0500

Hi all,

There have been multiple threads in the past discussing infinite
intervals:
https://www.postgresql.org/message-id/flat/4EB095C8.1050703%40agliodbs.com
https://www.postgresql.org/message-id/flat/200101241913.f0OJDUu45423%40hub.org
https://www.postgresql.org/message-id/flat/CANP8%2BjKTxQh4Mj%2BU3mWO3JHYb11SeQX9FW8SENrGbTdVxu6NNA%40mail.gmail.com

As well as an entry in the TODO list:
https://wiki.postgresql.org/wiki/Todo#Dates_and_Times

However, it doesn't seem like this was ever implemented. Is there still
any interest in this feature? If so, I'd like to try and implement it.

The proposed design from the most recent thread was to reserve
INT32_MAX months for infinity and INT32_MIN months for negative
infinity. As pointed out in the thread, these are currently valid
non-infinite intervals, but they are out of the documented range.

Thanks,
Joe Koshakow

---

From: Ashutosh Bapat <[email-masked]>
Date: Mon, 12 Dec 2022 18:35:45 +0530

Hi Joseph,
I stumbled upon this requirement a few times. So I started working on
this support in my spare time as a hobby project to understand
horology code in PostgreSQL. This was sitting in my repositories for
more than an year. Now that I have someone else showing an interest,
it's time for it to face the world. Rebased it, fixed conflicts.

PFA patch implementing infinite interval. It's still WIP, there are
TODOs in the code and also the commit message lists things that are
known to be incomplete. You might want to assess expected output
carefully

On Sun, Dec 11, 2022 at 12:51 AM Joseph Koshakow <[email-masked]> wrote:>
> non-infinite intervals, but they are out of the documented range.

The patch uses both months and days together to avoid this problem.

Please feel free to complete the patch, work on review comments etc. I
will help as and when I find time.

-- 
Best Wishes,
Ashutosh Bapat
From 09a8fef224a3682059fe1adf42957f693a41d242 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email-masked]>
Date: Thu, 30 Apr 2020 10:06:49 +0530
Subject: [PATCH] Support infinite interval

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 |  80 ++++++++++--
 src/test/regress/sql/interval.sql      |   8 ++
 4 files changed, 242 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..8242d28945 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;
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 0517b5b82b..92f60ad005 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;
-- 
2.25.1

---

From: Joseph Koshakow <[email-masked]>
Date: Thu, 15 Dec 2022 18:43:29 -0500

> known to be incomplete. You might want to assess expected output

That's great! I was also planning to just work on it as a hobby
project, so I'll try and review and add updates as I find free
time as well.

> The patch uses both months and days together to avoid this problem.

Can you expand on this part? I believe the full range of representable
intervals are considered valid as of v15.

- Joe Koshakow

---

From: Joseph Koshakow <[email-masked]>
Date: Sat, 17 Dec 2022 14:34:09 -0500

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 <[email-masked]>
Date: Sat, 17 Dec 2022 14:21:26 -0500
Subject: [PATCH] This is WIP.

TODOs
1. Various TODOs in code

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(-)

 

+		case DTK_LATE:

 	result = pstrdup(buf);
 
+	/*
 
+static void

 
+	/*
 
+	/*

+	/*

index 579e92e7b3..68d48a0b3e 100644

 

 
 
 SELECT r1.*, r2.*

 
 -- 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

 

 
 -- 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

---

From: Joseph Koshakow <[email-masked]>
Date: Sat, 17 Dec 2022 18:32:24 -0500

> is yes, we do change the sign of infinity.
I ended up doing some more work in the attached patch. Here are some
updates:

- I modified the arithmetic operators to more closely match IEEE
floats. Error messages are still all TODO, and they may have the wrong
error code.
- I implemented some more operators and functions.
- I moved the helper functions you created into macros in timestamp.h
to more closely match the implementation of infinite timestamps and
dates. Also so dates.c could access them.
- There seems to be an existing overflow error with interval
subtraction. Many of the arithmetic operators of the form
`X - Interval` are converted to `X + (-Interval)`. This will overflow
in the case that some interval field is INT32_MIN or INT64_MIN.
Additionally, negating a positive infinity interval won't result in a
negative infinity interval and vice versa. We'll have to come up with
an efficient solution for this.

- Joe Koshakow
From e6e764dd8f8423f2aec0fb3782f170c59557adf6 Mon Sep 17 00:00:00 2001
From: Joseph Koshakow <[email-masked]>
Date: Sat, 17 Dec 2022 14:21:26 -0500
Subject: [PATCH] This is WIP.

TODOs
1. Various TODOs in code

Ashutosh Bapat
---
 src/backend/utils/adt/date.c           |  20 +
 src/backend/utils/adt/datetime.c       |   2 +
 src/backend/utils/adt/timestamp.c      | 188 +++++++-
 src/include/datatype/timestamp.h       |  22 +
 src/test/regress/expected/interval.out | 613 ++++++++++++++++++++++++-
 src/test/regress/sql/interval.sql      | 121 +++++
 6 files changed, 949 insertions(+), 17 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/timestamp.c b/src/backend/utils/adt/timestamp.c
index 3f2508c0c4..e501e253a6 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 neg_interval_infinite(Interval *interval);
 
 /* 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];
 
+	if (INTERVAL_NOT_FINITE(span))
 
 	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)
 {
@@ -2083,7 +2116,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 +2809,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 +2887,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 +2928,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,10 +2969,29 @@ 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
 	{
+		elog(INFO, "Went to main branch. time: %ld, days: %d, months: %d", span->time, span->day, span->month);
 		if (span->month != 0)
 		{
 			struct pg_tm tt,
@@ -3032,8 +3094,19 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
 	TimestampTz result;
 	int			tz;
 
-	if (TIMESTAMP_NOT_FINITE(timestamp))
-		result = 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) && 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 (span->month != 0)
@@ -3133,6 +3206,13 @@ interval_um(PG_FUNCTION_ARGS)

+	if (INTERVAL_NOT_FINITE(interval))
+	{
+		memcpy(result, interval, sizeof(Interval));
+		neg_interval_infinite(result);
+		PG_RETURN_INTERVAL_P(result);
+	}
+
 	result->time = -interval->time;
 	/* overflow check copied from int4um */
 	if (interval->time != 0 && SAMESIGN(result->time, interval->time))
@@ -3192,6 +3272,24 @@ interval_pl(PG_FUNCTION_ARGS)

+	/*
+	 * 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_IS_NOEND(span1) && INTERVAL_IS_NOEND(span2)))
+	{
+		memcpy(result, span1, sizeof(Interval));
+		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")));
+	}
+
 	result->month = span1->month + span2->month;
 	/* overflow check copied from int4pl */
 	if (SAMESIGN(span1->month, span2->month) &&
@@ -3226,6 +3324,25 @@ interval_mi(PG_FUNCTION_ARGS)

+	/*
+	 * 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_IS_NOEND(span1) && INTERVAL_IS_NOBEGIN(span2)))
+	{
+		memcpy(result, span1, sizeof(Interval));
+		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")));
+	}
+
 	result->month = span1->month - span2->month;
 	/* overflow check copied from int4mi */
 	if (!SAMESIGN(span1->month, span2->month) &&
@@ -3271,6 +3388,18 @@ interval_mul(PG_FUNCTION_ARGS)

+	/*
+	 * Multiplying infinite interval by finite number keeps it infinite but may change
+	 * the sign.
+	 */
+	if (INTERVAL_NOT_FINITE(span))
+	{
+		memcpy(result, span, sizeof(Interval));
+		if (factor < 0.0)
+			neg_interval_infinite(result);
+		PG_RETURN_INTERVAL_P(result);
+	}
+
 	result_double = span->month * factor;
 	if (isnan(result_double) ||
 		result_double > INT_MAX || result_double < INT_MIN)
@@ -3362,6 +3491,18 @@ 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))
+	{
+		memcpy(result, span, sizeof(Interval));
+		if (factor < 0.0)
+			neg_interval_infinite(result);
+		PG_RETURN_INTERVAL_P(result);
+	}
+
 	result->month = (int32) (span->month / factor);
 	result->day = (int32) (span->day / factor);
 
@@ -3916,6 +4057,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 +4517,12 @@ interval_trunc(PG_FUNCTION_ARGS)

+	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);
@@ -5253,6 +5405,11 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
 	if (type == UNKNOWN_FIELD)
 		type = DecodeSpecial(0, lowunits, &val);
 
+	if (INTERVAL_NOT_FINITE(interval))
+	{
+		// TODO: copy logic from timestamp_part_common
+	}
+
 	if (type == UNITS)
 	{
 		interval2itm(*interval, tm);
@@ -5955,3 +6112,22 @@ 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 if it's infinite */
+static void
+neg_interval_infinite(Interval *interval)
+{
+	if (INTERVAL_IS_NOBEGIN(interval))
+	{
+		INTERVAL_NOEND(interval);
+	}
+	else if (INTERVAL_IS_NOEND(interval))
+	{
+		INTERVAL_NOBEGIN(interval);
+	}
+}
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.
index 579e92e7b3..2a3e9eaf52 100644

 

 
 
 SELECT r1.*, r2.*

 
 -- test inputting and outputting SQL standard interval literals
 SET IntervalStyle TO sql_standard;
@@ -1776,3 +1834,536 @@ SELECT extract(epoch from interval '1000000000 days');
  86400000000000.000000
 (1 row)
 
+-- infinite intervals
+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 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';
+ date_trunc 
+------------
+ date_trunc 
+------------
+ justify_interval 
+------------------
+ justify_interval 
+------------------
+ justify_interval 
+------------------
+ justify_interval 
+------------------
+ justify_interval 
+------------------
+ justify_interval 
+------------------
index 0517b5b82b..85b8007454 100644

 

 
 -- 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,116 @@ SELECT f1,
 
+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 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';

---

From: Joseph Koshakow <[email-masked]>
Date: Fri, 23 Dec 2022 18:03:36 -0500

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 <[email-masked]>
Date: Sat, 17 Dec 2022 14:21:26 -0500
Subject: [PATCH] This is WIP.

TODOs
1. Various TODOs in code

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(-)

 

 

index 3f2508c0c4..d108057ce5 100644
 
+static void EncodeSpecialInterval(Interval *interval, char *str);
+static void negate_interval(Interval *interval, Interval *result);

+		case DTK_LATE:

 	result = pstrdup(buf);
 
+	/*
 
+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;
 
@@ -2850,6 +2891,9 @@ interval_justify_hours(PG_FUNCTION_ARGS)
 
@@ -2888,6 +2932,9 @@ interval_justify_days(PG_FUNCTION_ARGS)
 
@@ -2926,7 +2973,25 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
 
@@ -3005,9 +3070,7 @@ timestamp_mi_interval(PG_FUNCTION_ARGS)
 	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;
 
@@ -3115,9 +3196,7 @@ timestamptz_mi_interval(PG_FUNCTION_ARGS)
 	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)

+	/*
+	 * 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)

+	/*
+	 * 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)

+	/*
+			negate_interval(span, result);
@@ -3362,6 +3503,19 @@ interval_div(PG_FUNCTION_ARGS)
 
+	/*
+			negate_interval(span, result);
 
@@ -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)

@@ -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

 
 /*
  * Julian date support.
index 579e92e7b3..7567d51661 100644

 

 
 
 SELECT r1.*, r2.*
 
 -- 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)
 
-select avg(f1) from interval_tbl;
@@ -820,8 +870,8 @@ SELECT interval '1 2:03:04.5678' minute to second(2);
@@ -832,7 +882,9 @@ SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
 
 -- 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

 
 
@@ -140,7 +147,7 @@ SELECT * FROM INTERVAL_TBL;
 
-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

---

From: Joseph Koshakow <[email-masked]>
Date: Fri, 30 Dec 2022 12:17:36 -0500

I have another update, I cleaned up some of the error messages, fixed
the horology tests, and ran pgindent.

- Joe
From 518c59be586abf5779c5727c2117b6a46b466503 Mon Sep 17 00:00:00 2001
From: Joseph Koshakow <[email-masked]>
Date: Sat, 17 Dec 2022 14:21:26 -0500
Subject: [PATCH] This is WIP.

TODOs
1. Various TODOs in code

Ashutosh Bapat
---
 src/backend/utils/adt/date.c           |  20 +
 src/backend/utils/adt/datetime.c       |  14 +-
 src/backend/utils/adt/timestamp.c      | 332 ++++++++-
 src/include/datatype/timestamp.h       |  22 +
 src/test/regress/expected/horology.out |   6 +-
 src/test/regress/expected/interval.out | 953 ++++++++++++++++++++++++-
 src/test/regress/sql/horology.sql      |   6 +-
 src/test/regress/sql/interval.sql      | 182 ++++-
 8 files changed, 1460 insertions(+), 75 deletions(-)

index 1cf7c7652d..c6259cd9c1 100644
 
+				 errmsg("cannot add infinite interval to time")));
 
+				 errmsg("cannot subtract infinite interval from time")));
 
+	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));

+	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;
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index b5b117a8ca..b60d91dfb8 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -70,7 +70,7 @@ static bool DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t,
 												  const char *abbr, pg_tz *tzp,
 												  int *offset, int *isdst);
 static pg_tz *FetchDynamicTimeZone(TimeZoneAbbrevTable *tbl, const datetkn *tp,
-								   DateTimeErrorExtra *extra);
+								   DateTimeErrorExtra * extra);

 const int	day_tab[2][13] =
@@ -977,7 +977,7 @@ ParseDateTime(const char *timestr, char *workbuf, size_t buflen,
 int
 DecodeDateTime(char **field, int *ftype, int nf,
 			   int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp,
-			   DateTimeErrorExtra *extra)
+			   DateTimeErrorExtra * extra)
 {
 	int			fmask = 0,
 				tmask,
@@ -1927,7 +1927,7 @@ DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t, const char *abbr, pg_tz *tzp,
 int
 DecodeTimeOnly(char **field, int *ftype, int nf,
 			   int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp,
-			   DateTimeErrorExtra *extra)
+			   DateTimeErrorExtra * extra)
 {
 	int			fmask = 0,
 				tmask,
@@ -3232,7 +3232,7 @@ DecodeTimezone(const char *str, int *tzp)
 int
 DecodeTimezoneAbbrev(int field, const char *lowtoken,
 					 int *ftype, int *offset, pg_tz **tz,
-					 DateTimeErrorExtra *extra)
+					 DateTimeErrorExtra * extra)
 {
 	const datetkn *tp;

@@ -4039,7 +4041,7 @@ DecodeUnits(int field, const char *lowtoken, int *val)
  * separate SQLSTATE codes, so ...
  */
 void
-DateTimeParseError(int dterr, DateTimeErrorExtra *extra,
+DateTimeParseError(int dterr, DateTimeErrorExtra * extra,
 				   const char *str, const char *datatype,
 				   Node *escontext)
 {
@@ -4918,7 +4920,7 @@ InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl)
  */
 static pg_tz *
 FetchDynamicTimeZone(TimeZoneAbbrevTable *tbl, const datetkn *tp,
-					 DateTimeErrorExtra *extra)
+					 DateTimeErrorExtra * extra)
 {
 	DynamicZoneAbbrev *dtza;
 
index 3f2508c0c4..bbdf0cfdea 100644

 
+		case DTK_LATE:

 	result = pstrdup(buf);
 
+	/*
 
+static void

@@ -2083,7 +2120,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));
 }

@@ -2775,6 +2814,9 @@ interval_justify_interval(PG_FUNCTION_ARGS)
 	result->day = span->day;
 	result->time = span->time;
 
@@ -2850,6 +2892,9 @@ interval_justify_hours(PG_FUNCTION_ARGS)
 
@@ -2888,6 +2933,9 @@ interval_justify_days(PG_FUNCTION_ARGS)
 
@@ -2926,7 +2974,25 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
 
+	 * 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. */
@@ -3005,9 +3071,7 @@ timestamp_mi_interval(PG_FUNCTION_ARGS)

 	return DirectFunctionCall2(timestamp_pl_interval,
 							   TimestampGetDatum(timestamp),
@@ -3032,7 +3096,25 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
 	TimestampTz result;
 	int			tz;
 
+	 * 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. */
@@ -3115,9 +3197,7 @@ timestamptz_mi_interval(PG_FUNCTION_ARGS)

 	return DirectFunctionCall2(timestamptz_pl_interval,
 							   TimestampGetDatum(timestamp),
@@ -3132,23 +3212,7 @@ interval_um(PG_FUNCTION_ARGS)
 	Interval   *result;

 	PG_RETURN_INTERVAL_P(result);
 }
@@ -3192,6 +3256,39 @@ interval_pl(PG_FUNCTION_ARGS)

+	/*
+	 * 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.
+	 */
+	/* TODO can be combined and simplified */
+	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 +3323,39 @@ interval_mi(PG_FUNCTION_ARGS)

+	/*
+	 * 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.
+	 */
+	/* TODO can be simplified and cleaned up */
+	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 +3401,19 @@ interval_mul(PG_FUNCTION_ARGS)

+	/*
+	 * Multiplying infinite interval by finite number keeps it infinite but
+	 * may change the sign.
+	 */
@@ -3362,6 +3505,19 @@ interval_div(PG_FUNCTION_ARGS)
 
+	/*
+	 * Dividing infinite interval by finite number keeps it infinite but may
+	 * change the sign.
+	 */
 
@@ -3916,6 +4072,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),
@@ -4371,6 +4532,12 @@ interval_trunc(PG_FUNCTION_ARGS)

@@ -5230,6 +5397,62 @@ extract_timestamptz(PG_FUNCTION_ARGS)
 }

+/*
+/*  TODO I don't actaully know if this is correct. */
+	/* TODO: Maybe everything should be returning inf/-inf? */
@@ -5253,6 +5476,34 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
 
@@ -5955,3 +6206,28 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
index d155f1b03b..ed7b2cc403 100644

+/*  TODO: Should we make custom NOBEGIN and NOEND constants for Interval? */
 
 /*
  * Julian date support.
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index de73683690..10b86963ee 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -939,6 +939,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          
 ----------------------------+-----------+----------------------------+----------------------------
@@ -1050,6 +1051,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   
 -------------+-------------------------------+-------------+-------------
@@ -1157,6 +1159,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    
 ----------------+-------------------------------+----------------+----------------
@@ -1432,6 +1435,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             
 ------------------------------+-------------------------------+------------------------------
@@ -1599,7 +1603,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 579e92e7b3..a31f17f3fb 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 r1.*, r2.*

 

 

 
 
+-- infinite intervals
+ERROR:  cannot add infinite interval to time
+ERROR:  cannot add infinite interval to time
+ERROR:  cannot subtract infinite interval from time
+ERROR:  cannot subtract infinite interval from time
+ERROR:  cannot add infinite interval to time
+ERROR:  cannot add infinite interval to time
+ERROR:  cannot subtract infinite interval from time
+ERROR:  cannot subtract infinite interval from time
+ERROR:  timestamps cannot be binned into infinite intervals
+ERROR:  timestamps cannot be binned into infinite intervals
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 2724a2bbc7..2494d852a6 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -173,14 +173,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
@@ -253,11 +256,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",

---

From: jian he <[email-masked]>
Date: Sat, 31 Dec 2022 10:39:10 +0530

> the horology tests, and ran pgindent.

Hi, there.

Since in float8 you can use '+inf', '+infinity', So should we also make
interval '+infinity' valid?
Also in timestamp. '+infinity'::timestamp is invalid, should we make it
valid.

In float8, select float8 'inf' / float8 'inf' return NaN. Now in your patch
 select interval 'infinity' / float8 'infinity'; returns infinity.
I am not sure it's right. I found this related post (
https://math.stackexchange.com/questions/181304/what-is-infinity-divided-by-infinity
).

  I recommend David Deutsch's <<The Beginning of Infinity>>

  Jian