[SOLVED] Date interval calculation issue

I have the following data:

- a start date

- a interval on which records should match (e.g. once every 7 days)

- a expiration specified in intervals (e.g '2' with a interval of 7 days, query shouldn't match 14 days after the start date)

I also have a column storing the interval unit (days, weeks, months). Because the interval units are constructs, I use a switch to evaluate different expressions based on the unit. E.g.

	
WHERE
 CASE interval_measure 
   WHEN "D" 
    THEN DAYOFYEAR(DATE_ADD(start_date, INTERVAL interval_amount DAY)) = DAYOFYEAR(NOW()) 
   WHEN "W" 
    THEN DAYOFYEAR(DATE_ADD(start_date, INTERVAL (interval_amount * 7) DAY)) = DAYOFYEAR(NOW()) 
   WHEN "M" 
    THEN DAYOFYEAR(DATE_ADD(start_date, INTERVAL interval_amount MONTH)) = DAYOFYEAR(NOW()) 
   WHEN "Y" 
    THEN DAYOFYEAR(DATE_ADD(start_date, INTERVAL (interval_amount * 12) MONTH)) = DAYOFYEAR(NOW())
END

The above only matches once a year, what I need is to match every interval. This also doesn't account for the expiration after which records shouldn't be matched.

Any help very much appreciated.

I guess the following should take care of the interval limit:

SELECT DAYOFYEAR(DATE_ADD('2008-11-25 00:00:00', INTERVAL 2 DAY)) = DAYOFYEAR(NOW()),
DATEDIFF(DATE_ADD('2008-11-25 00:00:00', INTERVAL (20 * 2) DAY), NOW()) > 0

where 20 is the limit, and 2 days is the interval. It still only matches once a year though.

Edit:

Now something like this works as long as the interval is in days:

(DATEDIFF(NOW(), '2008-11-19 00:00:00') % 7) = 0

Now I just have to get it to work with months...

Edit2:

Ok, now I got this to match the month:

SELECT MOD(MONTH('2009-07-25 00:00:00') - MONTH('2008-11-25 00:00:00'), 2)

where the first date is the time of running (NOW()), the second date the start date, and 2 the interval.

Now I just got to match the day of the month, which isn't as easy as it sounds when the day is over 29...

Edit3

Okay, as a compromise I could do this:

SELECT (
  IF(DAYOFMONTH('2008-11-30') > 29,
    DAYOFMONTH(LAST_DAY(NOW())) >= DAYOFMONTH('2008-11-30'),
    DAYOFMONTH(NOW()) = DAYOFMONTH('2008-11-30')
  )
)

But that has a potential inaccuracy of 2 days...

Ok, I ended up with this monstrosity..

 CASE interval_measure 
   WHEN "D" 
    THEN 
    	MOD(DATEDIFF(NOW(), start_date), interval_amount) = 0
    	AND DATEDIFF(DATE_ADD(start_date, INTERVAL (interval_limit * interval_amount) DAY), NOW()) > 0 
   WHEN "W" 
    THEN 
    	MOD(DATEDIFF(NOW(), start_date), (7 * interval_amount)) = 0
    	AND DATEDIFF(DATE_ADD(start_date, INTERVAL (7 * interval_limit * interval_amount) DAY), NOW()) > 0
   WHEN "M" 
    THEN
  IF(DAYOFMONTH(start_date) > 29,
    DAYOFMONTH(LAST_DAY(NOW())) >= DAYOFMONTH(start_date)
      AND MOD(MONTH(NOW()) - MONTH(start_date), interval_amount) = 0,
    DAYOFMONTH(NOW()) = DAYOFMONTH(start_date)
      AND MOD(MONTH(NOW()) - MONTH(start_date), interval_amount) = 0
  )
  AND DATEDIFF(DATE_ADD(start_date, INTERVAL (interval_limit * interval_amount) MONTH), NOW()) > 0
END

I seems like it works fairly well (though as I mentioned it has a inaccuracy of one or two for dates after the 29th of a month), but if someone could check my logic, or suggest improvements, please. That would be very much appreciated.

Are you looking for dates between now and some future date where datediff(datecol, now) MOD interval = 0

I'm not sure I understand what you are saying.

if I understand, you have a set of dates, and if the interval is 7 days and expiration is 4, you want to pull the dates indicated in the example below

[pre]

+---------------------+

| tdate              |

+---------------------+

| 2008-09-06 00:00:00 |  <-----    0 days

| 2008-09-07 00:00:00 |

| 2008-09-08 00:00:00 |

| 2008-09-09 00:00:00 |

| 2008-09-10 00:00:00 |

| 2008-09-11 00:00:00 |

| 2008-09-12 00:00:00 |

| 2008-09-13 00:00:00 |  <-----    +7 days

| 2008-09-14 00:00:00 |

| 2008-09-16 00:00:00 |

| 2008-09-17 00:00:00 |

| 2008-09-20 00:00:00 |  <-----    +14 days

| 2008-09-22 00:00:00 |

| 2008-09-23 00:00:00 |

| 2008-09-24 00:00:00 |

| 2008-09-25 00:00:00 |

| 2008-09-27 00:00:00 |  <-----    +21 days

| 2008-09-28 00:00:00 |

| 2008-09-30 00:00:00 |

| 2008-10-01 00:00:00 |

| 2008-10-02 00:00:00 |

| 2008-10-04 00:00:00 |  <-----    +28 days

| 2008-10-06 00:00:00 |

| 2008-10-10 00:00:00 |

| 2008-10-12 00:00:00 |

| 2008-10-19 00:00:00 |

| 2008-10-19 18:10:57 |

| 2008-10-20 00:00:00 |

| 2008-10-21 00:00:00 |

| 2008-10-22 00:00:00 |

| 2008-10-23 00:00:00 |

| 2008-10-25 00:00:00 |

| 2008-10-27 00:00:00 |

| 2008-10-29 00:00:00 |

| 2008-10-30 00:00:00 |

| 2008-10-31 00:00:00 |

| 2008-11-01 00:00:00 |

| 2008-11-04 00:00:00 |

| 2008-11-08 00:00:00 |

| 2008-11-09 00:00:00 |

+---------------------+

mysql> SELECT tdate, DATEDIFF(tdate, '2008-09-06') as d

    -> FROM data

    -> WHERE tdate BETWEEN '2008-09-06' AND '2008-09-06' + INTERVAL (4*7) DAY

    -> AND DATEDIFF(tdate, '2008-09-06') MOD 7 = 0

    -> ORDER BY tdate;

+---------------------+------+

| tdate              | d    |

+---------------------+------+

| 2008-09-06 00:00:00 |    0 |

| 2008-09-06 00:00:00 |    0 |

| 2008-09-13 00:00:00 |    7 |

| 2008-09-20 00:00:00 |  14 |

| 2008-09-27 00:00:00 |  21 |

| 2008-10-04 00:00:00 |  28 |

+---------------------+------+

[/pre]

Almost. I have to asses if one of these match today, and select only that row.

Which for days, led me to this:

MOD(DATEDIFF(NOW(), start_date), (interval_amount)) = 0
  AND DATEDIFF(DATE_ADD(start_date, INTERVAL (interval_limit * interval_amount) DAY), NOW()) > 0

Using syntax a little closer to what you are using, it would look like this:

/* Difference between today and start_date in days must be dividable by the interval */
DATEDIFF(NOW(), start_date) MOD (interval_amount)) = 0
/* Difference between today and the last day within the limit must be more than 0 days */
  AND DATEDIFF(start_date + INTERVAL (interval_limit * interval_amount) DAY, NOW()) > 0

