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.nay...@2ndquadrant.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 >