用MySQL在几分钟内求和datediff [英] Sum datediff in minutes with MySQL

问题描述

我有一个任务表，当我开始执行任务tStart和结束任务tEnd时要在其中输入.我想知道一段时间内我在任务上花费了多少分钟.那么此查询有什么问题呢?

I have a table of tasks where I enter when I started a task tStart and when I ended the task tEnd. I would like to know how many minutes of work I spent on tasks over a period of time. So whats wrong with this query?

SELECT SUM(MINUTE(DATEDIFF(tEnd, tStart)))
FROM tasks
WHERE tStart >= '2011-04-04'
    AND tEnd <= '2011-04-04'

推荐答案

您的条件

WHERE tStart >= '2011-04-04' AND tEnd <= '2011-04-04'

非常确保，仅保留的记录在哪里

Pretty much ensures that the only records that will be left are where

tStart = tEnd = '2011-04-04'

不用说，它们之间的DATEDIFF为0，总和为0.实际上，DATEDIFF 忽略时间部分，因此您需要使用TIMEDIFF + TIME_TO_SEC

It goes without saying that DATEDIFF between them gives 0, for a grand total sum of 0. In fact, DATEDIFF ignores the time portion, so you need to use TIMEDIFF + TIME_TO_SEC

SELECT SUM(TIME_TO_SEC(TIMEDIFF(tEnd, tStart))/60)
FROM tasks
WHERE tStart >= '2011-04-04' AND tEnd <= adddate('2011-04-04', interval 1 day)

注意对WHERE子句的更改.如果您要说的是今天开始的任何一天，今天结束的任何时候，那么范围应该是(已经假设开始<结束)

Notice the change to the WHERE clause. If you are trying to say, started any time today and ended any time today, then the range should be (already assuming start < end)

WHERE tStart >= '2011-04-04'
  AND tEnd < adddate('2011-04-04', interval 1 day)

您可以将其硬编码为2011-04-05，但是我在这里显示的是使用<(小于)并将结束日期增加一天.

You can hard code it to 2011-04-05, but using < (less than) and adding one day to the end limit is what I am showing here.

这篇关于用MySQL在几分钟内求和datediff的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！