On Wed, Feb 26, 2020 at 11:36 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > * In general, binning involves both an origin and a stride. When > working with plain numbers it's almost always OK to set the origin > to zero, but it's less clear to me whether that's all right for > timestamps. Do we need another optional argument? Even if we > don't, "zero" for tm_year is 1900, which is going to give results > that surprise somebody.
I tried the simplest way in the attached v5. Examples (third param is origin):
-- same result as no origin:
select date_trunc_interval('5 min'::interval, TIMESTAMP '2020-02-01
01:01:01', TIMESTAMP '2020-02-01');
date_trunc_interval
---------------------
2020-02-01 01:00:00
(1 row)
-- shift bins by 2.5 min:
select date_trunc_interval('5 min'::interval, TIMESTAMP '2020-02-1
01:01:01', TIMESTAMP '2020-02-01 00:02:30');
date_trunc_interval
---------------------
2020-02-01 00:57:30
(1 row)
-- align weeks to start on Sunday
select date_trunc_interval('7 days'::interval, TIMESTAMP '2020-02-11
01:01:01.0', TIMESTAMP '1900-01-02');
date_trunc_interval
---------------------
2020-02-09 00:00:00
(1 row)
I've put off adding documentation on the origin piece pending comments
about the approach.
I haven't thought seriously about timezone yet, but hopefully it's
just work and nothing to think too hard about.
--
John Naylor https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
v5-datetrunc_interval.patch
Description: Binary data
