How to calculate interval between datetime in MySQL?

Question

For example,how to calculate the interval between these two datetime:

2009-09-18 00:00:00

2009-10-17 00:00:00

EDIT

I mean to get the interval in the format of year-month-day hour:min:seconds

Answer 1

What about using datediff :

mysql> select abs(datediff('2009-09-18 00:00:00', '2009-10-17 00:00:00'));
+-------------------------------------------------------------+
| abs(datediff('2009-09-18 00:00:00', '2009-10-17 00:00:00')) |
+-------------------------------------------------------------+
|                                                          29 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

Quoting the manual :

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions.
Only the date parts of the values are used in the calculation.

Answer 2

You may try out DATEDIFF or TIMEDIFF