Both should accomplish the same, I think. The real issue is with months.

Sorry, joining in late here... where do things stand?

Same still - hits date is inaccurate when interval is in months and start_date is on the 29th, 30th or 31st of a month. If you haven't deducted it yet - it is for a daily cron.

SELECT * FROM table
WHERE
CASE interval_measure 
   WHEN "D" 
    THEN 
    	MOD(DATEDIFF(NOW(), start_date), interval_amount) = 0
    	AND DATEDIFF(DATE_ADD(start_date, INTERVAL (interval_limit * interval_amount) DAY), NOW()) > 0 
   WHEN "W" 
    THEN 
    	MOD(DATEDIFF(NOW(), start_date), (7 * interval_amount)) = 0
    	AND DATEDIFF(DATE_ADD(start_date, INTERVAL (7 * interval_limit * interval_amount) DAY), NOW()) > 0
   WHEN "M" 
    THEN
  IF(DAYOFMONTH(start_date) > 29,
    DAYOFMONTH(LAST_DAY(NOW())) >= DAYOFMONTH(start_date)
      AND MOD(MONTH(NOW()) - MONTH(start_date), interval_amount) = 0,
    DAYOFMONTH(NOW()) = DAYOFMONTH(start_date)
      AND MOD(MONTH(NOW()) - MONTH(start_date), interval_amount) = 0
  )
  AND DATEDIFF(DATE_ADD(start_date, INTERVAL (interval_limit * interval_amount) MONTH), NOW()) > 0
END

Not sure what you mean by "inaccurate" -- how do you want the month logic to work?

You know what, I made a mistake. It should work as expected. 

If the last day of the month is the 31th, and start_date is on the 30th, it will match on the 30th.

IF(30 > 29, 31 >= 30)

If the last day of the month is the 30th, and start_date is on the 31th, it will match on the 30th.

IF(31 > 29, 30 >= 30)

If start_date is 29 or smaller, it can just match the day.

Conclusion: there is nothing to fix. Yes, I'm a twat (this is where CV comes along and says "that's what I've been saying ).

It happens... don't forget it mark it "solved"... LOL.

Right.