Summing up total class duration for multi-day courses

Courses
1

Hey folks, I’m working on a project that involves tracking course schedules. I’ve got a MySQL table called class_schedule that stores info about different classes, including some that span multiple days. I’m trying to figure out how to add up the total hours and minutes for each course.

Here’s a sample of what the table looks like:

class_id | start_time           | end_time
101      | 2023-09-15 09:30:00  | 2023-09-15 12:30:00
101      | 2023-09-15 14:00:00  | 2023-09-15 17:00:00
101      | 2023-09-16 09:30:00  | 2023-09-16 12:30:00
101      | 2023-09-16 14:00:00  | 2023-09-16 17:00:00
101      | 2023-09-17 09:30:00  | 2023-09-17 12:30:00

I’m using PHP for my project, but I’d love to know if there’s a way to do this calculation directly in MySQL. Any ideas on how to tackle this? Thanks a bunch for your help!

2

Hey Ava_Books! That’s a cool project you’re working on. :blush: I’m curious, have you thought about how you’ll handle courses that might have irregular schedules? Like, what if a course has different durations on different days?

I totally get why you’d want to do this in MySQL - it’s super efficient! But I wonder, wouldn’t it be fun to explore some PHP solutions too? Maybe we could come up with something that gives you more flexibility for future features?

Oh, and random thought - are you planning to show this total duration to students? It might be neat to give them a heads up on how much time they’re committing to each course. Just a thought!

Anyway, I’m really interested to see how this turns out. Keep us posted on what solution you end up going with, okay? Good luck with your project!

3

To calculate the total duration for multi-day courses directly in MySQL, you can use the TIMEDIFF() function along with SUM(). Here’s a query that should do the trick:

SELECT 
    class_id,
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)))) AS total_duration
FROM 
    class_schedule
GROUP BY 
    class_id;

This query first calculates the difference between end_time and start_time for each session, converts it to seconds, sums up all the differences for each class, and then converts the result back to a time format.

If you need to break it down into hours and minutes, you can modify the query like this:

SELECT 
    class_id,
    FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time))) / 3600) AS total_hours,
    FLOOR((SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time))) % 3600) / 60) AS total_minutes
FROM 
    class_schedule
GROUP BY 
    class_id;

This approach should give you the total duration for each course, handling multi-day schedules efficiently within MySQL.

4

yo ava, have u thought bout using PHP’s DateTime class? it’s pretty sweet for handlin time stuff. u could loop thru ur results and add up the differences. might be more flexible if ur project grows. just a thought! lemme know if u want more details on that approach