CODEWITHSUNDEEP
mysqldaterange
Marco
Marco

Reputation: 57573

Counting rows between dates interval

I have a table having, among others, two DATETIME columns date_start and date_end. Now I need to count how many occurrencies I have in every interval, grouped by minute.

Sample

id   date_start            date_end
01   2014-01-01 08:10:28   2014-01-01 08:12:35   <-- 08:10, 08.11, 08.12 
02   2014-01-01 08:10:58   2014-01-01 08:11:26   <-- 08:10, 08.11
03   2014-01-01 08:11:04   2014-01-01 08:12:01   <-- 08:11, 08.12
04   2014-01-01 08:11:56   2014-01-01 08:11:58   <-- 08:11 
05   2014-01-01 08:12:06   2014-01-01 08:12:09   <-- 08:12
06   2014-01-01 08:14:06   2014-01-01 08:14:20   <-- 08:14

should produce

time               count
2014-01-01 08:10   2
2014-01-01 08:11   4
2014-01-01 08:12   3
2014-01-01 08:14   1

Probably I need a temporary table to store times in every interval so I can group them later...

Upvotes: 1

Views: 109

Answers (1)

Tin Tran
Tin Tran

Reputation: 6202

Try this one, just replace yourtable with your actual table name (2 occurences of yourtable).

SELECT DATE_FORMAT(H.hardTime,'%Y-%m-%d %H:%i') as time,
       COUNT(*) as count
FROM
(SELECT DATE_FORMAT(MIN(T1.date_start),'%Y-%m-%d %H:%i:00') + INTERVAL V.value MINUTE as hardTime
 FROM yourtable T1
 ,(SELECT T1.value+T2.value+T3.value+T4.value+T5.value+T6.value+T7.value
         as value FROM 
   (SELECT 0 as value UNION SELECT 1)T1,
   (SELECT 0 as value UNION SELECT 2)T2,
   (SELECT 0 as value UNION SELECT 4)T3,
   (SELECT 0 as value UNION SELECT 8)T4,
   (SELECT 0 as value UNION SELECT 16)T5,
   (SELECT 0 as value UNION SELECT 32)T6,
   (SELECT 0 as value UNION SELECT 64)T7
  )V
 GROUP BY V.value
 )H
INNER JOIN yourTable Y
ON H.hardTime BETWEEN DATE_FORMAT(Y.date_start,'%Y-%m-%d %H:%i:00') 
                  AND DATE_FORMAT(Y.date_end,'%Y-%m-%d %H:%i:00')
GROUP BY H.hardTime
ORDER BY H.hardTime

sqlfiddle (this sqlfiddle has data as before you edited your question).

Upvotes: 2

Related Questions