aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/utils/adt/date.c118
-rw-r--r--src/backend/utils/adt/datetime.c8
-rw-r--r--src/backend/utils/adt/formatting.c8
-rw-r--r--src/backend/utils/adt/timestamp.c95
-rw-r--r--src/include/datatype/timestamp.h79
-rw-r--r--src/interfaces/ecpg/pgtypeslib/dt.h28
-rw-r--r--src/interfaces/ecpg/pgtypeslib/timestamp.c6
-rw-r--r--src/test/regress/expected/date.out21
-rw-r--r--src/test/regress/expected/timestamp.out12
-rw-r--r--src/test/regress/expected/timestamptz.out24
-rw-r--r--src/test/regress/sql/date.sql6
-rw-r--r--src/test/regress/sql/timestamp.sql5
-rw-r--r--src/test/regress/sql/timestamptz.sql7
13 files changed, 364 insertions, 53 deletions
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 332db7e9c00..420f383a804 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -160,6 +160,7 @@ date_in(PG_FUNCTION_ARGS)
break;
}
+ /* Prevent overflow in Julian-day routines */
if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
@@ -167,6 +168,12 @@ date_in(PG_FUNCTION_ARGS)
date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
+ /* Now check for just-out-of-range dates */
+ if (!IS_VALID_DATE(date))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range: \"%s\"", str)));
+
PG_RETURN_DATEADT(date);
}
@@ -209,8 +216,7 @@ date_recv(PG_FUNCTION_ARGS)
/* Limit to the same range that date_in() accepts. */
if (DATE_NOT_FINITE(result))
/* ok */ ;
- else if (result < -POSTGRES_EPOCH_JDATE ||
- result >= JULIAN_MAX - POSTGRES_EPOCH_JDATE)
+ else if (!IS_VALID_DATE(result))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("date out of range")));
@@ -258,6 +264,7 @@ make_date(PG_FUNCTION_ARGS)
errmsg("date field value out of range: %d-%02d-%02d",
tm.tm_year, tm.tm_mon, tm.tm_mday)));
+ /* Prevent overflow in Julian-day routines */
if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
@@ -266,6 +273,13 @@ make_date(PG_FUNCTION_ARGS)
date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
+ /* Now check for just-out-of-range dates */
+ if (!IS_VALID_DATE(date))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range: %d-%02d-%02d",
+ tm.tm_year, tm.tm_mon, tm.tm_mday)));
+
PG_RETURN_DATEADT(date);
}
@@ -427,11 +441,21 @@ date_pli(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
int32 days = PG_GETARG_INT32(1);
+ DateADT result;
if (DATE_NOT_FINITE(dateVal))
- days = 0; /* can't change infinity */
+ PG_RETURN_DATEADT(dateVal); /* can't change infinity */
+
+ result = dateVal + days;
+
+ /* Check for integer overflow and out-of-allowed-range */
+ if ((days >= 0 ? (result < dateVal) : (result > dateVal)) ||
+ !IS_VALID_DATE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range")));
- PG_RETURN_DATEADT(dateVal + days);
+ PG_RETURN_DATEADT(result);
}
/* Subtract a number of days from a date, giving a new date.
@@ -441,11 +465,21 @@ date_mii(PG_FUNCTION_ARGS)
{
DateADT dateVal = PG_GETARG_DATEADT(0);
int32 days = PG_GETARG_INT32(1);
+ DateADT result;
if (DATE_NOT_FINITE(dateVal))
- days = 0; /* can't change infinity */
+ PG_RETURN_DATEADT(dateVal); /* can't change infinity */
+
+ result = dateVal - days;
+
+ /* Check for integer overflow and out-of-allowed-range */
+ if ((days >= 0 ? (result > dateVal) : (result < dateVal)) ||
+ !IS_VALID_DATE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range")));
- PG_RETURN_DATEADT(dateVal - days);
+ PG_RETURN_DATEADT(result);
}
/*
@@ -464,14 +498,18 @@ date2timestamp(DateADT dateVal)
TIMESTAMP_NOEND(result);
else
{
-#ifdef HAVE_INT64_TIMESTAMP
- /* date is days since 2000, timestamp is microseconds since same... */
- result = dateVal * USECS_PER_DAY;
- /* Date's range is wider than timestamp's, so check for overflow */
- if (result / USECS_PER_DAY != dateVal)
+ /*
+ * 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.
+ */
+ if (dateVal >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("date out of range for timestamp")));
+#ifdef HAVE_INT64_TIMESTAMP
+ /* date is days since 2000, timestamp is microseconds since same... */
+ result = dateVal * USECS_PER_DAY;
#else
/* date is days since 2000, timestamp is seconds since same... */
result = dateVal * (double) SECS_PER_DAY;
@@ -495,6 +533,16 @@ date2timestamptz(DateADT dateVal)
TIMESTAMP_NOEND(result);
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.
+ */
+ if (dateVal >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range for timestamp")));
+
j2date(dateVal + POSTGRES_EPOCH_JDATE,
&(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
tm->tm_hour = 0;
@@ -504,14 +552,18 @@ date2timestamptz(DateADT dateVal)
#ifdef HAVE_INT64_TIMESTAMP
result = dateVal * USECS_PER_DAY + tz * USECS_PER_SEC;
- /* Date's range is wider than timestamp's, so check for overflow */
- if ((result - tz * USECS_PER_SEC) / USECS_PER_DAY != dateVal)
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("date out of range for timestamp")));
#else
result = dateVal * (double) SECS_PER_DAY + tz;
#endif
+
+ /*
+ * Since it is possible to go beyond allowed timestamptz range because
+ * of time zone, check for allowed timestamp range after adding tz.
+ */
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range for timestamp")));
}
return result;
@@ -1053,7 +1105,17 @@ abstime_date(PG_FUNCTION_ARGS)
default:
abstime2tm(abstime, &tz, tm, NULL);
+ /* Prevent overflow in Julian-day routines */
+ if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("abstime out of range for date")));
result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
+ /* Now check for just-out-of-range dates */
+ if (!IS_VALID_DATE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("abstime out of range for date")));
break;
}
@@ -1678,7 +1740,13 @@ datetime_timestamp(PG_FUNCTION_ARGS)
result = date2timestamp(date);
if (!TIMESTAMP_NOT_FINITE(result))
+ {
result += time;
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+ }
PG_RETURN_TIMESTAMP(result);
}
@@ -2550,11 +2618,29 @@ datetimetz_timestamptz(PG_FUNCTION_ARGS)
TIMESTAMP_NOEND(result);
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.
+ */
+ if (date >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range for timestamp")));
#ifdef HAVE_INT64_TIMESTAMP
result = date * USECS_PER_DAY + time->time + time->zone * USECS_PER_SEC;
#else
result = date * (double) SECS_PER_DAY + time->time + time->zone;
#endif
+
+ /*
+ * Since it is possible to go beyond allowed timestamptz range because
+ * of time zone, check for allowed timestamp range after adding tz.
+ */
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range for timestamp")));
}
PG_RETURN_TIMESTAMP(result);
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index cdbf72cf699..2ea21b7028a 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -280,14 +280,16 @@ strtoi(const char *nptr, char **endptr, int base)
* and calendar date for all non-negative Julian days
* (i.e. from Nov 24, -4713 on).
*
- * These routines will be used by other date/time packages
- * - thomas 97/02/25
- *
* Rewritten to eliminate overflow problems. This now allows the
* routines to work correctly for all Julian day counts from
* 0 to 2147483647 (Nov 24, -4713 to Jun 3, 5874898) assuming
* a 32-bit integer. Longer types should also work to the limits
* of their precision.
+ *
+ * Actually, date2j() will work sanely, in the sense of producing
+ * valid negative Julian dates, significantly before Nov 24, -4713.
+ * We rely on it to do so back to Nov 1, -4713; see IS_VALID_JULIAN()
+ * and associated commentary in timestamp.h.
*/
int
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 49567643199..2b5622a9ee0 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -3525,6 +3525,7 @@ to_date(PG_FUNCTION_ARGS)
do_to_timestamp(date_txt, fmt, &tm, &fsec);
+ /* Prevent overflow in Julian-day routines */
if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
@@ -3533,6 +3534,13 @@ to_date(PG_FUNCTION_ARGS)
result = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
+ /* Now check for just-out-of-range dates */
+ if (!IS_VALID_DATE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range: \"%s\"",
+ text_to_cstring(date_txt))));
+
PG_RETURN_DATEADT(result);
}
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index c4f556a3ffa..3f013e3f5bb 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -261,7 +261,8 @@ timestamp_recv(PG_FUNCTION_ARGS)
/* rangecheck: see if timestamp_out would like it */
if (TIMESTAMP_NOT_FINITE(timestamp))
/* ok */ ;
- else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
+ else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0 ||
+ !IS_VALID_TIMESTAMP(timestamp))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
@@ -645,6 +646,14 @@ make_timestamp_internal(int year, int month, int day,
result = date * SECS_PER_DAY + time;
#endif
+ /* final range check catches just-out-of-range timestamps */
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
+ year, month, day,
+ hour, min, sec)));
+
return result;
}
@@ -702,6 +711,7 @@ make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
int32 min = PG_GETARG_INT32(4);
float8 sec = PG_GETARG_FLOAT8(5);
text *zone = PG_GETARG_TEXT_PP(6);
+ TimestampTz result;
Timestamp timestamp;
struct pg_tm tt;
int tz;
@@ -717,7 +727,14 @@ make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
tz = parse_sane_timezone(&tt, zone);
- PG_RETURN_TIMESTAMPTZ((TimestampTz) dt2local(timestamp, -tz));
+ result = dt2local(timestamp, -tz);
+
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ PG_RETURN_TIMESTAMPTZ(result);
}
/* timestamptz_out()
@@ -778,7 +795,8 @@ timestamptz_recv(PG_FUNCTION_ARGS)
/* rangecheck: see if timestamptz_out would like it */
if (TIMESTAMP_NOT_FINITE(timestamp))
/* ok */ ;
- else if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+ else if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0 ||
+ !IS_VALID_TIMESTAMP(timestamp))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
@@ -1925,7 +1943,7 @@ tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *result)
TimeOffset date;
TimeOffset time;
- /* Julian day routines are not correct for negative Julian days */
+ /* Prevent overflow in Julian-day routines */
if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
{
*result = 0; /* keep compiler quiet */
@@ -1957,6 +1975,13 @@ tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *result)
if (tzp != NULL)
*result = dt2local(*result, -(*tzp));
+ /* final range check catches just-out-of-range timestamps */
+ if (!IS_VALID_TIMESTAMP(*result))
+ {
+ *result = 0; /* keep compiler quiet */
+ return -1;
+ }
+
return 0;
}
@@ -2982,6 +3007,12 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
}
timestamp += span->time;
+
+ if (!IS_VALID_TIMESTAMP(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
result = timestamp;
}
@@ -3086,6 +3117,12 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
}
timestamp += span->time;
+
+ if (!IS_VALID_TIMESTAMP(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
result = timestamp;
}
@@ -4397,6 +4434,7 @@ timestamp_part(PG_FUNCTION_ARGS)
text *units = PG_GETARG_TEXT_PP(0);
Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
float8 result;
+ Timestamp epoch;
int type,
val;
char *lowunits;
@@ -4575,10 +4613,15 @@ timestamp_part(PG_FUNCTION_ARGS)
switch (val)
{
case DTK_EPOCH:
+ epoch = SetEpochTimestamp();
#ifdef HAVE_INT64_TIMESTAMP
- result = (timestamp - SetEpochTimestamp()) / 1000000.0;
+ /* try to avoid precision loss in subtraction */
+ if (timestamp < (PG_INT64_MAX + epoch))
+ result = (timestamp - epoch) / 1000000.0;
+ else
+ result = ((float8) timestamp - epoch) / 1000000.0;
#else
- result = timestamp - SetEpochTimestamp();
+ result = timestamp - epoch;
#endif
break;
@@ -4611,6 +4654,7 @@ timestamptz_part(PG_FUNCTION_ARGS)
text *units = PG_GETARG_TEXT_PP(0);
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
float8 result;
+ Timestamp epoch;
int tz;
int type,
val;
@@ -4792,10 +4836,15 @@ timestamptz_part(PG_FUNCTION_ARGS)
switch (val)
{
case DTK_EPOCH:
+ epoch = SetEpochTimestamp();
#ifdef HAVE_INT64_TIMESTAMP
- result = (timestamp - SetEpochTimestamp()) / 1000000.0;
+ /* try to avoid precision loss in subtraction */
+ if (timestamp < (PG_INT64_MAX + epoch))
+ result = (timestamp - epoch) / 1000000.0;
+ else
+ result = ((float8) timestamp - epoch) / 1000000.0;
#else
- result = timestamp - SetEpochTimestamp();
+ result = timestamp - epoch;
#endif
break;
@@ -5107,9 +5156,8 @@ timestamp_zone(PG_FUNCTION_ARGS)
tz = DetermineTimeZoneOffset(&tm, tzp);
if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("could not convert to time zone \"%s\"",
- tzname)));
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
}
else
{
@@ -5120,6 +5168,11 @@ timestamp_zone(PG_FUNCTION_ARGS)
}
}
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
PG_RETURN_TIMESTAMPTZ(result);
}
@@ -5198,6 +5251,11 @@ timestamp_izone(PG_FUNCTION_ARGS)
result = dt2local(timestamp, tz);
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
PG_RETURN_TIMESTAMPTZ(result);
} /* timestamp_izone() */
@@ -5337,9 +5395,8 @@ timestamptz_zone(PG_FUNCTION_ARGS)
errmsg("timestamp out of range")));
if (tm2timestamp(&tm, fsec, NULL, &result) != 0)
ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("could not convert to time zone \"%s\"",
- tzname)));
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
}
else
{
@@ -5350,6 +5407,11 @@ timestamptz_zone(PG_FUNCTION_ARGS)
}
}
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
PG_RETURN_TIMESTAMP(result);
}
@@ -5383,6 +5445,11 @@ timestamptz_izone(PG_FUNCTION_ARGS)
result = dt2local(timestamp, tz);
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
PG_RETURN_TIMESTAMP(result);
}
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index 12a2a8afadb..68a41ebdc51 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -147,27 +147,86 @@ typedef struct
/*
* Julian date support.
*
- * IS_VALID_JULIAN checks the minimum date exactly, but is a bit sloppy
- * about the maximum, since it's far enough out to not be especially
- * interesting.
+ * date2j() and j2date() nominally handle the Julian date range 0..INT_MAX,
+ * or 4714-11-24 BC to 5874898-06-03 AD. In practice, date2j() will work and
+ * give correct negative Julian dates for dates before 4714-11-24 BC as well.
+ * We rely on it to do so back to 4714-11-01 BC. Allowing at least one day's
+ * slop is necessary so that timestamp rotation doesn't produce dates that
+ * would be rejected on input. For example, '4714-11-24 00:00 GMT BC' is a
+ * legal timestamptz value, but in zones east of Greenwich it would print as
+ * sometime in the afternoon of 4714-11-23 BC; if we couldn't process such a
+ * date we'd have a dump/reload failure. So the idea is for IS_VALID_JULIAN
+ * to accept a slightly wider range of dates than we really support, and
+ * then we apply the exact checks in IS_VALID_DATE or IS_VALID_TIMESTAMP,
+ * after timezone rotation if any. To save a few cycles, we can make
+ * IS_VALID_JULIAN check only to the month boundary, since its exact cutoffs
+ * are not very critical in this scheme.
+ *
+ * It is correct that JULIAN_MINYEAR is -4713, not -4714; it is defined to
+ * allow easy comparison to tm_year values, in which we follow the convention
+ * that tm_year <= 0 represents abs(tm_year)+1 BC.
*/
#define JULIAN_MINYEAR (-4713)
#define JULIAN_MINMONTH (11)
#define JULIAN_MINDAY (24)
#define JULIAN_MAXYEAR (5874898)
+#define JULIAN_MAXMONTH (6)
+#define JULIAN_MAXDAY (3)
#define IS_VALID_JULIAN(y,m,d) \
- (((y) > JULIAN_MINYEAR \
- || ((y) == JULIAN_MINYEAR && \
- ((m) > JULIAN_MINMONTH \
- || ((m) == JULIAN_MINMONTH && (d) >= JULIAN_MINDAY)))) \
- && (y) < JULIAN_MAXYEAR)
-
-#define JULIAN_MAX (2147483494) /* == date2j(JULIAN_MAXYEAR, 1, 1) */
+ (((y) > JULIAN_MINYEAR || \
+ ((y) == JULIAN_MINYEAR && ((m) >= JULIAN_MINMONTH))) && \
+ ((y) < JULIAN_MAXYEAR || \
+ ((y) == JULIAN_MAXYEAR && ((m) < JULIAN_MAXMONTH))))
/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */
+/*
+ * Range limits for dates and timestamps.
+ *
+ * We have traditionally allowed Julian day zero as a valid datetime value,
+ * so that is the lower bound for both dates and timestamps.
+ *
+ * The upper limit for dates is 5874897-12-31, which is a bit less than what
+ * the Julian-date code can allow. We use that same limit for timestamps when
+ * using floating-point timestamps (so that the timezone offset problem would
+ * exist here too if there were no slop). For integer timestamps, the upper
+ * limit is 294276-12-31. The int64 overflow limit would be a few days later;
+ * again, leaving some slop avoids worries about corner-case overflow, and
+ * provides a simpler user-visible definition.
+ */
+
+/* First allowed date, and first disallowed date, in Julian-date form */
+#define DATETIME_MIN_JULIAN (0)
+#define DATE_END_JULIAN (2147483494) /* == date2j(JULIAN_MAXYEAR, 1, 1) */
+#ifdef HAVE_INT64_TIMESTAMP
+#define TIMESTAMP_END_JULIAN (109203528) /* == date2j(294277, 1, 1) */
+#else
+#define TIMESTAMP_END_JULIAN DATE_END_JULIAN
+#endif
+
+/* Timestamp limits */
+#ifdef HAVE_INT64_TIMESTAMP
+#define MIN_TIMESTAMP INT64CONST(-211813488000000000)
+/* == (DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */
+#define END_TIMESTAMP INT64CONST(9223371331200000000)
+/* == (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY */
+#else
+#define MIN_TIMESTAMP (-211813488000.0)
+/* == (DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY */
+#define END_TIMESTAMP 185330760393600.0
+/* == (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY */
+#endif
+
+/* Range-check a date (given in Postgres, not Julian, numbering) */
+#define IS_VALID_DATE(d) \
+ ((DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) <= (d) && \
+ (d) < (DATE_END_JULIAN - POSTGRES_EPOCH_JDATE))
+
+/* Range-check a timestamp */
+#define IS_VALID_TIMESTAMP(t) (MIN_TIMESTAMP <= (t) && (t) < END_TIMESTAMP)
+
#endif /* DATATYPE_TIMESTAMP_H */
diff --git a/src/interfaces/ecpg/pgtypeslib/dt.h b/src/interfaces/ecpg/pgtypeslib/dt.h
index 145e2b7c4fb..c0c3ac19016 100644
--- a/src/interfaces/ecpg/pgtypeslib/dt.h
+++ b/src/interfaces/ecpg/pgtypeslib/dt.h
@@ -287,22 +287,32 @@ do { \
*/
#define isleap(y) (((y) % 4) == 0 && (((y) % 100) != 0 || ((y) % 400) == 0))
-/* Julian date support for date2j() and j2date()
- *
- * IS_VALID_JULIAN checks the minimum date exactly, but is a bit sloppy
- * about the maximum, since it's far enough out to not be especially
- * interesting.
+/*
+ * Julian date support --- see comments in backend's timestamp.h.
*/
#define JULIAN_MINYEAR (-4713)
#define JULIAN_MINMONTH (11)
#define JULIAN_MINDAY (24)
#define JULIAN_MAXYEAR (5874898)
+#define JULIAN_MAXMONTH (6)
+#define JULIAN_MAXDAY (3)
+
+#define IS_VALID_JULIAN(y,m,d) \
+ (((y) > JULIAN_MINYEAR || \
+ ((y) == JULIAN_MINYEAR && ((m) >= JULIAN_MINMONTH))) && \
+ ((y) < JULIAN_MAXYEAR || \
+ ((y) == JULIAN_MAXYEAR && ((m) < JULIAN_MAXMONTH))))
+
+#ifdef HAVE_INT64_TIMESTAMP
+#define MIN_TIMESTAMP INT64CONST(-211813488000000000)
+#define END_TIMESTAMP INT64CONST(9223371331200000000)
+#else
+#define MIN_TIMESTAMP (-211813488000.0)
+#define END_TIMESTAMP 185330760393600.0
+#endif
-#define IS_VALID_JULIAN(y,m,d) ((((y) > JULIAN_MINYEAR) \
- || (((y) == JULIAN_MINYEAR) && (((m) > JULIAN_MINMONTH) \
- || (((m) == JULIAN_MINMONTH) && ((d) >= JULIAN_MINDAY))))) \
- && ((y) < JULIAN_MAXYEAR))
+#define IS_VALID_TIMESTAMP(t) (MIN_TIMESTAMP <= (t) && (t) < END_TIMESTAMP)
#define UTIME_MINYEAR (1901)
#define UTIME_MINMONTH (12)
diff --git a/src/interfaces/ecpg/pgtypeslib/timestamp.c b/src/interfaces/ecpg/pgtypeslib/timestamp.c
index cf1fed2c00b..f746a90f8b1 100644
--- a/src/interfaces/ecpg/pgtypeslib/timestamp.c
+++ b/src/interfaces/ecpg/pgtypeslib/timestamp.c
@@ -61,7 +61,7 @@ tm2timestamp(struct tm * tm, fsec_t fsec, int *tzp, timestamp * result)
time;
#endif
- /* Julian day routines are not correct for negative Julian days */
+ /* Prevent overflow in Julian-day routines */
if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
return -1;
@@ -83,6 +83,10 @@ tm2timestamp(struct tm * tm, fsec_t fsec, int *tzp, timestamp * result)
if (tzp != NULL)
*result = dt2local(*result, -(*tzp));
+ /* final range check catches just-out-of-range timestamps */
+ if (!IS_VALID_TIMESTAMP(*result))
+ return -1;
+
return 0;
} /* tm2timestamp() */
diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out
index 56c55201f59..418b1464250 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -816,6 +816,27 @@ SELECT date '1999 08 01';
1999-08-01
(1 row)
+-- Check upper and lower limits of date range
+SELECT date '4714-11-24 BC';
+ date
+---------------
+ 4714-11-24 BC
+(1 row)
+
+SELECT date '4714-11-23 BC'; -- out of range
+ERROR: date out of range: "4714-11-23 BC"
+LINE 1: SELECT date '4714-11-23 BC';
+ ^
+SELECT date '5874897-12-31';
+ date
+---------------
+ 5874897-12-31
+(1 row)
+
+SELECT date '5874898-01-01'; -- out of range
+ERROR: date out of range: "5874898-01-01"
+LINE 1: SELECT date '5874898-01-01';
+ ^
RESET datestyle;
--
-- Simple math
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index e9f5e7790f5..4a2fabddd9f 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -250,6 +250,18 @@ SELECT '' AS "64", d1 FROM TIMESTAMP_TBL;
| Mon Jan 01 17:32:01 2001
(65 rows)
+-- Check behavior at the lower boundary of the timestamp range
+SELECT '4714-11-24 00:00:00 BC'::timestamp;
+ timestamp
+-----------------------------
+ Mon Nov 24 00:00:00 4714 BC
+(1 row)
+
+SELECT '4714-11-23 23:59:59 BC'::timestamp; -- out of range
+ERROR: timestamp out of range: "4714-11-23 23:59:59 BC"
+LINE 1: SELECT '4714-11-23 23:59:59 BC'::timestamp;
+ ^
+-- The upper boundary differs between integer and float timestamps, so no check
-- Demonstrate functions and operators
SELECT '' AS "48", d1 FROM TIMESTAMP_TBL
WHERE d1 > timestamp without time zone '1997-01-02';
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 40a22540e55..fffcaf4bf8d 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -322,6 +322,30 @@ SELECT '' AS "64", d1 FROM TIMESTAMPTZ_TBL;
| Mon Jan 01 17:32:01 2001 PST
(66 rows)
+-- Check behavior at the lower boundary of the timestamp range
+SELECT '4714-11-24 00:00:00+00 BC'::timestamptz;
+ timestamptz
+---------------------------------
+ Sun Nov 23 16:00:00 4714 PST BC
+(1 row)
+
+SELECT '4714-11-23 16:00:00-08 BC'::timestamptz;
+ timestamptz
+---------------------------------
+ Sun Nov 23 16:00:00 4714 PST BC
+(1 row)
+
+SELECT 'Sun Nov 23 16:00:00 4714 PST BC'::timestamptz;
+ timestamptz
+---------------------------------
+ Sun Nov 23 16:00:00 4714 PST BC
+(1 row)
+
+SELECT '4714-11-23 23:59:59+00 BC'::timestamptz; -- out of range
+ERROR: timestamp out of range: "4714-11-23 23:59:59+00 BC"
+LINE 1: SELECT '4714-11-23 23:59:59+00 BC'::timestamptz;
+ ^
+-- The upper boundary differs between integer and float timestamps, so no check
-- Demonstrate functions and operators
SELECT '' AS "48", d1 FROM TIMESTAMPTZ_TBL
WHERE d1 > timestamp with time zone '1997-01-02';
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index e40b4c4856d..4553fd1c975 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -186,6 +186,12 @@ SELECT date '01 08 1999';
SELECT date '99 08 01';
SELECT date '1999 08 01';
+-- Check upper and lower limits of date range
+SELECT date '4714-11-24 BC';
+SELECT date '4714-11-23 BC'; -- out of range
+SELECT date '5874897-12-31';
+SELECT date '5874898-01-01'; -- out of range
+
RESET datestyle;
--
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index b22cd4871e3..b7957cbb9aa 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -143,6 +143,11 @@ INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 5097 BC');
SELECT '' AS "64", d1 FROM TIMESTAMP_TBL;
+-- Check behavior at the lower boundary of the timestamp range
+SELECT '4714-11-24 00:00:00 BC'::timestamp;
+SELECT '4714-11-23 23:59:59 BC'::timestamp; -- out of range
+-- The upper boundary differs between integer and float timestamps, so no check
+
-- Demonstrate functions and operators
SELECT '' AS "48", d1 FROM TIMESTAMP_TBL
WHERE d1 > timestamp without time zone '1997-01-02';
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index f4b455e7032..03dbc05aab7 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -162,6 +162,13 @@ SELECT 'Wed Jul 11 10:51:14 PST+03:00 2001'::timestamptz;
SELECT '' AS "64", d1 FROM TIMESTAMPTZ_TBL;
+-- Check behavior at the lower boundary of the timestamp range
+SELECT '4714-11-24 00:00:00+00 BC'::timestamptz;
+SELECT '4714-11-23 16:00:00-08 BC'::timestamptz;
+SELECT 'Sun Nov 23 16:00:00 4714 PST BC'::timestamptz;
+SELECT '4714-11-23 23:59:59+00 BC'::timestamptz; -- out of range
+-- The upper boundary differs between integer and float timestamps, so no check
+
-- Demonstrate functions and operators
SELECT '' AS "48", d1 FROM TIMESTAMPTZ_TBL
WHERE d1 > timestamp with time zone '1997-01-02';
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-01 05:22:21 +0000