MySQL Query where date in interval number of days-CodePudding

I have a table with invites and I want to query all invites that fall within an x number of day intervals of today. For example say 90 days, I want every invite that was created 90 days ago and 180 days ago and 270 days ago etc.

Then, for each interval slot, I'd like to add some wiggle room of x number of days on either side, for example 2, so now it's invites that were created between 88 and 92 days ago and between 178 and 182 days ago and between 268 and 272 days ago etc.

The start of my query for this is as follows:

SET @days_span = 90;
SET @days_variance = 2;

SELECT
  invites.id,
  invites.date_created_utc
FROM
  invites
WHERE
  -- between 88 and 92 days ago
  invites.date_created_utc BETWEEN date_sub(utc_timestamp(), interval (@days_span   @days_variance) DAY) AND date_sub(utc_timestamp(), interval (@days_span - @days_variance) DAY)
  -- between 178 and 182 days ago
  OR invites.date_created_utc BETWEEN date_sub(utc_timestamp(), interval (@days_span * 2   @days_variance) DAY) AND date_sub(utc_timestamp(), interval (@days_span * 2 - @days_variance) DAY)
  -- between 268 and 272 days ago
  OR invites.date_created_utc BETWEEN date_sub(utc_timestamp(), interval (@days_span * 3   @days_variance) DAY) AND date_sub(utc_timestamp(), interval (@days_span * 3 - @days_variance) DAY);
  -- etc.

For this I cheated by manually repeating the where clause and multiplying the day interval span. Now I don't know how to expand this to work for n number of intervals.

I've populated a dbfiddle with a temporary table and some test data here: https://dbfiddle.uk/XSE5iIEN

CodePudding user response：

Get the date difference, and then use the MOD operator to see how close it is to @days_span.

SET @days_span = 90;
SET @days_variance = 2;
SET @days_high_mod = @days_span - @days_variance; 

SELECT id, date_created_utc
FROM invites
WHERE DATEDIFF(utc_timestamp(), date_created_utc) MOD @days_span <= @days_variance 
   OR DATEDIFF(utc_timestamp(), date_created_utc) MOD @days_span >= @days_high_mod

You need the two comparisons because MOD will always return a positive number. So if it's 2 days before the start of the next interval, the modulus will be 88. That's what @days_high_mod is for.