Re: Group by time periods.

In article <giq2naFiasgU1_at_mid.individual.net>, J.O. Aho <user_at_example.net> 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...

You can always add another group:

select
      count(*) as amount
      ,avg(value)
      ,min(timestamp)
from tablename
group by date(timestamp), hour(timestamp), minute(timestamp) div 15

(Note that "div" is integer division, so no need for casts or floors).

Cheers
Tony

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