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

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这样的结果，怎么转换成天阿，谢谢

解决方案 »

  1.   

    关键要看你对 相差一天的定义是什么？ '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>
      

  2.   

    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>
      

  3.   

    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)