clock in / out使用mysql或php数组函数将入口时间合计为多个用户的总时间

Question

I have a database for clock in clock out system The table structure consists of time_id, user_id, weekNo, ClockYear, EntryDate, StartTime, EndTime and updated_at

basically the startTime is clock in and EndTime is clocked out time what im trying to do is run a query to get me the total worked hours by a user on a sepcific date total instead of total hours worked per individual entry. so currently i have the following query:

 SELECT CONCAT(users.first_name," ",users.last_name) AS theUser, 
 time_logs.*, TIMEDIFF(endTime,startTime) AS totTime FROM time_logs   
 LEFT JOIN users ON time_logs.user_id = users.user_id WHERE 
time_logs.entryDate >= "2016-08-17" AND time_logs.entryDate <= "2016-08-25" order by user_id ASC

and this returns the results as

[0] => Array
    (
        [theUser] => Sam Johnson
        [timeId] => 14
        [user_id] => 1
        [weekNo] => 34
        [clockYear] => 2016
        [entryDate] => 2016-08-23
        [startTime] => 2016-08-23 16:16:30
        [endTime] => 2016-08-23 17:36:14
        [updated_at] => 2016-08-23 17:36:14
        [totTime] => 01:19:44
    )

[1] => Array
    (
        [theUser] => Sam Johnson
        [timeId] => 15
        [user_id] => 1
        [weekNo] => 34
        [clockYear] => 2016
        [entryDate] => 2016-08-24
        [startTime] => 2016-08-24 01:00:00
        [endTime] => 2016-08-24 05:15:00
        [updated_at] => 
        [totTime] => 04:15:00
    )

 [3] => Array
    (
        [theUser] => Bob Doe
        [timeId] => 19
        [user_id] => 2
        [weekNo] => 34
        [clockYear] => 2016
        [entryDate] => 2016-08-24
        [startTime] => 2016-08-24 10:00:00
        [endTime] => 2016-08-24 13:15:00
        [updated_at] => 
        [totTime] => 03:15:00
    )

As you can see it gives total time for that specific entry but what i want to get is just one entry for each user with the total time totalled up for all entries by that user on that day combined so for user 1

[0] => Array
(
    [theUser] => Sam Johnson
    [user_id] => 1
    [weekNo] => 34
    [clockYear] => 2016
    [entryDate] => 2016-08-23
    [totTime] => 05:34:44
)

can anyone help with this not sure if i can do it using a query

Answer 1

You are almost there!
Assuming that totTime is a Time Field (Not a DateTime field) and I HOPE user_id is unique
Sorry I put XXX as I didn't want to spend the time writing this fully out

Select CONCAT(XXX) theUser, user_id,min(weekNo) weekNo,min(clockYear) clockYear, min(entryDate) entryDate,sum(TIMEDIFF(endTime,startTime)) totTime FROM XXX ORDER BY user_id GROUP BY user_id

That should give you what you want. The key is GROUP BY which you didn't have. You can change the MIN commands to MAX if that's what you want to show.

theUser will be picked from the last entry I believe, user_id should remain the same and the other fields will be calculated by the GROUP BY and I had to repeat the field names on the sum and min functions because sum and min return a unique fieldname