Re: Group by time periods.

From: Luuk <luuk_at_invalid.lan>
Date: Mon, 29 Apr 2019 23:13:31 +0200
Message-ID: <5cc768fb$0$22360$e4fe514c_at_news.xs4all.nl>

On 29-4-2019 22:26, J.O. Aho wrote:
[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!

>
> 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.
>

I think this is about the same as what you are doing?

select

      count(*) as amount
      ,avg(value)
      ,min(timestamp)

from tablename
group by date(timestamp),hour(timestamp)

-- 
Luuk

Received on Mon Apr 29 2019 - 23:13:31 CEST