Precedence of bitwise operators
Hi! Do I get it right, that bitwise operations have the same precedence? Query *SELECT 1 & 2 | 3, 3 | 1 & 2* returns 3 and 2 respectively. See also https://www.db-fiddle.com/f/iZHd8zG7A1HjbB6J2y8R7k/1. It looks like the result is calculated from left to right and operators have the same precedence. I checked relevant documentation pages ( https://www.postgresql.org/docs/current/functions-bitstring.html and https://www.postgresql.org/docs/current/sql-syntax-lexical.html) and couldn't find any information about bitwise operations precedence, only information about logical operations precedence. I'm not saying it's a bug, rather trying to clarify as precedence of bitwise operators is different in programming languages, say c++ ( https://en.cppreference.com/w/c/language/operator_precedence) or java ( https://docs.oracle.com/javase/tutorial/java/nutsandbolts/operators.html)
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 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? > 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. 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 going through the patching process I'm not familiar with. >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 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 <https://github.com/crate/crate/issues/11310> with Postgresql On Thu, Jul 22, 2021 at 7:28 PM John Naylor wrote: > > 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 earlier one: > > /* > * Make sure the returned timestamp is at the start of the bin, even if > * the origin is in the future. > */ > if (origin > timestamp && stride_usecs > 1) > tm_delta -= stride_usecs; > > I wonder if we should just disallow negative intervals here. > > -- > John Naylor > EDB: http://www.enterprisedb.com >
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) interval sign doesn't matter,
specifically #4 doesn't return 6-th of January.
1. SELECT date_bin('-2 days'::interval, timestamp '2001-01-01
00:00:00', timestamp
'2001-01-04 00:00:00'); -- 2001-01-02 00:00:00
2. SELECT date_bin('2 days'::interval, timestamp '2001-01-01
00:00:00', timestamp
'2001-01-04 00:00:00'); -- 2000-12-31 00:00:00
3. SELECT date_bin('2 days'::interval, timestamp '2001-01-04
00:00:00', timestamp
'2001-01-01 00:00:00'); -- 2001-01-03 00:00:00
4. SELECT date_bin('-2 days'::interval, timestamp '2001-01-04
00:00:00', timestamp
'2001-01-01 00:00:00'); -- 2001-01-03 00:00:00
On Thu, Jul 22, 2021 at 6:21 PM 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. In the attached patch for the March
> commitfest, I propose a new function date_trunc_interval(), which can
> truncate to arbitrary intervals, e.g.:
>
> select date_trunc_interval('15 minutes', timestamp '2020-02-16
> 20:48:40'); date_trunc_interval
> -
> 2020-02-16 20:45:00
> (1 row)
>
> With this addition, it might be possible to turn the existing
> date_trunc() functions into wrappers. I haven't done that here because
> it didn't seem practical at this point. For one, the existing
> functions have special treatment for weeks, centuries, and millennia.
>
> Note: I've only written the implementation for the type timestamp
> without timezone. Adding timezone support would be pretty simple, but
> I wanted to get feedback on the basic idea first before making it
> complete. I've also written tests and very basic documentation.
>
> --
> John Naylorhttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
