Skip to content

Commit 49ab61f

Browse files
peterepetere
committed
Add date_bin function
Similar to date_trunc, but allows binning by an arbitrary interval rather than just full units. Author: John Naylor <john.naylor@enterprisedb.com> Reviewed-by: David Fetter <david@fetter.org> Reviewed-by: Isaac Morland <isaac.morland@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Artur Zakirov <zaartur@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CACPNZCt4buQFRgy6DyjuZS-2aPDpccRkrJBmgUfwYc1KiaXYxg@mail.gmail.com
1 parent 1509c6f commit 49ab61f

File tree

8 files changed

+402
-1
lines changed

8 files changed

+402
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8730,6 +8730,20 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
87308730
</para></entry>
87318731
</row>
87328732

8733+
<row>
8734+
<entry role="func_table_entry"><para role="func_signature">
8735+
<function>date_bin</function> ( <type>interval</type>, <type>timestamp</type>, <type>timestamp</type> )
8736+
<returnvalue>timestamp</returnvalue>
8737+
</para>
8738+
<para>
8739+
Bin input into specified interval aligned with specified origin; see <xref linkend="functions-datetime-bin"/>
8740+
</para>
8741+
<para>
8742+
<literal>date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')</literal>
8743+
<returnvalue>2001-02-16 20:35:00</returnvalue>
8744+
</para></entry>
8745+
</row>
8746+
87338747
<row>
87348748
<entry role="func_table_entry"><para role="func_signature">
87358749
<indexterm>
@@ -9868,6 +9882,42 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
98689882
</para>
98699883
</sect2>
98709884

9885+
<sect2 id="functions-datetime-bin">
9886+
<title><function>date_bin</function></title>
9887+
9888+
<indexterm>
9889+
<primary>date_bin</primary>
9890+
</indexterm>
9891+
9892+
<para>
9893+
The function <function>date_bin</function> <quote>bins</quote> the input
9894+
timestamp into the specified interval (the <firstterm>stride</firstterm>)
9895+
aligned with a specified origin.
9896+
</para>
9897+
9898+
<para>
9899+
Examples:
9900+
<screen>
9901+
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
9902+
<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:30:00</computeroutput>
9903+
9904+
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
9905+
<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:32:30</computeroutput>
9906+
</screen>
9907+
</para>
9908+
9909+
<para>
9910+
In cases full units (1 minute, 1 hour, etc.), it gives the same result as
9911+
the analogous <function>date_trunc</function> call, but the difference is
9912+
that <function>date_bin</function> can truncate to an arbitrary interval.
9913+
</para>
9914+
9915+
<para>
9916+
The <parameter>stride</parameter> interval cannot contain units of month
9917+
or larger.
9918+
</para>
9919+
</sect2>
9920+
98719921
<sect2 id="functions-datetime-zoneconvert">
98729922
<title><literal>AT TIME ZONE</literal></title>
98739923

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

Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3812,6 +3812,43 @@ timestamptz_age(PG_FUNCTION_ARGS)
38123812
*---------------------------------------------------------*/
38133813

38143814

3815+
/* timestamp_bin()
3816+
* Bin timestamp into specified interval.
3817+
*/
3818+
Datum
3819+
timestamp_bin(PG_FUNCTION_ARGS)
3820+
{
3821+
Interval *stride = PG_GETARG_INTERVAL_P(0);
3822+
Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
3823+
Timestamp origin = PG_GETARG_TIMESTAMP(2);
3824+
Timestamp result,
3825+
tm_diff,
3826+
stride_usecs,
3827+
tm_delta;
3828+
3829+
if (TIMESTAMP_NOT_FINITE(timestamp))
3830+
PG_RETURN_TIMESTAMP(timestamp);
3831+
3832+
if (TIMESTAMP_NOT_FINITE(origin))
3833+
ereport(ERROR,
3834+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3835+
errmsg("origin out of range")));
3836+
3837+
if (stride->month != 0)
3838+
ereport(ERROR,
3839+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3840+
errmsg("timestamps cannot be binned into intervals containing months or years")));
3841+
3842+
stride_usecs = stride->day * USECS_PER_DAY + stride->time;
3843+
3844+
tm_diff = timestamp - origin;
3845+
tm_delta = tm_diff - tm_diff % stride_usecs;;
3846+
3847+
result = origin + tm_delta;
3848+
3849+
PG_RETURN_TIMESTAMP(result);
3850+
}
3851+
38153852
/* timestamp_trunc()
38163853
* Truncate timestamp to specified units.
38173854
*/
@@ -3946,6 +3983,43 @@ timestamp_trunc(PG_FUNCTION_ARGS)
39463983
PG_RETURN_TIMESTAMP(result);
39473984
}
39483985

