Thread: Time spent type field to calculate total time spent

I want to have a field in my MySQL db that holds a time spent value on a particular task. The problem is the TIME field type only holds specific times of day. So if a task took 30:10:54, for example, the field wouldn't allow it.

I also want to be able to add up all the times spent, for example on a particular client, to calculate the total time spent. But with a TIME field, it wouldn't add up 12:23:45 + 14:13:65 to give a total over 24 hours. Hope that makes sense.

Is there a way of holding this data in a single field? At the moment I just have a varchar field of 8 characters. I'm thinking maybe I have to have seperate hrs, mins and secs INT fields.

Any other ideas?

how are you calculating the time taken?
what if you store time as varchar as you doing it presently, and then add them via code taking it as time and then saving again??

R,

I would just store the time started and the time of completion and then do the calculation of time spent in the SQL or code.

Originally Posted by micky

how are you calculating the time taken?

It's a value manually input by the user.

Originally Posted by jmurrayhead

R,

I would just store the time started and the time of completion and then do the calculation of time spent in the SQL or code.

Having to enter a start time and end time would be too onerous for the user.

At the moment it's just a single textbox with a format of hrs:mins:secs

Originally Posted by richyrich

Having to enter a start time and end time would be too onerous for the user.

At the moment it's just a single textbox with a format of hrs:mins:secs

Hmm..I would think that trying to calculate the time yourself versus letting the computer do it for you would be more erroneous...and I'm sure I don't have to go into all that jazz about storing calculated values in a table being against normalized database design

Originally Posted by jmurrayhead

Hmm..I would think that trying to calculate the time yourself versus letting the computer do it for you would be more erroneous...and I'm sure I don't have to go into all that jazz about storing calculated values in a table being against normalized database design

It doesn't need to be that precise and isn't that vital. Within 5 minute chunks would be fine. It's a more likely scenario a user would say a task took 30 mins, for example, not I started that task at 09:31:30 and finished it at 10:03:45 so that took 32 mins 15 secs.

I just then want to be able to then add them all up which I can't do as a VARCHAR.

I see what you're saying about a calculated value, although I don't think that's quite as relevant in this case. I'm building this to a relevant user scenario rather than for precise timings.

J, are you saying in this scenario you'd still have the user enter the start date/time and end date/time and store them both?

aaah, I see what you mean now. In this case, I might just store the time spent in minutes in a numeric field (i.e. smallint, depending on how many minutes they will be able to enter).

Then, add all the minutes for the client and use a function to convert it to hours, or just add it all up as minutes and display that.

Originally Posted by jmurrayhead

aaah, I see what you mean now. In this case, I might just store the time spent in minutes in a numeric field (i.e. smallint, depending on how many minutes they will be able to enter).

Then, add all the minutes for the client and use a function to convert it to hours, or just add it all up as minutes and display that.

That might make sense. I guess I could also have the input still in Hrs:Mins:Secs and convert that into minutes also...

Actually, just hrs and mins would make more sense, otherwise I'll end up with fractions of minutes...

Thanks J...

Originally Posted by richyrich

That might make sense. I guess I could also have the input still in Hrs:Mins:Secs and convert that into minutes also...

Exactly, make it easy for them to input, then convert it to the storage unit in code.

Originally Posted by richyrich

Actually, just hrs and mins would make more sense, otherwise I'll end up with fractions of minutes...

Indeed, that would make it too difficult.

Originally Posted by richyrich

Thanks J...