How to iterate, by month, between two specified dates

I have a website that was launched a few months ago. I would like to write som开发者_Python百科e 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.

---

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.

---

I have a method which is optimal in results :

$begin = new DateTime( '2014-07-14' );
$end = new DateTime( '2014-08-01' );
$end = $end->modify( '+1 month' );
$interval = DateInterval::createFromDateString('1 month');

$period = new DatePeriod($begin, $interval, $end);

foreach($period as $dt) {
    var_dump($dt->format( "m" ));
}

Hope this will help.

---

If you start from last day of a 30 days month, you may lose some month in between using "+1 month". Try from 2015-06-30 to 2015-12-31 for example, it will skip september 2015.

Instead of using "+1 month", I suggest to use "first day of next month" instead:

$begin = new DateTime( '2015-06-30' );
$end = new DateTime( '2015-12-31' );
$end = $end->modify( 'first day of next month' );
$interval = DateInterval::createFromDateString('first day of next month');

$period = new DatePeriod($begin, $interval, $end);

foreach($period as $dt) {
    var_dump($dt->format( "m" ));
}

this will list september 2015 as well.

---

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.

---

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