On Thu, Jul 22, 2021 at 4:49 PM Bauyrzhan Sakhariyev <
baurzhansahariev@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 negative intervals (see queries #1 and #2 from above, they have same timestamp, origin and interval magnitude, difference is only in interval sign) - can it be that the version I downloaded from
https://www.enterprisedb.com/postgresql-early-experience doesn't include commit with that improvement?
Sorry, I wasn't clear. The intention is that the boundary is on the lower side, but query #1 doesn't follow that, so that's a bug in my view. I found while developing the feature that the sign of the stride didn't seem to matter, but evidently I didn't try with the origin in the future.
> > I wonder if we should just disallow negative intervals here.
>
> I cannot imagine somebody using negative as a constant argument but users can pass another column as a first argument date or some function(ts) - not likely but possible. A line in docs about the leftmost point of interval as start of the bin could be helpful.
In recent years there have been at least two attempts to add an absolute value function for intervals, and both stalled over semantics, so I'd rather just side-step the issue, especially as we're in beta.
> >In the case of full units (1 minute, 1 hour, etc.), it gives the same result as the analogous date_trunc call,
> Was not obvious to me that we need to supply Monday origin to make date_bin(1 week, ts) produce same result with date_trunc
The docs for date_trunc() don't mention this explicitly, but it might be worth mentioning ISO weeks. There is a nearby mention for EXTRACT():
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT"The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year."
> Sorry for the verbose report and thanks for the nice function - I know it's not yet released, was just playing around with beta as I want to align CrateDB date_bin with Postgresql
Thanks again for testing! This is good feedback.
--
John Naylor
EDB:
http://www.enterprisedb.com
