0

Suppose i have a table like this

id user_id activity_id start_time           duration
1   1       1          2015-12-02 12:24:22   00:17:25
1   1       2          2015-12-02 12:25:22   00:17:25
1   1       3          2015-12-02 12:26:22   00:17:25
1   1       4          2015-12-02 12:26:22   00:17:25
1   1       4          2015-12-02 12:27:22   00:17:25
1   1       4          2015-12-02 12:29:22   00:17:25
1   1       4          2015-12-02 12:33:22   00:17:25

Now,suppose i need a query something like which count the number within 3 minute from each other-for example like 12:24:22 is 4 it get count 24 25 and 26 12:25:22 is 2 it get count 25 and 24 12:25:22 is 4 it get count 25 26 and 27 12:26:22 is 4 it get count 24 25 and 26 12:26:22 is 3 it get count 26 and 27 ie it get count of near by 3 minute.

(but in Actually, i will have interval time is 10 minute). i need to count nearby every minute within interval from each.i see some solution like MySQL GROUP BY DateTime +/- 3 seconds. But i dont get how actually i can apply it in my suation.Can you please give me some hints to how to works with this. Thanks in advance.

Community
  • 1
  • 1
Mad Adh
  • 95
  • 9

2 Answers2

0
SELECT a.*, COUNT(a.start_time) AS TOT
FROM `your_table`AS a
INNER JOIN `your_table` AS b
WHERE ABS(TIME_TO_SEC(a.start_time) - TIME_TO_SEC(b.start_time)) < 180
GROUP BY a.start_time

Where 180 = 3 minutes(180sec)

@Jonathan Twite answer works too

Luca Giardina
  • 478
  • 4
  • 14
0

How about something like:

SELECT t1.start_time, COUNT(*) FROM table AS t1
CROSS JOIN table AS t2
WHERE ABS(TIMESTAMPDIFF(MINUTE, t1.start_time, t2.start_time)) < 10
GROUP BY t1.start_time

Note this would give matches with start_times within 10 minutes BOTH sides of the stated time.

Jonathan Twite
  • 932
  • 10
  • 24