Improve behavior of date_bin with origin in the future

Currently, when the origin is after the input, the result is the
timestamp at the end of the bin, rather than the beginning as
expected.  This puts the result consistently at the beginning of the
bin.

Author: John Naylor <john.naylor@enterprisedb.com>
Discussion: https://www.postgresql.org/message-id/CAFBsxsGjLDxQofRfH+d4KSAXxPf3MMevUG7s6EDfdBOvHLDLjw@mail.gmail.com

3 files changed, 102 insertions, 0 deletions

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index b2bdbcab576..280ee7f92ba 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -3846,6 +3846,13 @@ timestamp_bin(PG_FUNCTION_ARGS)
 	tm_diff = timestamp - origin;
 	tm_delta = tm_diff - tm_diff % stride_usecs;
 
+	/*
+	 * Make sure the returned timestamp is at the start of the bin,
+	 * even if the origin is in the future.
+	 */
+	if (origin > timestamp && stride_usecs > 1)
+		tm_delta -= stride_usecs;
+
 	result = origin + tm_delta;
 
 	PG_RETURN_TIMESTAMP(result);
@@ -4017,6 +4024,13 @@ timestamptz_bin(PG_FUNCTION_ARGS)
 	tm_diff = timestamp - origin;
 	tm_delta = tm_diff - tm_diff % stride_usecs;
 
+	/*
+	 * Make sure the returned timestamp is at the start of the bin,
+	 * even if the origin is in the future.
+	 */
+	if (origin > timestamp && stride_usecs > 1)
+		tm_delta -= stride_usecs;
+
 	result = origin + tm_delta;
 
 	PG_RETURN_TIMESTAMPTZ(result);
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 690656dfb2d..ac1a4a9b6ac 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -609,6 +609,60 @@ FROM (
  microsecond | 1 us     | t
 (7 rows)
 
+-- case 3: AD dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2020-03-02') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(7 rows)
+
+-- case 4: BC dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '0055-06-17 BC') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(7 rows)
+
 -- bin timestamps into arbitrary intervals
 SELECT
   interval,
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index c43a1f22688..d51e83127ae 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -203,6 +203,40 @@ FROM (
 ) intervals (str, interval),
 (VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
 
+-- case 3: AD dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2020-03-02') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);
+
+-- case 4: BC dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '0055-06-17 BC') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
+
 -- bin timestamps into arbitrary intervals
 SELECT
   interval,