mysql datetime 差异,MySql在两个时间戳之间的差异？

How can I get the difference between two timestamps in days? Should I be using a datetime column for this?

I switched my column to datetime. Simple subtraction doesn't seem to give me a result in days.

mysql> SELECT NOW(), last_confirmation_attempt, NOW() - last_confirmation_attempt AS diff FROM DateClubs HAVING diff IS NOT NULL ;

+---------------------+---------------------------+-----------------+

| NOW() | last_confirmation_attempt | diff |

+---------------------+---------------------------+-----------------+

| 2010-03-30 10:52:31 | 2010-03-16 10:41:47 | 14001084.000000 |

+---------------------+---------------------------+-----------------+

1 row in set (0.00 sec)

I don't think diff is in seconds, because when I divide diff by number of seconds in a day ( 86,400 ), I don't get a sensical answer:

mysql> SELECT NOW(), last_confirmation_attempt, ( NOW() - last_confirmation_attempt) / 86400 AS diff FROM DateClubs HAVING diff IS NOT NULL ;

+---------------------+---------------------------+----------------+

| NOW() | last_confirmation_attempt | diff |

+---------------------+---------------------------+----------------+

| 2010-03-30 10:58:58 | 2010-03-16 10:41:47 | 162.0568402778 |

+---------------------+---------------------------+----------------+

1 row in set (0.00 sec)

解决方案

If you're happy to ignore the time portion in the columns, DATEDIFF() will give you the difference you're looking for in days.

SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;

+------+

| days |

+------+

| 17 |

+------+