php - MYSQL - Timeclock Count Hours In/Out for each day of current week


Below is my table for my databasetimeclock that records each record of an employee clocking in/out. I am wanting to create a function that returns hours for each day Monday-Sunday of current week.

id  user    timeinout           type    function    
1   1   2015-08-05 20:20:32     in         1
2   1   2015-08-05 20:20:56     out        1
3   1   2015-08-05 20:29:25     in         1
4   1   2015-08-05 20:32:04     out        2
5   1   2015-08-05 20:36:28     in         1
6   1   2015-08-05 20:40:00     out        1
7   1   2015-08-06 09:05:39     in         1

I know this function is way wrong, but here is where I am. I have never done this before so I am at a real loss and all examples I have tried have failed.

function gettimeclockHours($user) {
  include $_SESSION['db'];
  try {
      $sql = "SELECT HOUR(timeinout) as hour, COUNT(*) as num_rows FROM timeclock WHERE user = :user GROUP BY HOUR(timeinout)";
      $s = $pdo->prepare($sql);
      $s->bindValue(':user', $user);
  catch (PDOException $e)
      $error = 'Error.' . $e->getMessage();
  $result = $s->fetch();
  return $result;

The Output example I am looking for is below.

Day of Week     Total Time
Monday          1.2 Hours

Thanks for your help in advance.




split dates by weekday (Mon, Tue etc.) actually simple question. You can use DAYNAME() function.

More interesting task is to calculate time when user inside. It could be sum(outs_time)-sum(ins_time), but in case, when user come at 23:45 and leave at 00:15 next day it won't work. in this case we need to add additional out at 24:00:00

I would go for following solution: 1. transform time to seconds from beginning of the day (TIME_TO_SEC() 2. check what the last action is "in" and make a flag in subquery

select if(max(if(type = 'in', timeinout, 0)) > max(if(type = 'out', timeinout, 0)), 1,0) // 1 - last type is in, 0 - last type is out
from timeclock t1 
  1. final query with adding 24 hours if last type = "in"

    select DAYNAME(timeinout) as weekday_name,
      sum(if(type = "out",TIME_TO_SEC(timeinout), 0)) - 
      sum(if(type = "in",TIME_TO_SEC(timeinout), 0)) + 24*60*60*
      (select if(max(if(type = 'in', timeinout, 0)) > max(if(type = 'out', timeinout, 0)), 1,0) // 1 - last type is in, 0 - last type is out
        from timeclock t1 
        where t1.user = t2.user and date(t1.timeinout) = date(t2.timeinout))
    )/60/60 hours
    FROM timeclock t2
    GROUP BY user, date(timeinout)

People are also looking for solutions to the problem: php - how to display unicode characters using jquery ajax?


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.