asked on

How many hours employee worked after 17:00 Mysql and datetime

I have a problem with a mysql query. This query shows me the time this employee clocks in and out at work, then shows me the text if he´s on vacation and then finally the totaltime in hours he was been working.

But the problem I need to fix now is that I need to know how many hours he worked after 17:00 or any time I put into the query.

I am using Mysql 4.0.26-nt

regards,
Morck

SELECT  t.timi_in as timi_in_RAW, t.timi_ut as timi_out_RAW, t.timi_sum as description, 
            SEC_TO_TIME(TIME_TO_SEC(DATE_FORMAT(t.timi_ut,'%T')) - TIME_TO_SEC( timi_in  )) AS 'TotalTime'   
            FROM m6106070710.timaskraning AS t
            WHERE t.salesperson like 'Aðalbjörg' 
            AND t.timi_in between '2011-06-01 00:00' AND '2011-06-29 00:00' 
            ORDER BY t.timi_in asc

Open in new window

Captureyyyyyy.PNG
timaskraning.txt

Kevin Cross

Hi.

Are all clock outs guaranteed to be on the same day, i.e., no clock outs at midnight or any other time of the next day?

morck

ASKER

@mwvisa1 No as you can see at the screen capture that person clocked in at 16-06 at 13:03 and out the 17-06 at 9:30 which leaves at totaltime of -03:33. But it´s up to the person in charge to fix these errors manually.

But any person who clocks in and out could be working over midnight yes. that is from 9:00 in the morning to 02:00 at midnigh.

morck

ASKER

but as you can see my current sql syntax doesn´t even support that :) so I need to fix that problem also.

Kevin Cross

I saw that after and yes I noticed that you had invalid calculation currently also. Working on sample SQL to show how to correct. For clarification, though, for your new requirement. When you say work after 17:00. If I clock in at 17:00 today and clock out tomorrow at 02:00, is the time worked you want to see 7 hours OR 9 hours?

Kevin Cross

In other words, is the before 5 p.m. filter on the start time only OR on the end time also?

morck

ASKER

I want to see 9 hours. but if it´s after 17:00 it´s all in the overtime column. But I would like to add the 17:00 as an parameter to the sql. so it´s changeable in the code.

Kevin Cross

SELECT t.timi_in as timi_in_RAW
     , t.timi_ut as timi_out_RAW
     , t.timi_sum as description
     , SEC_TO_TIME(
          -- check for end time earlier than 5 p.m.
          IF(DATE(t.timi_ut)=DATE(t.timi_in) AND HOUR(t.timi_ut)<17, 0, 
             TIME_TO_SEC(t.timi_ut) +
             -- add 24 hours worth of seconds for overnight
             IF(DATE(t.timi_ut)>DATE(t.timi_in), 86400, 0) -
             -- fix start time to 5 p.m. or later
             TIME_TO_SEC(IF(HOUR(t.timi_in)<17, '17:00', t.timi_in))
          )
       ) as TotalTime
FROM `timaskraning` AS t
WHERE t.salesperson = 'Aðalbjörg' -- use EQUALS for direct comparison
-- filter based on your date requirements, better to use >= AND <
AND t.timi_in >= '2011-06-01 00:00' AND t.timi_in < '2011-06-29 00:00'
AND t.timi_sum <> 'Vacation' -- exclude vacation time
ORDER BY t.timi_in asc
;

Open in new window

This should cover everything.

Kevin Cross

I see some multiple days in the data, so here is a more complete code to catch all errors -- as you said supervisors can decide what is acceptable or not.

SELECT t.timi_in as timi_in_RAW
     , t.timi_ut as timi_out_RAW
     , t.timi_sum as description
     , SEC_TO_TIME(
          TIME_TO_SEC(t.timi_ut) +
          -- add 24 hours worth of seconds for overnight
          (DATEDIFF(t.timi_ut, t.timi_in) * 86400) -
          TIME_TO_SEC(t.timi_in)
       ) as TotalTime
     , SEC_TO_TIME(
          -- check for end time earlier than 5 p.m.
          IF(DATE(t.timi_ut)=DATE(t.timi_in) AND HOUR(t.timi_ut)<17, 0, 
             TIME_TO_SEC(t.timi_ut) +
             -- add 24 hours worth of seconds for every day
             (DATEDIFF(t.timi_ut, t.timi_in) * 86400) -
             -- fix start time to 5 p.m. or later
             TIME_TO_SEC(IF(HOUR(t.timi_in)<17, '17:00', t.timi_in))
          )
       ) as OverTime
