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

Commit49ab61f

Browse files
committed
Add date_bin function
Similar to date_trunc, but allows binning by an arbitrary intervalrather 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 parent1509c6f commit49ab61f

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+
Timestamptimestamp=PG_GETARG_TIMESTAMP(1);
3823+
Timestamporigin=PG_GETARG_TIMESTAMP(2);
3824+
Timestampresult,
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+
TimestampTztimestamp=PG_GETARG_TIMESTAMPTZ(1);
3994+
TimestampTzorigin=PG_GETARG_TIMESTAMPTZ(2);
3995+
TimestampTzresult,
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-
#defineCATALOG_VERSION_NO202103241
56+
#defineCATALOG_VERSION_NO202103242
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)
[8]ページ先頭
©2009-2025 Movatter.jp