What is the best way to find the difference between two dates in a database??

I have a 'received' and a 'replied' field and i want to find the response time

Please can anyone help

I don't know if this is the best method, but I usually want the difference in seconds and then UNIX_TIMESTAMP() is the way to go.

So

SELECT id, UNIX_TIMESTAMP(replied)-UNIX_TIMESTAMP(received) AS resptime FROM tab;

would give you the difference between the two times in seconds.

you can then use the SEC_TO_TIME function to convert the seconds to HH:MM format

note: this works only if the seconds are less than 86400 (one day), but you can use CASE expressions with some further math to extract the remainder (modulus)

DATEDIFF() was added in MySQL 4.1.1.

DATEDIFF(expr,expr2)
DATEDIFF() returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31