Re: Group by time periods.
From: Luuk <luuk_at_invalid.lan>
Date: Tue, 30 Apr 2019 12:28:07 +0200
Message-ID: <5cc82336$0$22344$e4fe514c_at_news.xs4all.nl>
>> On 29-4-2019 22:26, J.O. Aho wrote:
>>> 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 4 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)
Date: Tue, 30 Apr 2019 12:28:07 +0200
Message-ID: <5cc82336$0$22344$e4fe514c_at_news.xs4all.nl>
[Quoted] On 30-4-2019 06:54, J.O. Aho wrote:
> On 29/04/2019 23.13, Luuk wrote:
>> On 29-4-2019 22:26, J.O. Aho wrote:
>>> 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 4 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)
> > Kind of, but no, yours will group by hour (which would be equal to > cast(UNIX_TIMESTAMP(timestamp) / 60 as unsigned)), while > cast(UNIX_TIMESTAMP(timestamp) / 60 * 15 as unsigned) will group things > in 4 minute intervals. > 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... >
or use slight more complex expressions, to get grouping in 12 minute groups:
group by
date(timestamp),
hour(timestamp),
(minute(timestamp)-(minute(timestamp)%12))/12
-- LuukReceived on Tue Apr 30 2019 - 12:28:07 CEST