1

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->prepare($sql);
      $s->bindValue(':user', $user);
      $s->execute();
  }
  catch (PDOException $e)
  {
      $error = 'Error.' . $e->getMessage();
      moduleError($error);
      exit();
  }
  $result = $s->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.

Improve this question
6
  • Do you want to know how much time each user has between each in and out from each day?
    – oNare
    Commented Aug 6, 2015 at 17:06
  • What I am wanting is the total amount of time the user has for the whole day that they are clocked in.
    – NUGGET
    Commented Aug 6, 2015 at 17:33
  • So, you want the time between 2015-08-05 20:20:32 AND 2015-08-05 20:40:00? Or every time spend between 2015-08-05 20:20:32 as in and 2015-08-05 20:20:56 as out?
    – oNare
    Commented Aug 6, 2015 at 18:20
  • ideally for each day I would like to calculate time between 2015-08-05 00:00:00 and 2015-08-06 00:00:00 or whatever day is input.
    – NUGGET
    Commented Aug 6, 2015 at 18:52
  • Do in's sometimes occur on day before the next out?
    – Vérace
    Commented Aug 6, 2015 at 18:58

1 Answer 1

Reset to default
0

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>='2015-07-27'
    AND tc.timeinout<'2015-08-03'
    AND tc.user=1
GROUP BY tc.user,DATE(tc.timeinout)
ORDER BY tc.timeinout ASC;

Test:

mysql> 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>='2015-07-27'
    -> AND tc.timeinout<'2015-08-03'
    -> AND tc.user=1
    -> GROUP BY tc.user,DATE(tc.timeinout)
    -> 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>

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>='2015-07-27'
            AND tc.timeinout<'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>='2015-07-27'
    AND tc.timeinout<'2015-08-03'
    AND tc.user=1
GROUP BY tc.user,DATE(tc.timeinout)
ORDER BY date ASC;
Improve this answer
6
  • One more question if you don't mind :) Can you tell me how to get the total for all hours within the results? Many thanks in advance.
    – NUGGET
    Commented Aug 10, 2015 at 15:38
  • Edit your question with your expected output or make a new one :)
    – oNare
    Commented Aug 10, 2015 at 15:43
  • Output expected has been updated. :)
    – NUGGET
    Commented Aug 10, 2015 at 18:49
  • But, Aren't you using Php? Make a sum of the field Minutes?
    – oNare
    Commented Aug 10, 2015 at 19:13
  • Yea, I am being dumb haha. Once again thanks for all your help.
    – NUGGET
    Commented Aug 10, 2015 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.