Re: truncating timestamps on arbitrary intervals

On Fri, 13 Mar 2020 at 03:13, John Naylor <john(dot)naylor(at)2ndquadrant(dot)com>
wrote:

> On Wed, Feb 26, 2020 at 11:36 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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 argument? Even if we
> > don't, "zero" for tm_year is 1900, which is going to give results
> > that surprise somebody.
>

- 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
> ---------------------
> 2020-02-09 00:00:00
> (1 row)
>

I'm confused by this. If my calendars are correct, both 1900-01-02
and 2020-02-11 are Tuesdays. So if the date being adjusted and the origin
are both Tuesday, shouldn't the day part be left alone when truncating to 7
days? Also, I'd like to confirm that the default starting point for 7 day
periods (weeks) is Monday, per ISO. I know it's very fashionable in North
America to split the weekend in half but it's not the international
standard.

Perhaps the starting point for dates should be either 0001-01-01 (the
proleptic beginning of the CE calendar) or 2001-01-01 (the beginning of the
current 400-year repeating cycle of leap years and weeks, and a Monday,
giving the appropriate ISO result for truncating to 7 day periods).