Re: truncating timestamps on arbitrary intervals

Is date_bin supposed to return the beginning of the bin? And does the sign
of an interval define the "direction" of the bin?
Judging by results of queries #1 and #2, sign of interval decides a
direction timestamp gets shifted to (in both cases ts < origin)
but when ts >origin (queries #3 and #4) interval sign doesn't matter,
specifically #4 doesn't return 6-th of January.

1. SELECT date_bin('-2 days'::interval, timestamp '2001-01-01
00:00:00', timestamp
'2001-01-04 00:00:00'); -- 2001-01-02 00:00:00
2. SELECT date_bin('2 days'::interval, timestamp '2001-01-01
00:00:00', timestamp
'2001-01-04 00:00:00'); -- 2000-12-31 00:00:00
3. SELECT date_bin('2 days'::interval, timestamp '2001-01-04
00:00:00', timestamp
'2001-01-01 00:00:00'); -- 2001-01-03 00:00:00
4. SELECT date_bin('-2 days'::interval, timestamp '2001-01-04
00:00:00', timestamp
'2001-01-01 00:00:00'); -- 2001-01-03 00:00:00

On Thu, Jul 22, 2021 at 6:21 PM John Naylor <john(dot)naylor(at)2ndquadrant(dot)com>
wrote:

> Hi,
>
> When analyzing time-series data, it's useful to be able to bin
> timestamps into equally spaced ranges. date_trunc() is only able to
> bin on a specified whole unit. In the attached patch for the March
> commitfest, I propose a new function date_trunc_interval(), which can
> truncate to arbitrary intervals, e.g.:
>
> select date_trunc_interval('15 minutes', timestamp '2020-02-16
> 20:48:40'); date_trunc_interval
> ---------------------
> 2020-02-16 20:45:00
> (1 row)
>
> With this addition, it might be possible to turn the existing
> date_trunc() functions into wrappers. I haven't done that here because
> it didn't seem practical at this point. For one, the existing
> functions have special treatment for weeks, centuries, and millennia.
>
> Note: I've only written the implementation for the type timestamp
> without timezone. Adding timezone support would be pretty simple, but
> I wanted to get feedback on the basic idea first before making it
> complete. I've also written tests and very basic documentation.
>
> --
> John Naylor https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>