title: SQL Timestamps - calculating SLA
body: <p>I have several tickets and want to provide a reporting based on Service Level Agreements with PHP. </p>
  
  <p>A new ticket might have the status 'new' with a certain timestamp and after some time the status 'closed' with a certain timestamp. </p>
  
  <p>The difference between those two timestamps should be calculated, however only working hours from monday to friday (8am to 4.30 pm) should be relevant. So if the ticket is created at 4pm on Friday and closed at 9am on the following Monday, the time elapsed should be 1.5 hours. </p>
  
  <p>Anyone an idea how to get the excepted results out of the database? </p>
  
  <p>The database is a mysql database, the bugtracking system is the open source BTS Mantis Bugtracker.</p>
  
  <p>A pretty simple part of the database tables:</p>
  
  <p>table bug: </p>
  
  <p>ID | status | date_created (TIMESTAMP) | last_modified (TIMESTAMP)</p>
  
  <p>table history</p>
  
  <p>ID | bug_id (ref bug) | status_old | status_new | date_modified (TIMESTAMP)</p>
  
  <p>My query in PHP: 
  Gets all the bugs that were set to status 30 in a certain timeframe. 
  For the highest SLA level this fram is between 0 and 2 hours. 
  Query works fine - but the timeframe does not care about working hours...</p>
  
  <p>SELECT bug.id as ID 
      FROM mantis_bug_table as bug, mantis_bug_history_table as history
      WHERE history.new_status = 30 and history.bug_id = bug.id 
      AND (history.date_modified - bug.date_submitted) &lt; '{$timeframe_end}' 
      AND (history.date_modified - bug.date_submitted) > '{$timeframe_start}'";</p>