Grouping hours by day of week and time of day

[jntcomputers]

I am working on a query of scheduled times and want to be able to display the results like this:



Saturday Afternoon: 35 hours

Sunday Morning: 32.5 Hours

Sunday Afternoon: 29 Hours

Saturday Evening: 21 Hours

(Morning: 6 a - 12p/Afternnon: 12:01p - 4p/Evening:4:01p - 8p)


And so on. I have gotten the query to pull the hours, I can post the query if needed. Any ideas?

[Barand]

A sample of the data would help. Are those hours aggregated totals?

[Mortier]

Yes post the query please because I can't quite understand what you're getting to.

Are you calculating the time between 2 dates? And how many hours there's left?

[Barand]

Without more info,

[code]$sql = "SELECT WEEKDAY(datecol) as dow,
       (CASE
            WHEN DATE_FORMAT(datecol, '%H%i) BETWEEN '0600' AND '1200' THEN 'morning'
            WHEN DATE_FORMAT(datecol, '%H%i) BETWEEN '1201' AND '1600' THEN 'afternoon'
            WHEN DATE_FORMAT(datecol, '%H%i) BETWEEN '1601' AND '2000' THEN 'evening'
            END
       ) as period
       SUM(hours) as total
       FROM mytablename
       GROUP BY dow, period";
$res = mysql_query($sql) or die (mysql_error());

while (list($dow, $per, $tot) = mysql_fetch_row($res)) {
       echo "$dow $per $tot hours<br/>";
}[/code]

[jntcomputers]

[code]$sql2 = "SELECT start_time, end_time FROM default_ScheduleEvents  WHERE (default_ScheduleEvents.aircraft_id = $this_aircraft_id)";
$recordSet2 = $conn->Execute($sql2);
if ($recordSet2 === false)
{
log_error($sql2);
}
$num_records = $recordSet2->RecordCount();
if ($num_records > 0) {
$hours_array = array();
while (!$recordSet2->EOF)
{
$st_time = strtotime(make_db_unsafe ($recordSet2->fields[start_time]));//start time in unix stamp
$en_time = strtotime(make_db_unsafe ($recordSet2->fields[end_time]));//end time in Unix stamp            
$this_date = DATE("M-d-Y",$st_time);
$flight = ($en_time - $st_time)/3600; //Flight time in hours
$day_of_week = date("l", $st_time);
if(isset($hours_array[$time_slot])){
//...add the current hour value to the
//existing value..
$hours_array[$day_of_week] += $flight;
} else {
//if the name isn't there, then add it and
//give it the value of the hours
$hours_array[$day_of_week] = $flight;
}
echo "<tr><td>$day_of_week</td><td>Time of day</td><td>$hours_array[$day_of_week]</td></tr>";
$recordSet2 ->MoveNext();
} // end if number > 0[/code]

This is what I have so far. This outputs the total hours scheduled for the day of the week. What I need is a way to take the hours a subdivide them into time of day (i.e. morning, afternoon, evening). The code I have may be no good to do this. The thing to remember is that a reservation might be made between two times, for example a reservation made from 8am to 2pm would be 4 hours in "Morning" and 2 hours in "Afternoon".