Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit496e58b

Browse files
committed
Improve behavior of date_bin with origin in the future
Currently, when the origin is after the input, the result is thetimestamp at the end of the bin, rather than the beginning asexpected. This puts the result consistently at the beginning of thebin.Author: John Naylor <john.naylor@enterprisedb.com>Discussion:https://www.postgresql.org/message-id/CAFBsxsGjLDxQofRfH+d4KSAXxPf3MMevUG7s6EDfdBOvHLDLjw@mail.gmail.com
1 parent99964c4 commit496e58b

File tree

3 files changed

+102
-0
lines changed

3 files changed

+102
-0
lines changed

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

Lines 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‎

Lines 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‎

Lines 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)
[8]ページ先頭
©2009-2025 Movatter.jp