Search code examples
mysqldatetimeadditionhour

How to add hours matching a mysql query?


Data (Table name: T1):

Teacher Subject Day Hour
Albert Blue Wednesday 10:00
Albert Blue Wednesday 12:00
Brandon Red Tuesday 09:00
Brandon Red Tuesday 11:00
Albert Cyan Monday 08:30
Albert Cyan Monday 10:30
Claudia Gray Thursday 08:00
Claudia Gray Thursday 10:00
Albert Pink Friday 13:00
Albert Pink Friday 14:30
Martha Green Wednesday 12:00
Martha Green Wednesday 14:00
Albert Yellow Friday 11:00
Albert Yellow Friday 12:30

As it can be seen, there is a record for the starting hour of a Subject and another for the finishing time of the same Subject (data comes like that). What I intend to know is the weekly amount of hours dedicated to classes by a specific teacher, let's say for "Albert".

Query:

$result = mysqli_query($link, "SELECT SUBTIME(max(Hour), min(Hour)) AS TeachTime, Subject FROM T1 WHERE Teachers LIKE '%Albert%' GROUP BY Subject ORDER BY Subject ASC") or die(mysqli_error($link));
if (mysqli_num_rows($result)!==0) {
echo "Weekly teaching time: ";
while($row = mysqli_fetch_array($result)){
echo $row['TeachTime']." Hrs. ";
}
}

Output:

Weekly teaching time: 02:00 Hrs. 02:00 Hrs. 01:30 Hrs. 01:30 Hrs.

Desired output:

Weekly teaching time: 07:00 Hrs.

As you can see, I don't know how to perform the addition of every resulting amount of hours. How can I achieve that?

I have also tried GROUP BY Teachers but results are weird... not the addition result.


Solution

  • This is the code I end up using thanks to the guidance of Bill Karwin and some more research:

    $result = mysqli_query($link, "SELECT *,
    SEC_TO_TIME(SUM(TIME_TO_SEC(WeekTime))) AS ProfTime FROM
    (SELECT *,
    SUBTIME(max(Hour), min(Hour)) AS WeekTime FROM T1
    WHERE Teacher LIKE '%Albert%' GROUP BY Teacher, Subject) AS C")
    or die(mysqli_error($link));
    if (mysqli_num_rows($result)!==0) {
    echo "Weekly teaching time: ";
    while($row = mysqli_fetch_array($result)){
    echo date('H:i', strtotime($row['ProfTime']))." Hrs. <br>";
    }}
    

    Bill's answer had made me realize that you can query results from an embedded query, e.g.:

    Query2 over results of (Query1 over data)

    Then, as results of query1 were in time format (HH:MM), I should get a total of the addition of all the instances retrieved, i.e., 01:00 + 02:00 + 01:30 = 04:30

    The addition has to be performed in query2 and I have found on tutorialspoint that the query SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(Time))) AS TotalTime does that.

    Finally, as the result comes in the the format HH:MM:SS and I only need HH:MM I applied a php convertion to the output: date('H:i', strtotime($row['TotalTime'])).