Counting working hours between dates in MySQL

Question

I am trying to create SQL query that calculates the number of hours between two dates (2015-01-01 12:12:12). The complexity of this query is on sum up hours only between Monday to Friday from 8am to 6pm.

I need it to calculate how long it took for a issue logged in our Mantis BT system to be resolved. Our clients can log issues outside our working hours, but I only want to calculate hours based on our working hours.

We calculate the difference between the date/time when the issue was logged (submitted_date) to when the issue was resolved (maximum updated_date and "resolved" status). The difficulty is that an issue can be resolved, and then reopened in the future. By using the maximum updated_date, my script is including the days that the issue was in "resolved" status, which is not correct. It should only include days while issue is open.

SELECT
    proj.name,
    bugs.id,
    summary,
    users2.username AS Assigned_to,
    CASE bugs.status
        WHEN  '80' THEN  'Resolved'
        WHEN  '90' THEN  'Closed'
    END AS Status,
    bugs.date_submitted AS date_submitted,
    MAX(date_modified) AS last_date_modified
FROM
    mantis_bug_table bugs
    LEFT JOIN mantis_bugnote_table notes ON notes.bug_id = bugs.id
    INNER JOIN mantis_bug_history_table hist ON bugs.id = hist.bug_id
    LEFT JOIN mantis_user_table users2 ON users2.id = bugs.handler_id
    INNER JOIN mantis_project_table proj ON proj.id = bugs.project_id 
WHERE
    bugs.project_id = 102 
    AND hist.field_name = 'status' 
    AND bugs.status IN (80,90) 
    AND date_modified >= '2015-01-01'
GROUP BY
    summary;

Answer 1

This tutorial is written for SQL, but can be replicated easily in MySQL.

You just need to define your working hours, then reference these to calculate the time.