php - 使用php计算数据库中用户的时差

Question

我已经研究了很多相关主题，但没有成功。我在 mysql 的 insurance 数据库中有一个表，它在不同的行中存储用户登录和注销时间。db name insurance

CREATE TABLE hsave_work_duration (
id_login int(11) NOT NULL AUTO_INCREMENT,
id_agent int(11) NOT NULL,
agent_name varchar(30) NOT NULL,
sys_time datetime NOT NULL,
status varchar(20) NOT NULL,
PRIMARY KEY (`id_login`)
);


id_login   id_agent  agent_name  sys_time               status
92          5         test       2014-06-01 22:00:00    Logout
91          5         test       2014-06-01 21:50:00    Login
90          4         abc        2014-06-01 21:40:05    Login
89          10        user       2014-06-01 18:00:00    Login

我获取了每个用户的登录时间，并计算了从登录时间到当前时间的总工作时间。tst.php

<?php
$sql="select              
sys_time from hsave_agent where          
status='Login'  order by id_login DESC";
$login=mysql_query($sql);
if(!$login){
die('no fetch frm db'.mysql_error());
}
?>

        <tr>

            <th>Id Login</th>
            <th>User Name</th>
            <th>Name</td>
            <th>Login Time</th>
            <th>Current Time</th>
            <th>Working Hours</th>
            </tr>   
<?php
while($row=mysql_fetch_assoc($login)){ 
$logintime=$row['sys_time'];
?>


<?php  // difference between login time and current time
$diff=(strtotime($currenttime)-strtotime($logintime));
$diff_years   = floor($diff / (365*60*60*24));
$diff_monthes  = floor(($diff - $diff_years * 365*60*60*24) / (30*60*60*24));
$diff_days    = floor(($diff - $diff_years * 365*60*60*24-      
$diff_monthes*30*60*60*24)/ (60*60*24));

$diff_hours   = floor(($diff - $diff_years * 365*60*60*24 - $diff_monthes*30*60*60*24 -   
$diff_days*60*60*24)/ (60*60));
$diff_minutes  = floor(($diff - $diff_years *    
365*60*60*24  -  $diff_monthes*30*60*60*24- $diff_days*60*60*24 - $diff_hours*60*60)/ 60);


// here you can format output, using variables above, for example:
$diff = $diff_days."days, ".$diff_hours." hours, ".$diff_minutes." minutes.";
?>
<td><?php echo $row['id_login'] ?></td> 
    <td><?php echo $row['agent_name'] ?></td>   
    <td><?php echo $row['name'] ?></td>
    <td><?php echo $logintime ?></td>
    <td><?php echo $currenttime;?></td>
    <td><?php echo $diff;?></td>

<?php 
// closing while loop of 1st iteration
}?>

运行这个我得到以下输出:

IdLogin  User    Login Time          Current Time         Working Hours
91       test   2014-06-01 21:50:50 2014-06-01 21:55:00   0days, 0 hours, 5 minutes.
90       abc    2014-06-01 21:40:05 2014-06-01 21:55:00   0days, 0 hours, 15 minutes.
89       user   2014-06-01 18:00:00 2014-06-01 21:55:00   0days, 3 hours, 55 minutes.

我想从数据库中获取同一用户登录和注销之间的差异。

Answer 1

你可以试试这个:

SELECT h1.id_login, h1.agent_name, h1.sys_time as LoginTime, h2.sys_time as LogoutTime, TIMEDIFF(h2.sys_time, h1.sys_time)
FROM hsave_agent h1, hsave_agent h2
WHERE h1.id_agent = h2.id_agent AND h1.status = 'Login' AND h2.status = 'Logout'