CODEWITHSUNDEEP
mysql
user1357015
user1357015

Reputation: 11686

counting number of observations in MySQL by 15 minute intervals

I'm trying to do something like this:

SELECT  COUNT(*) as numRecords, 
                ROUND(UNIX_TIMESTAMP(SqlUnixTime)/(15 * 60))  AS timeStamp 
            from myDB.myTABLE GROUP BY timeStamp WHERE SqlUnixTime > '1508618097';

I already have SqlUnixTime in my database. What I would like is a table that shows something like:

 timeStamp numRecords   
1   1676320        116 
2   1676321         80 
3   1676322         70 
4   1676323         78 
5   1676324        138 
6   1676325         44

I think I'm close but now sure where I am making the error.

Upvotes: 0

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

The correct syntax would be something like this:

SELECT ROUND(UNIX_TIMESTAMP(SqlUnixTime)/(15 * 60)) AS timeStamp,
      COUNT(*) as numRecords
FROM myDB.myTABLE
WHERE SqlUnixTime > 1508618097
GROUP BY timeStamp;

I would use FLOOR() instead of ROUND().

Upvotes: 1

Related Questions