Re: truncating timestamps on arbitrary intervals

On Wed, Jul 28, 2021 at 12:15 AM Michael Paquier wrote: > > On Tue, Jul 27, 2021 at 12:05:51PM -0400, John Naylor wrote: > > Concretely, I propose to push the attached on master and v14. Since we're > > in beta 2 and this thread might not get much attention, I've CC'd the RMT. > > (It looks like

Re: truncating timestamps on arbitrary intervals

On Tue, Jul 27, 2021 at 12:05:51PM -0400, John Naylor wrote: > Concretely, I propose to push the attached on master and v14. Since we're > in beta 2 and this thread might not get much attention, I've CC'd the RMT. (It looks like gmail has messed up a bit the format of your last message.) Hmm.

Re: truncating timestamps on arbitrary intervals

John Naylor writes: > Concretely, I propose to push the attached on master and v14. Since we're > in beta 2 and this thread might not get much attention, I've CC'd the RMT. +1, we can figure out whether that has a use some other time. regards, tom lane

Re: truncating timestamps on arbitrary intervals

I wrote: > On Thu, Jul 22, 2021 at 4:49 PM Bauyrzhan Sakhariyev < baurzhansahar...@gmail.com> wrote: > > > > > No, the boundary is intentionally the earlier one: > > > > I found that commit in GitHub, thanks for pointing it out. > > When I test locally origin_in_the_future case I get different

Re: truncating timestamps on arbitrary intervals

On Thu, Jul 22, 2021 at 4:49 PM Bauyrzhan Sakhariyev < baurzhansahar...@gmail.com> wrote: > > > No, the boundary is intentionally the earlier one: > > I found that commit in GitHub, thanks for pointing it out. > When I test locally origin_in_the_future case I get different results for positive and

Re: truncating timestamps on arbitrary intervals

On Thu, Jul 22, 2021 at 4:49 PM Bauyrzhan Sakhariyev < baurzhansahar...@gmail.com> wrote: > Not related to negative interval - I created a PR for adding zero check for stride https://github.com/postgres/postgres/pull/67 and after getting it closed I stopped right there - 1 line check doesn't worth

Re: truncating timestamps on arbitrary intervals

> No, the boundary is intentionally the earlier one: I found that commit in GitHub, thanks for pointing it out. When I test locally *origin_in_the_future *case I get different results for positive and negative intervals (see queries #1 and #2 from above, they have same timestamp, origin and

Re: truncating timestamps on arbitrary intervals

On Thu, Jul 22, 2021 at 12:24 PM Bauyrzhan Sakhariyev < baurzhansahar...@gmail.com> wrote: > > Is date_bin supposed to return the beginning of the bin? Thanks for testing! And yes. > And does the sign of an interval define the "direction" of the bin? No, the boundary is intentionally the

Re: truncating timestamps on arbitrary intervals

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)

Re: truncating timestamps on arbitrary intervals

On 22.04.21 11:16, Justin Pryzby wrote: It looks like we all missed that I misspelled "date_bin" as "date_trunc"...sorry. I will include this with my next round of doc review, in case you don't want to make a separate commit for it. fixed

Re: truncating timestamps on arbitrary intervals

On Fri, Apr 09, 2021 at 10:02:47PM +0200, Peter Eisentraut wrote: > On 30.03.21 18:50, John Naylor wrote: > > On Sat, Mar 27, 2021 at 1:06 PM Justin Pryzby wrote: > > > > > > The current docs seem to be missing a "synopsis", like > > > > > > + > > > +date_trunc(stride, > > timestamp, origin)

Re: truncating timestamps on arbitrary intervals

On 10.04.21 14:53, John Naylor wrote: On Sat, Apr 10, 2021 at 7:43 AM Peter Eisentraut > wrote: > > On 30.03.21 18:06, John Naylor wrote: > > Currently, when the origin is after the input, the result is the > > timestamp at the end of the bin,

Re: truncating timestamps on arbitrary intervals

On Sat, Apr 10, 2021 at 7:43 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > > On 30.03.21 18:06, John Naylor wrote: > > Currently, when the origin is after the input, the result is the > > timestamp at the end of the bin, rather than the beginning as expected. > > The attached

Re: truncating timestamps on arbitrary intervals

On 30.03.21 18:06, John Naylor wrote: Currently, when the origin is after the input, the result is the timestamp at the end of the bin, rather than the beginning as expected. The attached puts the result consistently at the beginning of the bin. In the patch + if (origin > timestamp &&

Re: truncating timestamps on arbitrary intervals

On 30.03.21 18:50, John Naylor wrote: On Sat, Mar 27, 2021 at 1:06 PM Justin Pryzby > wrote: > > The current docs seem to be missing a "synopsis", like > > + > +date_trunc(stride, timestamp, origin) > + The attached - adds a synopsis - adds a bit more

Re: truncating timestamps on arbitrary intervals

On Thu, Apr 1, 2021 at 9:11 AM Salek Talangi wrote: > > Hi all, > > it might be a bit late now, but do you know that TimescaleDB already has a similar feature, named time_bucket? > https://docs.timescale.com/latest/api#time_bucket > Perhaps that can help with some design decisions. Yes, thanks

Re: truncating timestamps on arbitrary intervals

Hi all, it might be a bit late now, but do you know that TimescaleDB already has a similar feature, named time_bucket? https://docs.timescale.com/latest/api#time_bucket Perhaps that can help with some design decisions. I saw your feature on Depesz' "Waiting for PostgreSQL 14" and remembered

Re: truncating timestamps on arbitrary intervals

On Sat, Mar 27, 2021 at 1:06 PM Justin Pryzby wrote: > > The current docs seem to be missing a "synopsis", like > > + > +date_trunc(stride, timestamp, origin) > + The attached - adds a synopsis - adds a bit more description to the parameters similar to those in date_trunc - documents that

Re: truncating timestamps on arbitrary intervals

Currently, when the origin is after the input, the result is the timestamp at the end of the bin, rather than the beginning as expected. The attached puts the result consistently at the beginning of the bin. -- John Naylor EDB: http://www.enterprisedb.com rationalize-future-origin.patch

Re: truncating timestamps on arbitrary intervals

On Wed, Mar 24, 2021 at 08:50:59PM +0100, Peter Eisentraut wrote: > On 24.03.21 18:58, John Naylor wrote: > > > As a potential follow-up, should we perhaps add named arguments?  That > > > might make the invocations easier to read, depending on taste. > > > > I think it's quite possible some

Re: truncating timestamps on arbitrary intervals

On 24.03.21 18:58, John Naylor wrote: > As a potential follow-up, should we perhaps add named arguments?  That > might make the invocations easier to read, depending on taste. I think it's quite possible some users will prefer that. All we need is to add something like proargnames =>

Re: truncating timestamps on arbitrary intervals

On 24.03.21 18:25, Erik Rijkers wrote: On 2021.03.24. 16:38 Peter Eisentraut wrote: Committed. 'In cases full units' seems strange. fixed, thanks

Re: truncating timestamps on arbitrary intervals

On Wed, Mar 24, 2021 at 1:25 PM Erik Rijkers wrote: > > 'In cases full units' seems strange. > > Not a native speaker but maybe the attached changes are improvements? -In cases full units (1 minute, 1 hour, etc.), it gives the same result as +In case of full units (1 minute, 1 hour,

Re: truncating timestamps on arbitrary intervals

On Wed, Mar 24, 2021 at 11:38 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > Committed. > > I noticed that some of the documentation disappeared between v9 and v10. > So I put that back and updated it appropriately. I also added a few > more test cases to cover some things

Re: truncating timestamps on arbitrary intervals

> On 2021.03.24. 16:38 Peter Eisentraut > wrote: > > Committed. > 'In cases full units' seems strange. Not a native speaker but maybe the attached changes are improvements? Erik Rijkers--- ./doc/src/sgml/func.sgml.orig 2021-03-24 18:16:01.269515354 +0100 +++ ./doc/src/sgml/func.sgml

Re: truncating timestamps on arbitrary intervals

On 18.01.21 21:54, John Naylor wrote: On Mon, Nov 23, 2020 at 1:44 PM John Naylor mailto:john.nay...@enterprisedb.com>> wrote: > > On Thu, Nov 12, 2020 at 9:56 AM Peter Eisentraut > wrote: > > - After reading the discussion a few times, I'm not so

Re: truncating timestamps on arbitrary intervals

On 1/18/21 3:54 PM, John Naylor wrote: On Mon, Nov 23, 2020 at 1:44 PM John Naylor mailto:john.nay...@enterprisedb.com>> wrote: > > On Thu, Nov 12, 2020 at 9:56 AM Peter Eisentraut > wrote: > > - After reading the discussion a few times, I'm not so

Re: truncating timestamps on arbitrary intervals

On Mon, Nov 23, 2020 at 1:44 PM John Naylor wrote: > > On Thu, Nov 12, 2020 at 9:56 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > > - After reading the discussion a few times, I'm not so sure anymore > > whether making this a cousin of date_trunc is the right way to go. As >

Re: truncating timestamps on arbitrary intervals

On Thu, Nov 12, 2020 at 9:56 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > > On 2020-06-30 06:34, John Naylor wrote: > > In v9, I've simplified the patch somewhat to make it easier for future > > work to build on. > > > > - When truncating on month-or-greater intervals, require

Re: truncating timestamps on arbitrary intervals

On 2020-06-30 06:34, John Naylor wrote: In v9, I've simplified the patch somewhat to make it easier for future work to build on. - When truncating on month-or-greater intervals, require the origin to align on month. This removes the need to handle weird corner cases that have no straightforward

Re: truncating timestamps on arbitrary intervals

In v9, I've simplified the patch somewhat to make it easier for future work to build on. - When truncating on month-or-greater intervals, require the origin to align on month. This removes the need to handle weird corner cases that have no straightforward behavior. - Remove hackish and possibly

Re: truncating timestamps on arbitrary intervals

On Tue, Mar 31, 2020 at 4:34 PM Artur Zakirov wrote: > Thank you for new version of the patch. Thanks for taking a look! Attached is v8, which addresses your points, adds tests and fixes some bugs. There are still some WIP detritus in the timezone code, so I'm not claiming it's ready, but it's

Re: truncating timestamps on arbitrary intervals

On 3/30/2020 9:30 PM, John Naylor wrote: I attempted this in the attached v7. There are 4 new functions for truncating timestamptz on an interval -- with and without origin, and with and without time zone. Thank you for new version of the patch. I'm not sure that I fully understand the

Re: truncating timestamps on arbitrary intervals

I wrote: > I'm going to look into implementing timezone while awaiting comments on v6. I attempted this in the attached v7. There are 4 new functions for truncating timestamptz on an interval -- with and without origin, and with and without time zone. Parts of it are hackish, and need more

Re: truncating timestamps on arbitrary intervals

On Tue, Mar 24, 2020 at 9:34 PM Tels wrote: > > Hello John, > > this looks like a nice feature. I'm wondering how it relates to the > following use-case: > > When drawing charts, the user can select pre-defined widths on times > (like "15 min", "1 hour"). > > The data for these slots is fitted

Re: truncating timestamps on arbitrary intervals

Hello John, this looks like a nice feature. I'm wondering how it relates to the following use-case: When drawing charts, the user can select pre-defined widths on times (like "15 min", "1 hour"). The data for these slots is fitted always to intervalls that start in 0 in the slot, e.g. if

Re: truncating timestamps on arbitrary intervals

On Sun, Mar 15, 2020 at 2:26 PM I wrote: > > To get more logical behavior, perhaps the optional parameter is better > as an offset instead of an origin. Alternatively (or additionally), > the function could do the math on int64 timestamps directly. For v6, I changed the algorithm to use pg_tm for

Re: truncating timestamps on arbitrary intervals

Hello, On 3/13/2020 4:13 PM, John Naylor wrote: I've put off adding documentation on the origin piece pending comments about the approach. I haven't thought seriously about timezone yet, but hopefully it's just work and nothing to think too hard about. Thank you for the patch. I looked it

Re: truncating timestamps on arbitrary intervals

On Fri, Mar 13, 2020 at 7:48 PM Isaac Morland wrote: > > On Fri, 13 Mar 2020 at 03:13, John Naylor wrote: > >> - align weeks to start on Sunday >> select date_trunc_interval('7 days'::interval, TIMESTAMP '2020-02-11 >> 01:01:01.0', TIMESTAMP '1900-01-02'); >> date_trunc_interval >>

Re: truncating timestamps on arbitrary intervals

On Fri, 13 Mar 2020 at 03:13, John Naylor wrote: > On Wed, Feb 26, 2020 at 11:36 PM Tom Lane wrote: > > > > * In general, binning involves both an origin and a stride. When > > working with plain numbers it's almost always OK to set the origin > > to zero, but it's less clear to me whether

Re: truncating timestamps on arbitrary intervals

On Wed, Feb 26, 2020 at 11:36 PM Tom Lane wrote: > > * In general, binning involves both an origin and a stride. When > working with plain numbers it's almost always OK to set the origin > to zero, but it's less clear to me whether that's all right for > timestamps. Do we need another optional

Re: truncating timestamps on arbitrary intervals

On Wed, Feb 26, 2020 at 11:36 PM Tom Lane wrote: > > John Naylor writes: > > [ v3-datetrunc_interval.patch ] > > A few thoughts: > > * In general, binning involves both an origin and a stride. When > working with plain numbers it's almost always OK to set the origin > to zero, but it's less

Re: truncating timestamps on arbitrary intervals

On Wed, Feb 26, 2020 at 06:38:57PM +0800, John Naylor wrote: > On Wed, Feb 26, 2020 at 3:51 PM David Fetter wrote: > > > > 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'); > > date_trunc_interval > >

Re: truncating timestamps on arbitrary intervals

John Naylor writes: > [ v3-datetrunc_interval.patch ] A few thoughts: * In general, binning involves both an origin and a stride. When working with plain numbers it's almost always OK to set the origin to zero, but it's less clear to me whether that's all right for timestamps. Do we need

Re: truncating timestamps on arbitrary intervals

On Wed, Feb 26, 2020 at 3:51 PM David Fetter wrote: > > 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'); > date_trunc_interval > ═ > 2001-01-01 00:00:00 > (1 row) You're quite right. I

Re: truncating timestamps on arbitrary intervals

On Wed, Feb 26, 2020 at 10:50:19AM +0800, John Naylor 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. Thanks for adding this very handy feature! > In the