Re: [PATCH] Integer overflow in timestamp[tz]_part() and date/time boundaries check
On 2/2/16, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 2/2/16 6:39 PM, Tom Lane wrote:
I'm inclined to think that a good solution would be to create an
artificial restriction to not accept years beyond, say, 100000 AD.
That would leave us with a lot of daylight to not have to worry
about corner-case overflows in timestamp arithmetic. I'm not sure
though where we'd need to enforce such a restriction; certainly in
timestamp[tz]_in, but where else?Probably some of the casts (I'd think at least timestamp->timestamptz).
Maybe timestamp[tz]_recv. Most of the time*pl* functions. :/
Please find attached a patch checks boundaries of date/timestamp[tz].
There are more functions: converting to/from timestamptz, truncating,
constructing from date and time etc.
I left the upper boundary as described[1]http://www.postgresql.org/docs/devel/static/datatype-datetime.html in the documentation
(294276-12-31 AD), lower - "as is" (4714-11-24 BC).
It is easy to change the lower boundary to 4713-01-01BC (as described
in the documentation) and it seems necessary because it allows to
simplify IS_VALID_JULIAN and IS_VALID_JULIAN4STAMPS and avoid the next
behavior:
postgres=# select
postgres-# to_char(date_trunc('week', '4713-01-01 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-29 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-28 BC'::date),'day');
to_char | to_char | to_char
-----------+-----------+-----------
monday | monday | thursday
(1 row)
since 4714-12-28 BC and to the past detection when a week is starting
is broken (because it is boundary of isoyears -4713 and -4712).
Is it worth to break undocumented range or leave it as is?
There is one more flaw: checking for a correctness begins from date
and if default TZ is not UTC, dump/restore of values of type
timestamptz which are close to allowed boundaries can be broken (and
such result can't be restored because date is not in allowed range):
postgres=# SET TIME ZONE 'GMT+1';
SET
postgres=# COPY (SELECT '4714-11-24 00:00:00.000000+00
BC'::timestamptz) TO STDOUT;
4714-11-23 23:00:00-01 BC
Also I'm asking for a help because the query (in default TZ='GMT+1'):
postgres=# SELECT '4714-11-24 00:00:00.000000+00 BC'::timestamptz;
in psql gives a result "4714-11-23 23:00:00-01 BC",
but in a testing system gives "Sun Nov 23 23:00:00 4714 GMT BC"
without TZ offset.
I don't see what can be added to the documentation with the applied patch.
More testings, finding bugs, uncovered functions, advice, comment
improvements are very appreciated.
[1]: http://www.postgresql.org/docs/devel/static/datatype-datetime.html
--
Best regards,
Vitaly Burovoy
Attachments:
fix_timestamps_overflow.v001.patchapplication/octet-stream; name=fix_timestamps_overflow.v001.patchDownload
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 332db7e..61555ae 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;
}
+ /* IS_VALID_JULIAN is enough for checking; IS_VALID_DATE is redundant */
if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
@@ -209,8 +210,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 +258,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)));
+ /* IS_VALID_JULIAN is enough for checking; IS_VALID_DATE is redundant */
if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
@@ -429,9 +430,16 @@ date_pli(PG_FUNCTION_ARGS)
int32 days = PG_GETARG_INT32(1);
if (DATE_NOT_FINITE(dateVal))
- days = 0; /* can't change infinity */
+ PG_RETURN_DATEADT(dateVal); /* can't change infinity */
- PG_RETURN_DATEADT(dateVal + days);
+ dateVal += days;
+
+ if (!IS_VALID_DATE(dateVal))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range")));
+
+ PG_RETURN_DATEADT(dateVal);
}
/* Subtract a number of days from a date, giving a new date.
@@ -443,9 +451,16 @@ date_mii(PG_FUNCTION_ARGS)
int32 days = PG_GETARG_INT32(1);
if (DATE_NOT_FINITE(dateVal))
- days = 0; /* can't change infinity */
+ PG_RETURN_DATEADT(dateVal); /* can't change infinity */
- PG_RETURN_DATEADT(dateVal - days);
+ dateVal -= days;
+
+ if (!IS_VALID_DATE(dateVal))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range")));
+
+ PG_RETURN_DATEADT(dateVal);
}
/*
@@ -464,14 +479,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 miminum values as timestamps,
+ * only upper boundary is to be checked for overflow.
+ */
+ if (dateVal >= (JULIAN_MAX4STAMPS - 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 +514,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 miminum values as timestamps,
+ * only upper boundary is to be checked for overflow.
+ */
+ if (dateVal >= (JULIAN_MAX4STAMPS - 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 +533,17 @@ 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,6 +1085,10 @@ abstime_date(PG_FUNCTION_ARGS)
default:
abstime2tm(abstime, &tz, tm, NULL);
+ 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;
break;
}
@@ -1678,7 +1714,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 +2592,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 miminum values as timestamps,
+ * only upper boundary is to be checked for overflow.
+ * Also save one day for possible correction by time zone
+ */
+ if (date >= (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE + 1))
+ 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 cdbf72c..0deb6f7 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -1579,7 +1579,7 @@ DetermineTimeZoneOffsetInternal(struct pg_tm * tm, pg_tz *tzp, pg_time_t *tp)
* timezone is GMT. (For a valid Julian date, integer overflow should be
* impossible with 64-bit pg_time_t, but let's check for safety.)
*/
- if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
+ if (!IS_VALID_JULIAN4STAMPS(tm->tm_year, tm->tm_mon, tm->tm_mday))
goto overflow;
date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - UNIX_EPOCH_JDATE;
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 1525d2a..380df6a 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -168,6 +168,11 @@ timestamp_in(PG_FUNCTION_ARGS)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range: \"%s\"", str)));
+
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range: \"%s\"", str)));
break;
case DTK_EPOCH:
@@ -261,7 +266,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")));
@@ -442,6 +448,11 @@ timestamptz_in(PG_FUNCTION_ARGS)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range: \"%s\"", str)));
+
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range: \"%s\"", str)));
break;
case DTK_EPOCH:
@@ -592,7 +603,7 @@ make_timestamp_internal(int year, int month, int day,
errmsg("date field value out of range: %d-%02d-%02d",
year, month, day)));
- if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
+ if (!IS_VALID_JULIAN4STAMPS(tm.tm_year, tm.tm_mon, tm.tm_mday))
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("date out of range: %d-%02d-%02d",
@@ -645,6 +656,14 @@ make_timestamp_internal(int year, int month, int day,
result = date * SECS_PER_DAY + time;
#endif
+ /* quick test for "YYYY-12-31 24:00:00" (YYYY is maximum allowed year) */
+ 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;
}
@@ -703,6 +722,7 @@ make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
float8 sec = PG_GETARG_FLOAT8(5);
text *zone = PG_GETARG_TEXT_PP(6);
Timestamp timestamp;
+ TimestampTz result;
struct pg_tm tt;
int tz;
fsec_t fsec;
@@ -717,7 +737,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 +805,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")));
@@ -1926,7 +1954,7 @@ tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *result)
TimeOffset time;
/* Julian day routines are not correct for negative Julian days */
- if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
+ if (!IS_VALID_JULIAN4STAMPS(tm->tm_year, tm->tm_mon, tm->tm_mday))
{
*result = 0; /* keep compiler quiet */
return -1;
@@ -2982,6 +3010,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 +3120,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;
}
@@ -3876,6 +3916,15 @@ timestamp_trunc(PG_FUNCTION_ARGS)
result = 0;
}
+ /*
+ * truncating the minimal timestamp (4714-11-24 BC) to year, month
+ * or even week leads to the result less than the minimum allowed.
+ */
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
PG_RETURN_TIMESTAMP(result);
}
@@ -4033,6 +4082,15 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
result = 0;
}
+ /*
+ * truncating the minimal timestamp (4714-11-24 BC) to year, month
+ * or even week leads to the result less than the minimum allowed.
+ */
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
PG_RETURN_TIMESTAMPTZ(result);
}
@@ -4396,6 +4454,7 @@ timestamp_part(PG_FUNCTION_ARGS)
{
text *units = PG_GETARG_TEXT_PP(0);
Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
+ Timestamp epoch;
float8 result;
int type,
val;
@@ -4575,10 +4634,15 @@ timestamp_part(PG_FUNCTION_ARGS)
switch (val)
{
case DTK_EPOCH:
+ epoch = SetEpochTimestamp();
#ifdef HAVE_INT64_TIMESTAMP
- result = (timestamp - SetEpochTimestamp()) / 1000000.0;
+ if (timestamp < (PG_INT64_MAX + epoch))
+ /* save maximal precision before converting to float8 */
+ result = (timestamp - epoch) / 1000000.0;
+ else
+ result = ((float8)timestamp - epoch) / 1000000.0;
#else
- result = timestamp - SetEpochTimestamp();
+ result = timestamp - epoch;
#endif
break;
@@ -4610,6 +4674,7 @@ timestamptz_part(PG_FUNCTION_ARGS)
{
text *units = PG_GETARG_TEXT_PP(0);
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+ Timestamp epoch;
float8 result;
int tz;
int type,
@@ -4792,8 +4857,13 @@ timestamptz_part(PG_FUNCTION_ARGS)
switch (val)
{
case DTK_EPOCH:
+ epoch = SetEpochTimestamp();
#ifdef HAVE_INT64_TIMESTAMP
- result = (timestamp - SetEpochTimestamp()) / 1000000.0;
+ if (timestamp < (PG_INT64_MAX + epoch))
+ /* save maximal precision before converting to float8 */
+ result = (timestamp - epoch) / 1000000.0;
+ else
+ result = ((float8)timestamp - epoch) / 1000000.0;
#else
result = timestamp - SetEpochTimestamp();
#endif
@@ -5120,6 +5190,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 +5273,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() */
@@ -5236,6 +5316,11 @@ timestamp2timestamptz(Timestamp timestamp)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
}
return result;
@@ -5266,6 +5351,10 @@ timestamptz_timestamp(PG_FUNCTION_ARGS)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
}
PG_RETURN_TIMESTAMP(result);
}
@@ -5350,6 +5439,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 +5477,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 12a2a8a..9b21ba8 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -105,6 +105,20 @@ typedef struct
#define USECS_PER_MINUTE INT64CONST(60000000)
#define USECS_PER_SEC INT64CONST(1000000)
+#ifdef HAVE_INT64_TIMESTAMP
+#define MIN_TIMESTAMP INT64CONST(-211813488000000000)
+/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */
+#define MAX_TIMESTAMP INT64CONST(9223371331200000000)
+/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */
+#else
+#define MIN_TIMESTAMP -211813488000.0
+/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 */
+#define MAX_TIMESTAMP 9223371331200.0
+/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 */
+#endif
+
+#define IS_VALID_TIMESTAMP(j) ((MIN_TIMESTAMP <= (j)) && ((j) < MAX_TIMESTAMP))
+
/*
* We allow numeric timezone offsets up to 15:59:59 either way from Greenwich.
* Currently, the record holders for wackiest offsets in actual use are zones
@@ -156,6 +170,7 @@ typedef struct
#define JULIAN_MINMONTH (11)
#define JULIAN_MINDAY (24)
#define JULIAN_MAXYEAR (5874898)
+#define JULIAN_MAXYEAR4STAMPS (294277)
#define IS_VALID_JULIAN(y,m,d) \
(((y) > JULIAN_MINYEAR \
@@ -164,10 +179,22 @@ typedef struct
|| ((m) == JULIAN_MINMONTH && (d) >= JULIAN_MINDAY)))) \
&& (y) < JULIAN_MAXYEAR)
+#define IS_VALID_JULIAN4STAMPS(y,m,d) \
+ (((y) > JULIAN_MINYEAR \
+ || ((y) == JULIAN_MINYEAR && \
+ ((m) > JULIAN_MINMONTH \
+ || ((m) == JULIAN_MINMONTH && (d) >= JULIAN_MINDAY)))) \
+ && (y) < JULIAN_MAXYEAR4STAMPS)
+
#define JULIAN_MAX (2147483494) /* == date2j(JULIAN_MAXYEAR, 1, 1) */
+#define JULIAN_MAX4STAMPS (109203528) /* == date2j(JULIAN_MAXYEAR4STAMPS, 1, 1) */
/* 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) */
+#define IS_VALID_DATE(j) \
+ ((-POSTGRES_EPOCH_JDATE <= (j)) && \
+ ((j) < (JULIAN_MAX - POSTGRES_EPOCH_JDATE)))
+
#endif /* DATATYPE_TIMESTAMP_H */
diff --git a/src/interfaces/ecpg/pgtypeslib/dt.h b/src/interfaces/ecpg/pgtypeslib/dt.h
index 145e2b7..32a350c 100644
--- a/src/interfaces/ecpg/pgtypeslib/dt.h
+++ b/src/interfaces/ecpg/pgtypeslib/dt.h
@@ -297,12 +297,14 @@ do { \
#define JULIAN_MINYEAR (-4713)
#define JULIAN_MINMONTH (11)
#define JULIAN_MINDAY (24)
-#define JULIAN_MAXYEAR (5874898)
+#define JULIAN_MAXYEAR4STAMPS (294277)
-#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_JULIAN4STAMPS(y,m,d) \
+ (((y) > JULIAN_MINYEAR \
+ || ((y) == JULIAN_MINYEAR && \
+ ((m) > JULIAN_MINMONTH \
+ || ((m) == JULIAN_MINMONTH && (d) >= JULIAN_MINDAY)))) \
+ && (y) < JULIAN_MAXYEAR4STAMPS)
#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 cf1fed2..2f5219b 100644
--- a/src/interfaces/ecpg/pgtypeslib/timestamp.c
+++ b/src/interfaces/ecpg/pgtypeslib/timestamp.c
@@ -62,7 +62,7 @@ tm2timestamp(struct tm * tm, fsec_t fsec, int *tzp, timestamp * result)
#endif
/* Julian day routines are not correct for negative Julian days */
- if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
+ if (!IS_VALID_JULIAN4STAMPS(tm->tm_year, tm->tm_mon, tm->tm_mday))
return -1;
dDate = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(2000, 1, 1);
diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out
index 56c5520..b197f3a 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -920,6 +920,66 @@ SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00'); -- 0
0
(1 row)
+-- corner cases for EPOCH from TIMESTAMPTZ
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '4714-11-24 00:00:00.000000+00 BC');
+ date_part
+---------------
+ -210866803200
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '294247-01-10 04:00:54.775805+00'); -- just before INT64_MAX in extract
+ date_part
+------------------
+ 9223372036854.78
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '294247-01-10 04:00:55.775806+00'); -- just after INT64_MAX in extract
+ date_part
+------------------
+ 9223372036855.78
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '294276-12-31 23:59:59.9+00');
+ date_part
+-----------------
+ 9224318015999.9
+(1 row)
+
+-- ERROR (not to forget to change test here when range become wider)
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '294276-12-31 24:00:00.000000+00'); -- should fail (timestamp out of range)
+ERROR: timestamp out of range: "294276-12-31 24:00:00.000000+00"
+LINE 1: SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '294276-12-31 24:00:00...
+ ^
+-- corner cases for EPOCH from TIMESTAMP
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '4714-11-24 00:00:00.000000 BC');
+ date_part
+---------------
+ -210866803200
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '294247-01-10 04:00:54.775805'); -- just before INT64_MAX in extract
+ date_part
+------------------
+ 9223372036854.78
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '294247-01-10 04:00:55.775806'); -- just after INT64_MAX in extract
+ date_part
+------------------
+ 9223372036855.78
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '294276-12-31 23:59:59.9');
+ date_part
+-----------------
+ 9224318015999.9
+(1 row)
+
+-- ERROR (not to forget to change test here when range become wider)
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '294276-12-31 24:00:00.000000'); -- should fail (timestamp out of range)
+ERROR: timestamp out of range: "294276-12-31 24:00:00.000000"
+LINE 1: SELECT EXTRACT(EPOCH FROM TIMESTAMP '294276-12-31 24:00:00.0...
+ ^
--
-- century
--
@@ -1452,3 +1512,66 @@ select make_time(10, 55, 100.1);
ERROR: time field value out of range: 10:55:100.1
select make_time(24, 0, 2.1);
ERROR: time field value out of range: 24:00:2.1
+-- corner cases
+-- (to prevent raising errors in *_out functions firstly write values to a table)
+CREATE TABLE test_dates(txt TEXT, val DATE);
+INSERT INTO test_dates SELECT v::text, v::date FROM(VALUES('4714-11-23 BC'))t(v); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_dates SELECT v::text, v::date FROM(VALUES('4714-11-24 BC'))t(v); -- OK
+INSERT INTO test_dates SELECT v::text, v::date FROM(VALUES('4713-01-01 BC'))t(v); -- OK
+INSERT INTO test_dates SELECT v::text, v::date FROM(VALUES('5874897-12-31'))t(v); -- OK
+INSERT INTO test_dates SELECT v::text, v::date FROM(VALUES('5874898-01-01'))t(v); -- should fail
+ERROR: date out of range: "5874898-01-01"
+SELECT txt, val FROM test_dates;
+ txt | val
+---------------+---------------
+ 4714-11-24 BC | 11-24-4714 BC
+ 4713-01-01 BC | 01-01-4713 BC
+ 5874897-12-31 | 12-31-5874897
+(3 rows)
+
+DROP TABLE test_dates;
+-- arithmetic operations(only with int values: intervals lead to convert dates to timestamps)
+CREATE TABLE test_dates(txt TEXT, delta int, val DATE);
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('4714-11-24 BC', -2147483648))t(v, d); -- should fail(overflow check)
+ERROR: date out of range
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('4714-11-24 BC', -1))t(v, d); -- should fail
+ERROR: date out of range
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('4714-11-24 BC', 0))t(v, d); -- OK
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('4714-11-24 BC', +1))t(v, d); -- OK
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', -1))t(v, d); -- OK
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', 0))t(v, d); -- OK
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +1))t(v, d); -- should fail
+ERROR: date out of range
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +2147483647))t(v, d); -- should fail(overflow check)
+ERROR: date out of range
+-- previously it resulted OK, +inf, -inf, very high result (can't reach normally)
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +2451698))t(v, d); -- should fail(not in allowed range)
+ERROR: date out of range
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +2451699))t(v, d); -- should fail(overflow check)
+ERROR: date out of range
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +2451700))t(v, d); -- should fail(overflow check)
+ERROR: date out of range
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +2451701))t(v, d); -- should fail(overflow check)
+ERROR: date out of range
+-- Currently it is impossible to reach overflow result of MAX_DATE+MAX_INT
+-- or MIN_DATE+MIN_INT and to be in range [MIN_DATE;MAX_DATE) because the 2nd
+-- addend is type of int4; in case of int8 it is possible thereby when int8 is
+-- allowed as an addend it is necessary to insert checking for overflow result.
+-- Note: originally "select '5874897-12-31'::date + 2147483647" => 4714-06-21 BC
+-- which is _very_ close (156 days) to MIN_DATE(4714-11-24 BC)
+SELECT v::text, d, v::date + d::bigint FROM(VALUES('5874897-12-31', +2147483648))t(v, d); -- should fail(no operator)
+ERROR: operator does not exist: date + bigint
+LINE 1: SELECT v::text, d, v::date + d::bigint FROM(VALUES('5874897-...
+ ^
+HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
+SELECT txt, delta, val FROM test_dates;
+ txt | delta | val
+---------------+-------+---------------
+ 4714-11-24 BC | 0 | 11-24-4714 BC
+ 4714-11-24 BC | 1 | 11-25-4714 BC
+ 5874897-12-31 | -1 | 12-30-5874897
+ 5874897-12-31 | 0 | 12-31-5874897
+(4 rows)
+
+DROP TABLE test_dates;
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 1fe02be..6ce6aae 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3039,4 +3039,38 @@ SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
2012-12-12 12:00:00 -01:30
(1 row)
+-- corner cases
+SET TIME ZONE 'UTC';
+SELECT to_date('4714 11 23 BC', 'YYYY MM DD BC'); -- ERROR: date out of range: "-4713 01 01"
+ERROR: date out of range: "4714 11 23 BC"
+SELECT to_date('4714 11 24 BC', 'YYYY MM DD BC'); -- OK
+ to_date
+---------------
+ 11-24-4714 BC
+(1 row)
+
+SELECT to_date('5874897 12 31', 'YYYY MM DD'); -- OK
+ to_date
+---------------
+ 12-31-5874897
+(1 row)
+
+SELECT to_date('5874898 01 01', 'YYYY MM DD'); -- ERROR: date out of range: "5874898 01 01"
+ERROR: date out of range: "5874898 01 01"
+SELECT to_timestamp('4714 11 23 23:59:59 BC', 'YYYY MM DD HH24:MI:SS BC'); -- should fail
+ERROR: timestamp out of range
+SELECT to_timestamp('4714 11 24 00:00:00 BC', 'YYYY MM DD HH24:MI:SS BC'); -- OK
+ to_timestamp
+---------------------------------
+ Mon Nov 24 00:00:00 4714 UTC BC
+(1 row)
+
+SELECT to_timestamp('294276 12 31 23:59:59', 'YYYY MM DD HH24:MI:SS'); -- OK
+ to_timestamp
+--------------------------------
+ Sun Dec 31 23:59:59 294276 UTC
+(1 row)
+
+SELECT to_timestamp('294277 01 01 00:00:00', 'YYYY MM DD HH24:MI:SS'); -- should fail
+ERROR: timestamp out of range
RESET TIME ZONE;
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index e9f5e77..8a75585 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -1592,3 +1592,206 @@ SELECT make_timestamp(2014,12,28,6,30,45.887);
Sun Dec 28 06:30:45.887 2014
(1 row)
+-- corner cases
+-- (to prevent raising errors in *_out functions insert values to a table and then select from it)
+SET TIME ZONE 'UTC';
+CREATE TABLE test_timestamps(txt TEXT, val timestamp);
+-- previously it resulted OK, +inf, -inf, ERROR (in timestamp_out!)
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294277-01-09 04:00:54.775806'))t(v); -- should fail
+ERROR: timestamp out of range: "294277-01-09 04:00:54.775806"
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294277-01-09 04:00:54.775807'))t(v); -- should fail
+ERROR: timestamp out of range: "294277-01-09 04:00:54.775807"
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294277-01-09 04:00:54.775808'))t(v); -- should fail
+ERROR: timestamp out of range: "294277-01-09 04:00:54.775808"
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294277-01-09 04:00:54.775809'))t(v); -- should fail
+ERROR: timestamp out of range: "294277-01-09 04:00:54.775809"
+-- current corner cases
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('4714-11-23 23:59:59.999999 BC'))t(v); -- should fail
+ERROR: timestamp out of range: "4714-11-23 23:59:59.999999 BC"
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('4714-11-24 00:00:00.000000 BC'))t(v); -- OK
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('4713-01-01 00:00:00.000000 BC'))t(v); -- OK
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294276-12-31 23:59:59.999999'))t(v); -- OK
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294276-12-31 24:00:00.000000'))t(v); -- should fail
+ERROR: timestamp out of range: "294276-12-31 24:00:00.000000"
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294277-01-01 00:00:00.000000'))t(v); -- should fail
+ERROR: timestamp out of range: "294277-01-01 00:00:00.000000"
+-- current corner cases with make_timestamp (note: negative years are not allowed)
+INSERT INTO test_timestamps SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamp(y,m,d,h,mm,s) FROM(VALUES(294276,12,31, 23,59,59.999999))t(y,m,d,h,mm,s); -- OK
+INSERT INTO test_timestamps SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamp(y,m,d,h,mm,s) FROM(VALUES(294276,12,31, 24,00,00.000000))t(y,m,d,h,mm,s); -- should fail
+ERROR: timestamp out of range: 294276-12-31 24:00:00
+INSERT INTO test_timestamps SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamp(y,m,d,h,mm,s) FROM(VALUES(294277,01,01, 00,00,00.000000))t(y,m,d,h,mm,s); -- should fail
+ERROR: date out of range: 294277-01-01
+-- conversion from date (has bigger allowed interval)
+INSERT INTO test_timestamps SELECT v::text, v::date::timestamp FROM(VALUES('4714-11-23 BC'))t(v); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamps SELECT v::text, v::date::timestamp FROM(VALUES('4714-11-24 BC'))t(v); -- OK
+INSERT INTO test_timestamps SELECT v::text, v::date::timestamp FROM(VALUES('294276-12-31'))t(v); -- OK
+INSERT INTO test_timestamps SELECT v::text, v::date::timestamp FROM(VALUES('294277-01-01'))t(v); -- should fail
+ERROR: date out of range for timestamp
+-- conversion from date+time (has bigger allowed interval)
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('4714-11-23 BC', '23:59:59.999999'))t(d,t); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('4714-11-23 BC', '24:00:00.000000'))t(d,t); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('4714-11-24 BC', '00:00:00.000000'))t(d,t); -- OK
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('294276-12-31', '23:59:59.999999'))t(d,t); -- OK
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('294276-12-31', '24:00:00.000000'))t(d,t); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('294277-01-01', '00:00:00.000000'))t(d,t); -- should fail
+ERROR: date out of range for timestamp
+SELECT txt, val FROM test_timestamps;
+ txt | val
+-------------------------------+-----------------------------------
+ 4714-11-24 00:00:00.000000 BC | Mon Nov 24 00:00:00 4714 BC
+ 4713-01-01 00:00:00.000000 BC | Thu Jan 01 00:00:00 4713 BC
+ 294276-12-31 23:59:59.999999 | Sun Dec 31 23:59:59.999999 294276
+ 294276-12-31 23:59:59.999999 | Sun Dec 31 23:59:59.999999 294276
+ 4714-11-24 BC | Mon Nov 24 00:00:00 4714 BC
+ 294276-12-31 | Sun Dec 31 00:00:00 294276
+ 4714-11-24 BC 00:00:00.000000 | Mon Nov 24 00:00:00 4714 BC
+ 294276-12-31 23:59:59.999999 | Sun Dec 31 23:59:59.999999 294276
+(8 rows)
+
+DROP TABLE test_timestamps;
+-- truncating tests
+CREATE TABLE test_timestamps(txt TEXT, unit text, val timestamp);
+-- truncating minimum allowed value results to even less value (not allowed)
+INSERT INTO test_timestamps SELECT v::text, u, date_trunc(u, v::timestamp) FROM(VALUES('day', '4714-11-24 00:00:00.000000 BC'))t(u,v); -- OK
+INSERT INTO test_timestamps SELECT v::text, u, date_trunc(u, v::timestamp) FROM(VALUES('week', '4714-11-24 00:00:00.000000 BC'))t(u,v); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, u, date_trunc(u, v::timestamp) FROM(VALUES('month', '4714-11-30 23:59:59.999999 BC'))t(u,v); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, u, date_trunc(u, v::timestamp) FROM(VALUES('year', '4714-12-31 23:59:59.999999 BC'))t(u,v); -- should fail
+ERROR: timestamp out of range
+SELECT txt, unit, val FROM test_timestamps;
+ txt | unit | val
+-------------------------------+------+-----------------------------
+ 4714-11-24 00:00:00.000000 BC | day | Mon Nov 24 00:00:00 4714 BC
+(1 row)
+
+DROP TABLE test_timestamps;
+-- time zone conversions tests
+CREATE TABLE test_timestamps(txt TEXT, tz text, val timestamptz);
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT-1', '4714-11-24 00:59:59.999999 BC'))t(tz,v); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT-1', '4714-11-24 01:00:00.000000 BC'))t(tz,v); -- OK
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT+0', '4714-11-24 00:59:59.999999 BC'))t(tz,v); -- OK
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT+0', '294276-12-31 23:59:59.999999'))t(tz,v); -- OK
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT+1', '294276-12-31 22:59:59.999999'))t(tz,v); -- OK
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT+1', '294276-12-31 23:00:00.000000'))t(tz,v); -- should fail
+ERROR: timestamp out of range
+SELECT txt, tz, val FROM test_timestamps;
+ txt | tz | val
+-------------------------------+-------+----------------------------------------
+ 4714-11-24 01:00:00.000000 BC | GMT-1 | Mon Nov 24 00:00:00 4714 UTC BC
+ 4714-11-24 00:59:59.999999 BC | GMT+0 | Mon Nov 24 00:59:59.999999 4714 UTC BC
+ 294276-12-31 23:59:59.999999 | GMT+0 | Sun Dec 31 23:59:59.999999 294276 UTC
+ 294276-12-31 22:59:59.999999 | GMT+1 | Sun Dec 31 23:59:59.999999 294276 UTC
+(4 rows)
+
+DROP TABLE test_timestamps;
+-- arithmetic operations
+CREATE TABLE test_timestamps(txt TEXT, delta INTERVAL, val timestamp);
+-- the same as '294277-01-09 04:00:54.77580*'::timestamp (with months) [should fail]
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775806'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775807'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775808'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775809'))t(v, d);
+ERROR: timestamp out of range
+-- the same as '294277-01-09 04:00:54.77580*'::timestamp (with days) [should fail]
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775806'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775807'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775808'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775809'))t(v, d);
+ERROR: timestamp out of range
+-- the same as '294277-01-09 04:00:54.77580*'::timestamp (with hours) [should fail]
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775806'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775807'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775808'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775809'))t(v, d);
+ERROR: timestamp out of range
+-- the same as '294277-01-09 04:00:54.77580*'::timestamp (with seconds) [should fail]
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '946699254.775806'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '946699254.775807'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '946699254.775808'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '946699254.775809'))t(v, d);
+ERROR: timestamp out of range
+-- current corner cases
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '-1mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '-1day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '-1sec'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '-0.000001sec'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', ' 0sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '+0.000001sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '+1sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '+1sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '+1day'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '+1mon'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1mon'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1day'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-0.000001sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', ' 0sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+0.000001sec'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1sec'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1sec'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+-- try to add/substract maximal/minimal intervals (check for unexpected overflow)
+-- years, months
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+2147483647mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-2147483648mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '+2147483647mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '-2147483648mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+-- days
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+2147483647day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-2147483648day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '+2147483647day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '-2147483648day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+SELECT txt, delta, val FROM test_timestamps;
+ txt | delta | val
+------------------------------+---------------------+------------------------------------
+ 4714-11-24 BC | @ 0 | Mon Nov 24 00:00:00 4714 BC
+ 4714-11-24 BC | @ 0.000001 secs | Mon Nov 24 00:00:00.000001 4714 BC
+ 4714-11-24 BC | @ 1 sec | Mon Nov 24 00:00:01 4714 BC
+ 4714-11-24 BC | @ 1 sec | Mon Nov 24 00:00:01 4714 BC
+ 4714-11-24 BC | @ 1 day | Tue Nov 25 00:00:00 4714 BC
+ 4714-11-24 BC | @ 1 mon | Wed Dec 24 00:00:00 4714 BC
+ 294276-12-31 23:59:59.999999 | @ 1 mon ago | Thu Nov 30 23:59:59.999999 294276
+ 294276-12-31 23:59:59.999999 | @ 1 day ago | Sat Dec 30 23:59:59.999999 294276
+ 294276-12-31 23:59:59.999999 | @ 1 sec ago | Sun Dec 31 23:59:58.999999 294276
+ 294276-12-31 23:59:59.999999 | @ 0.000001 secs ago | Sun Dec 31 23:59:59.999998 294276
+ 294276-12-31 23:59:59.999999 | @ 0 | Sun Dec 31 23:59:59.999999 294276
+(11 rows)
+
+DROP TABLE test_timestamps;
+RESET TIME ZONE;
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 40a2254..1abeaaf 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2487,3 +2487,325 @@ SELECT '2007-12-09 07:30:00 UTC'::timestamptz AT TIME ZONE 'VET';
Sun Dec 09 03:00:00 2007
(1 row)
+-- corner cases
+-- (to prevent raising errors in *_out functions insert values to a table and then select from it)
+SET TIME ZONE 'UTC';
+CREATE TABLE test_timestamptzs(txt TEXT, val timestamptz);
+-- previously it resulted OK, +inf, -inf, ERROR (in timestamp_out!)
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294277-01-09 03:00:54.775806-01'))t(v); -- should fail
+ERROR: timestamp out of range: "294277-01-09 03:00:54.775806-01"
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294277-01-09 03:00:54.775807-01'))t(v); -- should fail
+ERROR: timestamp out of range: "294277-01-09 03:00:54.775807-01"
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294277-01-09 03:00:54.775808-01'))t(v); -- should fail
+ERROR: timestamp out of range: "294277-01-09 03:00:54.775808-01"
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294277-01-09 03:00:54.775809-01'))t(v); -- should fail
+ERROR: timestamp out of range: "294277-01-09 03:00:54.775809-01"
+-- current corner cases
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4714-11-23 23:59:59.999999 BC'))t(v); -- should fail
+ERROR: timestamp out of range: "4714-11-23 23:59:59.999999 BC"
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4714-11-24 00:00:00.000000 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4713-01-01 00:00:00.000000 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294276-12-31 23:59:59.999999'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294276-12-31 24:00:00.000000'))t(v); -- should fail
+ERROR: timestamp out of range: "294276-12-31 24:00:00.000000"
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294277-01-01 00:00:00.000000'))t(v); -- should fail
+ERROR: timestamp out of range: "294277-01-01 00:00:00.000000"
+-- current corner cases with non-UTC time zones
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4714-11-24 00:59:59.999999+01 BC'))t(v); -- should fail
+ERROR: timestamp out of range: "4714-11-24 00:59:59.999999+01 BC"
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4714-11-24 01:00:00.000000+01 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4713-01-01 01:00:00.000000+01 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294276-12-31 22:59:59.999999-01'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294276-12-31 23:00:00.000000-01'))t(v); -- should fail
+ERROR: timestamp out of range: "294276-12-31 23:00:00.000000-01"
+-- current corner cases with make_timestamptz(current tz=UTC) (note: negative years are not allowed)
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamptz(y,m,d,h,mm,s) FROM(VALUES(294276,12,31, 23,59,59.999999))t(y,m,d,h,mm,s); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamptz(y,m,d,h,mm,s) FROM(VALUES(294276,12,31, 24,00,00.000000))t(y,m,d,h,mm,s); -- should fail
+ERROR: timestamp out of range: 294276-12-31 24:00:00
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamptz(y,m,d,h,mm,s) FROM(VALUES(294277,01,01, 00,00,00.000000))t(y,m,d,h,mm,s); -- should fail
+ERROR: date out of range: 294277-01-01
+-- current corner cases with make_timestamptz(in non-UTC TZ) (note: negative years are not allowed)
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s %s', y,m,d,h,mm,s,tz), make_timestamptz(y,m,d,h,mm,s,tz) FROM(VALUES(294276,12,31, 23,59,59.999999, 'GMT+0'))t(y,m,d,h,mm,s,tz); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s %s', y,m,d,h,mm,s,tz), make_timestamptz(y,m,d,h,mm,s,tz) FROM(VALUES(294276,12,31, 22,59,59.999999, 'GMT+1'))t(y,m,d,h,mm,s,tz); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s %s', y,m,d,h,mm,s,tz), make_timestamptz(y,m,d,h,mm,s,tz) FROM(VALUES(294276,12,31, 23,00,00.000000, 'GMT+1'))t(y,m,d,h,mm,s,tz); -- should fail
+ERROR: timestamp out of range
+-- conversion from date (has bigger allowed interval)
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-23 BC'))t(v); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-24 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294276-12-31'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294277-01-01'))t(v); -- should fail
+ERROR: date out of range for timestamp
+-- check for overflow because of time zones
+SET TIME ZONE 'GMT-1';
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-22 BC'))t(v); -- should fail
+ERROR: date out of range: "4714-11-22 BC"
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-23 BC'))t(v); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-24 BC'))t(v); -- should fail (-4714-11-23 23:00:00)
+ERROR: date out of range for timestamp
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-25 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294276-12-30'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294276-12-31'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294277-01-01'))t(v); -- should fail
+ERROR: date out of range for timestamp
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294277-01-02'))t(v); -- should fail
+ERROR: date out of range for timestamp
+SET TIME ZONE 'GMT+1';
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-22 BC'))t(v); -- should fail
+ERROR: date out of range: "4714-11-22 BC"
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-23 BC'))t(v); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-24 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-25 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294276-12-30'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294276-12-31'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294277-01-01'))t(v); -- should fail
+ERROR: date out of range for timestamp
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294277-01-02'))t(v); -- should fail
+ERROR: date out of range for timestamp
+-- restore UTC by default
+SET TIME ZONE 'UTC';
+-- conversion from date+time (has bigger allowed interval)
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '23:59:59.999999'))t(d,t); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '24:00:00.000000'))t(d,t); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '00:00:00.000000'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '23:59:59.999999'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '24:00:00.000000'))t(d,t); -- should fail
+ERROR: date out of range for timestamp
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294277-01-01', '00:00:00.000000'))t(d,t); -- should fail
+ERROR: date out of range for timestamp
+-- conversion from date+timeTZ (has bigger allowed interval)
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '23:59:59.999999-01'))t(d,t); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '24:00:00.000000-01'))t(d,t); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '00:00:00.000000-01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '00:59:59.999999-01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '01:00:00.000000-01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '22:59:59.999999-01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '23:00:00.000000-01'))t(d,t); -- should fail
+ERROR: date out of range for timestamp
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '23:59:59.999999-01'))t(d,t); -- should fail
+ERROR: date out of range for timestamp
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '24:00:00.000000-01'))t(d,t); -- should fail
+ERROR: date out of range for timestamp
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '23:59:59.999999+01'))t(d,t); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '24:00:00.000000+01'))t(d,t); -- should fail
+ERROR: date out of range: "4714-11-23 BC"
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '00:00:00.000000+01'))t(d,t); -- should fail
+ERROR: date out of range for timestamp
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '00:59:59.999999+01'))t(d,t); -- should fail
+ERROR: date out of range for timestamp
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '01:00:00.000000+01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '22:59:59.999999+01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '23:00:00.000000+01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '23:59:59.999999+01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '24:00:00.000000+01'))t(d,t); -- OK
+SELECT txt, val FROM test_timestamptzs;
+ txt | val
+------------------------------------+----------------------------------------
+ 4714-11-24 00:00:00.000000 BC | Mon Nov 24 00:00:00 4714 UTC BC
+ 4713-01-01 00:00:00.000000 BC | Thu Jan 01 00:00:00 4713 UTC BC
+ 294276-12-31 23:59:59.999999 | Sun Dec 31 23:59:59.999999 294276 UTC
+ 4714-11-24 01:00:00.000000+01 BC | Mon Nov 24 00:00:00 4714 UTC BC
+ 4713-01-01 01:00:00.000000+01 BC | Thu Jan 01 00:00:00 4713 UTC BC
+ 294276-12-31 22:59:59.999999-01 | Sun Dec 31 23:59:59.999999 294276 UTC
+ 294276-12-31 23:59:59.999999 | Sun Dec 31 23:59:59.999999 294276 UTC
+ 294276-12-31 23:59:59.999999 GMT+0 | Sun Dec 31 23:59:59.999999 294276 UTC
+ 294276-12-31 22:59:59.999999 GMT+1 | Sun Dec 31 23:59:59.999999 294276 UTC
+ 4714-11-24 BC | Mon Nov 24 00:00:00 4714 UTC BC
+ 294276-12-31 | Sun Dec 31 00:00:00 294276 UTC
+ 4714-11-25 BC | Mon Nov 24 23:00:00 4714 UTC BC
+ 294276-12-30 | Fri Dec 29 23:00:00 294276 UTC
+ 294276-12-31 | Sat Dec 30 23:00:00 294276 UTC
+ 4714-11-24 BC | Mon Nov 24 01:00:00 4714 UTC BC
+ 4714-11-25 BC | Tue Nov 25 01:00:00 4714 UTC BC
+ 294276-12-30 | Sat Dec 30 01:00:00 294276 UTC
+ 294276-12-31 | Sun Dec 31 01:00:00 294276 UTC
+ 4714-11-24 BC 00:00:00.000000 | Mon Nov 24 00:00:00 4714 UTC BC
+ 294276-12-31 23:59:59.999999 | Sun Dec 31 23:59:59.999999 294276 UTC
+ 4714-11-24 BC 00:00:00.000000-01 | Mon Nov 24 01:00:00 4714 UTC BC
+ 4714-11-24 BC 00:59:59.999999-01 | Mon Nov 24 01:59:59.999999 4714 UTC BC
+ 4714-11-24 BC 01:00:00.000000-01 | Mon Nov 24 02:00:00 4714 UTC BC
+ 294276-12-31 22:59:59.999999-01 | Sun Dec 31 23:59:59.999999 294276 UTC
+ 4714-11-24 BC 01:00:00.000000+01 | Mon Nov 24 00:00:00 4714 UTC BC
+ 294276-12-31 22:59:59.999999+01 | Sun Dec 31 21:59:59.999999 294276 UTC
+ 294276-12-31 23:00:00.000000+01 | Sun Dec 31 22:00:00 294276 UTC
+ 294276-12-31 23:59:59.999999+01 | Sun Dec 31 22:59:59.999999 294276 UTC
+ 294276-12-31 24:00:00.000000+01 | Sun Dec 31 23:00:00 294276 UTC
+(29 rows)
+
+DROP TABLE test_timestamptzs;
+-- truncating tests
+CREATE TABLE test_timestamptzs(txt TEXT, unit text, val timestamptz);
+-- truncating minimum allowed value results to even less value (not allowed)
+INSERT INTO test_timestamptzs SELECT v::text, u, date_trunc(u, v::timestamptz) FROM(VALUES('day', '4714-11-24 00:00:00.000000 BC'))t(u,v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, u, date_trunc(u, v::timestamptz) FROM(VALUES('week', '4714-11-24 00:00:00.000000 BC'))t(u,v); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, u, date_trunc(u, v::timestamptz) FROM(VALUES('month', '4714-11-30 23:59:59.999999 BC'))t(u,v); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, u, date_trunc(u, v::timestamptz) FROM(VALUES('year', '4714-12-31 23:59:59.999999 BC'))t(u,v); -- should fail
+ERROR: timestamp out of range
+SELECT txt, unit, val FROM test_timestamptzs;
+ txt | unit | val
+-------------------------------+------+---------------------------------
+ 4714-11-24 00:00:00.000000 BC | day | Mon Nov 24 00:00:00 4714 UTC BC
+(1 row)
+
+DROP TABLE test_timestamptzs;
+-- time zone conversions tests
+CREATE TABLE test_timestamptzs(txt TEXT, tz text, val timestamp);
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT-1', '4714-11-24 00:59:59.999999 BC'))t(tz,v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT-1', '4714-11-24 01:00:00.000000 BC'))t(tz,v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT+0', '4714-11-24 00:59:59.999999 BC'))t(tz,v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT+0', '294276-12-31 23:59:59.999999'))t(tz,v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT+1', '294276-12-31 22:59:59.999999'))t(tz,v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT+1', '294276-12-31 23:00:00.000000'))t(tz,v); -- should fail
+SELECT txt, tz, val FROM test_timestamptzs;
+ txt | tz | val
+-------------------------------+-------+------------------------------------
+ 4714-11-24 00:59:59.999999 BC | GMT-1 | Mon Nov 24 01:59:59.999999 4714 BC
+ 4714-11-24 01:00:00.000000 BC | GMT-1 | Mon Nov 24 02:00:00 4714 BC
+ 4714-11-24 00:59:59.999999 BC | GMT+0 | Mon Nov 24 00:59:59.999999 4714 BC
+ 294276-12-31 23:59:59.999999 | GMT+0 | Sun Dec 31 23:59:59.999999 294276
+ 294276-12-31 22:59:59.999999 | GMT+1 | Sun Dec 31 21:59:59.999999 294276
+ 294276-12-31 23:00:00.000000 | GMT+1 | Sun Dec 31 22:00:00 294276
+(6 rows)
+
+DROP TABLE test_timestamptzs;
+-- arithmetic operations
+CREATE TABLE test_timestamptzs(txt TEXT, delta INTERVAL, val timestamptz);
+-- the same as '294277-01-09 04:00:54.77580*'::timestamptz (with months) [should fail]
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775806'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775807'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775808'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775809'))t(v, d);
+ERROR: timestamp out of range
+-- the same as '294277-01-09 04:00:54.77580*'::timestamptz (with days) [should fail]
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775806'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775807'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775808'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775809'))t(v, d);
+ERROR: timestamp out of range
+-- the same as '294277-01-09 04:00:54.77580*'::timestamptz (with hours) [should fail]
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775806'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775807'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775808'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775809'))t(v, d);
+ERROR: timestamp out of range
+-- the same as '294277-01-09 04:00:54.77580*'::timestamptz (with seconds) [should fail]
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '946699254.775806'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '946699254.775807'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '946699254.775808'))t(v, d);
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '946699254.775809'))t(v, d);
+ERROR: timestamp out of range
+-- current corner cases
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '-1mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '-1day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '-1sec'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '-0.000001sec'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', ' 0sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '+0.000001sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '+1sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '+1sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '+1day'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '+1mon'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1mon'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1day'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-0.000001sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', ' 0sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+0.000001sec'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1sec'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1sec'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+-- try to add/substract maximal/minimal intervals (check for unexpected overflow)
+-- years, months
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+2147483647mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-2147483648mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '+2147483647mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '-2147483648mon'))t(v, d); -- should fail
+ERROR: timestamp out of range
+-- days
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+2147483647day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-2147483648day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '+2147483647day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '-2147483648day'))t(v, d); -- should fail
+ERROR: timestamp out of range
+SELECT txt, delta, val FROM test_timestamptzs;
+ txt | delta | val
+------------------------------+---------------------+----------------------------------------
+ 4714-11-24 BC | @ 0 | Mon Nov 24 00:00:00 4714 UTC BC
+ 4714-11-24 BC | @ 0.000001 secs | Mon Nov 24 00:00:00.000001 4714 UTC BC
+ 4714-11-24 BC | @ 1 sec | Mon Nov 24 00:00:01 4714 UTC BC
+ 4714-11-24 BC | @ 1 sec | Mon Nov 24 00:00:01 4714 UTC BC
+ 4714-11-24 BC | @ 1 day | Tue Nov 25 00:00:00 4714 UTC BC
+ 4714-11-24 BC | @ 1 mon | Wed Dec 24 00:00:00 4714 UTC BC
+ 294276-12-31 23:59:59.999999 | @ 1 mon ago | Thu Nov 30 23:59:59.999999 294276 UTC
+ 294276-12-31 23:59:59.999999 | @ 1 day ago | Sat Dec 30 23:59:59.999999 294276 UTC
+ 294276-12-31 23:59:59.999999 | @ 1 sec ago | Sun Dec 31 23:59:58.999999 294276 UTC
+ 294276-12-31 23:59:59.999999 | @ 0.000001 secs ago | Sun Dec 31 23:59:59.999998 294276 UTC
+ 294276-12-31 23:59:59.999999 | @ 0 | Sun Dec 31 23:59:59.999999 294276 UTC
+(11 rows)
+
+DROP TABLE test_timestamptzs;
+-- IN/OUT in different time zones
+SET TIME ZONE 'GMT-1';
+SELECT '4714-11-24 00:00:00.000000+00 BC'::timestamptz; --min allowed timestamptz
+ timestamptz
+---------------------------------
+ Mon Nov 24 01:00:00 4714 GMT BC
+(1 row)
+
+SELECT '294276-12-31 23:59:59.999999+00'::timestamptz; --max allowed timestamptz
+ timestamptz
+---------------------------------------
+ Mon Jan 01 00:59:59.999999 294277 GMT
+(1 row)
+
+SET TIME ZONE 'GMT+1';
+SELECT '4714-11-24 00:00:00.000000+00 BC'::timestamptz; --min allowed timestamptz
+ timestamptz
+---------------------------------
+ Sun Nov 23 23:00:00 4714 GMT BC
+(1 row)
+
+SELECT '294276-12-31 23:59:59.999999+00'::timestamptz; --max allowed timestamptz
+ timestamptz
+---------------------------------------
+ Sun Dec 31 22:59:59.999999 294276 GMT
+(1 row)
+
+RESET TIME ZONE;
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index e40b4c4..f277f71 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -217,6 +217,23 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
SELECT EXTRACT(EPOCH FROM DATE '1970-01-01'); -- 0
SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01'); -- 0
SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00'); -- 0
+
+-- corner cases for EPOCH from TIMESTAMPTZ
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '4714-11-24 00:00:00.000000+00 BC');
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '294247-01-10 04:00:54.775805+00'); -- just before INT64_MAX in extract
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '294247-01-10 04:00:55.775806+00'); -- just after INT64_MAX in extract
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '294276-12-31 23:59:59.9+00');
+-- ERROR (not to forget to change test here when range become wider)
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '294276-12-31 24:00:00.000000+00'); -- should fail (timestamp out of range)
+
+-- corner cases for EPOCH from TIMESTAMP
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '4714-11-24 00:00:00.000000 BC');
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '294247-01-10 04:00:54.775805'); -- just before INT64_MAX in extract
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '294247-01-10 04:00:55.775806'); -- just after INT64_MAX in extract
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '294276-12-31 23:59:59.9');
+-- ERROR (not to forget to change test here when range become wider)
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '294276-12-31 24:00:00.000000'); -- should fail (timestamp out of range)
+
--
-- century
--
@@ -340,3 +357,45 @@ select make_date(2013, 11, -1);
select make_date(-44, 3, 15); -- perhaps we should allow this sometime?
select make_time(10, 55, 100.1);
select make_time(24, 0, 2.1);
+
+-- corner cases
+-- (to prevent raising errors in *_out functions firstly write values to a table)
+CREATE TABLE test_dates(txt TEXT, val DATE);
+INSERT INTO test_dates SELECT v::text, v::date FROM(VALUES('4714-11-23 BC'))t(v); -- should fail
+INSERT INTO test_dates SELECT v::text, v::date FROM(VALUES('4714-11-24 BC'))t(v); -- OK
+INSERT INTO test_dates SELECT v::text, v::date FROM(VALUES('4713-01-01 BC'))t(v); -- OK
+INSERT INTO test_dates SELECT v::text, v::date FROM(VALUES('5874897-12-31'))t(v); -- OK
+INSERT INTO test_dates SELECT v::text, v::date FROM(VALUES('5874898-01-01'))t(v); -- should fail
+
+SELECT txt, val FROM test_dates;
+DROP TABLE test_dates;
+
+-- arithmetic operations(only with int values: intervals lead to convert dates to timestamps)
+CREATE TABLE test_dates(txt TEXT, delta int, val DATE);
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('4714-11-24 BC', -2147483648))t(v, d); -- should fail(overflow check)
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('4714-11-24 BC', -1))t(v, d); -- should fail
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('4714-11-24 BC', 0))t(v, d); -- OK
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('4714-11-24 BC', +1))t(v, d); -- OK
+
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', -1))t(v, d); -- OK
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', 0))t(v, d); -- OK
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +1))t(v, d); -- should fail
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +2147483647))t(v, d); -- should fail(overflow check)
+
+-- previously it resulted OK, +inf, -inf, very high result (can't reach normally)
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +2451698))t(v, d); -- should fail(not in allowed range)
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +2451699))t(v, d); -- should fail(overflow check)
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +2451700))t(v, d); -- should fail(overflow check)
+INSERT INTO test_dates SELECT v::text, d, v::date + d FROM(VALUES('5874897-12-31', +2451701))t(v, d); -- should fail(overflow check)
+
+-- Currently it is impossible to reach overflow result of MAX_DATE+MAX_INT
+-- or MIN_DATE+MIN_INT and to be in range [MIN_DATE;MAX_DATE) because the 2nd
+-- addend is type of int4; in case of int8 it is possible thereby when int8 is
+-- allowed as an addend it is necessary to insert checking for overflow result.
+-- Note: originally "select '5874897-12-31'::date + 2147483647" => 4714-06-21 BC
+-- which is _very_ close (156 days) to MIN_DATE(4714-11-24 BC)
+SELECT v::text, d, v::date + d::bigint FROM(VALUES('5874897-12-31', +2147483648))t(v, d); -- should fail(no operator)
+
+
+SELECT txt, delta, val FROM test_dates;
+DROP TABLE test_dates;
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index c81437b..39ad7c0 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -496,4 +496,18 @@ SELECT '2012-12-12 12:00 America/New_York'::timestamptz;
SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+-- corner cases
+SET TIME ZONE 'UTC';
+
+SELECT to_date('4714 11 23 BC', 'YYYY MM DD BC'); -- ERROR: date out of range: "-4713 01 01"
+SELECT to_date('4714 11 24 BC', 'YYYY MM DD BC'); -- OK
+SELECT to_date('5874897 12 31', 'YYYY MM DD'); -- OK
+SELECT to_date('5874898 01 01', 'YYYY MM DD'); -- ERROR: date out of range: "5874898 01 01"
+
+
+SELECT to_timestamp('4714 11 23 23:59:59 BC', 'YYYY MM DD HH24:MI:SS BC'); -- should fail
+SELECT to_timestamp('4714 11 24 00:00:00 BC', 'YYYY MM DD HH24:MI:SS BC'); -- OK
+SELECT to_timestamp('294276 12 31 23:59:59', 'YYYY MM DD HH24:MI:SS'); -- OK
+SELECT to_timestamp('294277 01 01 00:00:00', 'YYYY MM DD HH24:MI:SS'); -- should fail
+
RESET TIME ZONE;
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index b22cd48..ad94b23 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -225,3 +225,140 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
-- timestamp numeric fields constructor
SELECT make_timestamp(2014,12,28,6,30,45.887);
+
+-- corner cases
+-- (to prevent raising errors in *_out functions insert values to a table and then select from it)
+SET TIME ZONE 'UTC';
+
+CREATE TABLE test_timestamps(txt TEXT, val timestamp);
+
+-- previously it resulted OK, +inf, -inf, ERROR (in timestamp_out!)
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294277-01-09 04:00:54.775806'))t(v); -- should fail
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294277-01-09 04:00:54.775807'))t(v); -- should fail
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294277-01-09 04:00:54.775808'))t(v); -- should fail
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294277-01-09 04:00:54.775809'))t(v); -- should fail
+
+-- current corner cases
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('4714-11-23 23:59:59.999999 BC'))t(v); -- should fail
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('4714-11-24 00:00:00.000000 BC'))t(v); -- OK
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('4713-01-01 00:00:00.000000 BC'))t(v); -- OK
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294276-12-31 23:59:59.999999'))t(v); -- OK
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294276-12-31 24:00:00.000000'))t(v); -- should fail
+INSERT INTO test_timestamps SELECT v::text, v::timestamp FROM(VALUES('294277-01-01 00:00:00.000000'))t(v); -- should fail
+
+-- current corner cases with make_timestamp (note: negative years are not allowed)
+INSERT INTO test_timestamps SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamp(y,m,d,h,mm,s) FROM(VALUES(294276,12,31, 23,59,59.999999))t(y,m,d,h,mm,s); -- OK
+INSERT INTO test_timestamps SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamp(y,m,d,h,mm,s) FROM(VALUES(294276,12,31, 24,00,00.000000))t(y,m,d,h,mm,s); -- should fail
+INSERT INTO test_timestamps SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamp(y,m,d,h,mm,s) FROM(VALUES(294277,01,01, 00,00,00.000000))t(y,m,d,h,mm,s); -- should fail
+
+-- conversion from date (has bigger allowed interval)
+INSERT INTO test_timestamps SELECT v::text, v::date::timestamp FROM(VALUES('4714-11-23 BC'))t(v); -- should fail
+INSERT INTO test_timestamps SELECT v::text, v::date::timestamp FROM(VALUES('4714-11-24 BC'))t(v); -- OK
+INSERT INTO test_timestamps SELECT v::text, v::date::timestamp FROM(VALUES('294276-12-31'))t(v); -- OK
+INSERT INTO test_timestamps SELECT v::text, v::date::timestamp FROM(VALUES('294277-01-01'))t(v); -- should fail
+
+-- conversion from date+time (has bigger allowed interval)
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('4714-11-23 BC', '23:59:59.999999'))t(d,t); -- should fail
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('4714-11-23 BC', '24:00:00.000000'))t(d,t); -- should fail
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('4714-11-24 BC', '00:00:00.000000'))t(d,t); -- OK
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('294276-12-31', '23:59:59.999999'))t(d,t); -- OK
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('294276-12-31', '24:00:00.000000'))t(d,t); -- should fail
+INSERT INTO test_timestamps SELECT format('%s %s', d::text, t::text), "timestamp"(d::date, t::time) FROM(VALUES('294277-01-01', '00:00:00.000000'))t(d,t); -- should fail
+
+SELECT txt, val FROM test_timestamps;
+DROP TABLE test_timestamps;
+
+
+-- truncating tests
+CREATE TABLE test_timestamps(txt TEXT, unit text, val timestamp);
+-- truncating minimum allowed value results to even less value (not allowed)
+INSERT INTO test_timestamps SELECT v::text, u, date_trunc(u, v::timestamp) FROM(VALUES('day', '4714-11-24 00:00:00.000000 BC'))t(u,v); -- OK
+INSERT INTO test_timestamps SELECT v::text, u, date_trunc(u, v::timestamp) FROM(VALUES('week', '4714-11-24 00:00:00.000000 BC'))t(u,v); -- should fail
+INSERT INTO test_timestamps SELECT v::text, u, date_trunc(u, v::timestamp) FROM(VALUES('month', '4714-11-30 23:59:59.999999 BC'))t(u,v); -- should fail
+INSERT INTO test_timestamps SELECT v::text, u, date_trunc(u, v::timestamp) FROM(VALUES('year', '4714-12-31 23:59:59.999999 BC'))t(u,v); -- should fail
+
+SELECT txt, unit, val FROM test_timestamps;
+DROP TABLE test_timestamps;
+
+
+-- time zone conversions tests
+CREATE TABLE test_timestamps(txt TEXT, tz text, val timestamptz);
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT-1', '4714-11-24 00:59:59.999999 BC'))t(tz,v); -- should fail
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT-1', '4714-11-24 01:00:00.000000 BC'))t(tz,v); -- OK
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT+0', '4714-11-24 00:59:59.999999 BC'))t(tz,v); -- OK
+
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT+0', '294276-12-31 23:59:59.999999'))t(tz,v); -- OK
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT+1', '294276-12-31 22:59:59.999999'))t(tz,v); -- OK
+INSERT INTO test_timestamps SELECT v::text, tz, v::timestamp AT TIME ZONE tz FROM(VALUES('GMT+1', '294276-12-31 23:00:00.000000'))t(tz,v); -- should fail
+
+SELECT txt, tz, val FROM test_timestamps;
+DROP TABLE test_timestamps;
+
+
+-- arithmetic operations
+CREATE TABLE test_timestamps(txt TEXT, delta INTERVAL, val timestamp);
+
+-- the same as '294277-01-09 04:00:54.77580*'::timestamp (with months) [should fail]
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775806'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775807'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775808'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775809'))t(v, d);
+
+-- the same as '294277-01-09 04:00:54.77580*'::timestamp (with days) [should fail]
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775806'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775807'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775808'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775809'))t(v, d);
+
+-- the same as '294277-01-09 04:00:54.77580*'::timestamp (with hours) [should fail]
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775806'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775807'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775808'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775809'))t(v, d);
+
+-- the same as '294277-01-09 04:00:54.77580*'::timestamp (with seconds) [should fail]
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '946699254.775806'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '946699254.775807'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '946699254.775808'))t(v, d);
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294247-01-10', '946699254.775809'))t(v, d);
+
+-- current corner cases
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '-1mon'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '-1day'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '-1sec'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '-0.000001sec'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', ' 0sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '+0.000001sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '+1sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '+1sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '+1day'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 BC', '+1mon'))t(v, d); -- OK
+
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1mon'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1day'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-0.000001sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', ' 0sec'))t(v, d); -- OK
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+0.000001sec'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1sec'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1sec'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1day'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1mon'))t(v, d); -- should fail
+
+-- try to add/substract maximal/minimal intervals (check for unexpected overflow)
+-- years, months
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+2147483647mon'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-2147483648mon'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '+2147483647mon'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '-2147483648mon'))t(v, d); -- should fail
+-- days
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+2147483647day'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-2147483648day'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '+2147483647day'))t(v, d); -- should fail
+INSERT INTO test_timestamps SELECT v::text, d::interval, v::timestamp + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '-2147483648day'))t(v, d); -- should fail
+
+SELECT txt, delta, val FROM test_timestamps;
+DROP TABLE test_timestamps;
+
+
+RESET TIME ZONE;
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index f4b455e..1571f45 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -432,3 +432,207 @@ SELECT '2007-12-09 07:00:00 UTC'::timestamptz AT TIME ZONE 'VET';
SELECT '2007-12-09 07:00:01 UTC'::timestamptz AT TIME ZONE 'VET';
SELECT '2007-12-09 07:29:59 UTC'::timestamptz AT TIME ZONE 'VET';
SELECT '2007-12-09 07:30:00 UTC'::timestamptz AT TIME ZONE 'VET';
+
+-- corner cases
+-- (to prevent raising errors in *_out functions insert values to a table and then select from it)
+SET TIME ZONE 'UTC';
+
+CREATE TABLE test_timestamptzs(txt TEXT, val timestamptz);
+
+-- previously it resulted OK, +inf, -inf, ERROR (in timestamp_out!)
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294277-01-09 03:00:54.775806-01'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294277-01-09 03:00:54.775807-01'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294277-01-09 03:00:54.775808-01'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294277-01-09 03:00:54.775809-01'))t(v); -- should fail
+
+-- current corner cases
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4714-11-23 23:59:59.999999 BC'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4714-11-24 00:00:00.000000 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4713-01-01 00:00:00.000000 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294276-12-31 23:59:59.999999'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294276-12-31 24:00:00.000000'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294277-01-01 00:00:00.000000'))t(v); -- should fail
+
+-- current corner cases with non-UTC time zones
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4714-11-24 00:59:59.999999+01 BC'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4714-11-24 01:00:00.000000+01 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('4713-01-01 01:00:00.000000+01 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294276-12-31 22:59:59.999999-01'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::timestamptz FROM(VALUES('294276-12-31 23:00:00.000000-01'))t(v); -- should fail
+
+-- current corner cases with make_timestamptz(current tz=UTC) (note: negative years are not allowed)
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamptz(y,m,d,h,mm,s) FROM(VALUES(294276,12,31, 23,59,59.999999))t(y,m,d,h,mm,s); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamptz(y,m,d,h,mm,s) FROM(VALUES(294276,12,31, 24,00,00.000000))t(y,m,d,h,mm,s); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s', y,m,d,h,mm,s), make_timestamptz(y,m,d,h,mm,s) FROM(VALUES(294277,01,01, 00,00,00.000000))t(y,m,d,h,mm,s); -- should fail
+
+-- current corner cases with make_timestamptz(in non-UTC TZ) (note: negative years are not allowed)
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s %s', y,m,d,h,mm,s,tz), make_timestamptz(y,m,d,h,mm,s,tz) FROM(VALUES(294276,12,31, 23,59,59.999999, 'GMT+0'))t(y,m,d,h,mm,s,tz); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s %s', y,m,d,h,mm,s,tz), make_timestamptz(y,m,d,h,mm,s,tz) FROM(VALUES(294276,12,31, 22,59,59.999999, 'GMT+1'))t(y,m,d,h,mm,s,tz); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s-%s-%s %s:%s:%s %s', y,m,d,h,mm,s,tz), make_timestamptz(y,m,d,h,mm,s,tz) FROM(VALUES(294276,12,31, 23,00,00.000000, 'GMT+1'))t(y,m,d,h,mm,s,tz); -- should fail
+
+-- conversion from date (has bigger allowed interval)
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-23 BC'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-24 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294276-12-31'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294277-01-01'))t(v); -- should fail
+
+-- check for overflow because of time zones
+SET TIME ZONE 'GMT-1';
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-22 BC'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-23 BC'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-24 BC'))t(v); -- should fail (-4714-11-23 23:00:00)
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-25 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294276-12-30'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294276-12-31'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294277-01-01'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294277-01-02'))t(v); -- should fail
+
+SET TIME ZONE 'GMT+1';
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-22 BC'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-23 BC'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-24 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('4714-11-25 BC'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294276-12-30'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294276-12-31'))t(v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294277-01-01'))t(v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, v::date::timestamptz FROM(VALUES('294277-01-02'))t(v); -- should fail
+
+-- restore UTC by default
+SET TIME ZONE 'UTC';
+
+-- conversion from date+time (has bigger allowed interval)
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '23:59:59.999999'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '24:00:00.000000'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '00:00:00.000000'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '23:59:59.999999'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '24:00:00.000000'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294277-01-01', '00:00:00.000000'))t(d,t); -- should fail
+
+-- conversion from date+timeTZ (has bigger allowed interval)
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '23:59:59.999999-01'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '24:00:00.000000-01'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '00:00:00.000000-01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '00:59:59.999999-01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '01:00:00.000000-01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '22:59:59.999999-01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '23:00:00.000000-01'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '23:59:59.999999-01'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '24:00:00.000000-01'))t(d,t); -- should fail
+
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '23:59:59.999999+01'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-23 BC', '24:00:00.000000+01'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '00:00:00.000000+01'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '00:59:59.999999+01'))t(d,t); -- should fail
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('4714-11-24 BC', '01:00:00.000000+01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '22:59:59.999999+01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '23:00:00.000000+01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '23:59:59.999999+01'))t(d,t); -- OK
+INSERT INTO test_timestamptzs SELECT format('%s %s', d::text, t::text), "timestamptz"(d::date, t::timetz) FROM(VALUES('294276-12-31', '24:00:00.000000+01'))t(d,t); -- OK
+
+
+SELECT txt, val FROM test_timestamptzs;
+DROP TABLE test_timestamptzs;
+
+
+-- truncating tests
+CREATE TABLE test_timestamptzs(txt TEXT, unit text, val timestamptz);
+-- truncating minimum allowed value results to even less value (not allowed)
+INSERT INTO test_timestamptzs SELECT v::text, u, date_trunc(u, v::timestamptz) FROM(VALUES('day', '4714-11-24 00:00:00.000000 BC'))t(u,v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, u, date_trunc(u, v::timestamptz) FROM(VALUES('week', '4714-11-24 00:00:00.000000 BC'))t(u,v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, u, date_trunc(u, v::timestamptz) FROM(VALUES('month', '4714-11-30 23:59:59.999999 BC'))t(u,v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, u, date_trunc(u, v::timestamptz) FROM(VALUES('year', '4714-12-31 23:59:59.999999 BC'))t(u,v); -- should fail
+
+SELECT txt, unit, val FROM test_timestamptzs;
+DROP TABLE test_timestamptzs;
+
+
+-- time zone conversions tests
+CREATE TABLE test_timestamptzs(txt TEXT, tz text, val timestamp);
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT-1', '4714-11-24 00:59:59.999999 BC'))t(tz,v); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT-1', '4714-11-24 01:00:00.000000 BC'))t(tz,v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT+0', '4714-11-24 00:59:59.999999 BC'))t(tz,v); -- OK
+
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT+0', '294276-12-31 23:59:59.999999'))t(tz,v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT+1', '294276-12-31 22:59:59.999999'))t(tz,v); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, tz, v::timestamptz AT TIME ZONE tz FROM(VALUES('GMT+1', '294276-12-31 23:00:00.000000'))t(tz,v); -- should fail
+
+SELECT txt, tz, val FROM test_timestamptzs;
+DROP TABLE test_timestamptzs;
+
+
+-- arithmetic operations
+CREATE TABLE test_timestamptzs(txt TEXT, delta INTERVAL, val timestamptz);
+
+-- the same as '294277-01-09 04:00:54.77580*'::timestamptz (with months) [should fail]
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775806'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775807'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775808'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-09', '360 months 04:00:54.775809'))t(v, d);
+
+-- the same as '294277-01-09 04:00:54.77580*'::timestamptz (with days) [should fail]
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775806'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775807'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775808'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '10957 days 04:00:54.775809'))t(v, d);
+
+-- the same as '294277-01-09 04:00:54.77580*'::timestamptz (with hours) [should fail]
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775806'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775807'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775808'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '262972:00:54.775809'))t(v, d);
+
+-- the same as '294277-01-09 04:00:54.77580*'::timestamptz (with seconds) [should fail]
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '946699254.775806'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '946699254.775807'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '946699254.775808'))t(v, d);
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294247-01-10', '946699254.775809'))t(v, d);
+
+-- current corner cases
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '-1mon'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '-1day'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '-1sec'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '-0.000001sec'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', ' 0sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '+0.000001sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '+1sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '+1sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '+1day'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 BC', '+1mon'))t(v, d); -- OK
+
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1mon'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1day'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-1sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-0.000001sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', ' 0sec'))t(v, d); -- OK
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+0.000001sec'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1sec'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1sec'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1day'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+1mon'))t(v, d); -- should fail
+
+-- try to add/substract maximal/minimal intervals (check for unexpected overflow)
+-- years, months
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+2147483647mon'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-2147483648mon'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '+2147483647mon'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '-2147483648mon'))t(v, d); -- should fail
+-- days
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '+2147483647day'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('294276-12-31 23:59:59.999999', '-2147483648day'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '+2147483647day'))t(v, d); -- should fail
+INSERT INTO test_timestamptzs SELECT v::text, d::interval, v::timestamptz + d::interval FROM(VALUES('4714-11-24 01:00:00.000000 BC', '-2147483648day'))t(v, d); -- should fail
+
+SELECT txt, delta, val FROM test_timestamptzs;
+DROP TABLE test_timestamptzs;
+
+-- IN/OUT in different time zones
+SET TIME ZONE 'GMT-1';
+SELECT '4714-11-24 00:00:00.000000+00 BC'::timestamptz; --min allowed timestamptz
+SELECT '294276-12-31 23:59:59.999999+00'::timestamptz; --max allowed timestamptz
+
+SET TIME ZONE 'GMT+1';
+SELECT '4714-11-24 00:00:00.000000+00 BC'::timestamptz; --min allowed timestamptz
+SELECT '294276-12-31 23:59:59.999999+00'::timestamptz; --max allowed timestamptz
+
+
+RESET TIME ZONE;
On 2/24/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
On 2/2/16, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 2/2/16 6:39 PM, Tom Lane wrote:
I'm inclined to think that a good solution would be to create an
artificial restriction to not accept years beyond, say, 100000 AD.
That would leave us with a lot of daylight to not have to worry
about corner-case overflows in timestamp arithmetic. I'm not sure
though where we'd need to enforce such a restriction; certainly in
timestamp[tz]_in, but where else?Probably some of the casts (I'd think at least timestamp->timestamptz).
Maybe timestamp[tz]_recv. Most of the time*pl* functions. :/Please find attached a patch checks boundaries of date/timestamp[tz].
There are more functions: converting to/from timestamptz, truncating,
constructing from date and time etc.I left the upper boundary as described[1] in the documentation
(294276-12-31 AD), lower - "as is" (4714-11-24 BC).
It is easy to change the lower boundary to 4713-01-01BC (as described
in the documentation) and it seems necessary because it allows to
simplify IS_VALID_JULIAN and IS_VALID_JULIAN4STAMPS and avoid the next
behavior:postgres=# select
postgres-# to_char(date_trunc('week', '4713-01-01 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-29 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-28 BC'::date),'day');
to_char | to_char | to_char
-----------+-----------+-----------
monday | monday | thursday
(1 row)since 4714-12-28 BC and to the past detection when a week is starting
is broken (because it is boundary of isoyears -4713 and -4712).
Is it worth to break undocumented range or leave it as is?There is one more flaw: checking for a correctness begins from date
and if default TZ is not UTC, dump/restore of values of type
timestamptz which are close to allowed boundaries can be broken (and
such result can't be restored because date is not in allowed range):
postgres=# SET TIME ZONE 'GMT+1';
SET
postgres=# COPY (SELECT '4714-11-24 00:00:00.000000+00
BC'::timestamptz) TO STDOUT;
4714-11-23 23:00:00-01 BCAlso I'm asking for a help because the query (in default TZ='GMT+1'):
postgres=# SELECT '4714-11-24 00:00:00.000000+00 BC'::timestamptz;in psql gives a result "4714-11-23 23:00:00-01 BC",
but in a testing system gives "Sun Nov 23 23:00:00 4714 GMT BC"
without TZ offset.I don't see what can be added to the documentation with the applied patch.
More testings, finding bugs, uncovered functions, advice, comment
improvements are very appreciated.[1]http://www.postgresql.org/docs/devel/static/datatype-datetime.html
I'm sorry, gmail hasn't added a header "In-Reply-To" to the last email.
Previous thread is by the link[2].
/messages/by-id/CAKOSWNked3JOE++PEs49GMDNfR2ieu9A2jFCEZ6EW-+1c5_u9Q@mail.gmail.com
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
<overquoting>
Added to the commitfest 2016-03.
[CF] https://commitfest.postgresql.org/9/540/
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2/25/16 4:44 PM, Vitaly Burovoy wrote:
Added to the commitfest 2016-03.
This looks like a fairly straight-forward bug fix (the size of the patch
is deceptive because there a lot of new tests included). It applies
cleanly.
Anastasia, I see you have signed up to review. Do you have an idea when
you will get the chance to do that?
Thanks,
--
-David
david@pgmasters.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mar 14, 2016, at 6:23 AM, David Steele <david@pgmasters.net> wrote:
On 2/25/16 4:44 PM, Vitaly Burovoy wrote:
Added to the commitfest 2016-03.
This looks like a fairly straight-forward bug fix (the size of the patch is deceptive because there a lot of new tests included). It applies cleanly.
Anastasia, I see you have signed up to review. Do you have an idea when you will get the chance to do that?
The first thing I notice about this patch is that src/include/datatype/timestamp.h
has some #defines that are brittle. The #defines have comments explaining
their logic, but I'd rather embed that in the #define directly:
This:
+#ifdef HAVE_INT64_TIMESTAMP
+#define MIN_TIMESTAMP INT64CONST(-211813488000000000)
+/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */
+#define MAX_TIMESTAMP INT64CONST(9223371331200000000)
+/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */
+#else
+#define MIN_TIMESTAMP -211813488000.0
+/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 */
+#define MAX_TIMESTAMP 9223371331200.0
+/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 */
+#endifCould be written as:
#ifdef HAVE_INT64_TIMESTAMP
#define MIN_TIMESTAMP ((INT64CONST(0) - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY)
#define MAX_TIMESTAMP ((JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY)
#else
#define MIN_TIMESTAMP ((INT64CONST(0) - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY)
#define MAX_TIMESTAMP ((JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY)
#endif
I assume modern compilers would convert these to the same constants at compile-time,
rather than impose a runtime penalty. The #defines would be less brittle in the event, for
example, that the postgres epoch were ever changed.
Mark Dilger
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
14.03.2016 16:23, David Steele:
On 2/25/16 4:44 PM, Vitaly Burovoy wrote:
Added to the commitfest 2016-03.
This looks like a fairly straight-forward bug fix (the size of the
patch is deceptive because there a lot of new tests included). It
applies cleanly.Anastasia, I see you have signed up to review. Do you have an idea
when you will get the chance to do that?Thanks,
I've read the patch thoroughly and haven't found any problems. I think
that the patch is in a very good shape.
It fixes a bug and has an excellent set of tests.
There is an issue, mentioned in the thread above:
postgres=# select
postgres-# to_char(date_trunc('week', '4713-01-01 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-29 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-28 BC'::date),'day');
to_char | to_char | to_char
-----------+-----------+-----------
monday | monday | thursday
(1 row)
since 4714-12-28 BC and to the past detection when a week is starting
is broken (because it is boundary of isoyears -4713 and -4712).
Is it worth to break undocumented range or leave it as is?
But I suppose that behavior of undocumented dates is not essential.
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3/14/16, Mark Dilger <hornschnorter@gmail.com> wrote:
The first thing I notice about this patch is that
src/include/datatype/timestamp.h
has some #defines that are brittle. The #defines have comments explaining
their logic, but I'd rather embed that in the #define directly:This:
+#ifdef HAVE_INT64_TIMESTAMP +#define MIN_TIMESTAMP INT64CONST(-211813488000000000) +/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */ +#define MAX_TIMESTAMP INT64CONST(9223371331200000000) +/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */ +#else +#define MIN_TIMESTAMP -211813488000.0 +/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 */ +#define MAX_TIMESTAMP 9223371331200.0 +/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 */ +#endifCould be written as:
#ifdef HAVE_INT64_TIMESTAMP
#define MIN_TIMESTAMP
((INT64CONST(0) - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY)
#define MAX_TIMESTAMP
((JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY)
#else
#define MIN_TIMESTAMP
((INT64CONST(0) - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY)
#define MAX_TIMESTAMP
((JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY)
#endifI assume modern compilers would convert these to the same constants at
compile-time,
Firstly, Postgres is compiling not only by modern compilers.
rather than impose a runtime penalty.
Secondary, It is hard to write it correctly obeying Postgres' coding
standard (e.g. 80-columns border) and making it clear: it is not so
visual difference between USECS_PER_DAY and SECS_PER_DAY in the
expressions above (but it is vivid in comments in the file). Also a
questions raise "Why is INT64CONST(0) necessary in `#else' block"
(whereas `float' is necessary there) or "Why is INT64CONST set for
MIN_TIMESTAMP, but not for MAX_TIMESTAMP?" (JULIAN_MAX4STAMPS is _not_
int64).
The file is full of constants. For example, JULIAN_MAX and
SECS_PER_DAY are one of them.
I would leave it as is.
The #defines would be less brittle in
the event, for example, that the postgres epoch were ever changed.
I don't think it is real, and even in such case all constants are
collected together in the file and will be found and changed at once.
Mark Dilger
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3/14/16, Anastasia Lubennikova <a.lubennikova@postgrespro.ru> wrote:
14.03.2016 16:23, David Steele:
On 2/25/16 4:44 PM, Vitaly Burovoy wrote:
Added to the commitfest 2016-03.
This looks like a fairly straight-forward bug fix (the size of the
patch is deceptive because there a lot of new tests included). It
applies cleanly.Anastasia, I see you have signed up to review. Do you have an idea
when you will get the chance to do that?Thanks,
I've read the patch thoroughly and haven't found any problems. I think
that the patch is in a very good shape.
It fixes a bug and has an excellent set of tests.There is an issue, mentioned in the thread above:
postgres=# select
postgres-# to_char(date_trunc('week', '4713-01-01 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-29 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-28 BC'::date),'day');
to_char | to_char | to_char
-----------+-----------+-----------
monday | monday | thursday
(1 row)since 4714-12-28 BC and to the past detection when a week is starting
is broken (because it is boundary of isoyears -4713 and -4712).
Is it worth to break undocumented range or leave it as is?But I suppose that behavior of undocumented dates is not essential.
I'm sorry... What should I do with "Waiting on Author" state if you
don't have complaints?
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
15.03.2016 03:21, Vitaly Burovoy:
On 3/14/16, Anastasia Lubennikova <a.lubennikova@postgrespro.ru> wrote:
14.03.2016 16:23, David Steele:
On 2/25/16 4:44 PM, Vitaly Burovoy wrote:
Added to the commitfest 2016-03.
This looks like a fairly straight-forward bug fix (the size of the
patch is deceptive because there a lot of new tests included). It
applies cleanly.Anastasia, I see you have signed up to review. Do you have an idea
when you will get the chance to do that?Thanks,
I've read the patch thoroughly and haven't found any problems. I think
that the patch is in a very good shape.
It fixes a bug and has an excellent set of tests.There is an issue, mentioned in the thread above:
postgres=# select
postgres-# to_char(date_trunc('week', '4713-01-01 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-29 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-28 BC'::date),'day');
to_char | to_char | to_char
-----------+-----------+-----------
monday | monday | thursday
(1 row)
since 4714-12-28 BC and to the past detection when a week is starting
is broken (because it is boundary of isoyears -4713 and -4712).
Is it worth to break undocumented range or leave it as is?But I suppose that behavior of undocumented dates is not essential.
I'm sorry... What should I do with "Waiting on Author" state if you
don't have complaints?
I was going to set "Ready for Committer", but then I've noticed message
from Mark Dilger and changed my mind.
Now, when you answered him, I have no objections.
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mar 14, 2016, at 5:12 PM, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
On 3/14/16, Mark Dilger <hornschnorter@gmail.com> wrote:
The first thing I notice about this patch is that
src/include/datatype/timestamp.h
has some #defines that are brittle. The #defines have comments explaining
their logic, but I'd rather embed that in the #define directly:This:
+#ifdef HAVE_INT64_TIMESTAMP +#define MIN_TIMESTAMP INT64CONST(-211813488000000000) +/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */ +#define MAX_TIMESTAMP INT64CONST(9223371331200000000) +/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */ +#else +#define MIN_TIMESTAMP -211813488000.0 +/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 */ +#define MAX_TIMESTAMP 9223371331200.0 +/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 */ +#endifCould be written as:
#ifdef HAVE_INT64_TIMESTAMP
#define MIN_TIMESTAMP
((INT64CONST(0) - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY)
#define MAX_TIMESTAMP
((JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY)
#else
#define MIN_TIMESTAMP
((INT64CONST(0) - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY)
#define MAX_TIMESTAMP
((JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY)
#endifI assume modern compilers would convert these to the same constants at
compile-time,Firstly, Postgres is compiling not only by modern compilers.
Do you have an example of a compiler that will not do this constant folding
at compile time?
rather than impose a runtime penalty.
Secondary, It is hard to write it correctly obeying Postgres' coding
standard (e.g. 80-columns border) and making it clear: it is not so
visual difference between USECS_PER_DAY and SECS_PER_DAY in the
expressions above (but it is vivid in comments in the file).
Hmm. I think using USECS_PER_DAY is perfectly clear, but that is a personal
opinion. I don't see any way to argue if you don't see it that way.
Also a
questions raise "Why is INT64CONST(0) necessary in `#else' block"
(whereas `float' is necessary there)
You appear to be correct. The second half should be defined in terms of
float. I compiled on a system with int64 support, so I didn't notice. Thanks
for catching that.
or "Why is INT64CONST set for
MIN_TIMESTAMP, but not for MAX_TIMESTAMP?" (JULIAN_MAX4STAMPS is _not_
int64).
I was only casting the zero to int64. That doesn't seem necessary, so it can
be removed. Both MIN_TIMESTAMP and MAX_TIMESTAMP were defined
in terms of USECS_PER_DAY, which itself is defined in terms of INT64CONST,
so I believe they both work out to be an int64 constant.
The file is full of constants. For example, JULIAN_MAX and
SECS_PER_DAY are one of them.
JULIAN_MAXYEAR and JULIAN_MAXYEAR4STAMPS appear to be magic
numbers with no explanation. I would not point to them as examples to be
followed.
I would leave it as is.
The #defines would be less brittle in
the event, for example, that the postgres epoch were ever changed.I don't think it is real, and even in such case all constants are
collected together in the file and will be found and changed at once.
I agree that they would be found at once. I disagree that the example
is not real, as I have changed the postgres epoch myself in some builds,
to be able to use int32 timestamps on small devices.
Regards,
Mark Dilger
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mar 15, 2016, at 8:35 AM, Mark Dilger <hornschnorter@gmail.com> wrote:
On Mar 14, 2016, at 5:12 PM, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
On 3/14/16, Mark Dilger <hornschnorter@gmail.com> wrote:
The first thing I notice about this patch is that
src/include/datatype/timestamp.h
has some #defines that are brittle. The #defines have comments explaining
their logic, but I'd rather embed that in the #define directly:This:
+#ifdef HAVE_INT64_TIMESTAMP +#define MIN_TIMESTAMP INT64CONST(-211813488000000000) +/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */ +#define MAX_TIMESTAMP INT64CONST(9223371331200000000) +/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */ +#else +#define MIN_TIMESTAMP -211813488000.0 +/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 */ +#define MAX_TIMESTAMP 9223371331200.0 +/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 */ +#endifCould be written as:
#ifdef HAVE_INT64_TIMESTAMP
#define MIN_TIMESTAMP
((INT64CONST(0) - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY)
#define MAX_TIMESTAMP
((JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY)
#else
#define MIN_TIMESTAMP
((INT64CONST(0) - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY)
#define MAX_TIMESTAMP
((JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY)
#endifI assume modern compilers would convert these to the same constants at
compile-time,Firstly, Postgres is compiling not only by modern compilers.
Do you have an example of a compiler that will not do this constant folding
at compile time?rather than impose a runtime penalty.
Secondary, It is hard to write it correctly obeying Postgres' coding
standard (e.g. 80-columns border) and making it clear: it is not so
visual difference between USECS_PER_DAY and SECS_PER_DAY in the
expressions above (but it is vivid in comments in the file).Hmm. I think using USECS_PER_DAY is perfectly clear, but that is a personal
opinion. I don't see any way to argue if you don't see it that way.Also a
questions raise "Why is INT64CONST(0) necessary in `#else' block"
(whereas `float' is necessary there)You appear to be correct. The second half should be defined in terms of
float. I compiled on a system with int64 support, so I didn't notice. Thanks
for catching that.or "Why is INT64CONST set for
MIN_TIMESTAMP, but not for MAX_TIMESTAMP?" (JULIAN_MAX4STAMPS is _not_
int64).I was only casting the zero to int64. That doesn't seem necessary, so it can
be removed. Both MIN_TIMESTAMP and MAX_TIMESTAMP were defined
in terms of USECS_PER_DAY, which itself is defined in terms of INT64CONST,
so I believe they both work out to be an int64 constant.The file is full of constants. For example, JULIAN_MAX and
SECS_PER_DAY are one of them.JULIAN_MAXYEAR and JULIAN_MAXYEAR4STAMPS appear to be magic
numbers with no explanation. I would not point to them as examples to be
followed.I would leave it as is.
The #defines would be less brittle in
the event, for example, that the postgres epoch were ever changed.I don't think it is real, and even in such case all constants are
collected together in the file and will be found and changed at once.I agree that they would be found at once. I disagree that the example
is not real, as I have changed the postgres epoch myself in some builds,
to be able to use int32 timestamps on small devices.Regards,
Mark Dilger
I forgot to mention that I am not trying to block this commit. These were just
my observations about the patch, for your consideration.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Mark Dilger <hornschnorter@gmail.com> writes:
On Mar 14, 2016, at 5:12 PM, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
I don't think it is real, and even in such case all constants are
collected together in the file and will be found and changed at once.
I agree that they would be found at once. I disagree that the example
is not real, as I have changed the postgres epoch myself in some builds,
to be able to use int32 timestamps on small devices.
I concur with Vitaly that it's not this patch's job to make it easier to
change the epoch date. If you want to submit a patch for that purpose,
you're welcome to.
I have a bigger problem though: I see that the patch enforces AD 294277
as the endpoint for both integer and floating-point datetimes. This
contradicts the statement in the docs (section 8.5) that
Note that using floating-point datetimes allows a larger range of
timestamp values to be represented than shown above: from 4713 BC up
to 5874897 AD.
Since that is just about the only non-historical reason why somebody might
wish to use float timestamps, I'm rather reluctant to remove the feature,
especially without any discussion --- and I don't see any discussion of
this point upthread.
My feeling is we ought to preserve the old behavior here, which would
involve making JULIAN_MAXYEAR_FOR_TIMESTAMPS format-dependent and
adjusting the float values for the two derived constants; not much of a
problem code-wise. I think though that it would break quite a number of
the proposed new regression tests for the float case. TBH, I thought
the number of added test cases was rather excessive anyway, so I wouldn't
have a problem with just leaving out whichever ones don't pass with both
build options.
Comments?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I wrote:
My feeling is we ought to preserve the old behavior here, which would
involve making JULIAN_MAXYEAR_FOR_TIMESTAMPS format-dependent and
adjusting the float values for the two derived constants; not much of a
problem code-wise. I think though that it would break quite a number of
the proposed new regression tests for the float case. TBH, I thought
the number of added test cases was rather excessive anyway, so I wouldn't
have a problem with just leaving out whichever ones don't pass with both
build options.
Actually, it seems a lot of the provided test cases fail for float
timestamps anyway; there's an assumption that
294276-12-31 23:59:59.999999
294277-01-01 00:00:00.000000
are distinct timestamps, which they are not in float mode.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I had almost gotten to the point of being willing to commit this patch
when I noticed that it fails to fix the originally-complained-of-problem:
regression=# set time zone 'GMT+1';
SET
regression=# select '4714-11-24 00:00:00+00 BC'::timestamptz;
timestamptz
---------------------------
4714-11-23 23:00:00-01 BC
(1 row)
regression=# select '4714-11-23 23:00:00-01 BC'::timestamptz;
ERROR: timestamp out of range: "4714-11-23 23:00:00-01 BC"
LINE 1: select '4714-11-23 23:00:00-01 BC'::timestamptz;
^
The problem here is that the timestamp satisfies IS_VALID_TIMESTAMP just
fine, but its printed form contains a date that the Julian-day routines
can't handle.
AFAICS the only way that we can avoid a dump/reload hazard is to tighten
up the allowed range of timestamps by at least one day, so that any
timestamp that passes IS_VALID_TIMESTAMP() is guaranteed to print, in
any timezone, with a contained date that the Julian-day routines can
handle. I'd be inclined to set the lower limit of timestamps as
'4713-01-01 00:00 GMT BC' just to keep things simple. (The upper limit
can stay where it is.)
While dates don't have this timezone rotation problem, the documentation
says that they have the same lower-bound limit as timestamps, and there
are places in the code that assume that too. Is it okay to move their
lower bound as well?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2016-03-16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
My feeling is we ought to preserve the old behavior here, which would
involve making JULIAN_MAXYEAR_FOR_TIMESTAMPS format-dependent and
adjusting the float values for the two derived constants; not much of a
problem code-wise. I think though that it would break quite a number of
the proposed new regression tests for the float case.
I think I can be solved by adding new tests for new upper bound for
float values and creating a new version of expected file like
date_1.out, horology_1.out, timestamp_1.out and timestamptz.out (the
original files are for integer values; with prefix "_1" are for float
ones).
TBH, I thought
the number of added test cases was rather excessive anyway, so I wouldn't
have a problem with just leaving out whichever ones don't pass with both
build options.
The tests checks edge cases. In case of saving bigger interval of
allowed values for float values you'll remove checks when they should
fail. What's the left cases for?
On 2016-03-16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Actually, it seems a lot of the provided test cases fail for float
timestamps anyway; there's an assumption that
294276-12-31 23:59:59.999999
294277-01-01 00:00:00.000000
are distinct timestamps, which they are not in float mode.
I'm ready to change fractional part '.999999' to '.5' (to avoid issues
of different implementation of floating point on different
architectures) to emphasize "almost reached the threshold".
On 2016-03-16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
AFAICS the only way that we can avoid a dump/reload hazard is to tighten
up the allowed range of timestamps by at least one day, so that any
timestamp that passes IS_VALID_TIMESTAMP() is guaranteed to print, in
any timezone, with a contained date that the Julian-day routines can
handle. I'd be inclined to set the lower limit of timestamps as
'4713-01-01 00:00 GMT BC' just to keep things simple. (The upper limit
can stay where it is.)While dates don't have this timezone rotation problem, the documentation
says that they have the same lower-bound limit as timestamps, and there
are places in the code that assume that too. Is it okay to move their
lower bound as well?
I think it is important (see initial letter) since it is out of
supported interval (according to the documentation) and don't work as
expected in some cases (see "to_char(date_trunc('week', '4714-12-28
BC'::date),'day')"). It seems it leads to change
IS_VALID_JULIAN[_FOR_STAMPS] as well to something like:
#define IS_VALID_JULIAN(y,m,d) \
((JULIAN_MINYEAR < (y) && (y) < JULIAN_MAXYEAR)
||(JULIAN_MINYEAR == (y) && (m) == 12 && (d) == 31)
||(JULIAN_MAXYEAR == (y) && (m) == 01 && (d) == 01))
It can be correct if checks for IS_VALID_DATE is added in date.c to
places marked as "IS_VALID_JULIAN is enough for checking..."
All other places are (I'm sure) covered by IS_VALID_DATE/IS_VALID_TIMESTAMP.
What about the question:
On 2016-02-24, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
Also I'm asking for a help because the query (in default TZ='GMT+1'):
postgres=# SELECT '4714-11-24 00:00:00.000000+00 BC'::timestamptz;in psql gives a result "4714-11-23 23:00:00-01 BC",
but in a testing system gives "Sun Nov 23 23:00:00 4714 GMT BC"
without TZ offset.
Why there is "GMT" instead of "GMT+01"? Is it bug? If so should it be
fixed in this patch or can be fixed later?
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2016-03-15, Mark Dilger <hornschnorter@gmail.com> wrote:
On Mar 14, 2016, at 5:12 PM, Vitaly Burovoy <vitaly.burovoy@gmail.com>
wrote:On 3/14/16, Mark Dilger <hornschnorter@gmail.com> wrote:
The first thing I notice about this patch is that
src/include/datatype/timestamp.h
has some #defines that are brittle. The #defines have comments
explaining
their logic, but I'd rather embed that in the #define directly:This:
+#ifdef HAVE_INT64_TIMESTAMP +#define MIN_TIMESTAMP INT64CONST(-211813488000000000) +/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */ +#define MAX_TIMESTAMP INT64CONST(9223371331200000000) +/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */ +#else +#define MIN_TIMESTAMP -211813488000.0 +/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 */ +#define MAX_TIMESTAMP 9223371331200.0 +/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 */ +#endifCould be written as:
#ifdef HAVE_INT64_TIMESTAMP
#define MIN_TIMESTAMP
((INT64CONST(0) - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY)
#define MAX_TIMESTAMP
((JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * USECS_PER_DAY)
#else
#define MIN_TIMESTAMP
((INT64CONST(0) - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY)
#define MAX_TIMESTAMP
((JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * SECS_PER_DAY)
#endifI assume modern compilers would convert these to the same constants at
compile-time,Firstly, Postgres is compiling not only by modern compilers.
Do you have an example of a compiler that will not do this constant folding
at compile time?
No, I'm not good at knowing features of all versions and all kings of
compilers, but I'm sure constants are better than expressions for big
values. =)
rather than impose a runtime penalty.
Secondary, It is hard to write it correctly obeying Postgres' coding
standard (e.g. 80-columns border) and making it clear: it is not so
visual difference between USECS_PER_DAY and SECS_PER_DAY in the
expressions above (but it is vivid in comments in the file).Hmm. I think using USECS_PER_DAY is perfectly clear, but that is a
personal
opinion. I don't see any way to argue if you don't see it that way.
I'm talking about perception of the constants when they a very similar
but they are not justified by a single column (where difference
_in_expressions_ are clear). There was a difference by a single char
("U") only which is not so obvious without deep looking on it (be
honest I'd missed it until started to write an answer).
or "Why is INT64CONST set for
MIN_TIMESTAMP, but not for MAX_TIMESTAMP?" (JULIAN_MAX4STAMPS is _not_
int64).I was only casting the zero to int64. That doesn't seem necessary, so it
can
be removed. Both MIN_TIMESTAMP and MAX_TIMESTAMP were defined
in terms of USECS_PER_DAY, which itself is defined in terms of INT64CONST,
so I believe they both work out to be an int64 constant.
I hope so. But in such cases I'd prefer to _begin_ calculations from
int64, not to _finish_ by it.
It is impossible to pass constants (like JULIAN_MAX4STAMPS) to
INT64CONST macros. Inserting "(int64)" makes rows larger by 7 chars...
;-)
The #defines would be less brittle in
the event, for example, that the postgres epoch were ever changed.I don't think it is real, and even in such case all constants are
collected together in the file and will be found and changed at once.I agree that they would be found at once. I disagree that the example
is not real, as I have changed the postgres epoch myself in some builds,
to be able to use int32 timestamps on small devices.
Wow! I'm sorry, I didn't know about it.
But in such case (tighten to int32) there are more than two places
which should be changed. Two more (four with disabled
HAVE_INT64_TIMESTAMP) constants are not big deal with it.
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
The #defines would be less brittle in
the event, for example, that the postgres epoch were ever changed.I don't think it is real, and even in such case all constants are
collected together in the file and will be found and changed at once.I agree that they would be found at once. I disagree that the example
is not real, as I have changed the postgres epoch myself in some builds,
to be able to use int32 timestamps on small devices.Wow! I'm sorry, I didn't know about it.
But in such case (tighten to int32) there are more than two places
which should be changed. Two more (four with disabled
HAVE_INT64_TIMESTAMP) constants are not big deal with it.
Please, do not worry about that. I do not mean that your code needs
to be compatible with my fork.
Regards,
Mark Dilger
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I wrote:
I had almost gotten to the point of being willing to commit this patch
when I noticed that it fails to fix the originally-complained-of-problem:
...
AFAICS the only way that we can avoid a dump/reload hazard is to tighten
up the allowed range of timestamps by at least one day, so that any
timestamp that passes IS_VALID_TIMESTAMP() is guaranteed to print, in
any timezone, with a contained date that the Julian-day routines can
handle.
I realized that there's a way to do this without reducing the historical
limits of the datatypes. It's already the case that date2j() can handle
dates a little beyond the stated upper limit of the date type, and if
you take a close look at it, you'll realize that it doesn't have a
problem with dates a little before 4714-11-24 BC, either. (It looks
like it would work back to about 4800 BC, though I've not attempted
to verify that independently.) All we really need is for it to return
-1 for 4714-11-23 BC, and it certainly does that. Then, if we allow
such a date to pass IS_VALID_JULIAN, we can accept something like
'4714-11-23 23:00:00-01 BC'::timestamptz and apply the exact range
check only after doing the timestamp adjustment. The same trick
works at the upper end of the range. The key is simply that we have
to have an explicit range check at the end rather than relying entirely
on IS_VALID_JULIAN --- but this patch was about 80% of the way there
already.
So I fixed that up and committed it, with a very short set of new
regression test cases. I seriously doubt that the other ones add
enough value to be worth trying to make them work in both float- and
int-timestamp cases; though if you want to submit a new patch to
add more test cases we could consider it. My feeling about it is that
that kind of testing does nothing for errors of omission (ie functions
that fail to range-check their results), which is the most likely sort
of bug here, and so I doubt it's worth adding them to regression tests
that many people will run many times a day for a long time to come.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Anastasia Lubennikova <a.lubennikova@postgrespro.ru> writes:
There is an issue, mentioned in the thread above:
postgres=# select
postgres-# to_char(date_trunc('week', '4713-01-01 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-29 BC'::date),'day')
postgres-# ,to_char(date_trunc('week', '4714-12-28 BC'::date),'day');
to_char | to_char | to_char
-----------+-----------+-----------
monday | monday | thursday
(1 row)
since 4714-12-28 BC and to the past detection when a week is starting
is broken (because it is boundary of isoyears -4713 and -4712).
BTW, I think the actual problem is that j2day() figured that coercing
its argument to unsigned int would be sufficient to produce a sane
answer for negative inputs. It isn't. Nobody sees this with inputs
after 4714BC, but when probing in 4714 the code considers the
reference point 4714-01-04, which has a negative Julian date and so
we end up passing a negative date to j2day().
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3/16/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
So I fixed that up and committed it, with a very short set of new
regression test cases. I seriously doubt that the other ones add
enough value to be worth trying to make them work in both float- and
int-timestamp cases; though if you want to submit a new patch to
add more test cases we could consider it. My feeling about it is that
that kind of testing does nothing for errors of omission (ie functions
that fail to range-check their results), which is the most likely sort
of bug here, and so I doubt it's worth adding them to regression tests
that many people will run many times a day for a long time to come.regards, tom lane
Thank you very much! If you decide such kind of tests is not
necessary, it is enough for me.
Is there any reason to leave JULIAN_MINDAY and JULIAN_MAXDAY which are
not used now?
Also why JULIAN_MAXMONTH is set to "6" whereas
{DATE|TIMESTAMP}_END_JULIAN use "1" as month?
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Vitaly Burovoy <vitaly.burovoy@gmail.com> writes:
Is there any reason to leave JULIAN_MINDAY and JULIAN_MAXDAY which are
not used now?
Those are just there to document what the limits really are. Possibly
some code would need them in future.
Also why JULIAN_MAXMONTH is set to "6" whereas
{DATE|TIMESTAMP}_END_JULIAN use "1" as month?
Because we're intentionally allowing a wider range for IS_VALID_JULIAN
than for IS_VALID_DATE/TIMESTAMP.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers