How to take the amount of time using sql query?

Question

I have a ReportBuilder class to create a document (Excel). I want to select the employee and the date period , and then calculate the total working hour for this date. this is my php file

<?php
class EmployeeSummaryTimesheet extends ReportBuilder{

    public function getMainQuery(){
        $query = "SELECT
(SELECT `employee_id` from Employees where id = at.employee) as 'Employee',
(SELECT concat(`first_name`,' ',`middle_name`,' ', `last_name`) from Employees where id = at.employee) as 'Employee',

TIMEDIFF(out_time, in_time) as 'Working Hours'
FROM Attendance at";

        return $query;
}

public function getWhereQuery($request){
        if(($request['employee'] != "NULL" && !empty($request['employee']))){
            $query = "where employee = ? and in_time >= ? and out_time <= ? group by employee;";
            $params = array(
                $request['employee'],
                $request['date_start']." 00:00:00",
                $request['date_end']." 23:59:59",
            );
        }else{
            $query = "where in_time >= ? and out_time <= ? group by employee;";
            $params = array(
                $request['date_start']." 00:00:00",
                $request['date_end']." 23:59:59",
            );
        }
 return array($query, $params);
    }}

now I can’t get the sum of this specific time period im getting the first day TIMEDIFF (out_time, in_time) value just not the amount of working time help me

EDit - Here I have two Employee and Attendance tables (id, employee (this is the identifier of the employee from the Employee table), in_time, out_time)

Edit: I strip DATE_FORMAT (in_time, '% Y-% m-% d') as "Date", I don't need this column

Answer 1

,  

    public function getMainQuery(){

        $query = "SELECT
(SELECT `employee_id` from Employees where id = at.employee) as 'Employee',
(SELECT concat(`first_name`,' ',`middle_name`,' ', `last_name`) from Employees where id = at.employee) as 'Employee',
DATE_FORMAT(in_time, '%Y-%m') as 'MONTH',
CONCAT(
FLOOR(
SUM(CASE WHEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))<=0 THEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))=''
ELSE TIME_TO_SEC(TIMEDIFF(out_time,in_time)) END)/3600) ,':',
FLOOR(MOD(
SUM(CASE WHEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))<=0 THEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))=''
ELSE TIME_TO_SEC(TIMEDIFF(out_time,in_time)) END),3600)/60)) as 'WORKING HOURS'
FROM Attendance at";

        return $query;

    }

    public function getWhereQuery($request){
        if(($request['employee'] != "NULL" && !empty($request['employee']))){
            $query = "where employee = ? and in_time >= ? and out_time <= ? order by in_time;";
            $params = array(
                $request['employee'],
                $request['date_start']." 00:00:00",
                $request['date_end']." 23:59:59",
            );
        }else{
            $query = "where in_time >= ? and out_time <= ? group by employee;";
            $params = array(
                $request['date_start']." 00:00:00",
                $request['date_end']." 23:59:59",
            );
        }

        return array($query, $params);
    }
}

Answer 2

, . , .

select
  e.employee_id,
  concat(e.first_name, ' ', e.middle_name, ' ', e.last_name) as employee_name 
  date_format(at.in_time, '%Y-%m-%d') as 'Date',
  sum(timediff(at.out_time, at.in_time)) as 'Working Hours'
from attendance at
join employee e on e.id = at.employee
where ...
group by e.employee_id, date_format(at.in_time, '%Y-%m-%d');