Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: best approach for calculating work hours?

    i have a table with three fields (id, date, time) sample data

    1, 01/01/2009, 08:00:00
    2, 01/01/2009, 11:00:00
    3, 01/01/2009, 13:00:00
    4, 01/01/2009, 18:00:00

    this data equals 8 hours for that one day.

    i want to be able to calculate how many hours per day, should i do this on mysql or on my programming? or what is the best way of doing this?


    thank you.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by ferdna
    what is the best way of doing this?
    Redesign your table. At the moment it's difficult to work out which time is a start time and which is an end time. Then it's difficult to work out which end times go with which start times. The structure also doesn't allow you to cross a date boundrary ie start at 11pm and finish at 7am. I also believe putting time and date into separate fields also creates more issues than it solves.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    [QUOTE=ferdna]this data equals 8 hours for that one day./quote]wha????

    perhaps you should do this with application programming
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2003
    Posts
    6
    mike_bike_kite, how could i re-design it?

    r937, yeah i was thinking doing in code not sql.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by ferdna
    mike_bike_kite, how could i re-design it?
    Perhaps a table like this:
    Code:
    create table WorkTimes (
       id                       int,
       start_time           datetime,
       end_time             datetime null
    );
    
    -- your test data
    insert WorkTimes values ( 1, '2009-01-01 08:00:00','2009-01-01 11:00:00' );
    insert WorkTimes values ( 1, '2009-01-01 13:00:00','2009-01-01 18:00:00' );
    I'm not sure what the times actually relate to - are they times that various people have worked or what projects have been worked on? I put a random id field in there anyway. Something like the following will then show how many hours were worked on a given day. If there is no end_time set then it assumes you're still working.
    Code:
    select sum( 
               unix_timestamp( ifnull( end_time,now() ) ) - 
               unix_timestamp( start_time )
              ) / 3600
    from WorkTimes
    where date( start_time ) = '2009-01-01';
    You can lookup the functions sum, unix_timestamp and ifnull.

Posting Permissions

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