0

Im trying to get hold of how to get the difference between two mysql varchar strings they are of the format mm:ss where m stands for minutes and s for seconds. So for instance.

$a = 13:20;
$b = 13:00;

what function do I use so that

 $a - $b =  00:20 ?

$a = strtotime('13:20');
$b = strtotime('13:00');
echo "The time difference between 13:20 and 13:00 is : "  . ($a - $b);

Gives me 
The time difference between 13:20 and 13:00 is : 1200

Kindly take note of my original format. mm:ss
Alain P
  • 53
  • 1
  • 5
  • What you are providing is in hour format.... – Malik Naik Jul 05 '15 at 00:33
  • I guess so but unfortunately thats how its found in the database. How do I work around this. Thanks – Alain P Jul 05 '15 at 00:35
  • Show me how it's stored in your database.. – Malik Naik Jul 05 '15 at 00:37
  • I have a column entries like 18:30 , 66:15, 17:40, 16:59, 90:48 etc etc all in the form of mm:ss . Thanks – Alain P Jul 05 '15 at 00:42
  • [Convert `min:sec` format to seconds](http://stackoverflow.com/questions/4834202/convert-hhmmss-to-seconds-only/20874702#20874702), subtract seconds, and then [convert seconds back to `min:sec` format](http://stackoverflow.com/questions/3172332/convert-seconds-to-hourminutesecond/20870843#20870843). – Glavić Jul 05 '15 at 06:27

1 Answers1

0

Use strtotime()

Try this...

$time1 = explode(':', '13:20');
$time2 = explode(':', '13:00');

$a = 00 . ':' . $time1[0] . ':' . $time1[1];
$b = 00 . ':' . $time2[0] . ':' . $time2[1];

echo "The time difference between 13:20 and 13:00 is : "  . (strtotime($a) - strtotime($b));
Malik Naik
  • 1,472
  • 14
  • 16
  • $a = strtotime('13:20'); $b = strtotime('13:00'); echo "The time difference between 13:20 and 13:00 is : " . ($a - $b); Gives me The time difference between 13:20 and 13:00 is : 1200 Kindly take note of my original format. mm:ss – Alain P Jul 05 '15 at 00:26
  • `00:13:20` and `00:13:00` because if you use `13:20` and `13:00` it reads as `13:20:00` and `13:00:00` it returns 1200... – Malik Naik Jul 05 '15 at 00:29
  • Okay Thanks will give it a try – Alain P Jul 05 '15 at 00:51
  • It works , now how do I use that in a mysql to query a Database ? Thanks – Alain P Jul 05 '15 at 00:54
  • change `explode(':', '13:20')` with `explode(':', $row['time'])` – Malik Naik Jul 05 '15 at 00:55
  • Thanks will put that to work . Very Grateful – Alain P Jul 05 '15 at 00:59
  • Don't forget to mark this as answer.. – Malik Naik Jul 05 '15 at 01:00
  • "It works , now how do I use that in a mysql to query a Database" – David Soussan Jul 05 '15 at 01:15
  • You can do this in an SQL query thus: SELECT TIME(REPLACE(`col`, ':', '')). What this does is to strip the colon(:) out of the varchar value so that TIME() will parse it as minutes and seconds. You can then use the native date functions in your query to calculate the diference between 2 times. SELECT TIMEDIFF(TIME(REPLACE(`time1`)), TIME(REPLACE(`time1`))). That will give you the difference between the 2 times. I believe that was the question you were asking. I would add an answer except that this has been marked as a duplicate. – David Soussan Jul 05 '15 at 01:29
  • And that is a typo, should be SELECT TIMEDIFF(TIME(REPLACE('13:20',':','')), TIME(REPLACE('13:00',':',''))). Of course you would substitute the actual columns for the string literals in my example. – David Soussan Jul 05 '15 at 01:40
  • Will also give that a try. Thanks will let you know how it goes. – Alain P Jul 05 '15 at 01:49