0

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.

Peter Savnik
  • 763
  • 1
  • 8
  • 27
  • 2
    Possible duplicate of [Group mysql query by 15 min intervals](http://stackoverflow.com/questions/2793994/group-mysql-query-by-15-min-intervals) – Alex Jun 30 '16 at 13:49
  • Does `FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(`time`)/(60*10))*60*10)` in the select list return what you expect? – jarlh Jun 30 '16 at 13:50
  • I gives me time groups of 10 minutes. But i don't get the right count of devices pr hour. – Peter Savnik Jul 01 '16 at 13:46

1 Answers1

0

i don't think ROUND is the right function should be used here. DIV is more appropriate. this would work 

SELECT   UNIX_TIMESTAMP(time) DIV (10 * 60) AS time
,COUNT(DISTINCT data.`mac`) AS `count`
FROM     data 
GROUP BY time;
Al Ameen
  • 312
  • 3
  • 9
  • I think you have a good point about using DIV instead. At least it saves the ROUND function. However in your sql suggestion it selects the count of distinct mac's for 10 minutes and not for 1 hour. Sorry if the question was not clear. 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 – Peter Savnik Jul 01 '16 at 13:40