trying to calculate time difference (time spent)

[curtis_b]

I work for a commercial printing company. Today I started writing an application in php/mysql that our graphic designers will use to keep track of the time they are spending on specific projects - for billing purposes, project tracking, etc. It would be easy for me to let them fill in the time themselves, but that defeats the purpose. My goal is to have it act like a stop watch, they just click a button to start/stop the clock. I have 3 time columns - TimeStart, TimeEnd, and TimeTotal. I have already got to the point where the program auto fills in start and stop time, I'm just stuck on figuring out how to have the TimeTotal (difference of end - start) automatically fill in. I have read the dates + time section in mysql manual over and over. I have scoured the net for posts on this subject. I still can't figure it out. The MySQL manual references a function called TIMEDIFF. Not only can I not get it to work properly, the code examples given in the manual don't even work for me. Originally, I set up all the time columns as 'TIME', I tried switching them to 'TIMESTAMP' with no luck. If anyone out there knows how to do this (meaning you have successfully scripted said function) I would very much appreciate some guidance, and if you will, please dumb it down and be specific. Don't just post links to the mysql manual, I'm not lazy, I have read it, and for the first time - it's not helping. Thanks a lot! Curt

[shoz]

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']ALTER TABLE[/span] tablename MODIFY TimeStart DATETIME, MODIFY TimeEnd DATETIME [!--sql2--][/div][!--sql3--]

When entering the TimeStart and TimeEnd you'd use the NOW() function

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] table_name SET TimeStart = NOW() WHERE ...; [!--sql2--][/div][!--sql3--]

There's little need to Update the table with the TotalTime, as that can be calculated when SELECTing from the table

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] (SEC_TO_TIME(UNIX_TIMESTAMP(a_date) - UNIX_TIMESTAMP(a_date2))) AS TotalTime FROM tablename [!--sql2--][/div][!--sql3--]

If you'd still like to update the table with the result you can create a TIME column and use the same calculation in the UPDATE

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] tablename SET TotalTime = SEC_TO_TIME( ... WHERE ... [!--sql2--][/div][!--sql3--]

The TIMEDIFF function might not have been working because of the following

TIMEDIFF() was added in MySQL 4.1.1.

If you're not thinking of doing this already you may want to have a table such as the following. So that users can start and stop working on different projects.

proj_time
---------------------------------------------------
pt_id user_id    proj_id    time_start    time_end
1         2                4          start1           end1
2         2                4          start2           end2
---------------------------------------------------

When calculating the total you'd use something similar to

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] SEC_TO_TIME(SUM(UNIX_TIMESTAMP(time_start)-UNIX_TIMESTAMP(time_end))) FROM proj_time WHERE user_id = 2 [!--sql2--][/div][!--sql3--]

Remember to also manage whether or not the start and end time buttons should be active. Checking again when the script recieves the user's entry.

 

eg:

$query = SELECT * FROM table WHERE pt_id = ptid AND user_id = uid AND start_time > 0 AND end_time = 0
if (!mysql_num_rows($result))
{
   print 'couldn't complete request. Perhaps you've already entered a stop time';
}
else
{
    UPDATE
}

The query above will only work if you make the columns NOT NULL. If not, use the ISNULL() function instead of the > 0 = 0 comparison. Keep in mind also that it's only an example and you'll need to do more to make it fully useable.

 

Btw, It's easier to read what you post if you break the text into small paragraphs.

[curtis_b]

Shoz, thanks so much for the response. I haven't had time to get back to this project today, but I will definately make use of your help!