我经历了许多相关的主题,但都没有成功。我在mysql
的insurance
数据库中有一个表,它将用户登录和注销时间存储在不同的行中。数据库名称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.
我想知道同一用户从数据库中登录和注销的区别。
你可以试试这个:
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'
您可以使用我的示例查询,将@login@logout值替换为列名,并删除带有Set关键字的行。它对你来说应该很好。
SET @login = '2014-06-01 18:50:50', @logout = '2014-06-02 21:55:00';
SELECT TIMESTAMPDIFF(DAY,@login,@logout) AS days,
TIMESTAMPDIFF(HOUR,@login,@logout)-(24 * TIMESTAMPDIFF(DAY,@login,@logout)) AS hours,
TIMESTAMPDIFF(MINUTE,@login,@logout) -(60 * TIMESTAMPDIFF(HOUR,@login,@logout)) AS minutes;
这可能不是一个非常有效的解决方案,但它至少对我有用;(