通过PHP或mySQL计算时间长度？

Question

In my form users sumbit many events with individual times associated to each one.

e.g. For a Appendectomy the first event Event#1 began at 12:54:33pm and the last even Event#12 happened at 2:23:04pm. So for the Appendectomy there was 12 events.

These events are placed in a seperate table with a foreign key to the event name (Appendectomy).

So if I wanted to calculate the amount of time would it be better to do it through the database or using PHP.

I am unsure because of these reasons:

PHP:

The time formats are strings and I don't think you can subtract strings

The time formats are like this HH:MM:SS which may be complex to split the strings and reconcatenate them later.

MySQL:

The time formats in the the column are actual times not strings Would have to use mysql joins and then find the total events for that procedure. Which would be complex?

Stored procedure?

I am still new to alot of this so I was wondering what your guidance would be on this matter?

Answer 1

With SQL:

SELECT procedure_id, SUBTIME(events_max.start, events_min.start) AS duration
FROM procedure
JOIN ( SELECT procedure_id, SELECT MAX(time) AS start FROM procedure_events GROUP BY procedure_id ) AS events_max USING ( procedure_id )
JOIN ( SELECT procedure_id, SELECT MIN(time) AS start FROM procedure_events GROUP BY procedure_id ) AS events_min USING ( procedure_id )
GROUP BY procedure_id

Would give you the procedure IDs and duration of each procedure as given by the time difference between the start of the first event and the last event.