[Resolved] difference in time

robsgaming

I'm trying to create a simple time clock system in mysql. I have two colums in a row, timein and timeout. Both are timestamp 14 which gives the following format:

Year, Month, Day, Hour, Minutes, Seconds
20040225025453

What I'm trying to do is figure out the number of minutes between the two timestamps. Let's say I have clock in at

20040225025453

and clock out at

20040225025930

I want to do something where the system can automatically generate the number of minutes between the two timestamps.

Thanks in advance for any help.

YappyDog

You'll probably need to break up your timestamp into the individual pieces, and then use the mktime() function to convert it to a Unix timestamp. When you've done that for both timein and timeout you can subtract them to find the elapsed number of seconds.

http://us2.php.net/manual/en/function.mktime.php

LordShryku

Or convert it in the SQL using unix_timestamp. Or you could also use timediff...
http://www.mysql.com/doc/en/Date_and_time_functions.html

robsgaming

Thanks for the prompt reply. Do you have any suggestions on how to break up the current 14 timestamp?

YappyDog

the substr() function?

http://us2.php.net/manual/en/function.substr.php

robsgaming

actually, i think i figured out a much easier way to get the number of seconds:

$sql = "SELECT DATE_FORMAT(timein, '%m-%d-%Y %H:%i'), DATE_FORMAT(timeout, '%m-%d-%Y %h:%i'),clockStatus, TIME_TO_SEC( DATE_FORMAT( timeout, '%T' ) ) - TIME_TO_SEC( DATE_FORMAT( timein, '%T' ) ) AS timediff FROM timeClockPunches WHERE username = '$userName'";

So far, it seems like its working. I then take the number of seconds and divide by 60 to give the total number of minutes. Finally, I round the number.

Does this seem valid?

robsgaming

SWEET! I don't even need to round anything. I changed the date_format from %T to %H:%i and now it calculates the minutes.

If anybody needs to subtract time from 2 colums in a row in MySQL I believe this will work for you as well... of course you'll have to do a little customizing to match your table, rows, etc...

$sql = "SELECT DATE_FORMAT(timein, '%m-%d-%Y %H:%i'), DATE_FORMAT(timeout, '%m-%d-%Y %h:%i'),clockStatus, TIME_TO_SEC( DATE_FORMAT( timeout, '%H:%i' ) ) - TIME_TO_SEC( DATE_FORMAT( timein, '%H:%i' ) ) AS timediff FROM timeClockPunches WHERE username = '$userName'";

Thanks for the help guys.