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.

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.

[QUOTE=ferdna]this data equals 8 hours for that one day./quote]wha????

perhaps you should do this with application programming

mike_bike_kite, how could i re-design it?

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

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.