lundi 29 juin 2015

SQL: Group time By every 15 minutes

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

Aucun commentaire :

Enregistrer un commentaire