Improve behavior of date_bin with origin in the future

petere · petere · commit 496e58bb0e5e · 2021-04-10T19:33:46.000+02:00
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
diff --git 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
@@ -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
@@ -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,
