Re: Group by time periods.

[Quoted] [Quoted] On 29/04/2019 20.49, The Natural Philosopher wrote:
>
> I have a database with 5 minute samples of various quantities.
>
> e.g. consider a simplified table with
> id, value, timestamp
>
> where 'id' is integer, 'value' is a float and 'timestamp' is datetime.
>
>
> I would like to select in groups averaged by hour so that e,g., the
> output could be the average of 12 consecutive timestamps, plus a
> timestamp of the starting hour.
>
> wring procedural code to do it is not hard but I wondered if in fact
> there was a way to do it in Mysql.
>
> I thought of  of 'group by hour', but that would be just 24 samples!

[Quoted] [Quoted] Not sure if I misunderstood you, but something like

select

	count(*) as amount
	, avg(value)
	, cast(UNIX_TIMESTAMP(timestamp) / 60 * 15 as unsigned) as gdate
	, min(timestamp)
from tablename
	group by gdate;

This would make things grouped in section of 15 minutes, giving you the average value, number of rows and the time of the first event in that time segment.

As we use an alias for the calculation of the time frame, we don't have to repeat the calculation group by but use the alias. As we need to do the calculation of the time before we can group things, this will take some toll on the database server if you have a lot of rows.

-- 

  //Aho