mysql如何计算两个时间相差的天数？

[colour_trace]

google了一下，用datediff， 不符合要求，例子如下：
select datediff('2009-09-27 00:00:00','2009-09-26 23:59:59' ) 结果是1，不符合要求，明明才相差那么一点，根本不够一天，mysql的datediff，是取一个时间的日期后再进行比较的；
如果用 timediff
select timediff('2009-09-27 00:00:00','2009-09-26 23:59:59' ) 结果为00:00:01
select timediff('2009-10-01 12:35:11','2009-09-26 23:59:59' ) 结果为108:35:12
可是108:35:12这样的结果，怎么转换成天阿，谢谢

[ACMAIN_CHM]

关键要看你对 相差一天的定义是什么？ '

2009-09-27 00:00:00','2009-09-26 23:59:59' 结果应该是什么？
2009-09-27 23:59:58,'2009-09-26 23:59:59' 结果应该是什么 ?


用下面方法算到小数，然后可以自行再处理以达到你的要求。

mysql> select (UNIX_TIMESTAMP('2009-09-27 00:00:00') - UNIX_TIMESTAMP('2009-09-26 23:59:59'))/60/60/24 as dd;
+----------------+
| dd             |
+----------------+
| 0.000011574074 |
+----------------+
1 row in set (0.00 sec)

mysql> select (UNIX_TIMESTAMP('2009-10-01 12:35:11') - UNIX_TIMESTAMP('2009-09-26 23:59:59'))/60/60/24 as dd;
+----------------+
| dd             |
+----------------+
| 4.524444444444 |
+----------------+
1 row in set (0.00 sec)

mysql>

[ACMAIN_CHM]

mysql> select TIME_TO_SEC(timediff('2009-09-27 00:00:00','2009-09-26 23:59:59' )
)/60/60/24 as dd;
+----------------+
| dd             |
+----------------+
| 0.000011574074 |
+----------------+
1 row in set (0.00 sec)

mysql> select TIME_TO_SEC(timediff('2009-10-01 12:35:11','2009-09-26 23:59:59' )
)/60/60/24 as dd;
+----------------+
| dd             |
+----------------+
| 4.524444444444 |
+----------------+
1 row in set (0.00 sec)

mysql>

[sciland]

就用后面一种，取到之后转化一下就好了，而且也很精确
108:35:12这样的结果把108/24，取整就是4天，余12个小时，35分，12秒，用字符串转化判断下就好了

[nianzhang747]

mysql> set @time=TIME_TO_SEC(timediff('2009-10-01 12:35:11','2009-09-26 23:59:59'))/60/60/24;
Query OK, 0 rows affected (0.00 sec)

mysql> select floor(@time) into @day;
Query OK, 1 row affected (0.00 sec)

mysql> select floor((@time-@day)*24)  into @hour;
Query OK, 1 row affected (0.00 sec)

mysql> select floor(((@time-@day)*24-@hour)*60) into @minute;
Query OK, 1 row affected (0.00 sec)

mysql> select floor((((@time-@day)*24-@hour)*60-@minute)*60) into @second;
Query OK, 1 row affected (0.00 sec)

mysql> select concat(@day,' ',@hour,':',@minute,':',@second);
+------------------------------------------------+
| concat(@day,' ',@hour,':',@minute,':',@second) |
+------------------------------------------------+
| 4 12:35:11                                     |
+------------------------------------------------+
1 row in set (0.00 sec)