aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/utils/adt/date.c222
-rw-r--r--src/backend/utils/adt/jsonpath_exec.c71
-rw-r--r--src/backend/utils/adt/timestamp.c115
-rw-r--r--src/include/utils/date.h3
-rw-r--r--src/include/utils/timestamp.h2
-rw-r--r--src/test/regress/expected/horology.out72
-rw-r--r--src/test/regress/sql/horology.sql27
7 files changed, 260 insertions, 252 deletions
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 057051fa855..a470cf890a2 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -554,15 +554,24 @@ date_mii(PG_FUNCTION_ARGS)
/*
* Promote date to timestamp.
*
- * On overflow error is thrown if 'overflow' is NULL. Otherwise, '*overflow'
- * is set to -1 (+1) when result value exceed lower (upper) boundary and zero
- * returned.
+ * On successful conversion, *overflow is set to zero if it's not NULL.
+ *
+ * If the date is finite but out of the valid range for timestamp, then:
+ * if overflow is NULL, we throw an out-of-range error.
+ * if overflow is not NULL, we store +1 or -1 there to indicate the sign
+ * of the overflow, and return the appropriate timestamp infinity.
+ *
+ * Note: *overflow = -1 is actually not possible currently, since both
+ * datatypes have the same lower bound, Julian day zero.
*/
Timestamp
date2timestamp_opt_overflow(DateADT dateVal, int *overflow)
{
Timestamp result;
+ if (overflow)
+ *overflow = 0;
+
if (DATE_IS_NOBEGIN(dateVal))
TIMESTAMP_NOBEGIN(result);
else if (DATE_IS_NOEND(dateVal))
@@ -570,7 +579,6 @@ date2timestamp_opt_overflow(DateADT dateVal, int *overflow)
else
{
/*
- * Date's range is wider than timestamp's, so check for boundaries.
* Since dates have the same minimum values as timestamps, only upper
* boundary need be checked for overflow.
*/
@@ -579,7 +587,8 @@ date2timestamp_opt_overflow(DateADT dateVal, int *overflow)
if (overflow)
{
*overflow = 1;
- return (Timestamp) 0;
+ TIMESTAMP_NOEND(result);
+ return result;
}
else
{
@@ -597,7 +606,7 @@ date2timestamp_opt_overflow(DateADT dateVal, int *overflow)
}
/*
- * Single-argument version of date2timestamp_opt_overflow().
+ * Promote date to timestamp, throwing error for overflow.
*/
static TimestampTz
date2timestamp(DateADT dateVal)
@@ -608,9 +617,12 @@ date2timestamp(DateADT dateVal)
/*
* Promote date to timestamp with time zone.
*
- * On overflow error is thrown if 'overflow' is NULL. Otherwise, '*overflow'
- * is set to -1 (+1) when result value exceed lower (upper) boundary and zero
- * returned.
+ * On successful conversion, *overflow is set to zero if it's not NULL.
+ *
+ * If the date is finite but out of the valid range for timestamptz, then:
+ * if overflow is NULL, we throw an out-of-range error.
+ * if overflow is not NULL, we store +1 or -1 there to indicate the sign
+ * of the overflow, and return the appropriate timestamptz infinity.
*/
TimestampTz
date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
@@ -620,6 +632,9 @@ date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
*tm = &tt;
int tz;
+ if (overflow)
+ *overflow = 0;
+
if (DATE_IS_NOBEGIN(dateVal))
TIMESTAMP_NOBEGIN(result);
else if (DATE_IS_NOEND(dateVal))
@@ -627,7 +642,6 @@ date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
else
{
/*
- * Date's range is wider than timestamp's, so check for boundaries.
* Since dates have the same minimum values as timestamps, only upper
* boundary need be checked for overflow.
*/
@@ -636,7 +650,8 @@ date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
if (overflow)
{
*overflow = 1;
- return (TimestampTz) 0;
+ TIMESTAMP_NOEND(result);
+ return result;
}
else
{
@@ -664,13 +679,15 @@ date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
if (overflow)
{
if (result < MIN_TIMESTAMP)
+ {
*overflow = -1;
+ TIMESTAMP_NOBEGIN(result);
+ }
else
{
- Assert(result >= END_TIMESTAMP);
*overflow = 1;
+ TIMESTAMP_NOEND(result);
}
- return (TimestampTz) 0;
}
else
{
@@ -685,7 +702,7 @@ date2timestamptz_opt_overflow(DateADT dateVal, int *overflow)
}
/*
- * Single-argument version of date2timestamptz_opt_overflow().
+ * Promote date to timestamptz, throwing error for overflow.
*/
static TimestampTz
date2timestamptz(DateADT dateVal)
@@ -726,16 +743,30 @@ date2timestamp_no_overflow(DateADT dateVal)
* Crosstype comparison functions for dates
*/
+int32
+date_cmp_timestamp_internal(DateADT dateVal, Timestamp dt2)
+{
+ Timestamp dt1;
+ int overflow;
+
+ dt1 = date2timestamp_opt_overflow(dateVal, &overflow);
+ if (overflow > 0)
+ {
+ /* dt1 is larger than any finite timestamp, but less than infinity */
+ return TIMESTAMP_IS_NOEND(dt2) ? -1 : +1;
+ }
+ Assert(overflow == 0); /* -1 case cannot occur */
+
+ return timestamp_cmp_internal(dt1, dt2);
+}
+
Datum
date_eq_timestamp(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
- Timestamp dt1;
-
- dt1 = date2timestamp(dateVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) == 0);
}
Datum
@@ -743,11 +774,8 @@ date_ne_timestamp(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
- Timestamp dt1;
- dt1 = date2timestamp(dateVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) != 0);
}
Datum
@@ -755,11 +783,8 @@ date_lt_timestamp(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
- Timestamp dt1;
- dt1 = date2timestamp(dateVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) < 0);
}
Datum
@@ -767,11 +792,8 @@ date_gt_timestamp(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
- Timestamp dt1;
- dt1 = date2timestamp(dateVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) > 0);
}
Datum
@@ -779,11 +801,8 @@ date_le_timestamp(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
- Timestamp dt1;
- dt1 = date2timestamp(dateVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) <= 0);
}
Datum
@@ -791,11 +810,8 @@ date_ge_timestamp(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
- Timestamp dt1;
- dt1 = date2timestamp(dateVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt2) >= 0);
}
Datum
@@ -803,11 +819,29 @@ date_cmp_timestamp(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
- Timestamp dt1;
- dt1 = date2timestamp(dateVal);
+ PG_RETURN_INT32(date_cmp_timestamp_internal(dateVal, dt2));
+}
- PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
+int32
+date_cmp_timestamptz_internal(DateADT dateVal, TimestampTz dt2)
+{
+ TimestampTz dt1;
+ int overflow;
+
+ dt1 = date2timestamptz_opt_overflow(dateVal, &overflow);
+ if (overflow > 0)
+ {
+ /* dt1 is larger than any finite timestamp, but less than infinity */
+ return TIMESTAMP_IS_NOEND(dt2) ? -1 : +1;
+ }
+ if (overflow < 0)
+ {
+ /* dt1 is less than any finite timestamp, but more than -infinity */
+ return TIMESTAMP_IS_NOBEGIN(dt2) ? +1 : -1;
+ }
+
+ return timestamptz_cmp_internal(dt1, dt2);
}
Datum
@@ -815,11 +849,8 @@ date_eq_timestamptz(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
- dt1 = date2timestamptz(dateVal);
-
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) == 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) == 0);
}
Datum
@@ -827,11 +858,8 @@ date_ne_timestamptz(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
- dt1 = date2timestamptz(dateVal);
-
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) != 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) != 0);
}
Datum
@@ -839,11 +867,8 @@ date_lt_timestamptz(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
- dt1 = date2timestamptz(dateVal);
-
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) < 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) < 0);
}
Datum
@@ -851,11 +876,8 @@ date_gt_timestamptz(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
- dt1 = date2timestamptz(dateVal);
-
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) > 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) > 0);
}
Datum
@@ -863,11 +885,8 @@ date_le_timestamptz(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
- dt1 = date2timestamptz(dateVal);
-
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) <= 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) <= 0);
}
Datum
@@ -875,11 +894,8 @@ date_ge_timestamptz(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
- dt1 = date2timestamptz(dateVal);
-
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) >= 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt2) >= 0);
}
Datum
@@ -887,11 +903,8 @@ date_cmp_timestamptz(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
-
- dt1 = date2timestamptz(dateVal);
- PG_RETURN_INT32(timestamptz_cmp_internal(dt1, dt2));
+ PG_RETURN_INT32(date_cmp_timestamptz_internal(dateVal, dt2));
}
Datum
@@ -899,11 +912,8 @@ timestamp_eq_date(PG_FUNCTION_ARGS)
{
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- Timestamp dt2;
- dt2 = date2timestamp(dateVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) == 0);
}
Datum
@@ -911,11 +921,8 @@ timestamp_ne_date(PG_FUNCTION_ARGS)
{
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- Timestamp dt2;
-
- dt2 = date2timestamp(dateVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) != 0);
}
Datum
@@ -923,11 +930,8 @@ timestamp_lt_date(PG_FUNCTION_ARGS)
{
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- Timestamp dt2;
- dt2 = date2timestamp(dateVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) > 0);
}
Datum
@@ -935,11 +939,8 @@ timestamp_gt_date(PG_FUNCTION_ARGS)
{
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- Timestamp dt2;
-
- dt2 = date2timestamp(dateVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) < 0);
}
Datum
@@ -947,11 +948,8 @@ timestamp_le_date(PG_FUNCTION_ARGS)
{
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- Timestamp dt2;
- dt2 = date2timestamp(dateVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) >= 0);
}
Datum
@@ -959,11 +957,8 @@ timestamp_ge_date(PG_FUNCTION_ARGS)
{
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- Timestamp dt2;
-
- dt2 = date2timestamp(dateVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
+ PG_RETURN_BOOL(date_cmp_timestamp_internal(dateVal, dt1) <= 0);
}
Datum
@@ -971,11 +966,8 @@ timestamp_cmp_date(PG_FUNCTION_ARGS)
{
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- Timestamp dt2;
- dt2 = date2timestamp(dateVal);
-
- PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
+ PG_RETURN_INT32(-date_cmp_timestamp_internal(dateVal, dt1));
}
Datum
@@ -983,11 +975,8 @@ timestamptz_eq_date(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- TimestampTz dt2;
-
- dt2 = date2timestamptz(dateVal);
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) == 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) == 0);
}
Datum
@@ -995,11 +984,8 @@ timestamptz_ne_date(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- TimestampTz dt2;
- dt2 = date2timestamptz(dateVal);
-
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) != 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) != 0);
}
Datum
@@ -1007,11 +993,8 @@ timestamptz_lt_date(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- TimestampTz dt2;
-
- dt2 = date2timestamptz(dateVal);
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) < 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) > 0);
}
Datum
@@ -1019,11 +1002,8 @@ timestamptz_gt_date(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- TimestampTz dt2;
- dt2 = date2timestamptz(dateVal);
-
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) > 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) < 0);
}
Datum
@@ -1031,11 +1011,8 @@ timestamptz_le_date(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- TimestampTz dt2;
-
- dt2 = date2timestamptz(dateVal);
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) <= 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) >= 0);
}
Datum
@@ -1043,11 +1020,8 @@ timestamptz_ge_date(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- TimestampTz dt2;
- dt2 = date2timestamptz(dateVal);
-
- PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) >= 0);
+ PG_RETURN_BOOL(date_cmp_timestamptz_internal(dateVal, dt1) <= 0);
}
Datum
@@ -1055,11 +1029,8 @@ timestamptz_cmp_date(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
DateADT dateVal = PG_GETARG_DATEADT(1);
- TimestampTz dt2;
-
- dt2 = date2timestamptz(dateVal);
- PG_RETURN_INT32(timestamptz_cmp_internal(dt1, dt2));
+ PG_RETURN_INT32(-date_cmp_timestamptz_internal(dateVal, dt1));
}
/*
@@ -1079,6 +1050,7 @@ in_range_date_interval(PG_FUNCTION_ARGS)
Timestamp valStamp;
Timestamp baseStamp;
+ /* XXX we could support out-of-range cases here, perhaps */
valStamp = date2timestamp(val);
baseStamp = date2timestamp(base);
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 28be845770a..1059f34130a 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -2602,93 +2602,36 @@ castTimeToTimeTz(Datum time, bool useTz)
return DirectFunctionCall1(time_timetz, time);
}
-/*---
- * Compares 'ts1' and 'ts2' timestamp, assuming that ts1 might be overflowed
- * during cast from another datatype.
- *
- * 'overflow1' specifies overflow of 'ts1' value:
- * 0 - no overflow,
- * -1 - exceed lower boundary,
- * 1 - exceed upper boundary.
- */
-static int
-cmpTimestampWithOverflow(Timestamp ts1, int overflow1, Timestamp ts2)
-{
- /*
- * All the timestamps we deal with in jsonpath are produced by
- * to_datetime() method. So, they should be valid.
- */
- Assert(IS_VALID_TIMESTAMP(ts2));
-
- /*
- * Timestamp, which exceed lower (upper) bound, is always lower (higher)
- * than any valid timestamp except minus (plus) infinity.
- */
- if (overflow1)
- {
- if (overflow1 < 0)
- {
- if (TIMESTAMP_IS_NOBEGIN(ts2))
- return 1;
- else
- return -1;
- }
- if (overflow1 > 0)
- {
- if (TIMESTAMP_IS_NOEND(ts2))
- return -1;
- else
- return 1;
- }
- }
-
- return timestamp_cmp_internal(ts1, ts2);
-}
-
/*
- * Compare date to timestamptz without throwing overflow error during cast.
+ * Compare date to timestamp.
+ * Note that this doesn't involve any timezone considerations.
*/
static int
cmpDateToTimestamp(DateADT date1, Timestamp ts2, bool useTz)
{
- TimestampTz ts1;
- int overflow = 0;
-
- ts1 = date2timestamp_opt_overflow(date1, &overflow);
-
- return cmpTimestampWithOverflow(ts1, overflow, ts2);
+ return date_cmp_timestamp_internal(date1, ts2);
}
/*
- * Compare date to timestamptz without throwing overflow error during cast.
+ * Compare date to timestamptz.
*/
static int
cmpDateToTimestampTz(DateADT date1, TimestampTz tstz2, bool useTz)
{
- TimestampTz tstz1;
- int overflow = 0;
-
checkTimezoneIsUsedForCast(useTz, "date", "timestamptz");
- tstz1 = date2timestamptz_opt_overflow(date1, &overflow);
-
- return cmpTimestampWithOverflow(tstz1, overflow, tstz2);
+ return date_cmp_timestamptz_internal(date1, tstz2);
}
/*
- * Compare timestamp to timestamptz without throwing overflow error during cast.
+ * Compare timestamp to timestamptz.
*/
static int
cmpTimestampToTimestampTz(Timestamp ts1, TimestampTz tstz2, bool useTz)
{
- TimestampTz tstz1;
- int overflow = 0;
-
checkTimezoneIsUsedForCast(useTz, "timestamp", "timestamptz");
- tstz1 = timestamp2timestamptz_opt_overflow(ts1, &overflow);
-
- return cmpTimestampWithOverflow(tstz1, overflow, tstz2);
+ return timestamp_cmp_timestamptz_internal(ts1, tstz2);
}
/*
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 4128e3a7392..ea0ada704f2 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -2156,16 +2156,34 @@ timestamp_hash_extended(PG_FUNCTION_ARGS)
* Cross-type comparison functions for timestamp vs timestamptz
*/
+int32
+timestamp_cmp_timestamptz_internal(Timestamp timestampVal, TimestampTz dt2)
+{
+ TimestampTz dt1;
+ int overflow;
+
+ dt1 = timestamp2timestamptz_opt_overflow(timestampVal, &overflow);
+ if (overflow > 0)
+ {
+ /* dt1 is larger than any finite timestamp, but less than infinity */
+ return TIMESTAMP_IS_NOEND(dt2) ? -1 : +1;
+ }
+ if (overflow < 0)
+ {
+ /* dt1 is less than any finite timestamp, but more than -infinity */
+ return TIMESTAMP_IS_NOBEGIN(dt2) ? +1 : -1;
+ }
+
+ return timestamptz_cmp_internal(dt1, dt2);
+}
+
Datum
timestamp_eq_timestamptz(PG_FUNCTION_ARGS)
{
Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
-
- dt1 = timestamp2timestamptz(timestampVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) == 0);
}
Datum
@@ -2173,11 +2191,8 @@ timestamp_ne_timestamptz(PG_FUNCTION_ARGS)
{
Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
- dt1 = timestamp2timestamptz(timestampVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) != 0);
}
Datum
@@ -2185,11 +2200,8 @@ timestamp_lt_timestamptz(PG_FUNCTION_ARGS)
{
Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
-
- dt1 = timestamp2timestamptz(timestampVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) < 0);
}
Datum
@@ -2197,11 +2209,8 @@ timestamp_gt_timestamptz(PG_FUNCTION_ARGS)
{
Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
-
- dt1 = timestamp2timestamptz(timestampVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) > 0);
}
Datum
@@ -2209,11 +2218,8 @@ timestamp_le_timestamptz(PG_FUNCTION_ARGS)
{
Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
-
- dt1 = timestamp2timestamptz(timestampVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) <= 0);
}
Datum
@@ -2221,11 +2227,8 @@ timestamp_ge_timestamptz(PG_FUNCTION_ARGS)
{
Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
-
- dt1 = timestamp2timestamptz(timestampVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) >= 0);
}
Datum
@@ -2233,11 +2236,8 @@ timestamp_cmp_timestamptz(PG_FUNCTION_ARGS)
{
Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
- TimestampTz dt1;
- dt1 = timestamp2timestamptz(timestampVal);
-
- PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
+ PG_RETURN_INT32(timestamp_cmp_timestamptz_internal(timestampVal, dt2));
}
Datum
@@ -2245,11 +2245,8 @@ timestamptz_eq_timestamp(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
- TimestampTz dt2;
-
- dt2 = timestamp2timestamptz(timestampVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) == 0);
}
Datum
@@ -2257,11 +2254,8 @@ timestamptz_ne_timestamp(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
- TimestampTz dt2;
-
- dt2 = timestamp2timestamptz(timestampVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) != 0);
}
Datum
@@ -2269,11 +2263,8 @@ timestamptz_lt_timestamp(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
- TimestampTz dt2;
- dt2 = timestamp2timestamptz(timestampVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) > 0);
}
Datum
@@ -2281,11 +2272,8 @@ timestamptz_gt_timestamp(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
- TimestampTz dt2;
-
- dt2 = timestamp2timestamptz(timestampVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) < 0);
}
Datum
@@ -2293,11 +2281,8 @@ timestamptz_le_timestamp(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
- TimestampTz dt2;
-
- dt2 = timestamp2timestamptz(timestampVal);
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) >= 0);
}
Datum
@@ -2305,11 +2290,8 @@ timestamptz_ge_timestamp(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
- TimestampTz dt2;
- dt2 = timestamp2timestamptz(timestampVal);
-
- PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
+ PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) <= 0);
}
Datum
@@ -2317,11 +2299,8 @@ timestamptz_cmp_timestamp(PG_FUNCTION_ARGS)
{
TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
- TimestampTz dt2;
-
- dt2 = timestamp2timestamptz(timestampVal);
- PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
+ PG_RETURN_INT32(-timestamp_cmp_timestamptz_internal(timestampVal, dt1));
}
@@ -5178,9 +5157,12 @@ timestamp_timestamptz(PG_FUNCTION_ARGS)
/*
* Convert timestamp to timestamp with time zone.
*
- * On overflow error is thrown if 'overflow' is NULL. Otherwise, '*overflow'
- * is set to -1 (+1) when result value exceed lower (upper) boundary and zero
- * returned.
+ * On successful conversion, *overflow is set to zero if it's not NULL.
+ *
+ * If the timestamp is finite but out of the valid range for timestamptz, then:
+ * if overflow is NULL, we throw an out-of-range error.
+ * if overflow is not NULL, we store +1 or -1 there to indicate the sign
+ * of the overflow, and return the appropriate timestamptz infinity.
*/
TimestampTz
timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow)
@@ -5191,10 +5173,14 @@ timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow)
fsec_t fsec;
int tz;
+ if (overflow)
+ *overflow = 0;
+
if (TIMESTAMP_NOT_FINITE(timestamp))
return timestamp;
- if (!timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL))
+ /* We don't expect this to fail, but check it pro forma */
+ if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
{
tz = DetermineTimeZoneOffset(tm, session_timezone);
@@ -5207,13 +5193,16 @@ timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow)
else if (overflow)
{
if (result < MIN_TIMESTAMP)
+ {
*overflow = -1;
+ TIMESTAMP_NOBEGIN(result);
+ }
else
{
- Assert(result >= END_TIMESTAMP);
*overflow = 1;
+ TIMESTAMP_NOEND(result);
}
- return (TimestampTz) 0;
+ return result;
}
}
@@ -5225,7 +5214,7 @@ timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow)
}
/*
- * Single-argument version of timestamp2timestamptz_opt_overflow().
+ * Promote timestamp to timestamptz, throwing error for overflow.
*/
static TimestampTz
timestamp2timestamptz(Timestamp timestamp)
diff --git a/src/include/utils/date.h b/src/include/utils/date.h
index 4cdb1f97cc8..6fc491e6a6d 100644
--- a/src/include/utils/date.h
+++ b/src/include/utils/date.h
@@ -72,6 +72,9 @@ extern int32 anytime_typmod_check(bool istz, int32 typmod);
extern double date2timestamp_no_overflow(DateADT dateVal);
extern Timestamp date2timestamp_opt_overflow(DateADT dateVal, int *overflow);
extern TimestampTz date2timestamptz_opt_overflow(DateADT dateVal, int *overflow);
+extern int32 date_cmp_timestamp_internal(DateADT dateVal, Timestamp dt2);
+extern int32 date_cmp_timestamptz_internal(DateADT dateVal, TimestampTz dt2);
+
extern void EncodeSpecialDate(DateADT dt, char *str);
extern DateADT GetSQLCurrentDate(void);
extern TimeTzADT *GetSQLCurrentTime(int32 typmod);
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index 03a1de569f0..16c3fd8ec97 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -99,6 +99,8 @@ extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2);
extern TimestampTz timestamp2timestamptz_opt_overflow(Timestamp timestamp,
int *overflow);
+extern int32 timestamp_cmp_timestamptz_internal(Timestamp timestampVal,
+ TimestampTz dt2);
extern int isoweek2j(int year, int week);
extern void isoweek2date(int woy, int *year, int *mon, int *mday);
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 7f82dcfbfef..d56decd9940 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -2,6 +2,12 @@
-- HOROLOGY
--
SET DateStyle = 'Postgres, MDY';
+SHOW TimeZone; -- Many of these tests depend on the prevailing setting
+ TimeZone
+----------
+ PST8PDT
+(1 row)
+
--
-- Test various input formats
--
@@ -2076,6 +2082,72 @@ SELECT '' AS "16", f1 AS "timestamp", date(f1) AS date
DROP TABLE TEMP_TIMESTAMP;
--
+-- Comparisons between datetime types, especially overflow cases
+---
+SELECT '2202020-10-05'::date::timestamp; -- fail
+ERROR: date out of range for timestamp
+SELECT '2202020-10-05'::date > '2020-10-05'::timestamp as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2020-10-05'::timestamp > '2202020-10-05'::date as f;
+ f
+---
+ f
+(1 row)
+
+SELECT '2202020-10-05'::date::timestamptz; -- fail
+ERROR: date out of range for timestamp
+SELECT '2202020-10-05'::date > '2020-10-05'::timestamptz as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2020-10-05'::timestamptz > '2202020-10-05'::date as f;
+ f
+---
+ f
+(1 row)
+
+-- This conversion may work depending on timezone
+SELECT '4714-11-24 BC'::date::timestamptz;
+ timestamptz
+---------------------------------
+ Mon Nov 24 00:00:00 4714 PST BC
+(1 row)
+
+SET TimeZone = 'UTC-2';
+SELECT '4714-11-24 BC'::date::timestamptz; -- fail
+ERROR: date out of range for timestamp
+SELECT '4714-11-24 BC'::date < '2020-10-05'::timestamptz as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::date as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4714-11-24 BC'::timestamp < '2020-10-05'::timestamptz as t;
+ t
+---
+ t
+(1 row)
+
+SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::timestamp as t;
+ t
+---
+ t
+(1 row)
+
+RESET TimeZone;
+--
-- Formats
--
SET DateStyle TO 'US,Postgres';
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index fed21a53c86..fa92a80d0e6 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -3,6 +3,8 @@
--
SET DateStyle = 'Postgres, MDY';
+SHOW TimeZone; -- Many of these tests depend on the prevailing setting
+
--
-- Test various input formats
--
@@ -280,6 +282,31 @@ SELECT '' AS "16", f1 AS "timestamp", date(f1) AS date
DROP TABLE TEMP_TIMESTAMP;
--
+-- Comparisons between datetime types, especially overflow cases
+---
+
+SELECT '2202020-10-05'::date::timestamp; -- fail
+SELECT '2202020-10-05'::date > '2020-10-05'::timestamp as t;
+SELECT '2020-10-05'::timestamp > '2202020-10-05'::date as f;
+
+SELECT '2202020-10-05'::date::timestamptz; -- fail
+SELECT '2202020-10-05'::date > '2020-10-05'::timestamptz as t;
+SELECT '2020-10-05'::timestamptz > '2202020-10-05'::date as f;
+
+-- This conversion may work depending on timezone
+SELECT '4714-11-24 BC'::date::timestamptz;
+SET TimeZone = 'UTC-2';
+SELECT '4714-11-24 BC'::date::timestamptz; -- fail
+
+SELECT '4714-11-24 BC'::date < '2020-10-05'::timestamptz as t;
+SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::date as t;
+
+SELECT '4714-11-24 BC'::timestamp < '2020-10-05'::timestamptz as t;
+SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::timestamp as t;
+
+RESET TimeZone;
+
+--
-- Formats
--
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-20 21:09:37 +0000