Grouping into interval of 5 minutes within a time range

This works with every interval.

PostgreSQL

SELECT    TIMESTAMP WITH TIME ZONE 'epoch' +    INTERVAL '1 second' * round(extract('epoch' from timestamp) / 300) * 300 as timestamp,    name,    count(b.name)FROM time a, id WHERE …GROUP BY round(extract('epoch' from timestamp) / 300), name

MySQL

SELECT    timestamp,  -- not sure about that    name,    count(b.name)FROM time a, id WHERE …GROUP BY UNIX_TIMESTAMP(timestamp) DIV 300, name

I came across the same issue.

I found that it is easy to group by any minute interval isjust dividing epoch by minutes in amount of seconds and then either rounding or using floor to get ride of the remainder. So if you want to get interval in 5 minutes you would use 300 seconds.

    SELECT COUNT(*) cnt,     to_timestamp(floor((extract('epoch' from timestamp_column) / 300 )) * 300)     AT TIME ZONE 'UTC' as interval_alias    FROM TABLE_NAME GROUP BY interval_alias

interval_alias       cnt-------------------  ----  2010-11-16 10:30:00  22010-11-16 10:35:00  102010-11-16 10:45:00  82010-11-16 10:55:00  11 

This will return the data correctly group by the selected minutes interval; however, it will not return the intervals that don't contains any data. In order to get those empty intervals we can use the function generate_series.

    SELECT generate_series(MIN(date_trunc('hour',timestamp_column)),    max(date_trunc('minute',timestamp_column)),'5m') as interval_alias FROM     TABLE_NAME

Result:

interval_alias       -------------------    2010-11-16 10:30:00  2010-11-16 10:35:002010-11-16 10:40:00   2010-11-16 10:45:002010-11-16 10:50:00   2010-11-16 10:55:00   

Now to get the result with interval with zero occurrences we just outer join both result sets.

    SELECT series.minute as interval,  coalesce(cnt.amnt,0) as count from        (       SELECT count(*) amnt,       to_timestamp(floor((extract('epoch' from timestamp_column) / 300 )) * 300)       AT TIME ZONE 'UTC' as interval_alias       from TABLE_NAME  group by interval_alias       ) cnt        RIGHT JOIN        (           SELECT generate_series(min(date_trunc('hour',timestamp_column)),       max(date_trunc('minute',timestamp_column)),'5m') as minute from TABLE_NAME        ) series  on series.minute = cnt.interval_alias

The end result will include the series with all 5 minute intervals even those that have no values.

interval             count-------------------  ----  2010-11-16 10:30:00  22010-11-16 10:35:00  102010-11-16 10:40:00  02010-11-16 10:45:00  82010-11-16 10:50:00  0 2010-11-16 10:55:00  11 

The interval can be easily changed by adjusting the last parameter of generate_series. In our case we use '5m' but it could be any interval we want.

You should rather use GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 300 instead of round(../300) because of the rounding I found that some records are counted into two grouped result sets.