|From:||David Fetter <david(at)fetter(dot)org>|
|To:||John Naylor <john(dot)naylor(at)2ndquadrant(dot)com>|
|Subject:||Re: truncating timestamps on arbitrary intervals|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
On Wed, Feb 26, 2020 at 10:50:19AM +0800, John Naylor wrote:
> 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.
Thanks for adding this very handy feature!
> 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)
I believe the following should error out, but doesn't.
# SELECT date_trunc_interval('1 year 1 ms', TIMESTAMP '2001-02-16 20:38:40');
> 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.
I agree that turning it into a wrapper would be separate work.
> 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.
Please find attached an update that I believe fixes the bug I found in
a principled way.
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
|Next Message||Sandro Santilli||2020-02-26 08:11:21||Re: Marking some contrib modules as trusted extensions|
|Previous Message||Masahiko Sawada||2020-02-26 07:47:56||Re: Identifying user-created objects|