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.
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.:
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
