I have a database with a mac and a timestamp where the device was observed. I want to calculate how many devices i have pr. hour in resolution of 10 minutes.
I have tried with:
SELECT
FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(`time`)/(60*10))*60*10) AS `time`
,COUNT(DISTINCT data.`mac`) AS `count`
FROM data
GROUP BY FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(`time`)/(60*10))*10*60);
This gives the wrong result if i compare it to a manual SELECT statement where i count for just 1 hour time interval.
I want to calculate how many distinct devices pr. hour in resolution of 10 minutes eq: count distinct macs in time interval 12:00:00 - 13:00:00 next interval should be 12:10:00 - 13:10 and so on...
I'm using a MYSQL database.