From: | John Naylor <john(dot)naylor(at)2ndquadrant(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | truncating timestamps on arbitrary intervals |
Date: | 2020-02-26 02:50:19 |
Message-ID: | CACPNZCt4buQFRgy6DyjuZS-2aPDpccRkrJBmgUfwYc1KiaXYxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
v1-datetrunc_interval.patch | application/octet-stream | 8.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Noah Misch | 2020-02-26 05:36:12 | Re: [HACKERS] WAL logging problem in 9.4.3? |
Previous Message | yuzuko | 2020-02-26 02:32:49 | Re: Autovacuum on partitioned table |