PHP Difference between MySql TIME fields

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

Hi all.

I have two time fields in my database: "start_time" and "stop_time". These fields track the time a machine worked (hh:mm:ss). There is a view page where I'd like to display the time difference between these two for the user. Subtracting them from each other just gives "0".

So let's imagine that:
PHP Code: [ Select ]
$start_time = '128:12:05';
$stop_time = '129:15:10';
  1. $start_time = '128:12:05';
  2. $stop_time = '129:15:10';


I'd like to display the following to the user:
Machine X worked 01:03:05 today.

Hopefully someone can help.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9089
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

I am a little confused on the $start_time and $stop_time variables you have there. I am thinking that they are really the start time and the stop time and they only track the time a machine has worked once you subtract them correct?

I think the reason I am confused is because the hours are so high in the start time and stop time, which makes it appear they are actually just accumulating hours, but I am not sure.

I need some clarification a bit, as I was going to suggest you use unix timestamps instead as those are easier to work with in my opinion.

Subtracting those two numbers give you a 0 because they are not real numbers, they are just strings. It would be like subtracting "some sentence" from "another sentence". You would likely end up with 0 there as well.

If you want to subtract numbers in that format you are going to likely need to create a function to deal with it. For example:

PHP Code: [ Select ]
function SubtractTime($start, $end) {
   list($startHours,$startMins,$startSecs) = explode(":", $start);
   list($endHours,$endMins,$endSecs) = explode(":", $end);
   return (sprintf("%02d", $endHours - $startHours) . ':' . sprintf("%02d", $endMins - $startMins) . ':' . sprintf("%02d", $endSecs - $startSecs));
}
  1. function SubtractTime($start, $end) {
  2.    list($startHours,$startMins,$startSecs) = explode(":", $start);
  3.    list($endHours,$endMins,$endSecs) = explode(":", $end);
  4.    return (sprintf("%02d", $endHours - $startHours) . ':' . sprintf("%02d", $endMins - $startMins) . ':' . sprintf("%02d", $endSecs - $startSecs));
  5. }
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9089
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

After further research it actually looks like MySQL has a function for dealing with subtracting time. So you could avoid PHP altogether by simply selecting the time like this for instance:

SQL Code: [ Select ]
SELECT SUBTIME('129:15:10','128:12:05');


You can see it worked:

Code: [ Select ]
mysql> SELECT SUBTIME('129:15:10','128:12:05');
+----------------------------------+
| SUBTIME('129:15:10','128:12:05') |
+----------------------------------+
| 01:03:05             |
+----------------------------------+
1 row in set (0.01 sec)
  1. mysql> SELECT SUBTIME('129:15:10','128:12:05');
  2. +----------------------------------+
  3. | SUBTIME('129:15:10','128:12:05') |
  4. +----------------------------------+
  5. | 01:03:05             |
  6. +----------------------------------+
  7. 1 row in set (0.01 sec)


You would instead use selected fields, but the above works as well if you hardcode the time like I did.

So it looks like you have two options, one via PHP, and one via MySQL now ;)
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

Ah, thanks so much, that is what I was looking for, the MySql version will be better for me.

And yeah, this is a daily report that needs to be filled in and will just track how many hours a machine has worked. So yeah, I just track hours.

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 88 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.