CODEWITHSUNDEEP
mysqlsql
sinesine
sinesine

Reputation: 183

Incorrect format in cumulative sum of time column

I have a query that allows me to view a running total of a certain time column in my database, by using a user-defined variable:

SET @total_duration := '00:00:00';
SELECT duration, (@total_duration := @total_duration + duration) AS cumulative_duration
FROM tbl_flights

However, the results appear like this:

enter image description here

I'm pretty sure the running total is correct, but that it just needs formatting to appear like hh:mm:ss. I'm not sure how to do this, if anyone could help me out it would be greatly appreciated.

Upvotes: 1

Views: 38

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

Try using time_to_sec and sec_to_time functions:

The order by is required to get consistent results. Assuming you want to find this cumulative sum in the increasing order of one or more columns , say flight_id:

SET @total_duration := 0;
SELECT 
    duration,
    sec_to_time(@total_duration := @total_duration
                 + time_to_sec(duration)) AS cumulative_duration
FROM tbl_flights
ORDER BY flight_id -- required to get consistent results
                   -- (change the column name in "order by" as needed.)

Upvotes: 3

Related Questions