Geek Answers Handbook

Amount dates in minutes with MySQL

I have a task table that I enter in when I started the tStart task and when I finished the tEnd task. I would like to know how many minutes of work I spent on tasks over a period of time. So what is wrong with this request?

SELECT SUM(MINUTE(DATEDIFF(tEnd, tStart)))
FROM tasks
WHERE tStart >= '2011-04-04'
    AND tEnd <= '2011-04-04'
+5
source share
1 answer

Your condition

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

To a large extent, ensures that the only entries to be left are where

tStart = tEnd = '2011-04-04'

It goes without saying that the DATEDIFF in between gives 0 for a total of 0. In fact, the DATEDIFF ignores the time part, 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. , , ( start < end)

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

2011-04-05, < () , .

+7

More articles:


All Articles