doc/src/sgml/func.sgml | 26 ++++++-
src/backend/utils/adt/timestamp.c | 126 ++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/timestamp.out | 35 +++++++++
src/test/regress/sql/timestamp.sql | 22 ++++++
5 files changed, 211 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ceda48e0fc..3863c222a2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6949,6 +6949,15 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
2 days 03:00:00
+
+ date_trunc_interval(interval, timestamp)
+ timestamp
+ Truncate to specified precision; see
+
+ date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40')
+ 2001-02-16 20:30:00
+
+
@@ -7818,7 +7827,7 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
- <function>date_trunc</function>
+ <function>date_trunc</function>, <function>date_trunc_interval</function>
date_trunc
@@ -7902,6 +7911,21 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00
+
+
+ The function date_trunc_interval is
+ similar to the date_trunc, except that it
+ truncates to an arbitrary interval.
+
+
+
+ Example:
+
+SELECT date_trunc_interval('5 minutes', TIMESTAMP '2001-02-16 20:38:40');
+Result: 2001-02-16 20:35:00
+
+
+
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 0b6c9d5ea8..9613f86355 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -30,6 +30,7 @@
#include "nodes/nodeFuncs.h"
#include "nodes/supportnodes.h"
#include "parser/scansup.h"
+#include "port/pg_bitutils.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datetime.h"
@@ -3804,6 +3805,131 @@ timestamptz_age(PG_FUNCTION_ARGS)
*---------------------------------------------------------*/
+/* timestamp_trunc_interval()
+ * Truncate timestamp to specified interval.
+ */
+Datum
+timestamp_trunc_interval(PG_FUNCTION_ARGS)
+{
+ Interval *interval = PG_GETARG_INTERVAL_P(0);
+ Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
+ Timestamp result;
+ fsec_t ifsec,
+ tfsec;
+ uint32 unit = 0;
+
+ struct pg_tm it;
+ struct pg_tm tt;
+
+ if (TIMESTAMP_NOT_FINITE(timestamp))
+ PG_RETURN_TIMESTAMP(timestamp);
+
+ if (interval2tm(*interval, &it, &ifsec) != 0)
+ elog(ERROR, "could not convert interval to tm");
+
+ if (timestamp2tm(timestamp, NULL, &tt, &tfsec, NULL, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ if (it.tm_year != 0)
+ {
+ tt.tm_year = it.tm_year * (tt.tm_year / it.tm_year);
+ unit |= DTK_M(DTK_YEAR);
+ }
+ if (it.tm_mon != 0)
+ {
+ if (it.tm_mon > tt.tm_mon)
+ tt.tm_mon = 1;
+ else
+ tt.tm_mon = it.tm_mon * (tt.tm_mon / it.tm_mon);
+
+ unit |= DTK_M(DTK_MONTH);
+ }
+ if (it.tm_mday != 0)
+ {
+ if (it.tm_mday > tt.tm_mday)
+ tt.tm_mday = 1;
+ else
+ tt.tm_mday = it.tm_mday * (tt.tm_mday / it.tm_mday);
+
+ unit |= DTK_M(DTK_DAY);
+ }
+ if (it.tm_hour != 0)
+ {
+ tt.tm_hour = it.tm_hour * (tt.tm_hour / it.tm_hour);
+ unit |= DTK_M(DTK_HOUR);
+ }
+ if (it.tm_min != 0)
+ {
+ tt.tm_min = it.tm_min * (tt.tm_min / it.tm_min);
+ unit |= DTK_M(DTK_MINUTE);
+ }
+ if (it.tm_sec != 0)
+ {
+ tt.tm_sec = it.tm_sec * (tt.tm_sec / it.tm_sec);
+ unit |= DTK_M(DTK_SECOND);
+ }
+ if (ifsec != 0)
+ {
+ tfsec = ifsec * (tfsec / ifsec);
+
+ if (ifsec >= 1000)
+ unit |= DTK_M(DTK_MILLISEC);
+ else
+ unit |= DTK_M(DTK_MICROSEC);
+ }
+
+ if (unit == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval not initialized")));
+ else
+ {
+ int popcount = pg_popcount32(unit);
+
+ if (popcount > 1)
+ ereport(ERROR,
+ // WIP is there a better errcode?
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("only one interval unit allowed for truncation")));
+ }
+
+ /*
+ * Justify all lower timestamp units and throw an error if any
+ * of the lower interval units are non-zero.
+ */
+ switch (unit)
+ {
+ case DTK_M(DTK_YEAR):
+ tt.tm_mon = 1;
+ case DTK_M(DTK_MONTH):
+ tt.tm_mday = 1;
+ case DTK_M(DTK_DAY):
+ tt.tm_hour = 0;
+ case DTK_M(DTK_HOUR):
+ tt.tm_min = 0;
+ case DTK_M(DTK_MINUTE):
+ tt.tm_sec = 0;
+ case DTK_M(DTK_SECOND):
+ tfsec = 0;
+ case DTK_M(DTK_MILLISEC):
+ case DTK_M(DTK_MICROSEC):
+ break;
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("interval unit not supported")));
+ }
+
+ if (tm2timestamp(&tt, tfsec, NULL, &result) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ PG_RETURN_TIMESTAMP(result);
+}
+
/* timestamp_trunc()
* Truncate timestamp to specified units.
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0345118cdb..ddb1b84b52 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5660,6 +5660,9 @@
{ oid => '2020', descr => 'truncate timestamp to specified units',
proname => 'date_trunc', prorettype => 'timestamp',
proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
+{ oid => '8989', descr => 'truncate timestamp to specified interval',
+ proname => 'date_trunc_interval', prorettype => 'timestamp',
+ proargtypes => 'interval timestamp', prosrc => 'timestamp_trunc_interval' },
{ oid => '2021', descr => 'extract field from timestamp',
proname => 'date_part', prorettype => 'float8',
proargtypes => 'text timestamp', prosrc => 'timestamp_part' },
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 5f97505a30..1acb2e78a8 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -545,6 +545,41 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17
| Mon Feb 23 00:00:00 2004
(1 row)
+-- truncate on intervals
+SELECT
+ interval,
+ date_trunc_interval(interval::interval, ts)
+FROM (
+ VALUES
+ ('50 years'),
+ ('1 month'),
+ ('6 months'),
+ ('7 days'),
+ ('15 days'),
+ ('2 hours'),
+ ('15 minutes'),
+ ('10 seconds'),
+ ('100 milliseconds'),
+ ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+ interval | date_trunc_interval
+------------------+--------------------------------
+ 50 years | Sat Jan 01 00:00:00 2000
+ 1 month | Sat Feb 01 00:00:00 2020
+ 6 months | Wed Jan 01 00:00:00 2020
+ 7 days | Fri Feb 07 00:00:00 2020
+ 15 days | Sat Feb 01 00:00:00 2020
+ 2 hours | Tue Feb 11 14:00:00 2020
+ 15 minutes | Tue Feb 11 15:30:00 2020
+ 10 seconds | Tue Feb 11 15:44:10 2020
+ 100 milliseconds | Tue Feb 11 15:44:17.7 2020
+ 250 microseconds | Tue Feb 11 15:44:17.71375 2020
+(10 rows)
+
+-- disallow multiple units (ms + us is an exception)
+SELECT date_trunc_interval('1 year 1 microsecond', TIMESTAMP '2001-02-16 20:38:40.123456');
+ERROR: only one interval unit allowed for truncation
-- Test casting within a BETWEEN qualifier
SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7b58c3cfa5..41485f6aa7 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -166,6 +166,28 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
+-- truncate on intervals
+SELECT
+ interval,
+ date_trunc_interval(interval::interval, ts)
+FROM (
+ VALUES
+ ('50 years'),
+ ('1 month'),
+ ('6 months'),
+ ('7 days'),
+ ('15 days'),
+ ('2 hours'),
+ ('15 minutes'),
+ ('10 seconds'),
+ ('100 milliseconds'),
+ ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+
+-- disallow multiple units (ms + us is an exception)
+SELECT date_trunc_interval('1 year 1 microsecond', TIMESTAMP '2001-02-16 20:38:40.123456');
+
-- Test casting within a BETWEEN qualifier
SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
FROM TIMESTAMP_TBL