Date puzzle

How would you calculate the next occurrence of an event running every n days?

I have a mysql table storing the start date and the number of days between occurrences to happen indefinitely. I would like a report that shows the next occurrence (date) for each row.


Yes raj had that in his php code example. I started with that but was missing something, I had to break it down a little further to understand and debug. I’ll check out that class. Date calculations are sometimes hard for me to get my head around, but their understanding is essential.

I agree with felgall, salathe’s solution is cleanest. I have not yet utilized views in MySQL but will start reading about them.

The select statement Salathe supplied is probably the simplest way to do this as it simply returns the exact date you are looking for from the database call in the first place so that it is as if that date were stored in the database from the start.

The only way of making it any simpler than that would be to set up a view on the table that includes that result as a field that can be directly accessed by name as if the next occurrence date were stored in the database.

OK, but did you know you could do this?

echo date('Y m d' ,  strtotime( '2010-08-12 + 7 days') );

gives 2010-08-19

It might not be applicable in your case because I don’t know enough about it - but if the periods are days then this is a lot more comfortable to work with, and easier to read when you come back later, thats all I want to point out.

The datetime class I mentioned earlier builds upon the strtotime() function too.

Here is my solution, a little more verbose and not as clean as your solutions probably would have been, but it’s just my process and it looks like it’s working thanks to your help.

$sql = "SELECT *, DATEDIFF(CURRENT_DATE, recurring_start_date) as days_diff, CEIL((DATEDIFF(CURRENT_DATE, recurring_start_date) / recurring_interval)) as next_interval, FLOOR((DATEDIFF(CURRENT_DATE, recurring_start_date) / recurring_interval)) as prev_interval FROM recurring_orders";

// database connection stuff...
while($myrow = mysql_fetch_assoc($result)){
	$add_seconds = $myrow['next_interval'] * $myrow['recurring_interval'] * 24 * 60 * 60;
	$next_udate = strtotime($myrow['recurring_start_date']) + $add_seconds;
	$next_date = date('Y-m-d', $next_udate);

     // decided I might as well display the last shipment date as well... 
	$less_seconds = $myrow['prev_interval'] * $myrow['recurring_interval'] * 24 * 60 * 60;
	$prev_udate =  strtotime($myrow['recurring_start_date']) + $less_seconds; 
	$prev_date = date('Y-m-d', $prev_udate);


If the start date and interval combination means that the event happens today, would you expect the “next” occurrence to be today or the next one in the future? The following assumes, today.

            DATEDIFF(CURRENT_DATE, start_date),
        ) DAY
    ) AS next
FROM puzzle;

The calculation basically goes:

  • Get the difference, in days, between today and the start date
  • Calculate the number of days until the next occurrence (diff modulo interval)
  • Add the number of days until the next occurrence to today, in order to get the date of the next occurrence

(Quick idea, may not be particularly optimised nor tailored 100% to your needs)

Well, assuming your start date field name is ‘startdate’ and occurrence int field is ‘nextoccurenceinterval’.

SELECT startdate, DATE_ADD(startdate, INTERVAL nextoccuranceinterval DAY) AS next_event FROM events_table;


$startdate = '2010-08-05'; 
$n = 45; 
echo date('Y-m-d', strtotime("+ $n days", strtotime($startdate)));

See the output. To get more next events I think you are not weak in math (like me) and you are aware of the PHP loops too. And I hope you can do with the examples above.

I think so too :slight_smile:
But I gave you the math, rajug and cups gave you the links, why don’t you try and put something together. Then, if you can’t resolve it completely, come back here and post your work.

Guido I think your math is closer to what I am looking to do.

Rajug, that would give me the next occurrence date from the start date. These events happen indefinitely and I am looking for the next date it will occur in the future.

Further clarification:

I have a MySQL table containing a start date of type date, and an occurrence interval of type int representing the number of days between occurrences. I am looking for the next future occurrence for each row.. So the start date might be 2009-12-15 and the occurrence interval 45, when is the next occurrence in the future?




SELECT startdate, DATE_ADD(startdate, INTERVAL 7 DAY) AS next_date FROM events_table;


$startdate = '2010-08-05';
$n = 7;
echo date('Y-m-d', strtotime("+ $n days", strtotime($startdate)));

Events every n days?

If that is likely to open up and become things like:

every tuesday
every second tuesday of the month
in two weeks time
same day next week for next 6 weeks
(others patterns not yet dreamed of, but your client may want)

IF SO I my experience there is no doubt you should be doing this in PHP, and your biggest friend will be the datetime class if you are running 5.2 or better.

If you are running 5.3 then it comes with all the bells and whistles.

You could use the MySQL functions to calculate the difference in days between today and the start date, and divide that number by the number of days between occurrences, and if the rest of that division is 0, it’s a match :slight_smile:

Yes, I am aware of the various date functions available in MySQL and PHP. I was hoping for some help in regards to how you would use the functions to do the necessary calculations in either MySQL or PHP. I am thinking MySQL might be the most optimal, but I leave that up to you.


mysql or [URL=“”]php

I am converting the amount of time to a unix timestamp (expressed in seconds) before adding it to the recurring start date unix timestamp. There are other simpler ways to do this mentioned earlier in the thread… I just happened to end up with this through my debugging process.

Maybe I really haven’t grasped the problem but a solution to a problem involving numbers of days - requires seconds to be counted?