Calculating the sum of time differences

[ro1960]

I have the following query:

SELECT DATE_FORMAT(date_start, '%d/%m/%Y') AS Fdate_start, TIMEDIFF(date_end,date_start) AS calc_duration, type, type_detail, category FROM `delegation` WHERE (date_start >= '$date_start' and date_end <= '$date_end')

I want to be able to add all the calc_duration in the results as total_duration for example.

I tried some solutions with SUM but got nowhere.

[rocknbil]

I thought someone else might have picked this up . . . there is only one way I know of to do this.

The internal date/time math functions work only with valid date/time formats. It's awesome for single row calcs or comparison of two static date values, but when you want to add them across multiple records it gets sticky.

You could do a convoluted date math in a while loop, incrementing and converting values as you traverse matching records, but it would indeed be convoluted.

The only way I know of to do this is to use unix_timestamp() [dev.mysql.com]. The Unix timstamp for any given date is the number of seconds since January 1, 1970. Since it's seconds, this makes it an integer and it's very easy to do simple math on integers.

so you would probably extract the records as a unix_timestamp, do your basic math, possibly

sum (unix_timestamp(date_end) - unix_timestamp(date_start)) as tot_time [where clause] [group clause]

You might need a group by clause for sum

Then when you're done, convert the total seconds to an hour format using sec_to_time() [dev.mysql.com] (or some PHP function to do the same thing.)

You might even get it in one statement:

sum sec_to_time(unix_timestamp(date_end) - unix_timestamp(date_start)) as tot_time [where clause] [group clause]

Sorry I don't have sample code handy, it's been a while since I did this but this should at least get you started.

Edited: Not from_unix_time(), this gives a date. Use sec_to_time() for the total time.

[ro1960]

Thanks, I'll look into your solution.