[RESOLVED] Iterating through hours between 2 dates

skip

Hi guys,

I am trying to iterate through the hours between 2 dates so I can count the number of entries in a database table made between each hour of any given day * time up to now + 1

I have my start and end dates like this:

$starttime = date('Y-m-d 00:00:01'); // I want this so it starts at the very begining of the day 1 sec pas midnight
$endtime data('Y-m-d H:i:s', strtotime("+1 hour")); // this is the current hour we are in

I can work out how many hours have passed between my start and end times

$diff = floor((strtotime($endtime)-strtotime($starttime))/3600);

from this info I want to iterate through each hour block from stattime to endtime to get 2 date times so I can run a sql query to count between those 2 times like:

$query = mysql_query("select count(id) as myCount from table where dateField  between '2012-05-03 00:00:01 and '2012-05-03 01:00:00'"); // this would be the start of the loop 
// then we would need to start at the last end time and + an hour

$query = mysql_query("select count(id) as myCount from table where dateField  between '2012-05-03 01:00:00 and '2012-05-03 02:00:00'"); // and so on until endtime

I hope I made sense and that someone can help.

Thanks in advance.

Derokorian

How about something like this (untested):

SELECT COUNT(*) as myCount, TIMESTAMP( CONCAT( DATE(MIN(dateField)),' ',HOUR(MIN(dateField)),':00:00' ) ) as myTime FROM myTable GROUP BY CONCAT( DATE(dateField), HOUR(dateField) )

Should return the counts for each hour, and the hour for that count. This is better than running a query for each and every hour.

Edit: This would count every row in the DB, to get only rows between start and end just use a WHERE clause like you have already:

SELECT COUNT(*) as myCount, TIMESTAMP( CONCAT( DATE(MIN(dateField)),' ',HOUR(MIN(dateField)),':00:00' ) ) as myTime FROM myTable WHERE dateField BETWEEN '$startTime' AND '$endTime' GROUP BY CONCAT( DATE(dateField), HOUR(dateField) )

Just don't forget to make sure the start and end times are formatted properly!

skip

Hi Thanks for the reply is there anyway I could get it to return 0 counts too so I get a count for each hour between those dates even if there is no count?

skip

I am not sure I know what you mean could you provide a demo?