Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: Working time between two datetimes

    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 ?
    Last edited by gtk; 03-24-09 at 05:43.

  2. #2
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    The problem here is that I get only hours.
    I can't have minutes

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you considered using the MySQL date and time functions (date_sub looks a good choice)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    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

  5. #5
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    I managed it differently with several TIMEDIFF()s.
    Pain in the arse to maintain

  6. #6
    Join Date
    Mar 2009
    Posts
    2
    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..

  7. #7
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    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 ?
    Last edited by gtk; 03-27-09 at 11:33.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what in the world is the date column a foreign key to??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    I imagined a calendar table with holidays for example

  10. #10
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    By the way Rudy, how do you manage working times like in my first post ?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i tend to use the TIME datatype

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Me too

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •