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