CODEWITHSUNDEEP
mysqldatetimetimesum
Clarus Dignus
Clarus Dignus

Reputation: 3897

Data type and sum time duration

What I have:

A MySQL table consisting of a "duration" column.

05:00:00
00:30:00
00:30:00
00:15:00
01:45:00
00:30:00
01:30:00
02:30:00
01:00:00
01:30:00

Data Type:

I first used TIME but from what I've researched, TIME is for storing a point in time rather than a duration of time. TIME also represents a limitation in that it can't exceed 23 hours.

I've instead opted for the DATETIME data type. My column now looks as follows:

0000-00-00 05:00:00
0000-00-00 00:30:00
0000-00-00 00:30:00
0000-00-00 00:15:00
0000-00-00 01:45:00
0000-00-00 00:30:00
0000-00-00 01:30:00
0000-00-00 02:30:00
0000-00-00 01:00:00
0000-00-00 01:30:00

Sum:

I need the total duration. I.e. SUM of the "duration" column in HH:MM:SS.

Summing the column produces "134000".

SELECT sum(`duration`) FROM `my_table`

My question:

How do I sum the duration column in a HH:MM:SS format?

Upvotes: 0

Views: 134

Answers (1)

Samir Selia
Samir Selia

Reputation: 7065

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`duration`)));

TIME_TO_SEC - will convert datetime to seconds. Then the SUM in your case will be 54000

SEC_TO_TIME - convert it back to datetime format

Output

15:00:00

Upvotes: 2

Related Questions