Harea Costicla Harea Costicla - 1 month ago 14
MySQL Question

Get sql by consecutive hours

I have a question. Suppose I have this table in SQL:

date user_id
2015-03-17 00:06:12 143
2015-03-17 01:06:12 143
2015-03-17 02:06:12 143
2015-03-17 09:06:12 143
2015-03-17 10:10:10 200


I want to get the number of consecutive hours. For example, for user 143, I want to get 2 hours, for user 200 0 hours. I tried like this :

select user_id, TIMESTAMPDIFF(HOUR,min(date), max(date)) as hours
from myTable
group by user_id


But this query fetches all non-consecutive hours. Is it possible to solve the problem with a query, or do I need to post-process the results in PHP?

Answer

Another version using temporary variables:

SET @u := 0;
SET @pt := 0;
SET @s := 0;

SELECT `user_id`, MAX(`s`) `conseq` FROM
(
  SELECT
  @s := IF(@u = `user_id`,
    IF(UNIX_TIMESTAMP(`date`) - @pt = 3600, @s + 1, @s),
      0) s,
  @u := `user_id` `user_id`,
  @pt := UNIX_TIMESTAMP(`date`) pt
  FROM `users`
  ORDER BY `date`
) AS t
GROUP BY `user_id`

The subquery sorts the rows by date, then compares user_id with the previous value. If user IDs are equal, calculates the difference between date and the previous timestamp @pt. If the difference is an hour (3600 seconds), then the @s counter is incremented by one. Otherwise, the counter is reset to 0:

s user_id pt
0 143     1426529172
1 143     1426532772
2 143     1426536372
2 143     1426561572
0 200     1426565410

The outer query collects the maximum counter values per user_id, since the maximum counter value corresponds to the last counter value per user_id.

Output

user_id conseq
143     2
200     0     

Note, the query accepts the difference of exactly 1 hour. If you want a more flexible condition, simply adjust the comparison. For example, you can accept a difference in interval between 3000 and 4000 seconds as follows:

  @s := IF(@u = `user_id`,
    IF( (UNIX_TIMESTAMP(`date`) - @pt) BETWEEN 3000 AND 4000, @s + 1, @s),
      0) s