Jian,
I incorporated your changes and updated interval.out and ran
pgindent. Looks like some of the error messages have changed and we
have some issues with parsing "+infinity" after rebasing.
- Joe
From 4bf672f9079322cffde635dff2078582fca55f09 Mon Sep 17 00:00:00 2001
From: Joseph Koshakow <kosh...@gmail.com>
Date: Sat, 17 Dec 2022 14:21:26 -0500
Subject: [PATCH] This is WIP.
TODOs
1. Various TODOs in code.
2. Correctly implement interval_part for infinite intervals.
3. Fix Tests.
4. Should we just use the months field to test for infinity?
5. Update docs
Ashutosh Bapat and Joe Koshakow
---
src/backend/utils/adt/date.c | 20 +
src/backend/utils/adt/datetime.c | 14 +-
src/backend/utils/adt/timestamp.c | 425 ++++++++++++---
src/include/datatype/timestamp.h | 22 +
src/test/regress/expected/horology.out | 6 +-
src/test/regress/expected/interval.out | 691 +++++++++++++++++++++++--
src/test/regress/sql/horology.sql | 6 +-
src/test/regress/sql/interval.sql | 191 ++++++-
8 files changed, 1264 insertions(+), 111 deletions(-)
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 99171d9c92..8334b9053f 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -2067,6 +2067,11 @@ time_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeADT result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite interval to time")));
+
result = time + span->time;
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
@@ -2085,6 +2090,11 @@ time_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeADT result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot subtract infinite interval from time")));
+
result = time - span->time;
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
@@ -2599,6 +2609,11 @@ timetz_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeTzADT *result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite interval to time")));
+
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
result->time = time->time + span->time;
@@ -2621,6 +2636,11 @@ timetz_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeTzADT *result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot subtract infinite interval from time")));
+
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
result->time = time->time - span->time;
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index d166613895..4192e7a74b 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] =
@@ -978,7 +978,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,
@@ -1928,7 +1928,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,
@@ -3233,7 +3233,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;
@@ -3635,6 +3635,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;
@@ -4040,7 +4042,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)
{
@@ -4919,7 +4921,7 @@ InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl)
*/
static pg_tz *
FetchDynamicTimeZone(TimeZoneAbbrevTable *tbl, const datetkn *tp,
- DateTimeErrorExtra *extra)
+ DateTimeErrorExtra * extra)
{
DynamicZoneAbbrev *dtza;
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 928c330897..87277f2f3b 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -79,6 +79,8 @@ static bool AdjustIntervalForTypmod(Interval *interval, int32 typmod,
static TimestampTz timestamp2timestamptz(Timestamp timestamp);
static Timestamp timestamptz2timestamp(TimestampTz timestamp);
+static void EncodeSpecialInterval(Interval *interval, char *str);
+static void negate_interval(Interval *interval, Interval *result);
/* common code for timestamptypmodin and timestamptztypmodin */
static int32
@@ -943,6 +945,14 @@ interval_in(PG_FUNCTION_ARGS)
errmsg("interval out of range")));
break;
+ case DTK_LATE:
+ INTERVAL_NOEND(result);
+ break;
+
+ case DTK_EARLY:
+ INTERVAL_NOBEGIN(result);
+ break;
+
default:
elog(ERROR, "unexpected dtype %d while parsing interval \"%s\"",
dtype, str);
@@ -965,8 +975,13 @@ interval_out(PG_FUNCTION_ARGS)
*itm = &tt;
char buf[MAXDATELEN + 1];
- interval2itm(*span, itm);
- EncodeInterval(itm, IntervalStyle, buf);
+ if (INTERVAL_NOT_FINITE(span))
+ EncodeSpecialInterval(span, buf);
+ else
+ {
+ interval2itm(*span, itm);
+ EncodeInterval(itm, IntervalStyle, buf);
+ }
result = pstrdup(buf);
PG_RETURN_CSTRING(result);
@@ -1352,6 +1367,13 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod,
INT64CONST(0)
};
+ /*
+ * Infinite interval after being subjected to typmod conversion remains
+ * infinite.
+ */
+ if (INTERVAL_NOT_FINITE(interval))
+ return true;
+
/*
* Unspecified range and precision? Then not necessary to adjust. Setting
* typmod to -1 is the convention for all data types.
@@ -1545,6 +1567,17 @@ EncodeSpecialTimestamp(Timestamp dt, char *str)
elog(ERROR, "invalid argument for EncodeSpecialTimestamp");
}
+static void
+EncodeSpecialInterval(Interval *interval, char *str)
+{
+ if (INTERVAL_IS_NOBEGIN(interval))
+ strcpy(str, EARLY);
+ else if (INTERVAL_IS_NOEND(interval))
+ strcpy(str, LATE);
+ else /* shouldn't happen */
+ elog(ERROR, "invalid argument for EncodeSpecialInterval");
+}
+
Datum
now(PG_FUNCTION_ARGS)
{
@@ -2027,6 +2060,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;
}
@@ -2044,6 +2079,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;
}
@@ -2077,7 +2114,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));
}
@@ -2769,6 +2808,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))
@@ -2844,6 +2886,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,
@@ -2882,6 +2927,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))
@@ -2920,8 +2968,26 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
Timestamp result;
- 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))
+ || (INTERVAL_IS_NOBEGIN(span) && !TIMESTAMP_NOT_FINITE(timestamp))
+ || (!INTERVAL_NOT_FINITE(span) && TIMESTAMP_IS_NOBEGIN(timestamp)))
+ TIMESTAMP_NOBEGIN(result);
+
+ else if ((INTERVAL_IS_NOEND(span) && TIMESTAMP_IS_NOEND(timestamp))
+ || (INTERVAL_IS_NOEND(span) && !TIMESTAMP_NOT_FINITE(timestamp))
+ || (!INTERVAL_NOT_FINITE(span) && TIMESTAMP_IS_NOEND(timestamp)))
+ TIMESTAMP_NOEND(result);
+
+ else if ((!INTERVAL_NOT_FINITE(span) && TIMESTAMP_NOT_FINITE(timestamp))
+ || (INTERVAL_NOT_FINITE(span) && !TIMESTAMP_NOT_FINITE(timestamp)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite values with opposite signs")));
else
{
if (span->month != 0)
@@ -2999,9 +3065,7 @@ timestamp_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
Interval tspan;
- tspan.month = -span->month;
- tspan.day = -span->day;
- tspan.time = -span->time;
+ negate_interval(span, &tspan);
return DirectFunctionCall2(timestamp_pl_interval,
TimestampGetDatum(timestamp),
@@ -3026,8 +3090,26 @@ 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))
+ || (INTERVAL_IS_NOBEGIN(span) && !TIMESTAMP_NOT_FINITE(timestamp))
+ || (!INTERVAL_NOT_FINITE(span) && TIMESTAMP_IS_NOBEGIN(timestamp)))
+ TIMESTAMP_NOBEGIN(result);
+
+ else if ((INTERVAL_IS_NOEND(span) && TIMESTAMP_IS_NOEND(timestamp))
+ || (INTERVAL_IS_NOEND(span) && !TIMESTAMP_NOT_FINITE(timestamp))
+ || (!INTERVAL_NOT_FINITE(span) && TIMESTAMP_IS_NOEND(timestamp)))
+ TIMESTAMP_NOEND(result);
+
+ else if ((!INTERVAL_NOT_FINITE(span) && TIMESTAMP_NOT_FINITE(timestamp))
+ || (INTERVAL_NOT_FINITE(span) && !TIMESTAMP_NOT_FINITE(timestamp)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite values with opposite signs")));
else
{
if (span->month != 0)
@@ -3109,9 +3191,7 @@ timestamptz_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
Interval tspan;
- tspan.month = -span->month;
- tspan.day = -span->day;
- tspan.time = -span->time;
+ negate_interval(span, &tspan);
return DirectFunctionCall2(timestamptz_pl_interval,
TimestampGetDatum(timestamp),
@@ -3126,23 +3206,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);
}
@@ -3186,28 +3250,57 @@ interval_pl(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
- result->month = span1->month + span2->month;
- /* overflow check copied from int4pl */
- if (SAMESIGN(span1->month, span2->month) &&
- !SAMESIGN(result->month, span1->month))
+ /*
+ * 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_NOBEGIN(span1) && !INTERVAL_NOT_FINITE(span2))
+ || (!INTERVAL_NOT_FINITE(span1) && INTERVAL_IS_NOBEGIN(span2)))
+ INTERVAL_NOBEGIN(result);
+
+ else if
+ ((INTERVAL_IS_NOEND(span1) && INTERVAL_IS_NOEND(span2))
+ || (INTERVAL_IS_NOEND(span1) && !INTERVAL_NOT_FINITE(span2))
+ || (!INTERVAL_NOT_FINITE(span1) && INTERVAL_IS_NOEND(span2)))
+ INTERVAL_NOEND(result);
+
+ else if (INTERVAL_NOT_FINITE(span1) && INTERVAL_NOT_FINITE(span2))
+ {
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ errmsg("cannot add infinite values with opposite signs")));
+ }
+ else
+ {
+ result->month = span1->month + span2->month;
+ /* overflow check copied from int4pl */
+ if (SAMESIGN(span1->month, span2->month) &&
+ !SAMESIGN(result->month, span1->month))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->day = span1->day + span2->day;
- if (SAMESIGN(span1->day, span2->day) &&
- !SAMESIGN(result->day, span1->day))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ result->day = span1->day + span2->day;
+ if (SAMESIGN(span1->day, span2->day) &&
+ !SAMESIGN(result->day, span1->day))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->time = span1->time + span2->time;
- if (SAMESIGN(span1->time, span2->time) &&
- !SAMESIGN(result->time, span1->time))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ result->time = span1->time + span2->time;
+ if (SAMESIGN(span1->time, span2->time) &&
+ !SAMESIGN(result->time, span1->time))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ }
PG_RETURN_INTERVAL_P(result);
}
@@ -3220,28 +3313,56 @@ interval_mi(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
- result->month = span1->month - span2->month;
- /* overflow check copied from int4mi */
- if (!SAMESIGN(span1->month, span2->month) &&
- !SAMESIGN(result->month, span1->month))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ /*
+ * Subtracting two infinite intervals with different signs results in an
+ * infinite interval with the same sign as the left operand. Subtracting
+ * two infinte intervals with the same sign results in an error.
+ */
+ if ((INTERVAL_IS_NOBEGIN(span1) && INTERVAL_IS_NOEND(span2))
+ || (INTERVAL_IS_NOBEGIN(span1) && !INTERVAL_NOT_FINITE(span2))
+ || (!INTERVAL_NOT_FINITE(span1) && INTERVAL_IS_NOEND(span2)))
+ INTERVAL_NOBEGIN(result);
- result->day = span1->day - span2->day;
- if (!SAMESIGN(span1->day, span2->day) &&
- !SAMESIGN(result->day, span1->day))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ else if ((INTERVAL_IS_NOEND(span1) && INTERVAL_IS_NOBEGIN(span2))
+ || (INTERVAL_IS_NOEND(span1) && !INTERVAL_NOT_FINITE(span2))
+ || (!INTERVAL_NOT_FINITE(span1) && INTERVAL_IS_NOBEGIN(span2)))
+ INTERVAL_NOEND(result);
- result->time = span1->time - span2->time;
- if (!SAMESIGN(span1->time, span2->time) &&
- !SAMESIGN(result->time, span1->time))
+ else if (INTERVAL_NOT_FINITE(span1) && INTERVAL_NOT_FINITE(span2))
+ {
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ errmsg("cannot subtract infinite values with the same signs")));
+ }
+ else
+ {
+ result->month = span1->month - span2->month;
+ /* overflow check copied from int4mi */
+ if (!SAMESIGN(span1->month, span2->month) &&
+ !SAMESIGN(result->month, span1->month))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ result->day = span1->day - span2->day;
+ if (!SAMESIGN(span1->day, span2->day) &&
+ !SAMESIGN(result->day, span1->day))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ result->time = span1->time - span2->time;
+ if (!SAMESIGN(span1->time, span2->time) &&
+ !SAMESIGN(result->time, span1->time))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ }
PG_RETURN_INTERVAL_P(result);
}
@@ -3265,6 +3386,24 @@ interval_mul(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ if (isnan(factor))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ /*
+ * Multiplying infinite interval by finite number keeps it infinite but
+ * may change the sign.
+ */
+ if (INTERVAL_NOT_FINITE(span))
+ {
+ if (factor < 0.0)
+ negate_interval(span, result);
+ else
+ memcpy(result, span, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result_double = span->month * factor;
if (isnan(result_double) ||
result_double > INT_MAX || result_double < INT_MIN)
@@ -3325,6 +3464,11 @@ interval_mul(PG_FUNCTION_ARGS)
errmsg("interval out of range")));
result->time = (int64) result_double;
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
PG_RETURN_INTERVAL_P(result);
}
@@ -3356,6 +3500,29 @@ interval_div(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
+ if (isnan(factor))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ /*
+ * Dividing infinite interval by finite number keeps it infinite but may
+ * change the sign.
+ */
+ if (INTERVAL_NOT_FINITE(span))
+ {
+ if (isinf(factor))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ if (factor < 0.0)
+ negate_interval(span, result);
+ else
+ memcpy(result, span, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result->month = (int32) (span->month / factor);
result->day = (int32) (span->day / factor);
@@ -3377,6 +3544,11 @@ interval_div(PG_FUNCTION_ARGS)
result->day += (int32) month_remainder_days;
result->time = rint(span->time / factor + sec_remainder * USECS_PER_SEC);
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
PG_RETURN_INTERVAL_P(result);
}
@@ -3910,6 +4082,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),
@@ -4365,6 +4542,12 @@ interval_trunc(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ if (INTERVAL_NOT_FINITE(interval))
+ {
+ memcpy(result, interval, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
VARSIZE_ANY_EXHDR(units),
false);
@@ -5224,6 +5407,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.
*/
@@ -5247,6 +5486,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);
@@ -5949,3 +6216,33 @@ 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;
+
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ }
+}
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index 21a37e21e9..ba9f39a445 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -160,6 +160,28 @@ struct pg_itm_in
#define TIMESTAMP_NOT_FINITE(j) (TIMESTAMP_IS_NOBEGIN(j) || TIMESTAMP_IS_NOEND(j))
+/* TODO: Should we make custom NOBEGIN and NOEND constants for Interval? */
+#define INTERVAL_NOBEGIN(i) \
+ do { \
+ (i->time) = DT_NOBEGIN; \
+ (i->day) = PG_INT32_MIN; \
+ (i->month) = PG_INT32_MIN; \
+ } while (0)
+
+#define INTERVAL_IS_NOBEGIN(i) \
+ ((i->time) == DT_NOBEGIN && (i->day) == PG_INT32_MIN && (i->month) == PG_INT32_MIN)
+
+#define INTERVAL_NOEND(i) \
+ do { \
+ (i->time) = DT_NOEND; \
+ (i->day) = PG_INT32_MAX; \
+ (i->month) = PG_INT32_MAX; \
+ } while (0)
+
+#define INTERVAL_IS_NOEND(i) \
+ ((i->time) == DT_NOEND && (i->day) == PG_INT32_MAX && (i->month) == PG_INT32_MAX)
+
+#define INTERVAL_NOT_FINITE(i) (INTERVAL_IS_NOBEGIN(i) || INTERVAL_IS_NOEND(i))
/*
* Julian date support.
diff --git a/src/test/regress/expected/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..84b4dd920a 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -52,6 +52,18 @@ SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
9 years 1 mon -12 days +13:14:00
(1 row)
+SELECT INTERVAL 'infinity' AS "eternity";
+ eternity
+----------
+ infinity
+(1 row)
+
+SELECT INTERVAL '-infinity' AS "beginning of time";
+ beginning of time
+-------------------
+ -infinity
+(1 row)
+
CREATE TABLE INTERVAL_TBL (f1 interval);
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
@@ -63,6 +75,8 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity');
-- badly formatted interval
INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
ERROR: invalid input syntax for type interval: "badly formatted interval"
@@ -72,6 +86,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 +135,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 +152,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 +163,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 +173,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 +192,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 +208,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 +217,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 +253,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 +262,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 +272,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 +433,14 @@ SELECT * FROM INTERVAL_TBL;
@ 6 years
@ 5 mons
@ 5 mons 12 hours
-(10 rows)
+ infinity
+ -infinity
+(12 rows)
-- test avg(interval), which is somewhat fragile since people have been
-- known to change the allowed input syntax for type interval without
-- updating pg_aggregate.agginitval
-select avg(f1) from interval_tbl;
+select avg(f1) from interval_tbl where isfinite(f1);
avg
-------------------------------------------------
@ 4 years 1 mon 10 days 4 hours 18 mins 23 secs
@@ -820,8 +869,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 +881,9 @@ SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
6 years | 6 years | 6 years
5 mons | 5 mons | 00:00:00
5 mons 12:00:00 | 5 mons 12:00:00 | 00:00:00
-(10 rows)
+ infinity | infinity | infinity
+ -infinity | -infinity | -infinity
+(12 rows)
-- test inputting and outputting SQL standard interval literals
SET IntervalStyle TO sql_standard;
@@ -1578,31 +1629,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 +1732,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 +1820,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 +1831,571 @@ 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: cannot add infinite values with opposite signs
+SELECT date '-infinity' + interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT date '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date 'infinity' - interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT date 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date '-infinity' - interval '-infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT interval 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' + interval '-infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT interval '-infinity' + interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT interval '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval 'infinity' + interval '10 days';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' + interval '10 days';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' + interval '1 month 1 day 1 us';
+ERROR: interval out of range
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' + interval '-1 month -1 day -1 us';
+ERROR: interval out of range
+SELECT interval 'infinity' - interval 'infinity';
+ERROR: cannot subtract infinite values with the same signs
+SELECT interval 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' - interval '-infinity';
+ERROR: cannot subtract infinite values with the same signs
+SELECT interval 'infinity' - interval '10 days';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' - interval '10 days';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' - interval '-1 month -1 day -1 us';
+ERROR: interval out of range
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' - interval '1 month 1 day 1 us';
+ERROR: interval out of range
+SELECT timestamp '1995-08-06 12:30:15' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp '1995-08-06 12:30:15' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '1995-08-06 12:30:15' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '1995-08-06 12:30:15' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp 'infinity' + interval '-infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT timestamp '-infinity' + interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT timestamp '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp 'infinity' - interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT timestamp 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '-infinity' - interval '-infinity';
+ERROR: cannot add infinite values with opposite signs
+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: cannot add infinite values with opposite signs
+SELECT timestamptz '-infinity' + interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT timestamptz '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz 'infinity' - interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT timestamptz 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - interval '-infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT time '11:27:42' + interval 'infinity';
+ERROR: cannot add infinite interval to time
+SELECT time '11:27:42' + interval '-infinity';
+ERROR: cannot add infinite interval to time
+SELECT time '11:27:42' - interval 'infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT time '11:27:42' - interval '-infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT timetz '11:27:42' + interval 'infinity';
+ERROR: cannot add infinite interval to time
+SELECT timetz '11:27:42' + interval '-infinity';
+ERROR: cannot add infinite interval to time
+SELECT timetz '11:27:42' - interval 'infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT timetz '11:27:42' - interval '-infinity';
+ERROR: cannot subtract infinite interval from time
+DO $$
+DECLARE
+ intv interval;
+ intv1 interval;
+ intvs interval[] := '{+infinity,-infinity}';
+ OPERATOR text[] := '{<,<=,=, >,>=,<>}';
+ opr text;
+ result boolean;
+BEGIN
+ FOREACH intv IN ARRAY intvs LOOP
+ FOREACH opr IN ARRAY OPERATOR LOOP
+ FOREACH intv1 IN ARRAY intvs LOOP
+ EXECUTE 'select interval ' || quote_literal(intv) || ' '
+ || opr || ' interval ' || quote_literal(intv1) INTO result;
+ RAISE NOTICE '%'
+ ,(format('%10s %2s %10s %2s'
+ ,intv
+ ,opr
+ ,intv1
+ ,result));
+ END LOOP;
+ END LOOP;
+ END LOOP;
+END
+$$;
+NOTICE: infinity < infinity f
+NOTICE: infinity < -infinity f
+NOTICE: infinity <= infinity t
+NOTICE: infinity <= -infinity f
+NOTICE: infinity = infinity t
+NOTICE: infinity = -infinity f
+NOTICE: infinity > infinity f
+NOTICE: infinity > -infinity t
+NOTICE: infinity >= infinity t
+NOTICE: infinity >= -infinity t
+NOTICE: infinity <> infinity f
+NOTICE: infinity <> -infinity t
+NOTICE: -infinity < infinity t
+NOTICE: -infinity < -infinity f
+NOTICE: -infinity <= infinity t
+NOTICE: -infinity <= -infinity t
+NOTICE: -infinity = infinity f
+NOTICE: -infinity = -infinity t
+NOTICE: -infinity > infinity f
+NOTICE: -infinity > -infinity f
+NOTICE: -infinity >= infinity f
+NOTICE: -infinity >= -infinity t
+NOTICE: -infinity <> infinity t
+NOTICE: -infinity <> -infinity f
+SELECT -interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT -interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT -interval '-2147483647 months -2147483647 days -9223372036854775807 us';
+ERROR: interval out of range
+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' * 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' * '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval 'infinity' * 'nan';
+ERROR: interval out of range
+SELECT interval '-infinity' * 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' * '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' * 'nan';
+ERROR: interval out of range
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' * 2;
+ERROR: interval out of range
+SELECT interval 'infinity' / 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 interval 'infinity' / 'infinity';
+ERROR: interval out of range
+SELECT interval 'infinity' / '-infinity';
+ERROR: interval out of range
+SELECT interval 'infinity' / 'nan';
+ERROR: interval out of range
+SELECT interval '-infinity' / 'infinity';
+ERROR: interval out of range
+SELECT interval '-infinity' / '-infinity';
+ERROR: interval out of range
+SELECT interval '-infinity' / 'nan';
+ERROR: interval out of range
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' / 0.5;
+ERROR: interval out of range
+SELECT date_bin('infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+ERROR: timestamps cannot be binned into infinite intervals
+SELECT date_bin('-infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+ERROR: timestamps cannot be binned into infinite intervals
+SELECT date_trunc('hour', interval 'infinity');
+ date_trunc
+------------
+ infinity
+(1 row)
+
+SELECT date_trunc('hour', interval '-infinity');
+ date_trunc
+------------
+ -infinity
+(1 row)
+
+DO $$
+DECLARE
+ intv interval;
+ intvs interval[] := '{+infinity,-infinity}';
+ units text[] := '{us,ms,second,minute,hour,day,month
+ ,quarter,year,decade,century,millennium,epoch}';
+ unit text;
+ result double precision;
+BEGIN
+ FOREACH intv IN ARRAY intvs LOOP
+ FOREACH unit IN ARRAY units LOOP
+ EXECUTE 'select date_part( ' || quote_literal(unit) || ' ,interval '
+ || quote_literal(intv) || ' )' INTO result;
+ RAISE NOTICE '% %'
+ ,(format('date_part(%10s, interval %10s) yield', unit, intv))
+ ,result;
+ END LOOP;
+ END LOOP;
+END
+$$;
+NOTICE: date_part( us, interval infinity) yield <NULL>
+NOTICE: date_part( ms, interval infinity) yield <NULL>
+NOTICE: date_part( second, interval infinity) yield <NULL>
+NOTICE: date_part( minute, interval infinity) yield <NULL>
+NOTICE: date_part( hour, interval infinity) yield <NULL>
+NOTICE: date_part( day, interval infinity) yield <NULL>
+NOTICE: date_part( month, interval infinity) yield <NULL>
+NOTICE: date_part( quarter, interval infinity) yield <NULL>
+NOTICE: date_part( year, interval infinity) yield Infinity
+NOTICE: date_part( decade, interval infinity) yield Infinity
+NOTICE: date_part( century, interval infinity) yield Infinity
+NOTICE: date_part(millennium, interval infinity) yield Infinity
+NOTICE: date_part( epoch, interval infinity) yield Infinity
+NOTICE: date_part( us, interval -infinity) yield <NULL>
+NOTICE: date_part( ms, interval -infinity) yield <NULL>
+NOTICE: date_part( second, interval -infinity) yield <NULL>
+NOTICE: date_part( minute, interval -infinity) yield <NULL>
+NOTICE: date_part( hour, interval -infinity) yield <NULL>
+NOTICE: date_part( day, interval -infinity) yield <NULL>
+NOTICE: date_part( month, interval -infinity) yield <NULL>
+NOTICE: date_part( quarter, interval -infinity) yield <NULL>
+NOTICE: date_part( year, interval -infinity) yield -Infinity
+NOTICE: date_part( decade, interval -infinity) yield -Infinity
+NOTICE: date_part( century, interval -infinity) yield -Infinity
+NOTICE: date_part(millennium, interval -infinity) yield -Infinity
+NOTICE: date_part( epoch, interval -infinity) yield -Infinity
+DO $$
+DECLARE
+ intv interval;
+ intvs interval[] := '{+infinity,-infinity}';
+ units text[] := '{us,ms,second,minute,hour,day,month
+ ,quarter,year,decade,century,millennium,epoch}';
+ unit text;
+ result numeric;
+BEGIN
+ FOREACH intv IN ARRAY intvs LOOP
+ FOREACH unit IN ARRAY units LOOP
+ EXECUTE 'select extract( ' || quote_literal(unit)
+ || ' from interval ' || quote_literal(intv) || ' )' INTO result;
+ RAISE NOTICE '% %'
+ ,(format('extract(%10s from interval %9s) yield', unit, intv))
+ ,result;
+ END LOOP;
+ END LOOP;
+END
+$$;
+NOTICE: extract( us from interval infinity) yield <NULL>
+NOTICE: extract( ms from interval infinity) yield <NULL>
+NOTICE: extract( second from interval infinity) yield <NULL>
+NOTICE: extract( minute from interval infinity) yield <NULL>
+NOTICE: extract( hour from interval infinity) yield <NULL>
+NOTICE: extract( day from interval infinity) yield <NULL>
+NOTICE: extract( month from interval infinity) yield <NULL>
+NOTICE: extract( quarter from interval infinity) yield <NULL>
+NOTICE: extract( year from interval infinity) yield Infinity
+NOTICE: extract( decade from interval infinity) yield Infinity
+NOTICE: extract( century from interval infinity) yield Infinity
+NOTICE: extract(millennium from interval infinity) yield Infinity
+NOTICE: extract( epoch from interval infinity) yield Infinity
+NOTICE: extract( us from interval -infinity) yield <NULL>
+NOTICE: extract( ms from interval -infinity) yield <NULL>
+NOTICE: extract( second from interval -infinity) yield <NULL>
+NOTICE: extract( minute from interval -infinity) yield <NULL>
+NOTICE: extract( hour from interval -infinity) yield <NULL>
+NOTICE: extract( day from interval -infinity) yield <NULL>
+NOTICE: extract( month from interval -infinity) yield <NULL>
+NOTICE: extract( quarter from interval -infinity) yield <NULL>
+NOTICE: extract( year from interval -infinity) yield -Infinity
+NOTICE: extract( decade from interval -infinity) yield -Infinity
+NOTICE: extract( century from interval -infinity) yield -Infinity
+NOTICE: extract(millennium from interval -infinity) yield -Infinity
+NOTICE: extract( epoch from interval -infinity) yield -Infinity
+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/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",
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 0517b5b82b..51195d71d1 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -14,6 +14,8 @@ SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";
SELECT INTERVAL '1.5 months' AS "One month 15 days";
SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
+SELECT INTERVAL 'infinity' AS "eternity";
+SELECT INTERVAL '-infinity' AS "beginning of time";
CREATE TABLE INTERVAL_TBL (f1 interval);
@@ -27,10 +29,13 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity');
-- badly formatted interval
INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity');
-- Test non-error-throwing API
SELECT pg_input_is_valid('1.5 weeks', 'interval');
@@ -140,7 +145,7 @@ SELECT * FROM INTERVAL_TBL;
-- known to change the allowed input syntax for type interval without
-- updating pg_aggregate.agginitval
-select avg(f1) from interval_tbl;
+select avg(f1) from interval_tbl where isfinite(f1);
-- test long interval input
select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 seconds'::interval;
@@ -509,13 +514,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 +583,179 @@ 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 '2147483646 months 2147483646 days 9223372036854775806 us' + interval '1 month 1 day 1 us';
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' + interval '-1 month -1 day -1 us';
+SELECT interval 'infinity' - interval 'infinity';
+SELECT interval 'infinity' - interval '-infinity';
+SELECT interval '-infinity' - interval 'infinity';
+SELECT interval '-infinity' - interval '-infinity';
+SELECT interval 'infinity' - interval '10 days';
+SELECT interval '-infinity' - interval '10 days';
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' - interval '-1 month -1 day -1 us';
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' - interval '1 month 1 day 1 us';
+SELECT timestamp '1995-08-06 12:30:15' + interval 'infinity';
+SELECT timestamp '1995-08-06 12:30:15' + interval '-infinity';
+SELECT timestamp '1995-08-06 12:30:15' - interval 'infinity';
+SELECT timestamp '1995-08-06 12:30:15' - interval '-infinity';
+SELECT timestamp 'infinity' + interval 'infinity';
+SELECT timestamp 'infinity' + interval '-infinity';
+SELECT timestamp '-infinity' + interval 'infinity';
+SELECT timestamp '-infinity' + interval '-infinity';
+SELECT timestamp 'infinity' - interval 'infinity';
+SELECT timestamp 'infinity' - interval '-infinity';
+SELECT timestamp '-infinity' - interval 'infinity';
+SELECT timestamp '-infinity' - interval '-infinity';
+SELECT timestamptz '1995-08-06 12:30:15' + interval 'infinity';
+SELECT timestamptz '1995-08-06 12:30:15' + interval '-infinity';
+SELECT timestamptz '1995-08-06 12:30:15' - interval 'infinity';
+SELECT timestamptz '1995-08-06 12:30:15' - interval '-infinity';
+SELECT timestamptz 'infinity' + interval 'infinity';
+SELECT timestamptz 'infinity' + interval '-infinity';
+SELECT timestamptz '-infinity' + interval 'infinity';
+SELECT timestamptz '-infinity' + interval '-infinity';
+SELECT timestamptz 'infinity' - interval 'infinity';
+SELECT timestamptz 'infinity' - interval '-infinity';
+SELECT timestamptz '-infinity' - interval 'infinity';
+SELECT timestamptz '-infinity' - interval '-infinity';
+SELECT time '11:27:42' + interval 'infinity';
+SELECT time '11:27:42' + interval '-infinity';
+SELECT time '11:27:42' - interval 'infinity';
+SELECT time '11:27:42' - interval '-infinity';
+SELECT timetz '11:27:42' + interval 'infinity';
+SELECT timetz '11:27:42' + interval '-infinity';
+SELECT timetz '11:27:42' - interval 'infinity';
+SELECT timetz '11:27:42' - interval '-infinity';
+
+DO $$
+DECLARE
+ intv interval;
+ intv1 interval;
+ intvs interval[] := '{+infinity,-infinity}';
+ OPERATOR text[] := '{<,<=,=, >,>=,<>}';
+ opr text;
+ result boolean;
+BEGIN
+ FOREACH intv IN ARRAY intvs LOOP
+ FOREACH opr IN ARRAY OPERATOR LOOP
+ FOREACH intv1 IN ARRAY intvs LOOP
+ EXECUTE 'select interval ' || quote_literal(intv) || ' '
+ || opr || ' interval ' || quote_literal(intv1) INTO result;
+ RAISE NOTICE '%'
+ ,(format('%10s %2s %10s %2s'
+ ,intv
+ ,opr
+ ,intv1
+ ,result));
+ END LOOP;
+ END LOOP;
+ END LOOP;
+END
+$$;
+
+SELECT -interval 'infinity';
+SELECT -interval '-infinity';
+SELECT -interval '-2147483647 months -2147483647 days -9223372036854775807 us';
+SELECT interval 'infinity' * 2;
+SELECT interval 'infinity' * -2;
+SELECT interval '-infinity' * 2;
+SELECT interval '-infinity' * -2;
+SELECT interval 'infinity' * 'infinity';
+SELECT interval 'infinity' * '-infinity';
+SELECT interval 'infinity' * 'nan';
+SELECT interval '-infinity' * 'infinity';
+SELECT interval '-infinity' * '-infinity';
+SELECT interval '-infinity' * 'nan';
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' * 2;
+
+SELECT interval 'infinity' / 3;
+SELECT interval 'infinity' / -3;
+SELECT interval '-infinity' / 3;
+SELECT interval '-infinity' / -3;
+SELECT interval 'infinity' / 'infinity';
+SELECT interval 'infinity' / '-infinity';
+SELECT interval 'infinity' / 'nan';
+SELECT interval '-infinity' / 'infinity';
+SELECT interval '-infinity' / '-infinity';
+SELECT interval '-infinity' / 'nan';
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' / 0.5;
+
+SELECT date_bin('infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+SELECT date_bin('-infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+SELECT date_trunc('hour', interval 'infinity');
+SELECT date_trunc('hour', interval '-infinity');
+
+DO $$
+DECLARE
+ intv interval;
+ intvs interval[] := '{+infinity,-infinity}';
+ units text[] := '{us,ms,second,minute,hour,day,month
+ ,quarter,year,decade,century,millennium,epoch}';
+ unit text;
+ result double precision;
+BEGIN
+ FOREACH intv IN ARRAY intvs LOOP
+ FOREACH unit IN ARRAY units LOOP
+ EXECUTE 'select date_part( ' || quote_literal(unit) || ' ,interval '
+ || quote_literal(intv) || ' )' INTO result;
+ RAISE NOTICE '% %'
+ ,(format('date_part(%10s, interval %10s) yield', unit, intv))
+ ,result;
+ END LOOP;
+ END LOOP;
+END
+$$;
+
+
+DO $$
+DECLARE
+ intv interval;
+ intvs interval[] := '{+infinity,-infinity}';
+ units text[] := '{us,ms,second,minute,hour,day,month
+ ,quarter,year,decade,century,millennium,epoch}';
+ unit text;
+ result numeric;
+BEGIN
+ FOREACH intv IN ARRAY intvs LOOP
+ FOREACH unit IN ARRAY units LOOP
+ EXECUTE 'select extract( ' || quote_literal(unit)
+ || ' from interval ' || quote_literal(intv) || ' )' INTO result;
+ RAISE NOTICE '% %'
+ ,(format('extract(%10s from interval %9s) yield', unit, intv))
+ ,result;
+ END LOOP;
+ END LOOP;
+END
+$$;
+
+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
