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

You can just subtract datetimes and it will return the value:

select (now() - interval 2 day) - (now() - interval 5 day);

The result isn't a datetime (it's some decimal coded date -- 3000000 for three days in this case), but it isn't correct to say consider a datetime and an interval as the same datatype.

Answer 2

You may try out DATEDIFF or TIMEDIFF

Answer 3

Hi,

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.