CODEWITHSUNDEEP
mysqltime
Sri
Sri

Reputation: 39

Mysql sum two column timestamps

I have columns Mon, Tue, wed, thu, fri, sat, sun with timestamps for every day to a project in times table

I need to get total sum of hours spent by adding all columns for a project.

My actual query is like this -

My Actually query is like this - SELECT time(SEC_TO_TIME(SUM(TIME_TO_SEC(fri))) + SEC_TO_TIME(SUM(TIME_TO_SEC(thu))) + SEC_TO_TIME(SUM(TIME_TO_SEC(tue)))) FROM timesheets WHERE project_id = 5

Could anyone help me on writing exact query to fetch all the columns time and sum as a total

Thanks in advance

Upvotes: 0

Views: 3584

Answers (3)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

I don't think there exists a time function that takes more than two arguments to sum them.
Following snippet may be helpful to you: 

select 
    addtime( mon, addtime( tue, addtime( wed, addtime( thu, 
             addtime( fri, addtime( sat, sun ) ) ) ) ) ) total_time_spent

Above query returns total time spent in hours:minutes:seconds format.
To select only hours spent in total, use time_format function. 

select time_format( total_time_spent, '%H' ) total_hours_spent

Note that this query only returns hours part of the total time spent. You will loose minutes and seconds if any exists.

Update:
Following snippet can give you total hours and minutes spent. 

select 
    time_format( 
        addtime( mon, addtime( tue, addtime( wed, addtime( thu, 
            addtime( fri, addtime( sat, sun ) ) ) ) ) ),
        '%H:%i' ) total_hours_and_mins_spent

Upvotes: 1

Olivier Coilland
Olivier Coilland

Reputation: 3096

Have you tried ?

SELECT ADDTIME(time1, time2);

Upvotes: 2

jkrcma
jkrcma

Reputation: 1202

There is a very long chapter in MySQL documentation about dates and times including parsing, types, conversion and formats.

Cast your columns to TIME type and benefit from it:

SELECT Mon + Tue

Upvotes: 0

Related Questions