3986+
/* timestamptz_bin()
3987+
* Bin timestamptz into specified interval using specified origin.
3988+
*/
3989+
Datum
3990+
timestamptz_bin(PG_FUNCTION_ARGS)
3991+
{
3992+
Interval *stride = PG_GETARG_INTERVAL_P(0);
3993+
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
3994+
TimestampTz origin = PG_GETARG_TIMESTAMPTZ(2);
3995+
TimestampTz result,
3996+
stride_usecs,
3997+
tm_diff,
3998+
tm_delta;
3999+
4000+
if (TIMESTAMP_NOT_FINITE(timestamp))
4001+
PG_RETURN_TIMESTAMPTZ(timestamp);
4002+
4003+
if (TIMESTAMP_NOT_FINITE(origin))
4004+
ereport(ERROR,
4005+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4006+
errmsg("origin out of range")));
4007+
4008+
if (stride->month != 0)
4009+
ereport(ERROR,
4010+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4011+
errmsg("timestamps cannot be binned into intervals containing months or years")));
4012+
4013+
stride_usecs = stride->day * USECS_PER_DAY + stride->time;
4014+
4015+
tm_diff = timestamp - origin;
4016+
tm_delta = tm_diff - tm_diff % stride_usecs;;
4017+
4018+
result = origin + tm_delta;
4019+
4020+
PG_RETURN_TIMESTAMPTZ(result);
4021+
}
4022+
39494023
/*
39504024
* Common code for timestamptz_trunc() and timestamptz_trunc_zone().
39514025
*

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 202103241
56+
#define CATALOG_VERSION_NO 202103242
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5813,6 +5813,17 @@
58135813
{ oid => '2020', descr => 'truncate timestamp to specified units',
58145814
proname => 'date_trunc', prorettype => 'timestamp',
58155815
proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
5816+
5817+
{ oid => '8990',
5818+
descr => 'bin timestamp into specified interval',
5819+
proname => 'date_bin', prorettype => 'timestamp',
5820+
proargtypes => 'interval timestamp timestamp',
5821+
prosrc => 'timestamp_bin' },
5822+
{ oid => '8993',
5823+
descr => 'bin timestamp with time zone into specified interval',
5824+
proname => 'date_bin', prorettype => 'timestamptz',
5825+
proargtypes => 'interval timestamptz timestamptz', prosrc => 'timestamptz_bin' },
5826+
58165827
{ oid => '2021', descr => 'extract field from timestamp',
58175828
proname => 'date_part', prorettype => 'float8',
58185829
proargtypes => 'text timestamp', prosrc => 'timestamp_part' },

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

Lines changed: 96 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -545,6 +545,102 @@ SELECT date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc
545545
Mon Feb 23 00:00:00 2004
546546
(1 row)
547547

548+
-- verify date_bin behaves the same as date_trunc for relevant intervals
549+
-- case 1: AD dates, origin < input
550+
SELECT
551+
str,
552+
interval,
553+
date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2001-01-01') AS equal
554+
FROM (
555+
VALUES
556+
('week', '7 d'),
557+
('day', '1 d'),
558+
('hour', '1 h'),
559+
('minute', '1 m'),
560+
('second', '1 s'),
561+
('millisecond', '1 ms'),
562+
('microsecond', '1 us')
563+
) intervals (str, interval),
564+
(VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);
565+
str | interval | equal
566+
-------------+----------+-------
567+
week | 7 d | t
568+
day | 1 d | t
569+
hour | 1 h | t
570+
minute | 1 m | t
571+
second | 1 s | t
572+
millisecond | 1 ms | t
573+
microsecond | 1 us | t
574+
(7 rows)
575+
576+
-- case 2: BC dates, origin < input
577+
SELECT
578+
str,
579+
interval,
580+
date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2000-01-01 BC') AS equal
581+
FROM (
582+
VALUES
583+
('week', '7 d'),
584+
('day', '1 d'),
585+
('hour', '1 h'),
586+
('minute', '1 m'),
587+
('second', '1 s'),
588+
('millisecond', '1 ms'),
589+
('microsecond', '1 us')
590+
) intervals (str, interval),
591+
(VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
592+
str | interval | equal
593+
-------------+----------+-------
594+
week | 7 d | t
595+
day | 1 d | t
596+
hour | 1 h | t
597+
minute | 1 m | t
598+
second | 1 s | t
599+
millisecond | 1 ms | t
600+
microsecond | 1 us | t
601+
(7 rows)
602+
603+
-- bin timestamps into arbitrary intervals
604+
SELECT
605+
interval,
606+
ts,
607+
origin,
608+
date_bin(interval::interval, ts, origin)
609+
FROM (
610+
VALUES
611+
('15 days'),
612+
('2 hours'),
613+
('1 hour 30 minutes'),
614+
('15 minutes'),
615+
('10 seconds'),
616+
('100 milliseconds'),
617+
('250 microseconds')
618+
) intervals (interval),
619+
(VALUES (timestamp '2020-02-11 15:44:17.71393')) ts (ts),
620+
(VALUES (timestamp '2001-01-01')) origin (origin);
621+
interval | ts | origin | date_bin
622+
-------------------+--------------------------------+--------------------------+--------------------------------
623+
15 days | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Thu Feb 06 00:00:00 2020
624+
2 hours | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 14:00:00 2020
625+
1 hour 30 minutes | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 15:00:00 2020
626+
15 minutes | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 15:30:00 2020
627+
10 seconds | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 15:44:10 2020
628+
100 milliseconds | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 15:44:17.7 2020
629+
250 microseconds | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 15:44:17.71375 2020
630+
(7 rows)
631+
632+
-- shift bins using the origin parameter:
633+
SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2020-02-01 00:02:30');
634+
date_bin
635+
--------------------------
636+
Sat Feb 01 00:57:30 2020
637+
(1 row)
638+
639+
-- disallow intervals with months or years
640+
SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
641+
ERROR: timestamps cannot be binned into intervals containing months or years
642+
SELECT date_bin('5 years'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
643+
ERROR: timestamps cannot be binned into intervals containing months or years
548644
-- Test casting within a BETWEEN qualifier
549645
SELECT d1 - timestamp without time zone '1997-01-02' AS diff
550646
FROM TIMESTAMP_TBL

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

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -663,6 +663,72 @@ SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'VET
663663
Thu Feb 15 20:00:00 2001 PST
664664
(1 row)
665665

666+
-- verify date_bin behaves the same as date_trunc for relevant intervals
667+
SELECT
668+
str,
669+
interval,
670+
date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts, timestamp with time zone '2001-01-01+11') AS equal
671+
FROM (
672+
VALUES
673+
('day', '1 d'),
674+
('hour', '1 h'),
675+
('minute', '1 m'),
676+
('second', '1 s'),
677+
('millisecond', '1 ms'),
678+
('microsecond', '1 us')
679+
) intervals (str, interval),
680+
(VALUES (timestamptz '2020-02-29 15:44:17.71393+00')) ts (ts);
681+
str | interval | equal
682+
-------------+----------+-------
683+
day | 1 d | t
684+
hour | 1 h | t
685+
minute | 1 m | t
686+
second | 1 s | t
687+
millisecond | 1 ms | t
688+
microsecond | 1 us | t
689+
(6 rows)
690+
691+
-- bin timestamps into arbitrary intervals
692+
SELECT
693+
interval,
694+
ts,
695+
origin,
696+
date_bin(interval::interval, ts, origin)
697+
FROM (
698+
VALUES
699+
('15 days'),
700+
('2 hours'),
701+
('1 hour 30 minutes'),
702+
('15 minutes'),
703+
('10 seconds'),
704+
('100 milliseconds'),
705+
('250 microseconds')
706+
) intervals (interval),
707+
(VALUES (timestamptz '2020-02-11 15:44:17.71393')) ts (ts),
708+
(VALUES (timestamptz '2001-01-01')) origin (origin);
709+
interval | ts | origin | date_bin
710+
-------------------+------------------------------------+------------------------------+------------------------------------
711+
15 days | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Thu Feb 06 00:00:00 2020 PST
712+
2 hours | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 14:00:00 2020 PST
713+
1 hour 30 minutes | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:00:00 2020 PST
714+
15 minutes | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:30:00 2020 PST
715+
10 seconds | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:44:10 2020 PST
716+
100 milliseconds | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:44:17.7 2020 PST
717+
250 microseconds | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:44:17.71375 2020 PST
718+
(7 rows)
719+
720+
-- shift bins using the origin parameter:
721+
SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2020-02-01 00:02:30+00');
722+
date_bin
723+
------------------------------
724+
Fri Jan 31 16:57:30 2020 PST
725+
(1 row)
726+
727+
-- disallow intervals with months or years
728+
SELECT date_bin('5 months'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
729+
ERROR: timestamps cannot be binned into intervals containing months or years
730+
SELECT date_bin('5 years'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
731+
ERROR: timestamps cannot be binned into intervals containing months or years
666732
-- Test casting within a BETWEEN qualifier
667733
SELECT d1 - timestamp with time zone '1997-01-02' AS diff
668734
FROM TIMESTAMPTZ_TBL

0 commit comments

Comments
 (0)