MySQL从两个日期开始的总经过时间 [英] Mysql total elapsed times from two dates

问题描述

如果我有两行

id created          start            stop
1  28-01-2011 23:00 28-01-2011 23:00 28-01-2011 23:01  
2  28-01-2011 23:10 28-01-2011 23:10 28-01-2011 23:11

我可以运行哪种查询来获取一个日期的总经过时间，所以对于2011年1月28日，总时间为2分钟.

What query can i run to get the total elapsed time for one date, so for 28-01-2011 the total time was 2 minutes.

这可能吗?

推荐答案

一种选择很简单:

select sum(unix_timestamp(stop) - 
    unix_timestamp(start))/60 from table
    where date(created) = '28-01-2011';

mysql> select now(), date_add(now(), interval 10 minute);
+---------------------+-------------------------------------+
| now()               | date_add(now(), interval 10 minute) |
+---------------------+-------------------------------------+
| 2011-10-04 13:29:56 | 2011-10-04 13:39:56                 |
+---------------------+-------------------------------------+
1 row in set (0.01 sec)

mysql> select sum(unix_timestamp(now()) -
          unix_timestamp(date_add(now(), interval 10 minute)));
+----------------------------------------------------------------------------------+
| sum(unix_timestamp(now()) - unix_timestamp(date_add(now(), interval 10 minute))) |
+----------------------------------------------------------------------------------+
|                                                                             -600 |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select sum(unix_timestamp(now()) - 
          unix_timestamp(date_add(now(), interval 10 minute))) / 60 ;
+---------------------------------------------------------------------------------------+
| sum(unix_timestamp(now()) - unix_timestamp(date_add(now(), interval 10 minute))) / 60 |
+---------------------------------------------------------------------------------------+
|                                                                              -10.0000 |
+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这篇关于MySQL从两个日期开始的总经过时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！