lalthung lalthung - 2 years ago 92
SQL Question

MySQL total login hours become negative in the afternoon

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

code which stores the first login details to hours table:

$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

$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:


$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
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>";

<?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
because this is an offline project not online.

Answer Source

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.


$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:

// 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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download