Jump to content

calculate times


Recommended Posts

i have no idea how to do this in all honesty, hence my post.

i have a table called task_statuses, which records the statuses and times of a given task,  and those tasks are allocated to an engineer for the day.

before the engineer is at the task he/she will hit a button called Notify, which will send an sms and email to notify an engineer is on the way....when at the tasks the engineer has 3 further statuses to choose from depending on the outcome of task No Access, Completed or Rearranged.


task statuses
Uncommenced                        default (no task status)                                                      
Notified                           start                                                      
No Access|Completed|Rearranged     end


If the engineer has no access at 8am, then there would be 2 statues added.

1. Notifiied

2. No Access


now the engineer may try again at 11am.  so before arriving the engineer will click a button called retry which will add Notified to the tasks_statuses and then is able to choose the 2nd outcome of the task

3. Notifed (Back to square one)

4. 2nd Outcome (Completed?)


what i want to do is caluclate the complete time spent on the task.


Here is a typical example:


1st attempt: 60 mins

2nd attempt: 28 mins.

Total task time : 88 mins


How is this possible please?

Thank you


Link to comment
Share on other sites

It's case of "If you want to go there, I wouldn't start from here".

If the second visit were a separate task (so the first two as task #1 and second two are task #2, then life would be easier.

It's then simple (pseudocode)

SELECT MAX(created) - MIN(created) GROUP BY task_id.

You can then aggregate by each engineer for the day


  • Like 1
Link to comment
Share on other sites

For the sake of completeness, given

mysql> select * from task;
| task_id | user_id |
|       1 |       1 |
|       2 |       1 |
|       3 |       2 |

mysql> select * from task_status;
| ts_id | task_id | status    | created             |
|     1 |       1 | Notified  | 2018-09-22 15:30:00 |
|     2 |       1 | No access | 2018-09-22 16:30:00 |
|     3 |       2 | Notified  | 2018-09-23 09:30:00 |
|     4 |       2 | Completed | 2018-09-23 09:58:00 |
|     5 |       3 | Notified  | 2018-09-22 10:30:00 |
|     6 |       3 | Completed | 2018-09-22 11:45:00 |


     , SEC_TO_TIME(SUM(time_to_sec(timetaken))) as totaltime
        SELECT task_id
             , timediff(MAX(created), MIN(created)) as timetaken
        FROM task_status
        GROUP BY task_id
        ) tots USING (task_id)
GROUP BY user_id;

| user_id | totaltime |
|       1 | 01:28:00  |
|       2 | 01:15:00  |


Link to comment
Share on other sites

Beautiful stuff barand, although I've dropped the task statuses and added status, start time stamp and end timestamp to my tasks tablet and did it that way.  

Ps. I've still used what you've done for me previosuly but adding this will alter task times depending on task start and end, making things more accurate.

Thank you again for your efforts

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.