MYSQL - Timeclock Count Hours In/Out for each day of current week Below is my table for my database timeclock 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-&gt;prepare($sql);
      $s-&gt;bindValue(':user', $user);
      $s-&gt;execute();
  }
  catch (PDOException $e)
  {
      $error = 'Error.' . $e-&gt;getMessage();
      moduleError($error);
      exit();
  }
  $result = $s-&gt;fetch();
  return $result;
}


The Output example I am looking for is below.

Day of Week                  Total Time
Monday                       1.2 Hours
Tuesday     
Wednesday                    3.0 Hours
Thursday    
Friday  
Saturday    
Sunday  
Total for current results    4.2 Hours


Thanks for your help in advance.
You could get it using MIN,MAX and WEEKDAY with your actual design:

Table:

CREATE TABLE `test`.`timeclock` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user` INT NULL,
  `timeinout` DATETIME NULL,
  `type` ENUM('in', 'out') NULL,
  `function` INT NULL,
  PRIMARY KEY (`id`) );


Data:

insert into timeclock(id,user,timeinout,type,function)VALUES
('1','1','2015-07-27 20:20:32','in','1'),
('2','1','2015-07-27 20:20:56','out','1'),
('3','1','2015-07-27 20:29:25','in','1'),
('4','1','2015-07-28 20:32:04','out','2'),
('5','1','2015-07-28 20:36:28','in','1'),
('6','1','2015-07-29 20:40:00','out','1'),
('7','1','2015-07-29 20:45:39','in','1'),
('8','1','2015-07-30 20:20:32','in','1'),
('9','1','2015-07-30 20:20:56','out','1'),
('10','1','2015-07-31 20:29:25','in','1'),
('11','1','2015-07-31 20:32:04','out','2'),
('12','1','2015-08-01 20:36:28','in','1'),
('13','1','2015-08-01 20:40:00','out','1'),
('14','1','2015-08-02 20:20:32','in','1'),
('15','1','2015-08-02 20:20:56','out','1'),
('16','1','2015-08-02 20:29:25','in','1'),
('17','1','2015-08-02 20:32:04','out','2'),
('18','1','2015-08-02 20:36:28','in','1'),
('19','1','2015-08-02 20:40:00','out','1');


Query:

SELECT 
    tc.user,
    tcmin.min,
    tcmax.max,
    CASE 
        WHEN WEEKDAY(tc.timeinout)=0 THEN 'Monday' 
        WHEN WEEKDAY(tc.timeinout)=1 THEN 'Tuesday'
        WHEN WEEKDAY(tc.timeinout)=2 THEN 'Wednesday'
        WHEN WEEKDAY(tc.timeinout)=3 THEN 'Thursday'
        WHEN WEEKDAY(tc.timeinout)=4 THEN 'Friday'
        WHEN WEEKDAY(tc.timeinout)=5 THEN 'Saturday'
        WHEN WEEKDAY(tc.timeinout)=6 THEN 'Sunday' END AS WeekDay,
    TIMESTAMPDIFF(MINUTE,tcmin.min,tcmax.max) AS Minutes
FROM test.timeclock AS tc
LEFT JOIN (SELECT 
            user,
            MIN(timeinout) as min
          FROM test.timeclock
          GROUP BY user,DATE(timeinout)) as tcmin
    ON (tcmin.user=tc.user AND DATE(tcmin.min)=DATE(tc.timeinout))
LEFT JOIN (SELECT 
            user,
            MAX(timeinout) as max
          FROM test.timeclock
          GROUP BY user,DATE(timeinout)) as tcmax
    ON (tcmax.user=tc.user AND DATE(tcmax.max)=DATE(tc.timeinout))
WHERE 
    tc.timeinout&gt;='2015-07-27'
    AND tc.timeinout&lt;'2015-08-03'
    AND tc.user=1
GROUP BY tc.user,DATE(tc.timeinout)
ORDER BY tc.timeinout ASC;


Test:

mysql&gt; SELECT 
    -&gt; tc.user,
    -&gt; tcmin.min,
    -&gt; tcmax.max,
    -&gt; CASE 
    -&gt; WHEN WEEKDAY(tc.timeinout)=0 THEN 'Monday' 
    -&gt; WHEN WEEKDAY(tc.timeinout)=1 THEN 'Tuesday'
    -&gt; WHEN WEEKDAY(tc.timeinout)=2 THEN 'Wednesday'
    -&gt; WHEN WEEKDAY(tc.timeinout)=3 THEN 'Thursday'
    -&gt; WHEN WEEKDAY(tc.timeinout)=4 THEN 'Friday'
    -&gt; WHEN WEEKDAY(tc.timeinout)=5 THEN 'Saturday'
    -&gt; WHEN WEEKDAY(tc.timeinout)=6 THEN 'Sunday' END AS WeekDay,
    -&gt; TIMESTAMPDIFF(MINUTE,tcmin.min,tcmax.max) AS Minutes
    -&gt; FROM test.timeclock AS tc
    -&gt; LEFT JOIN (SELECT 
    -&gt; user,
    -&gt; MIN(timeinout) as min
    -&gt;   FROM test.timeclock
    -&gt;   GROUP BY user,DATE(timeinout)) as tcmin
    -&gt; ON (tcmin.user=tc.user AND DATE(tcmin.min)=DATE(tc.timeinout))
    -&gt; LEFT JOIN (SELECT 
    -&gt; user,
    -&gt; MAX(timeinout) as max
    -&gt;   FROM test.timeclock
    -&gt;   GROUP BY user,DATE(timeinout)) as tcmax
    -&gt; ON (tcmax.user=tc.user AND DATE(tcmax.max)=DATE(tc.timeinout))
    -&gt; WHERE 
    -&gt; tc.timeinout&gt;='2015-07-27'
    -&gt; AND tc.timeinout&lt;'2015-08-03'
    -&gt; AND tc.user=1
    -&gt; GROUP BY tc.user,DATE(tc.timeinout)
    -&gt; ORDER BY tc.timeinout ASC;
+------+---------------------+---------------------+-----------+---------+
| user | min                 | max                 | WeekDay   | Minutes |
+------+---------------------+---------------------+-----------+---------+
|    1 | 2015-07-27 20:20:32 | 2015-07-27 20:29:25 | Monday    |       8 |
|    1 | 2015-07-28 20:32:04 | 2015-07-28 20:36:28 | Tuesday   |       4 |
|    1 | 2015-07-29 20:40:00 | 2015-07-29 20:45:39 | Wednesday |       5 |
|    1 | 2015-07-30 20:20:32 | 2015-07-30 20:20:56 | Thursday  |       0 |
|    1 | 2015-07-31 20:29:25 | 2015-07-31 20:32:04 | Friday    |       2 |
|    1 | 2015-08-01 20:36:28 | 2015-08-01 20:40:00 | Saturday  |       3 |
|    1 | 2015-08-02 20:20:32 | 2015-08-02 20:40:00 | Sunday    |      19 |
+------+---------------------+---------------------+-----------+---------+
7 rows in set (0.00 sec)

mysql&gt; 


EDIT 2:

SELECT 
    user,
    '2020-01-01' as date,
    'Total for current results:' as WeekDay,
    SUM(Minutes) as Minutes
FROM (SELECT 
            tc.user,
            DATE(tc.timeinout) as date,
            CASE 
                WHEN WEEKDAY(tc.timeinout)=0 THEN 'Monday' 
                WHEN WEEKDAY(tc.timeinout)=1 THEN 'Tuesday'
                WHEN WEEKDAY(tc.timeinout)=2 THEN 'Wednesday'
                WHEN WEEKDAY(tc.timeinout)=3 THEN 'Thursday'
                WHEN WEEKDAY(tc.timeinout)=4 THEN 'Friday'
                WHEN WEEKDAY(tc.timeinout)=5 THEN 'Saturday'
                WHEN WEEKDAY(tc.timeinout)=6 THEN 'Sunday' END AS WeekDay,
            TIMESTAMPDIFF(MINUTE,tcmin.min,tcmax.max) AS Minutes
        FROM test.timeclock AS tc
        LEFT JOIN (SELECT 
                    user,
                    MIN(timeinout) as min
                  FROM test.timeclock
                  GROUP BY user,DATE(timeinout)) as tcmin
            ON (tcmin.user=tc.user AND DATE(tcmin.min)=DATE(tc.timeinout))
        LEFT JOIN (SELECT 
                    user,
                    MAX(timeinout) as max
                  FROM test.timeclock
                  GROUP BY user,DATE(timeinout)) as tcmax
            ON (tcmax.user=tc.user AND DATE(tcmax.max)=DATE(tc.timeinout))
        WHERE 
            tc.timeinout&gt;='2015-07-27'
            AND tc.timeinout&lt;'2015-08-03'
            AND tc.user=1
        GROUP BY tc.user,DATE(tc.timeinout)
        ORDER BY date ASC) AS Total
UNION ALL
SELECT 
    tc.user,
    DATE(tc.timeinout) as date,
    CASE 
        WHEN WEEKDAY(tc.timeinout)=0 THEN 'Monday' 
        WHEN WEEKDAY(tc.timeinout)=1 THEN 'Tuesday'
        WHEN WEEKDAY(tc.timeinout)=2 THEN 'Wednesday'
        WHEN WEEKDAY(tc.timeinout)=3 THEN 'Thursday'
        WHEN WEEKDAY(tc.timeinout)=4 THEN 'Friday'
        WHEN WEEKDAY(tc.timeinout)=5 THEN 'Saturday'
        WHEN WEEKDAY(tc.timeinout)=6 THEN 'Sunday' END AS WeekDay,
    TIMESTAMPDIFF(MINUTE,tcmin.min,tcmax.max) AS Minutes
FROM test.timeclock AS tc
LEFT JOIN (SELECT 
            user,
            MIN(timeinout) as min
          FROM test.timeclock
          GROUP BY user,DATE(timeinout)) as tcmin
    ON (tcmin.user=tc.user AND DATE(tcmin.min)=DATE(tc.timeinout))
LEFT JOIN (SELECT 
            user,
            MAX(timeinout) as max
          FROM test.timeclock
          GROUP BY user,DATE(timeinout)) as tcmax
    ON (tcmax.user=tc.user AND DATE(tcmax.max)=DATE(tc.timeinout))
WHERE 
    tc.timeinout&gt;='2015-07-27'
    AND tc.timeinout&lt;'2015-08-03'
    AND tc.user=1
GROUP BY tc.user,DATE(tc.timeinout)
ORDER BY date ASC;