CODEWITHSUNDEEP
phpmysql
piyush
piyush

Reputation: 321

How to calculate Total hours using query in mysql?

I have field named "Time" in my table. I want to calculate total hours.

Time
3:00:00
4:00:00
5:00:00

Output should be 2hr. How can I achieve this? Please help me to solve it.

I tried below query but it did not work as expected.

SELECT  SEC_TO_TIME( SUM( TIME_TO_SEC( `timeSpent` ) ) ) AS timeSum  
FROM YourTableName

Upvotes: 0

Views: 528

Answers (2)

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

suppose your table have an id as a primary key auto incremental, so you query will be:

SELECT FLOOR(sum( abs( SEC_TO_TIME(TIME_TO_SEC(t1.`timeSpent`))  -  SEC_TO_TIME(TIME_TO_SEC(t2.`timeSpent`)) )/10000)) as total 
FROM YourTableName t1
inner join YourTableName t2 
on t1.id = t2.id + 1;

Upvotes: 0

ulentini
ulentini

Reputation: 2412

If you need the maximum time span between values, then try with:

SELECT MAX(timeSpent) - MIN(timeSpent) FROM YourTableName

Upvotes: 2

Related Questions