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.

[Fumigator]

You need to use TIMESTAMPDIFF() for this, as it returns an integer value which represents the difference in number of units (unit being second, minute, month, etc). TIMEDIFF() returns a time value-- HH:MM:SS, which can't be used with SUM().

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_timestampdiff

[ro1960]

Thanks this works. But I guess I wasn't clear enough in my request. I am trying to get two sums in my HTML table: one sub-total per category and one grand total for all categories displayed. I am attaching a screenshot of the output and my full code. For example, the first category "DS" should have a sub-total of 06:30:00 and the grand total of all rows should be 11:30:00 (04:30:00 + 02:00:00 + 01:00:00 + 04:00:00). Is my code suited for this or should I look into a different solution?

http://roproductions.com/Picture1.png

$query = mysql_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') $condition ORDER BY date_start");


// initialize header and set it to an empty string
$header = '';
while ($row=mysql_fetch_assoc($query))
{

// we only want to print the header if it hasn't been seen in the result yet
if ($header!=$row['category'])
{
// printing blank row for legibility
print "<tr bgcolor=\"#eeeeee\"><td colspan=\"4\"></td></tr>";
print "<tr bgcolor=\"#666666\"><td colspan=\"4\"><b>".$row['category']."</b></td></tr>\n";
// means the current header display is for $row['category']
$header = $row['category'];
}
print "<div style=\"margin-bottom:8px\">";

print "<tr bgcolor=\"#cccccc\"><td>".$row['Fdate_start']."</td><td>".$row['calc_duration']."</td><td>".$row['type']."</td><td>".$row['detail']."</td></tr>";
}
?>

[Fumigator]

Yes I understood. You need to use the SUM() function on time values, as durations, not as time values. You can't use the SUM() function on the result of the TIMEDIFF() function, so you need to use the TIMESTAMPDIFF() function instead.

Now, you can STILL select a TIMEDIFF() value to display on each row. But you also need to select TIMESTAMPDIFF() to do your summations. Just keep in mind that SUM() will not give you the results you expect on a time value.

Since you are using PHP, you can of course also use PHP functions to sum up the durations.

[ro1960]

Sorry I had to set this project aside for a while.

So I used your recommandation and modified my query as follows:

SELECT DATE_FORMAT(date_start, '%d/%m/%Y') AS Fdate_start, TIMEDIFF(date_end,date_start) AS calc_duration, SUM(TIMESTAMPDIFF(SECOND,date_start,date_end)) AS time_diff, type, type_detail, category FROM `delegation` WHERE category='DP' AND (`date_start` >= '$date_start' and `date_end` <= '$date_end') $condition ORDER BY date_start

I am able to get the sum as seconds, but only one row is displayed in my results, the individual results are gone. If I remove SUM(TIMESTAMPDIFF(SECOND,date_start,date_end)) AS time_diff from the query, I get all the matching rows.

How can I get each matching row and the sum?

[Old Pedant]

Read again what Fumigator wrote:

Since you are using PHP, you can of course also use PHP functions to sum up the durations.

So something like this:
SELECT DATE_FORMAT(date_start, '%d/%m/%Y') AS Fdate_start,
TIMEDIFF(date_end,date_start) AS calc_duration,
TIMESTAMPDIFF(SECOND,date_start,date_end)) AS time_diff,
type, type_detail, category
FROM `delegation`
WHERE category='DP'
AND (`date_start` >= '$date_start' and `date_end` <= '$date_end')
$condition
ORDER BY date_start

Then, in your PHP code, as you read each record in a loop:

$total_timediff = 0;
while ( ... in the loop ... )
{
$total_timediff += value_from_record["time_diff"];
...
}
... dump out $total_timediff in format you want using PHP ...

[ro1960]

Thanks for your reply. What are you considering as value_from_record?

[Old Pedant]

Means I don't know PHP, so it's whatever code you use to get the value of a field from one record of the query.

In ASP it would be something like
total_timediff = total_timediff + RS("time_diff")

But I don't know the syntax for the equivalent PHP.