mysql - 查找日期范围内的所有数据，然后计算每月总计

Question

我有一个 mysql 表，其中每条记录都有一个 startDateTime 和一个 finishDateTime。我希望能够返回每个月的总时间。

我当前的查询是这样的:

$dateRange = "BETWEEN '$startDate' AND '$finishDate'"; 

$sql = 
"SELECT 
    TIMESTAMPDIFF(MINUTE, StartDateTime, FinishDateTime) As Duration,
    StartDateTime As Start, FinishDateTime As Finish
FROM Entries
WHERE StartDateTime $dateRange AND FinishDateTime $dateRange";

表格条目

| StartDateTime       | FinishDateTime |
| 2016-08-18 10:00:00 | 2016-08-18 11:00:00 |
| 2016-08-18 12:00:00 | 2016-08-18 14:00:00 |
| 2016-08-31 17:00:00 | 2016-09-01 09:00:00 |

Desired Output
| Month               | Duration       |
| Aug                 | 10             |
| Sept                | 9              |

这将返回每条记录的持续时间，但不是每个月的总持续时间。我需要在此查询中添加或更改什么才能获取所需的数据？

Answer 1

假设每个条目都在同一个月开始和完成，请尝试以下操作:

$sql = 
    "SELECT 
        SUM(TIMESTAMPDIFF(HOUR, StartDateTime, FinishDateTime)) As Duration
    FROM Entries
    WHERE 
        YEAR(StartDateTime) = YEAR(FinishDateTime) 
        AND MONTH(StartDateTime) = MONTH(FinishDateTime)
        AND StartDateTime $dateRange 
        AND FinishDateTime $dateRange
    GROUP BY MONTH(StartDateTime)";