title: mysql calculate duration from two dates
body: <p>Hi I have a log database table in mysql which captures a start date and time and an end date and time.</p>
  
  <p>The start and now the stop time is inserted to the record.</p>
  
  <p>I have a third field which is duration which I would like displayed as hh:mm:ss</p>
  
  <p>The schema looks like this at present</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `log` (
    `uid` int(20) NOT NULL AUTO_INCREMENT,
    `room` int(11) NOT NULL,
    `start` datetime NOT NULL,
    `stop` datetime NOT NULL,
    `duration` datetime DEFAULT NULL,
    `participants` int(3) NOT NULL,
    `recorded` varchar(3) NOT NULL,
    `rec_file` varchar(35) NOT NULL,
    PRIMARY KEY (`uid`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Log File' AUTO_INCREMENT=106 ;
  </code></pre>
  
  <p>I'm trying to create a trigger which calculates the duration and writes it to the duration field as data is inserted</p>
  
  <p>Currently the trigger I am trying to debug looks like this</p>
  
  <pre><code>BEGIN
       SET NEW.duration = (TIMEDIFF (NEW.start,NEW.stop)) ;
  END
  </code></pre>
  
  <p>The result is the duration field remains set to 0000-00-00 00:00:00.000000</p>
  
  <p>Any suggestions on how to make this work are greatfully recived</p>