MySQL total login hours become negative in the afternoon

Question

I'm storing users total login hours which I achieve by first storing the login time during the login and then on logout I store the time_out. Then to find total login hours, I use the following code. It all looked fine during the morning hours but now in the afternoon, the total count and time becomes negative.

Here is my login.php code which stores the first login details to hours table:

date_default_timezone_set('America/New_York');
$chk = date("Y-m-d");
$result = mysql_query("SELECT * FROM hours WHERE member_id = '".$_SESSION['MEMBER_ID']."' AND member_name = '".$_SESSION['LOGIN_NAME']."' AND time_in LIKE '%".$chk."%'");
$dt = date("Y-m-d");
if(mysql_num_rows($result) == '0'){
mysql_query("INSERT INTO hours (member_id, member_name, team, time_in) VALUES ('".$_SESSION['MEMBER_ID']."', '".$_SESSION['LOGIN_NAME']."', '".$_SESSION['TEAM']."', '".$dt."')");
}

now the logout.php so update time on logout

$id = $_SESSION['MEMBER_ID'];
date_default_timezone_set('America/New_York');
$dt = date("Y-m-d h:i:s");  
$max = mysql_query("SELECT MAX(hours_id) FROM hours WHERE member_id = '".$id."'");
$row = mysql_fetch_row($max);
$sql = "UPDATE hours SET time_out = '".$dt."' WHERE hours_id = '".$row[0]."'";
mysql_query($sql) or die(mysql_error());

And here is how I check a users total login hours for the day or entire month:

<?php 

$query = "select member_id, member_name, team, time_in, time_out, sec_to_time(unix_timestamp(time_out) - unix_timestamp(time_in)) AS totalhours from hours where member_id='7'";
$result = mysql_query($query)or die(mysql_error());
$rowNo = 1; //Increment Row Number
$total_time="00:00:00";
while($row = mysql_fetch_assoc($result)){
$time = $row['totalhours'];

$secs = strtotime($time)-strtotime("00:00:00");
$total_time = date("H:i:s",strtotime($total_time)+$secs);


echo "<tr align='left'>";    
echo"<td><font color='white'>" .$rowNo++."</font>.</td>";
echo"<td><font color='white'>" .$row['member_name']."</font>.</td>";
echo"<td><font color='white'>" .date('Y-M-d - h:i:s a ', strtotime($row['time_in']))."</font>.</td>";
echo"<td><font color='white'>" .date('Y-M-d - h:i:s a ', strtotime($row['time_out']))."</font>.</td>";
echo"<td><font color='white'>" .$time." Hrs</font>.</td>"; 
echo "</tr>";          
}

?>          
</table>
<?php echo '<p align="right"><font size="4" color="black">Sub Total:</font> '.$total_time.' Hrs </p>'; ?>

What could be wrong here that after 12noon time is getting negative and all total login hours are displaying wrong?

Note: I'm not using PDO because this is an offline project not online.

"and yes this is not pdo because its an offline project not online" Your assumption that PDO can't connect to localhost is misguided, I do it all the time. — HPierce, Jul 30 '16 at 19:01
it does i knw mate but this is a very old script converting it will take forever :) — lalthung, Jul 30 '16 at 19:04
i have attached an image please take a look the sub total hours don't seems ok — lalthung, Jul 30 '16 at 19:14

Blue · Accepted Answer · 2016-07-30T19:05:48.033

1

Because MySQL doesn't support passing a meridiem (AM or PM), you need to properly pass things in 24 hour format. In order to store it properly, for example 1pm, instead of 1am, you would need to store 13:00:00.

Use:

$dt = date("Y-m-d H:i:s");

(Capitol H) for 24 hour format.

See example 4 of the date() documentation for PHP, where it mentions the MySQL format:

<?php
// Assuming today is March 10th, 2001, 5:16:18 pm, and that we are in the
// Mountain Standard Time (MST) Time Zone

$today = date("F j, Y, g:i a"); // March 10, 2001, 5:16 pm
$today = date("m.d.y"); // 03.10.01
$today = date("j, n, Y"); // 10, 3, 2001
$today = date("Ymd"); // 20010310
$today = date('h-i-s, j-m-y, it is w Day'); // 05-16-18, 10-03-01, 1631 1618 6 Satpm01
$today = date('\i\t \i\s \t\h\e jS \d\a\y.'); // it is the 10th day.
$today = date("D M j G:i:s T Y"); // Sat Mar 10 17:16:18 MST 2001
$today = date('H:m:s \m \i\s\ \m\o\n\t\h'); // 17:03:18 m is month
$today = date("H:i:s"); // 17:16:18
$today = date("Y-m-d H:i:s"); // 2001-03-10 17:16:18 (the MySQL DATETIME format)
?>

edited Jul 30 '16 at 19:05

answered Jul 30 '16 at 18:59

Blue

22,608
7
62
92

mate i have posted an image please take a look the sub total of all hours don't seems to be calculating well – lalthung Jul 30 '16 at 19:15
@lalthung This will not fix old data that's already been inputted. You'll have to go and manually edit them in the database by adding 12 to the afternoon times. 1pm => 13, 2pm => 14, etc...My solution will fix things going forward – Blue Jul 30 '16 at 19:18
Also, how are you getting the sub total? Is this a different question? If so, you should [create a new one](http://stackoverflow.com/questions/ask) – Blue Jul 30 '16 at 19:20
no not different its in the same question subtotal is displaying wrong $total_time="00:00:00"; $secs = strtotime($time)-strtotime("00:00:00"); $total_time = date("H:i:s",strtotime($total_time)+$secs); – lalthung Jul 30 '16 at 19:22
thats what im wonder looks ok to me for it worked just fine earlier in the morning. starting to get a headache now – lalthung Jul 30 '16 at 19:33
Try just `$total_time = gmdate("H:i:s", $time);` – Blue Jul 30 '16 at 19:34
I need to see what the values for `$time` are. `$time` should be the number of seconds between logout and login (Getting you the total login time in seconds). I followed [this post](http://stackoverflow.com/a/3172358/4875631) to convert it to Hour:Minute:Second format. – Blue Jul 30 '16 at 19:39
if i echo echo $time = $row['totalhours'].; i get 00:13:27 00:00:06 00:00:58, 00:00:04, 00:00:04, 00:01:04 – lalthung Jul 30 '16 at 19:43
Get rid of `sectotime` in mysql. Use the `gmdate` function in php to format it nicely, and just simply add up the seconds and use `gmdate` with php on the total (of seconds) to get the total in h:m:s format. – Blue Jul 30 '16 at 19:43

MySQL total login hours become negative in the afternoon

1 Answers1