dongliantong3229
2018-05-09 13:30
浏览 86
已采纳

从日期时间计算加班时间

I am trying to calculate overtime based on a overtimefactor of 07:24:00:0000 hours

My database looks like this

| id  |user_id|     starttime       | endtime
|  0  |   1   | 2018-05-09 04:30:00 | 2018-05-09 017:30:00
|  1  |   1   | 2018-05-10 06:30:00 | 2018-05-10 017:30:00
|  2  |   1   | 2018-05-11 04:30:00 | 2018-05-11 015:30:00

I am having a hard time figuring out the selectstatement that would do the job - I want ALL combined overtime as a result

My function i have now looks like this:

public function getOvertimeTotal($id, $selectedMonth) {
      $this->db->query("SELECT 
      COUNT(SUBTIME(TIMESTAMPDIFF(HOUR, starttime, endtime), '07:24:00:0000')) as overTimeTotal
      FROM hours
      WHERE user_id = :id
      AND month(starttid) = :selectedMonth");

      $this->db->bind(':id', $id);
      $this->db->bind(':selectedMonth', $selectedMonth);

      $row = $this->db->single();

      return $row;

    }

What am i doing wrong?

图片转代码服务由CSDN问答提供 功能建议

我试图根据07:24:00:0000小时的超时因子来计算加班时间 \ n

我的数据库看起来像这样

  |  id | user_id |  starttime | 结束时间\ N |  0 |  1 |  2018-05-09 04:30:00 |  2018-05-09 017:30:00 
 |  1 |  1 |  2018-05-10 06:30:00 |  2018-05-10 017:30:00 
 |  2 |  1 |  2018-05-11 04:30:00 |  2018-05-11 015:30:00 
   
 
 

我很难搞清楚能够完成工作的选择陈述 - 我希望所有加班加班 结果

我的功能现在看起来像这样:

  public function getOvertimeTotal($ id,$ selectedMonth){
 $ this  - > db-> query(“SELECT 
 COUNT(SUBTIME(TIMESTAMPDIFF(HOUR,starttime,endtime),'07:24:00:0000'))as overTimeTotal 
 FROM hours 
 WHERE user_id =:id  
 AND月(starttid)=:selectedMonth“); 
 
 $ this-> db-> bind(':id',$ id); 
 $ this-> db-> bind(  ':selectedMonth',$ selectedMonth); 
 
 $ row = $ this-> db-> single(); 
 
返回$ row; 
 
} 
   
 
 

我做错了什么?

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doutuoji8418 2018-05-09 13:57
    已采纳

    This should help you calculate overtime in fractional hours,

    SUM((TIMESTAMPDIFF(SECOND, starttime, endtime) - TIME_TO_SEC('07:24:00:0000')) / 3600)
    

    SQLFiddle link


    If you need the value converted back in time remove division by 3600 part from above and use SEC_TO_TIME() function

    SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, starttime, endtime) - TIME_TO_SEC('07:24:00:0000')))
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dongpo9071 2018-05-09 13:38

    You could try to compute the overtime in a subquery and then aggregate over the named result column:

    SELECT SUM(overtime) AS overTimeTotal 
    FROM (SELECT starttime - endtime AS overtime FROM tablename WHERE [...])
    

    I am leaving out the WHERE part for simplicity.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题