Get sql by consecutive hours

Question

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?

why should the result for user 143 be 2? sorry i dont quite get it but i really want to help you solve this — jogoe
– jogoe, Commented Nov 2, 2016 at 8:52
Because I get difference for consecutive hours for this user, So consecutive is : 2015-03-17 00:06:12 2015-03-17 01:06:12 2015-03-17 02:06:12 — Harea Costicla
– Harea Costicla, Commented Nov 2, 2016 at 8:54

fancyPants · Accepted Answer · 2016-11-02 09:38:53Z

2

Use a variable to compare with the previous row.

SELECT user_id, SUM(cont_hour) FROM (
    SELECT
    user_id,
    IF(CONCAT(DATE(@prev_date), ' ', HOUR(@prev_date), ':00:00') - INTERVAL 1 HOUR = CONCAT(DATE(t.date), ' ', HOUR(t.date), ':00:00')
AND @prev_user = t.user_id, 1, 0) AS cont_hour
    , @prev_date := t.date
    , @prev_user := t.user_id
    FROM
    table t
    , (SELECT @prev_date := NULL, @prev_user := NULL) var_init_subquery
    WHERE t.date BETWEEN <this> AND <that>
    ORDER BY t.date
) sq
GROUP BY user_id;

I made the comparison a bit more complicated than you expected, but I thought it's necessary, that you don't just compare the hour, but also, that it's the same date (or the previous day, when it's around midnight).

you can read more about user variables here

As a short explanation: The ORDER BY is very important, as well as the order in the SELECT clause. The @prev_date holds the "previous row", cause we assign the value of the current row after we made our comparison.

edited Nov 2, 2016 at 9:38

answered Nov 2, 2016 at 9:12

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Harea Costicla Over a year ago

I need to get all data between : WHERE date>='2016-11-01 00:00:00' AND date<='2016-11-01 23:59:59'

Harea Costicla Over a year ago

And group by user_id

Harea Costicla Over a year ago

The idee is to get data from 50 min to 70 min

fancyPants Over a year ago

"Not work"...Now that's an error message I can work with. Also this site is not a code writing service. When you don't get what the hell I'm doing here, you're supposed to ask. You should understand what's going on, not copy&paste answers. And just to be sure, it's not the first time, that this mistake was made, you have actually replaced <this> and <that>?

Ruslan Osmanov · Accepted Answer · 2016-11-02 09:53:05Z

1

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

edited Nov 2, 2016 at 9:53

answered Nov 2, 2016 at 9:20

Ruslan Osmanov

21.8k8 gold badges52 silver badges62 bronze badges

4 Comments

Harea Costicla Over a year ago

Exist another solution because I get error : 'Can't execute the query "SET @u := 0; :((((

Ruslan Osmanov Over a year ago

@HareaCosticla, check out this fiddle

Harea Costicla Over a year ago

the idee is that when I tried to execute in HeidiSql it's works fine, when I tried to execute query from PHP I get this error

Ruslan Osmanov Over a year ago

@HareaCosticla, try to split the queries into different PHP calls. By the way, with mysqli, you don't need to initialize the user variables with SET.

Collectives™ on Stack Overflow

Get sql by consecutive hours

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related