FROM `timaskraning` AS t
WHERE t.salesperson = 'Aðalbjörg' -- use EQUALS for direct comparison
-- filter based on your date requirements, better to use >= AND <
AND t.timi_in >= '2011-06-01 00:00' AND t.timi_in < '2011-06-29 00:00'
AND t.timi_sum <> 'Vacation' -- exclude vacation time
ORDER BY t.timi_in asc
;

Open in new window

Kevin Cross

Probably an easier way to do it if I thought it out -- just translated what you had -- hopefully it works for you.

morck

ASKER

I get an error with this sql syntax, does it work for Mysql 4.0.26-nt ? I just copy pasted it to test it out.

Kevin Cross

Hmmm. Sorry, I am using 5.x and may have included newer syntax inadvertently. Let me check.

Kevin Cross

Argh. TIME() was added in 4.1.1, which by the way adds TIMEDIFF(...) which I had forgotten about. That would simplify the formula greatly. DATEDIFF(...) is also 4.1.1 add, so guess I am just spoiled. :)

You will have to use DATE_FORMAT(...) as you had before.

I am about to go in transit, so will check back later if you run into issues modifying script.
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

morck

ASKER

this works perfectly now ! exept that it should always be overtime on weekends :) but I know I didn´t say that in the original post but if you know a simple solution to that I would be very happy else I just accept this answer you gave erlier.

Kevin Cross

You can test that with DAYOFWEEK() function. If the day of the week is 1 or 7 then you are on a weekend.
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_dayofweek

morck

ASKER

excellent solution and response time ! saved my day

Kevin Cross

SELECT t.timi_in as timi_in_RAW
     , t.timi_ut as timi_out_RAW
     , t.timi_sum as description
     , SEC_TO_TIME(
          TIME_TO_SEC(t.timi_ut) +
          -- add 24 hours worth of seconds for overnight
          ((TO_DAYS(t.timi_ut)-TO_DAYS(t.timi_in)) * 86400) -
          TIME_TO_SEC(t.timi_in)
       ) as TotalTime
     , SEC_TO_TIME(
          -- check for end time earlier than 5 p.m.
          IF(TO_DAYS(t.timi_ut)=TO_DAYS(t.timi_in) AND HOUR(t.timi_ut)<17 AND DAYOFWEEK(t.timi_ut) BETWEEN 2 AND 6, 0, 
             TIME_TO_SEC(t.timi_ut) +
             -- add 24 hours worth of seconds for every day
             ((TO_DAYS(t.timi_ut)-TO_DAYS(t.timi_in)) * 86400) -
             -- fix start time to 5 p.m. or later
             TIME_TO_SEC(IF(HOUR(t.timi_in)<17 AND DAYOFWEEK(t.timi_ut) BETWEEN 2 AND 6, '17:00', t.timi_in))
          )
       ) as OverTime
FROM `timaskraning` AS t
WHERE t.salesperson = 'Aðalbjörg' -- use EQUALS for direct comparison
-- filter based on your date requirements, better to use >= AND <
AND t.timi_in >= '2011-06-01 00:00' AND t.timi_in < '2011-06-29 00:00'
AND t.timi_sum <> 'Vacation' -- exclude vacation time
ORDER BY t.timi_in asc
;

Open in new window

Kevin Cross

You are most welcome!
Best regards and happy coding,

Kevin

Kevin Cross

P.S. (sorry for the extra e-mail) If you pass in a parameter, you can pass '17:00' or 'HH:mm' and then in places where you see 17 replace with HOUR(@TheTime) and places you see '17:00' with @TheTime where "@TheTime" is whatever you name your input variable.