如何计算不同项目签入/签出的小时和分钟数

Question

我们有一个名为：的表

• 活动

每一行都有以下值：

• id
• 类型(签入/签出(
• 开始(varchar 2020-11-30 15:45(

给定2020年11月30日这样的特定日期，假设我们有几次办理入住/退房，如何计算这一天的小时和分钟？

这就是我们试图获得全天总和的函数：

function sum_all_hours_by_day($fxemploye_id, $day){
$core_settings = Setting::first();
$hours = 0;
$date = date_create($day);
$date_format = date_format($date, 'Y-m-d');
$date_format_start = $date_format .  " 00:00";
$date_format_end = $date_format . " 23:59";
$options = array('conditions' => array('fxemploye_id = ? and start >= ? and start <= ?', $fxemploye_id, $date_format_start,$date_format_end));
$activities = FxemployeHasActivity::all($options);
$i = 0;
$d1 = 0; //Checkin
$d2 = 1; //Checkout
foreach(array($activities) as $ibd){
$date1 = $ibd[$d1]->start;
$date2 = $ibd[$d2]->end;
$timestamp1 = strtotime($date1);
$timestamp2 = strtotime($date2);

if($timestamp1 == true && $timestamp2 == true){
$sum = abs((int)$timestamp2 - (int)$timestamp1)/(60*60); 
$hours = $hours + $sum;
}
$d1 = $d1++;
$d2 = $d2++;
$i++;
}

return $hours;
}

Answer 1

我认为你想要的东西可以在MySQL中很容易地实现(假设你使用的是MySQL(：

SELECT SUM(UNIX_TIMESTAMP(start) - UNIXTIMESTAMP(end))
FROM activities
WHERE `start` > X and `end` < y AND fxemploye_id = N

然后在Codeigniter中使用QueryBuilder，它看起来像这样：

$db = $this->db;
$db->select('SUM(UNIX_TIMESTAMP(start) - UNIXTIMESTAMP(end)) as Result');
$db->where('start >',$date_format_start);
$db->where('end <',$date_format_end);
$db->where('fxemploye_id',$fxemploye_id);
$data = $db->get('activities')->result_array();

Answer 2

我已经用一种方法解决了这个问题，我认为这不是最好的方法，但假设数据库结构是这样的，它就可以作为一个函数工作。如果有人能表演，那将是受欢迎的。

function sum_all_hours_by_day($fxemploye_id, $day){
$core_settings = Setting::first();

//You can debug forcing the $day = '2020-11-19';
$date = date_create($day);
$date_format = date_format($date, 'Y-m-d');
//Adding the time as we don't have that from database
$date_format_start = $date_format .  " 00:00";
$date_format_end = $date_format . " 23:59";
$options = array('conditions' => array('fxemploye_id = ? and start >= ? and start <= ?', $fxemploye_id, $date_format_start,$date_format_end));
//Getting all checkins between two days as a range
$activities = FxemployeHasActivity::all($options);
//We get the $d1 as a first checkin 2020-11-19 08:15
//We get the $d2 as a first checkout 2020-11-19 13:14
$i = 0;
$d1 = 0; 
$d2 = 1;
$hours = 0;
foreach($activities as $ibd){
$date1 = $aActivities[$d1]->start;
$date2 = $aActivities[$d2]->end;
//Convert tot timestamp
$timestamp1 = strtotime($date1);
$timestamp2 = strtotime($date2);

//That's the formula where we can now the range between two dates in seconds
$sum = abs((int)$timestamp2 - (int)$timestamp1)/3600; 

//Sum that for the next
$hours = $hours + $sum;
//Move to the next checkin/checkout in the array $activities
$d1 = $d1 + 2;
$d2 = $d2 + 2;
$i++;
}
//Return in hours
return $hours;
}

由于我们有像5.88这样的浮动小时，我们必须转换为小时和分钟，我们可以这样做：

<?php echo sprintf('%02d:%02d', (int) $time, fmod($time, 1) * 60); ?>

感谢大家！