SQL: Group time By every 15 minutes

Question

I have a table with the following structure

id      date      timestamp  licenseid   storeid    deviceid    value
1     2015-06-12   17:36:15   lic0001       1         0add      52
2     2015-06-12   17:36:15   lic0002       1         0add      54
3     2015-06-12   17:36:15   lic0003       1         0add      53
4     2015-06-12   17:36:21   lic0001       1         0add      54
5     2015-06-12   17:36:21   lic0002       1         0add      59
6     2015-06-12   17:36:21   lic0003       1         0add      62
7     2015-06-12   17:36:21   lic0004       1         0add      55
8     2015-06-12   17:36:15   lic0001       1         0bdd      53
9     2015-06-12   17:36:15   lic0002       1         0bdd      52
10    2015-06-12   17:36:15   lic0003       1         0bdd      52

I need to check if any of the license has stopped reporting values. The approach I am thinking of is getting time_stamps in group of 15 minutes and then checking if the data is missing for any license for longer duration and then deep diving.

Need help with Query that can group by time_stamp every 15 minutes starting from 00:00 and showing count of values within that time duration. I have tried the below query but the time stamp doesn't show the correct value. It is showing just one row with timekey as 0.

SELECT count(*),  round(unix_timestamp(time_stamp)/(15*60)) AS timekey
FROM     data
GROUP BY timekey;

PS: I have used this query from this post as stated above but the query returns 0 for timekey : Group mysql query by 15 min intervals

possible duplicate of [Group mysql query by 15 min intervals](http://stackoverflow.com/questions/2793994/group-mysql-query-by-15-min-intervals) — Robert, Jun 29 '15 at 11:09

Gordon Linoff · Answer 1 · 2015-06-29T19:59:22.000

0

UNIX_TIMESTAMP() doesn't work on a time argument. It works on a datetime. So, one method is:

SELECT count(*), 
       round(unix_timestamp(addtime(date(0), time_stamp) )/(15*60)) AS timekey
FROM data
GROUP BY timekey;

EDIT:

You can always use the brute force approach:

SELECT COUNT(*), hour(timestamp) as hr, floor(minute(timestamp) / 15) as minute
FROM data
GROUP BY hr, minute;

edited Jun 29 '15 at 19:59

answered Jun 29 '15 at 11:12

Gordon Linoff

1,242,037
58
646
786

This is returning null for timekey – fheo Jun 29 '15 at 13:28

score 0 · Answer 2 · answered Jun 29 '15 at 11:20

I don't have MySQL to test my proposed solution at the moment, but can you try using CASE statement?

SELECT (CASE 
WHEN MINUTE(timestamp) BETWEEN 0 AND 15 THEN 1
WHEN MINUTE(timestamp) BETWEEN 16 AND 30 THEN 2
WHEN MINUTE(timestamp) BETWEEN 31 AND 45 THEN 3
ELSE 4 END) AS MINUTEGROUP

Then Group By on MINUTEGROUP

Tomalak · Accepted Answer · 2015-06-29T14:43:21.343

With

create table foo (id int, date date, time_stamp time);

insert into foo (id, date, time_stamp) values (1, '2015-06-12', '17:26:15');
insert into foo (id, date, time_stamp) values (2, '2015-06-12', '17:26:15');
insert into foo (id, date, time_stamp) values (3, '2015-06-12', '17:26:15');
insert into foo (id, date, time_stamp) values (4, '2015-06-12', '17:36:21');
insert into foo (id, date, time_stamp) values (5, '2015-06-12', '17:36:21');
insert into foo (id, date, time_stamp) values (6, '2015-06-12', '17:36:21');
insert into foo (id, date, time_stamp) values (7, '2015-06-12', '18:36:21');
insert into foo (id, date, time_stamp) values (8, '2015-06-12', '18:36:15');
insert into foo (id, date, time_stamp) values (9, '2015-06-12', '18:36:15');
insert into foo (id, date, time_stamp) values (10, '2015-06-12', '18:36:15');

It could look like

select
    date,
    count(*),
    min(addtime(date, time_stamp)) min_time,
    max(addtime(date, time_stamp)) max_time,
    date_add(date, interval timestampdiff(second, date, addtime(date, time_stamp)) div 15 * 15 minute) start_time
from
    foo
group by
    date,
    timestampdiff(minute, date, addtime(date, time_stamp)) div 15

See http://sqlfiddle.com/#!9/c5ced/37/0

What is the difference between this one and the previous one you had with minute as the criteria? — fheo, Jun 29 '15 at 13:26
None, I switched to seconds for consistency. But you are right, switching to minutes consistently makes a lot more sense. See edit. Equivalent, but the meaning is clearer. — Tomalak, Jun 29 '15 at 14:42

SQL: Group time By every 15 minutes

3 Answers3