How to iterate, by month, between two specified dates

Question

I have a website that was launched a few months ago. I would like to write some code that will look through every month, since launch, and grab metrics for that month.

My question is: What is the best way to specify a start date, and then iterate by month, all the way up until the current month (or to another specified date).

Does anyone have any suggestions? I'm using PHP and a mySQL database.

Answer 1

You could use strtotime, to increment the date by +1 month:

$date1 = strtotime('2009-01-01');
$date2 = strtotime('2010-01-01');

while ($date1 <= $date2) {
  echo date('Y-m-d', $date1) . "\n";
  $date1 = strtotime('+1 month', $date1);
}

And if you have PHP >= 5.3.0, you can use DateTime::add with an DateInterval object

Check the above example here.

Answer 2

$start_date = mktime(0, 0, 0, 1, 1, 2009);
$end_date = mktime(0, 0, 0, 10, 1, 2009);

$current_date = $start_date;
while($current_date <= $end_date) {
     $current_date = strtotime("+1 month", $current_date);
}

That is one way to do it. Another one includes two loops over months and years separately.

But if you are know SQL well, you can get all your metrics and GROUP BY EXTRACT(YEAR_MONTH FROM date) right there in DB, which will most likely be faster.

Answer 3

I don't know the schema of your stored metrics, so here's a generic example:

Select total pageviews per month from Aug 1 thru Nov 30 2009

The code for MySQL:

SELECT DATE_FORMAT(s.date, '%Y-%m') AS  year_month , SUM( s.pageviews ) AS  s.pageviews_total 
FROM statistics s
WHERE s.date BETWEEN '2009-08-01' AND '2009-11-30'
GROUP BY DATE_FORMAT(s.date, '%Y-%m') 
ORDER BY DATE_FORMAT(s.date, '%Y-%m')

Having that aggregated table output may be enough for you. If not, you can loop through it with PHP and perform other manipulations.