[Resolved] Difference between two DateTime Fields.

partypete

Hello,

I'm making an app to track employee hours but I've hit a snag. I need to be able to calculate the difference between two datetime fields (signed_in and signed_out) and display the difference in hours and minutes.

So if a record has:

signed_in               signed_out
2003-11-24 07:00:15     2003-11-24 15:30:00

The difference would be 8 hours 29 minutes (and 45 seconds)

LordShryku, recommended I try the following:

SELECT (Signed_Out - Signed_In) 
FROM table

But when I do this, the answer outputted by mysql is a string of numbers which I'm not sure what they represent. They don't seem to be seconds. ie:

2003-11-25 01:25:31  -  2003-11-24 07:02:05  =  942326

I can't use functions such as TIMEDIFF() because my mysql version is only 4.0.15

Does anyone know how to calculate the difference in hours and minutes between two DateTime fields?

Thank you!

Sxooter

Well, this is one of those things you can do easily in postgresql, but mysql looks like it's still not quite done yet. I thought 4.0 did intervals. Hmmm. Maybe it's 4.1 that's supporting them properly now.

Are you sure that mysql is interpreting those fields properly for the math? It might be converting them to varchar first or something weird maybe.

I'd convert both to unix timestamps (i.e. seconds since epoch) and do the math in php for now. Or switch databases.

partypete

Hey Sxooter,

Yeah it looks like mySQL has lots of great time/date functions coming that will make things quite a bit simpler, but not till 4.1.1.

I think I'm going to give up on trying to calculate the difference in MySQL and just use php. I've got the following code in PHP that gives me the difference I'm looking for, but in seconds.

<?php $diff_in_sec = abs(strtotime($row_rs_search_log['punch_out'])-strtotime($row_rs_search_log['punch_in'])); 
		  echo $diff_in_sec; ?>

Now I just have to figure out how to turn seconds into hours and minutes........... off to php.net I go......

Any ideas?

Thanks again, :-)

Sxooter

Sorry, I stopped using MySQL for anything like this a long time ago (I still do some basic content management stuff in it for fun) and switched over to pgsql a long time ago.

It's not hard to do by hand as long as you don't have to worry about the switch to daylight savings, just divide it by 60 to get minutes, 60 more to get hours, 24 to get days and so on. I.e. if you get back 12345 seconds, then you get 205.75 minutes. .75 minutes = 45 seconds, so that's 205 minutes and 45 seconds. To get the 45 seconds, we just multiply the part to the right of the decimial by 60, the number we divided by to get there.

next, we divied the integer portion we got back (i.e.the minutes) by the number of minutes in an hour. 205 divided by 60 = 3.4166667. Again, multiply the .4166667 part by 60 to get the number of minutes, the part to the left will be hours. If the integer portion is >=24 then we now need to divide by 24 in the same manner.

So, for 12345 seconds we get 3 hours, 25 minutes, and 45 seconds.

partypete

Thanks for all your help Sxooter.

I've got it working the way I wanted so alls good now. 🆒 I'll post what I assembled incase anyone else would like to do the same.

<?php $diff_in_sec = abs(strtotime($row_rs_search_log['punch_out'])-strtotime($row_rs_search_log['punch_in']));
$hours = floor($diff_in_sec/3600);
$remaining_seconds = $diff_in_sec - $hours*3600;
$minutes = floor($remaining_seconds/60);
echo $hours;
echo $minutes;
?>

Peter