On Fri, 13 Mar 2020 at 03:13, John Naylor <john.nay...@2ndquadrant.com> wrote:
> On Wed, Feb 26, 2020 at 11:36 PM Tom Lane <t...@sss.pgh.pa.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).