How calculate the difference of time (+/-) between each row?

Question

I'm echoing my MYSQL database using PHP and trying to calculate the difference of "time" in each row for the column "time". I have "time" stored in TIME format HH:MM:SS (00:00:00) in the MYSQL database. Example of the output I'm trying to achieve:

Saved On | Time (MM:SS) | + / - Time (MM:SS)
----------------------------------
TIMEDATE | 00:11       | + 00:05 |
----------------------------------
TIMEDATE | 00:06       |  00:00  |
----------------------------------
TIMEDATE | 00:06       | - 1:00  |
----------------------------------
TIMEDATE | 00:07       |  00:00  |
----------------------------------

My current mysql query + php:

$result = mysql_query("SELECT * FROM timer WHERE user_id = '$userid' ORDER BY savedon DESC") 
or die(mysql_error());  

echo "<table class='sortable'>";
echo "<tr> <th>Saved On</th> <th>Time (MM:SS)</th> <th>+/-</th> </tr>";
    while($row = mysql_fetch_array( $result )) {
echo "<tr><td>";
echo $row['savedon'];
echo "</td><td>"; 
echo $row['time'];
echo "</td><td>"; 
echo $row['time']; // My issue is right here. I have no clue on how to calculate the +/- difference of times
echo "</td></tr>";  

}

echo "</table>";

My issues:

I'm trying to format the time to drop the hour prefix. Example: 00:06 (MM:SS) instead of 00:00:06 (HH:MM:SS).
Each row should calculate the difference of times as demonstrated above.

I'm not sure if this should be handled with MYSQL in the query or with PHP. Any help would be appreciated.

score 1 · Accepted Answer · answered Feb 21 '15 at 06:53

1

I think that is better make this in PHP

Make a loop and inside you can use something like this:

$one = new DateTime($firstDate);
$two = new DateTime($secondDate);
$interval = $two->diff($one);

echo $interval->format('%I:%S');

http://php.net/manual/en/dateinterval.format.php

answered Feb 21 '15 at 06:53

Adrian Cid Almaguer

7,815
13
41
63

How would I do this with $row['time'];? I'm guessing using `for`? Sorry I haven't done this before. – michelle Feb 21 '15 at 21:46
how you calculate the diference? – Adrian Cid Almaguer Feb 21 '15 at 21:52
yeah I'm still confused sorry to sound dumb – michelle Feb 21 '15 at 22:19
what represent the column +/-? – Adrian Cid Almaguer Feb 21 '15 at 22:22
The difference between rows Time (MM:SS) – michelle Feb 21 '15 at 22:25
which rows? Can you explain? – Adrian Cid Almaguer Feb 21 '15 at 22:27
If you look at the table above there's an example of +/- of the Column time. I just edited the table to make it clearer. – michelle Feb 21 '15 at 22:31
ok, at the first row, how you obtain the value + 00:05 in the third column – Adrian Cid Almaguer Feb 21 '15 at 22:35
There is a + 00:05 increase from 00:06 to 00:11 – michelle Feb 21 '15 at 23:11

score 0 · Answer 2 · answered Jan 20 '16 at 09:43

0

Can use subselect if need time difference between [id] and [id before id].

SELECT TIMEDIFF(t1.time_row, (
    SELECT time_row FROM tablename t2 WHERE t2.id < t1.id ORDER BY id DESC LIMIT 1)
  )
FROM tablename t1

answered Jan 20 '16 at 09:43

Ondřej Machala

133
1
6

How calculate the difference of time (+/-) between each row?

2 Answers2