Working time between two datetimes

gtk · #1 (**permalink**) 03-23-09, 11:13

Hi guys

I need to calculate a working duration between two datetimes.
Let's say a guy should work on Mon, Tue, Wed, Thi, Fri between
9:00 and 18:00. He started a project Wed at 14:00 and finished
it Mon of the folowing week at 13:00.
I need to know how many hours did he spend on the project.

I have imagined a dates and times tables like

Code:

+------------+
| dates      |
+------------+
| ...        |
| 2009-03-22 |
| 2009-03-23 |
| 2009-03-24 |
| 2009-03-25 | <- Start
| 2009-03-26 |
| 2009-03-27 |
| 2009-03-28 |
| 2009-03-29 |
| 2009-03-30 | <- End
| 2009-03-31 |
| 2009-04-01 |
| 2009-04-02 |
| 2009-04-03 |
| ...        |
+------------+

+-------+
| times |
+-------+
| 01:00 |
| 02:00 |
| 03:00 |
| 04:00 |
| 05:00 |
| 06:00 |
| 07:00 |
| 08:00 |
| 09:00 | <- Working time Start
| 10:00 |
| 11:00 |
| 12:00 |
| 13:00 |
| 14:00 |
| 15:00 |
| 16:00 |
| 17:00 |
| 18:00 | <- Working time End
| 19:00 |
| 20:00 |
| 21:00 |
| 22:00 |
| 23:00 |
| 24:00 |
+-------+

with a query like

Code:

SET start_time DATETIME = "2009-03-25 14:00";
SET end_time DATETIME = "2009-03-30 13:00";
SET working_time_start TIME = "09:00";
SET working_time_end TIME = "18:00";
SET working_day_start INT = 1; // <- Monday
SET working_day_end INT = 5; // <- Friday

SELECT COUNT(DISTINCT CONCAT(dt.date, " ", tm.time)) AS "working_time"
FROM dates AS dt, time AS tm
WHERE CONCAT(dt.date, " ", tm.time) BETWEEN start_time AND end_time
  AND tm.time BETWEEN working_time_start AND working_time_end
  AND DATE_FORMAT(dt.date, "%w") BETWEEN working_day_start AND working_day_end)

I didn't try the solution and
I'm not sure if it's the best one.
Do someone have a better one ?

gtk · #2 (**permalink**) 03-24-09, 06:57

The problem here is that I get only hours.
I can't have minutes

healdem · #3 (**permalink**) 03-24-09, 07:19

have you considered using the MySQL date and time functions (date_sub looks a good choice)

gtk · #4 (**permalink**) 03-24-09, 11:49

I don't get how to implement my need with DATE_SUB().
This function returns a DATETIME, I need a duration in hour.
In my example, between 2009-03-25 14:00 and 2009-03-30 13:00,
I should get 27 hours worked.
Wednesday 25 March (14:00 - 18:00 -> 4h)
+ Thirsday 26 March (08:00 - 18:00 -> 9h)
+ Friday 27 March (08:00 - 18:00 -> 9h)
+ Monday 30 March (08:00 - 13:00 -> 5h)
4 + 9 + 9 + 5 = 27
Could you tell me more using DATE_DUB() ?
I think we could do it with DATE_DIFF() but I don't see how

gtk · #5 (**permalink**) 03-26-09, 13:23

I managed it differently with several TIMEDIFF()s.
Pain in the arse to maintain

chennai schools · #6 (**permalink**) 03-27-09, 08:09

i was developing an attendance system & thought of making it available for free for all

& i face same such problem with the dates & time calculations & not able to fullfil my requirements & finally droped the whole project

one of the requirement for which i didnt find solutions is
i use to record all members in & out punch times (that wasnt the problem)
then have to generate a report with Member's Name & Hours present for a selected day
one more report is to list the no of days where the members present more than 8hours along with their Names

if somebody help me with this i can continue further..

gtk · #7 (**permalink**) 03-27-09, 08:44

This should not be too tricky to do.
In your table attendancy you should have
one record per day, per person.
Like:

Code:

+------------+------+--------------+
| Name       | Type | Properties   |
+------------+------+--------------+
| id         | INT  | PK, AutoIncr |
| date       | DATE | FK, Index    |
| person_id  | INT  | FK, Index    |
| start_time | TIME |              |
| end_time   | TIME |              |
+------------+------+--------------+

Example Data

+----+------------+-----------+------------+----------+
| id | date       | person_id | start_time | end_time |
+----+------------+-----------+------------+----------+
|  1 | 2009-03-25 |         1 |   09:10:00 | 18:20:00 |
|  2 | 2009-03-25 |         2 |   09:20:00 | 18:00:00 |
|  3 | 2009-03-26 |         1 |   08:25:00 | 19:05:00 |
|  4 | 2009-03-27 |         1 |   08:55:00 | 18:05:00 |
|  5 | 2009-03-27 |         2 |   13:30:00 | 18:00:00 |
+----+------------+-----------+------------+----------+

Now let's get the number of days where your guys are working more than 8 hours

Code:

SELECT
  ps.name AS "Guy"
, COUNT(DISTINCT
    IF(TIMEDIFF(at.end_time, at.start_time) >= "08:00"
      , at.date
      , NULL
    )
  ) AS "Days>8"
FROM person ps
LEFT JOIN attendancy at ON (at.person_id = ps.id)
GROUP BY ps.person_id

you will get something like

Code:

+------+--------+
| Guy  | Days>8 |
+------+--------+
| guy1 |      3 |
| guy2 |      1 |
+------+--------+

What do you think ?

r937 · #8 (**permalink**) 03-27-09, 10:54

what in the world is the date column a foreign key to??

gtk · #9 (**permalink**) 03-27-09, 11:14

I imagined a calendar table with holidays for example

gtk · #10 (**permalink**) 03-27-09, 11:24

By the way Rudy, how do you manage working times like in my first post ?

r937 · #11 (**permalink**) 03-27-09, 11:26

i tend to use the TIME datatype

gtk · #12 (**permalink**) 03-27-09, 11:34

Me too

Internet Services

Web Development

Digital Marketing

Consumer Tech