diff options
| author | Peter Eisentraut <peter@eisentraut.org> | 2021-03-24 16:16:14 +0100 |
|---|---|---|
| committer | Peter Eisentraut <peter@eisentraut.org> | 2021-03-24 16:18:24 +0100 |
| commit | 49ab61f0bdc93984a8d36b602f6f2a15f09ebcc7 (patch) | |
| tree | 9002c71f5585965e1b3a685fe1edbd07d3e84d8e /doc/src | |
| parent | 1509c6fc29c07d13c9a590fbd6f37c7576f58ba6 (diff) | |
| download | postgresql-49ab61f0bdc93984a8d36b602f6f2a15f09ebcc7.tar.gz postgresql-49ab61f0bdc93984a8d36b602f6f2a15f09ebcc7.zip | |
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
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/func.sgml | 50 |
1 files changed, 50 insertions, 0 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1d3429fbd9c..769e5fe44c9 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -8732,6 +8732,20 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); <row> <entry role="func_table_entry"><para role="func_signature"> + <function>date_bin</function> ( <type>interval</type>, <type>timestamp</type>, <type>timestamp</type> ) + <returnvalue>timestamp</returnvalue> + </para> + <para> + Bin input into specified interval aligned with specified origin; see <xref linkend="functions-datetime-bin"/> + </para> + <para> + <literal>date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')</literal> + <returnvalue>2001-02-16 20:35:00</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> <indexterm> <primary>date_part</primary> </indexterm> @@ -9868,6 +9882,42 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33'); </para> </sect2> + <sect2 id="functions-datetime-bin"> + <title><function>date_bin</function></title> + + <indexterm> + <primary>date_bin</primary> + </indexterm> + + <para> + The function <function>date_bin</function> <quote>bins</quote> the input + timestamp into the specified interval (the <firstterm>stride</firstterm>) + aligned with a specified origin. + </para> + + <para> + Examples: +<screen> +SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01'); +<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:30:00</computeroutput> + +SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30'); +<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:32:30</computeroutput> +</screen> + </para> + + <para> + In cases full units (1 minute, 1 hour, etc.), it gives the same result as + the analogous <function>date_trunc</function> call, but the difference is + that <function>date_bin</function> can truncate to an arbitrary interval. + </para> + + <para> + The <parameter>stride</parameter> interval cannot contain units of month + or larger. + </para> + </sect2> + <sect2 id="functions-datetime-zoneconvert"> <title><literal>AT TIME ZONE</literal></title> |