title: Sum timestamp differences inside a time interval
body: <p>I use Firebird. I want to find the total working time between two dates for a given contact.</p>
  
  <p>There are two tables: WORK and PAY</p>
  
  <pre><code>IDWORK | IDCONTACTS | STARTWORK        | ENDWORK
  ----------------------------------------------------------
   1     | 14         | 04/20/2014 09:02 | 04/20/2014 18:10
   2     | 14         | 04/21/2014 08:41 | 
   3     | 14         | 04/20/2014 08:56 | 04/20/2014 17:01
   4     | 10         | 04/18/2014 09:28 | 04/18/2014 16:22
  
  
  IDPAY | IDCONTACTS | DATE1            | DATE2            | WORKTIME
  --------------------------------------------------------------------
  1     | 14         | 04/10/2014 09:02 | 04/27/2014 09:02 |
  </code></pre>
  
  <p>I want to find the total working time between DATE1 and DATE2. I tried to make a trigger on PAY before insert and update:</p>
  
  <pre><code>begin
      UPDATE PAY
          SET WORKTIME = (SELECT SUM(DATEDIFF(MINUTE FROM STARTWORK TO ENDWORK))
                      FROM WORK, PAY
                           WHERE WORK.IDCONTACTS = PAY.IDCONTACTS
                           AND (STARTWORK BETWEEN DATE1 AND DATE2)
                           GROUP BY WORK.IDCONTACTS)
          WHERE NEW.IDPAY = OLD.IDPAY;
  end
  </code></pre>
  
  <p>but it doesn't work. I also have to check if ENDWORK has a NULL value and raise an error.</p>