Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 496e58b

Browse filesBrowse files
peterepetere
committed
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
1 parent 99964c4 commit 496e58b
Copy full SHA for 496e58b

File tree

Expand file treeCollapse file tree

3 files changed

+102
-0
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+102
-0
lines changed

‎src/backend/utils/adt/timestamp.c

Copy file name to clipboardExpand all lines: src/backend/utils/adt/timestamp.c
+14Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3846,6 +3846,13 @@ timestamp_bin(PG_FUNCTION_ARGS)
38463846
tm_diff = timestamp - origin;
38473847
tm_delta = tm_diff - tm_diff % stride_usecs;
38483848

3849+
/*
3850+
* Make sure the returned timestamp is at the start of the bin,
3851+
* even if the origin is in the future.
3852+
*/
3853+
if (origin > timestamp && stride_usecs > 1)
3854+
tm_delta -= stride_usecs;
3855+
38493856
result = origin + tm_delta;
38503857

38513858
PG_RETURN_TIMESTAMP(result);
@@ -4017,6 +4024,13 @@ timestamptz_bin(PG_FUNCTION_ARGS)
40174024
tm_diff = timestamp - origin;
40184025
tm_delta = tm_diff - tm_diff % stride_usecs;
40194026

4027+
/*
4028+
* Make sure the returned timestamp is at the start of the bin,
4029+
* even if the origin is in the future.
4030+
*/
4031+
if (origin > timestamp && stride_usecs > 1)
4032+
tm_delta -= stride_usecs;
4033+
40204034
result = origin + tm_delta;
40214035

40224036
PG_RETURN_TIMESTAMPTZ(result);

‎src/test/regress/expected/timestamp.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/timestamp.out
+54Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -609,6 +609,60 @@ FROM (
609609
microsecond | 1 us | t
610610
(7 rows)
611611

612+
-- case 3: AD dates, origin > input
613+
SELECT
614+
str,
615+
interval,
616+
date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2020-03-02') AS equal
617+
FROM (
618+
VALUES
619+
('week', '7 d'),
620+
('day', '1 d'),
621+
('hour', '1 h'),
622+
('minute', '1 m'),
623+
('second', '1 s'),
624+
('millisecond', '1 ms'),
625+
('microsecond', '1 us')
626+
) intervals (str, interval),
627+
(VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);
628+
str | interval | equal
629+
-------------+----------+-------
630+
week | 7 d | t
631+
day | 1 d | t
632+
hour | 1 h | t
633+
minute | 1 m | t
634+
second | 1 s | t
635+
millisecond | 1 ms | t
636+
microsecond | 1 us | t
637+
(7 rows)
638+
639+
-- case 4: BC dates, origin > input
640+
SELECT
641+
str,
642+
interval,
643+
date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '0055-06-17 BC') AS equal
644+
FROM (
645+
VALUES
646+
('week', '7 d'),
647+
('day', '1 d'),
648+
('hour', '1 h'),
649+
('minute', '1 m'),
650+
('second', '1 s'),
651+
('millisecond', '1 ms'),
652+
('microsecond', '1 us')
653+
) intervals (str, interval),
654+
(VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
655+
str | interval | equal
656+
-------------+----------+-------
657+
week | 7 d | t
658+
day | 1 d | t
659+
hour | 1 h | t
660+
minute | 1 m | t
661+
second | 1 s | t
662+
millisecond | 1 ms | t
663+
microsecond | 1 us | t
664+
(7 rows)
665+
612666
-- bin timestamps into arbitrary intervals
613667
SELECT
614668
interval,

‎src/test/regress/sql/timestamp.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/timestamp.sql
+34Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -203,6 +203,40 @@ FROM (
203203
) intervals (str, interval),
204204
(VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
205205

206+
-- case 3: AD dates, origin > input
207+
SELECT
208+
str,
209+
interval,
210+
date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2020-03-02') AS equal
211+
FROM (
212+
VALUES
213+
('week', '7 d'),
214+
('day', '1 d'),
215+
('hour', '1 h'),
216+
('minute', '1 m'),
217+
('second', '1 s'),
218+
('millisecond', '1 ms'),
219+
('microsecond', '1 us')
220+
) intervals (str, interval),
221+
(VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);
222+
223+
-- case 4: BC dates, origin > input
224+
SELECT
225+
str,
226+
interval,
227+
date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '0055-06-17 BC') AS equal
228+
FROM (
229+
VALUES
230+
('week', '7 d'),
231+
('day', '1 d'),
232+
('hour', '1 h'),
233+
('minute', '1 m'),
234+
('second', '1 s'),
235+
('millisecond', '1 ms'),
236+
('microsecond', '1 us')
237+
) intervals (str, interval),
238+
(VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
239+
206240
-- bin timestamps into arbitrary intervals
207241
SELECT
208242
interval,

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.