Re: truncating timestamps on arbitrary intervals
| От | Bauyrzhan Sakhariyev |
|---|---|
| Тема | Re: truncating timestamps on arbitrary intervals |
| Дата | |
| Msg-id | CAKpL73vZmLuFVuwF26FJ+Nk11PVHhAnQRoREFcA03x7znRoFvA@mail.gmail.com обсуждение исходный текст |
| Ответ на | truncating timestamps on arbitrary intervals (John Naylor <john.naylor@2ndquadrant.com>) |
| Ответы |
Re: truncating timestamps on arbitrary intervals
|
| Список | pgsql-hackers |
Is
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.
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.naylor@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
В списке pgsql-hackers по дате отправления: