Re: truncating timestamps on arbitrary intervals

On Mon, Nov 23, 2020 at 1:44 PM John Naylor <john(dot)naylor(at)enterprisedb(dot)com>
wrote:
>
> On Thu, Nov 12, 2020 at 9:56 AM Peter Eisentraut <
peter(dot)eisentraut(at)enterprisedb(dot)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
> > you mentioned, there are some behaviors specific to date_trunc that
> > don't really make sense in date_trunc_interval, and maybe we'll have
> > more of those.

For v10, I simplified the behavior by decoupling the behavior from
date_trunc() and putting in some restrictions as discussed earlier. It's
much simpler now. It could be argued that it goes too far in that
direction, but it's easy to reason about and we can put back some features
as we see fit.

> > Also, date_trunc_interval isn't exactly a handy name.
> > Maybe something to think about. It's obviously fairly straightforward
> > to change it.
>
> Effectively, it puts timestamps into bins, so maybe date_bin() or
something like that?

For v10 I went with date_bin() so we can see how that looks.

> > - There were various issues with the stride interval having months and
> > years. I'm not sure we even need that. It could be omitted unless you
> > are confident that your implementation is now sufficient.
>
> Months and years were a bit tricky, so I'd be happy to leave that out if
there is not much demand for it. date_trunc() already has quarters,
decades, centuries, and millenia.

I removed months and years for this version, but that can be reconsidered
of course. The logic is really simple now.

> > - Also, negative intervals could be prohibited, but I suppose that
> > matters less.

I didn't go this far, but probably should before long.

> > - Then again, I'm thinking that maybe we should make the origin
> > mandatory. Otherwise, the default answers when having strides larger
> > than a day are entirely arbitrary, e.g.,

I've tried this and like the resulting simplification.

> > - I'm wondering whether we need the date_trunc_interval(interval,
> > timestamptz, timezone) variant. Isn't that the same as
> > date_trunc_interval(foo AT ZONE xyz, value)?
>
> I based this on 600b04d6b5ef6 for date_trunc(), whose message states:
>
> date_trunc(field, timestamptz, zone_name)
>
> is the same as
>
> date_trunc(field, timestamptz at time zone zone_name) at time zone
zone_name
>
> so without the shorthand, you need to specify the timezone twice, once
for the calculation, and once for the output.

In light of making the origin mandatory, it no longer makes sense to have a
time zone parameter, since we can specify the time zone on the origin; and
if desired on the output as well.

--
John Naylor
EDB: http://www.enterprisedb.com