Jump to content


Photo

Grouping hours by day of week and time of day


  • Please log in to reply
4 replies to this topic

#1 jntcomputers

jntcomputers
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 25 April 2006 - 05:50 PM

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?


#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 25 April 2006 - 05:53 PM

A sample of the data would help. Are those hours aggregated totals?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Mortier

Mortier
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 25 April 2006 - 05:54 PM

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?

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 25 April 2006 - 06:13 PM

Without more info,

$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/>";
}

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 jntcomputers

jntcomputers
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 25 April 2006 - 09:40 PM

$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

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users