diff options
| -rw-r--r-- | src/backend/utils/adt/timestamp.c | 14 | ||||
| -rw-r--r-- | src/test/regress/expected/timestamp.out | 54 | ||||
| -rw-r--r-- | src/test/regress/sql/timestamp.sql | 34 |
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, |