[SOLVED] Date interval calculation issue

[448191]

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.

[448191]

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...

[448191]

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.

[Barand]

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

[448191]

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

[Barand]

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]

[448191]

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.

[fenway]

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

[448191]

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

[fenway]

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

[448191]

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 ).

[fenway]

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

[448191]

Right.