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


Share this post

Link to post
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

Share this post

Link to post
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  |


Share this post

Link to post
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

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


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.