Re: Group by time periods.

In article <5cc82336$0$22344$e4fe514c_at_news.xs4all.nl>, Luuk <luuk_at_invalid.lan> wrote:
> 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

[Quoted] group by date(timestamp), hour(timestamp), minute(timestamp) div 12

Cheers
Tony

-- 
Tony Mountifield
Work: tony_at_softins.co.uk - http://www.softins.co.uk
Play: tony_at_mountifield.org - http://tony.mountifield.org