Re: Group by time periods.

On 30.04.2019 06:54, J.O. Aho wrote:
> yours will group by hour (which would be equal to
> cast(UNIX_TIMESTAMP(timestamp) / 60 as unsigned))
...
> If you want 15 minutes intervals (as I originally wrote), then you need
> cast(UNIX_TIMESTAMP(timestamp) / 60 * 4 as unsigned).
...
> Sure yours is faster, but you can only group it in 1 hour intervals and next
> smaller would be 1 minute intervals...

Calculations based on the UNIX_TIMESTAMP will be reasonably fast. In fact I expect the conversion from DATETIME to be the most costly part here. In order to achive good performance, one would constrain the select to a range of timestamps and make sure there is an index on the `timestamp` column.

If this is meant to be used with many data sources, long time periods or in a highly concurrent environment, then I suggest to look at a specialized time-series database. Personally I'm very happy with rrdtool for that, but there are many more.

If you stay with MySQL (or another general purpose SQL DBMS) then you might want to look at how rrdtool manages data and adapt this to SQL. With that I mean to

• restrict the amount of data by constantly weeding out old rows
• generate and materialize(!) aggregate data, if needed you can define a whole hierarchy of aggregates
• besides averages you might also need min() or max() aggregates