CODEWITHSUNDEEP
mysql
ayayaya
ayayaya

Reputation: 25

How to add time values to get the total for a row?

Say I have a schedule table like this:

+------------+----------+---------+--------------+----------+----------+-----------+----------+----------+----------+-----------+----------+----------+-----+
| MonStart | MonEnd   | TuesStart | TuesEnd  | WedStart | WedEnd   | ThurStart | ThurEnd  | FriStart | FriEnd   | SatStart | SatEnd   | SunStart | SunEnd   |
+------------+----------+---------+--------------+----------+----------+-----------+----------+----------+----------+-----------+----------+----------+-----+
| 08:00:00 | 15:00:00 | 08:00:00  | 15:00:00 | 09:00:00 | 16:00:00 | 09:00:00  | 15:00:00 | 10:00:00 | 16:00:00 | 06:00:00 | 08:00:00 | 08:00:00 | 10:00:00 |
+------------+----------+---------+--------------+----------+----------+-----------+----------+----------+----------+-----------+----------+----------+-----+

Basically its just a schedule of when someone works and I would like to get the total hours worked for that week. How would I go about doing that? So I can do subtime for each day then convert that into seconds. Thats about how far I can get right now. I dont know how to then add each of those together.

Upvotes: 0

Views: 26

Answers (1)

forpas
forpas

Reputation: 164174

You can do it with the functions TIMEDIFF() and TIME_TO_SEC():

SELECT
  TIME_TO_SEC(TIMEDIFF(MonEnd, MonStart)) +  
  TIME_TO_SEC(TIMEDIFF(TuesEnd, TuesStart)) +  
  TIME_TO_SEC(TIMEDIFF(WedEnd, WedStart)) +  
  TIME_TO_SEC(TIMEDIFF(ThurEnd, ThurStart)) +  
  TIME_TO_SEC(TIMEDIFF(FriEnd, FriStart)) +  
  TIME_TO_SEC(TIMEDIFF(SatEnd, SatStart)) +  
  TIME_TO_SEC(TIMEDIFF(SunEnd, SunStart)) week_total
FROM tablename

See the demo.
Results:

| week_total |
| ---------- |
| 133200     |

Upvotes: 1

Related Questions