Group MySQL Query by 15 Min Intervals

Group mysql query by 15 min intervals

SELECT   FLOOR(UNIX_TIMESTAMP(timestamp)/(15 * 60)) AS timekey
FROM     table
GROUP BY timekey;

Mysql DateTime group by 15 mins

One way is to just use > and < in order to get everything within a range, but you may find this to be simpler:

SELECT ... 

GROUP BY ( 4 * HOUR( thistime ) + FLOOR( MINUTE( thistime ) / 15 )) 

from
http://forums.mysql.com/read.php?10,202789,202807

MySQL - Grouping Time Ranges by Fifteen Minute Intervals

Alright. It took some doing, but I figured it out with RToyo's help:

First: create a list of fifteen minute intervals:

        select 
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) as 'IntervalStart',
        addtime(time('00:14:59'),sec_to_time(900 * (a.a + b.a * 10))) as 'IntervalEnd'
    from (
        select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    where
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) between '06:00:00' and '18:45:00'

Next: join the Timekeeping table by testing the overlap between the StartTime and Endtime fields:

left join (     select 
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) as 'Interval Start',
        addtime(time('00:14:59'),sec_to_time(900 * (a.a + b.a * 10))) as 'Interval End'
    from (
        select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    where
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) between '06:00:00' and '18:45:00') as Intervals
on timekeepingtable.StartTime <= Intervals.IntervalStart
and timekeepingtable.EndTime >= Intervals.IntervalEnd

Then just modify the StartTime and EndTime fields to be bounded by the intervals:

        if(time(pgrp.StartTime) < Intervals.IntervalStart,Intervals.IntervalStart,time(pgrp.StartTime)) as 'Start Time',
    if(time(pgrp.EndTime) > Intervals.IntervalEnd, Intervals.IntervalEnd,time(pgrp.EndTime)) as 'End Time',

How to group datebase records into 15-minute time intervals

First of all, you have a subtle error in your WHERE clause. You need:

where access_time >= '2013-05-28 02:00:00' 
  and access_time < '2013-05-28 10:00:00'

because your quarter-hour ranges run from a particular time until the moment before another particular time. You need <, not <=, for the end of your time range.

Then, you need an expression that can take an arbitrary DATETIME expression and convert it to the DATETIME of the beginning of the quarter-hour in which it occurs.

This will do that.

DATE_FORMAT(datestamp,'%Y-%m-%d %H:00:00') +
            INTERVAL (MINUTE(datestamp) -
                      MINUTE(datestamp) MOD 15) MINUTE

It turns, for example '2014-05-07 14:53:22', into '2014-05-07 14:45:00'.

You can define it as a stored function like this if you like:

DELIMITER $$
DROP FUNCTION IF EXISTS `TRUNC_15_MINUTES`$$
CREATE  FUNCTION `TRUNC_15_MINUTES`(datestamp DATETIME) 
                 RETURNS DATETIME
    NO SQL
    DETERMINISTIC
    RETURN DATE_FORMAT(datestamp,'%Y-%m-%d %H:00:00') +
                INTERVAL (MINUTE(datestamp) -
                          MINUTE(datestamp) MOD 15) MINUTE$$
DELIMITER ;

You can then write your query like this:

 select TRUNC_15_MINUTES(access_time) AS period_starting,
        user, count(user) as users 
   from user_access
 where access_time >= '2013-05-28 02:00:00' 
   and access_time <  '2013-05-28 10:00:00'
 group by TRUNC_15_MINUTES(access_time), user
 order by TRUNC_15_MINUTES(access_time), user

This is written up here. http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

SQL: Group time By every 15 minutes

With

create table foo (id int, date date, time_stamp time);

insert into foo (id, date, time_stamp) values (1, '2015-06-12', '17:26:15');
insert into foo (id, date, time_stamp) values (2, '2015-06-12', '17:26:15');
insert into foo (id, date, time_stamp) values (3, '2015-06-12', '17:26:15');
insert into foo (id, date, time_stamp) values (4, '2015-06-12', '17:36:21');
insert into foo (id, date, time_stamp) values (5, '2015-06-12', '17:36:21');
insert into foo (id, date, time_stamp) values (6, '2015-06-12', '17:36:21');
insert into foo (id, date, time_stamp) values (7, '2015-06-12', '18:36:21');
insert into foo (id, date, time_stamp) values (8, '2015-06-12', '18:36:15');
insert into foo (id, date, time_stamp) values (9, '2015-06-12', '18:36:15');
insert into foo (id, date, time_stamp) values (10, '2015-06-12', '18:36:15');

It could look like

select
    date,
    count(*),
    min(addtime(date, time_stamp)) min_time,
    max(addtime(date, time_stamp)) max_time,
    date_add(date, interval timestampdiff(second, date, addtime(date, time_stamp)) div 15 * 15 minute) start_time
from
    foo
group by
    date,
    timestampdiff(minute, date, addtime(date, time_stamp)) div 15

See http://sqlfiddle.com/#!9/c5ced/37/0

MYSQL grouping a date interval by past 10 minutes

The problem is the WHERE clause. You need to remove the excess seconds before doing the aggregation.

One method is to convert to seconds, divide by 60, truncate the number, and multiply by 60. The following converts the value back to a date/time value, so an index on that column will be used:

SELECT date_format(timestamp, '%H:%i:%s'), COUNT(*) as count
FROM test
WHERE timestamp >= from_unixtime(floor(unix_timestamp(now()) / 60) * 60) - interval 10 minute AND
      timestamp < from_unixtime(floor(unix_timestamp(now()) / 60) * 60)
GROUP BY DATE_FORMAT(timestamp, '%i')
ORDER BY timestamp ASC

mysql group based on time intervals

Your query appears to be correct it's just that your data is unique on interval/beaconid/getewayid so it happens to return 'all' rows...

If I understand your data you probably want to group by 5 min intervals, and to get a value that is the same for every five min interval you may opt to just divide the timestamp by 300 seconds - something like:

select min(`datetime`) as `start`,
  (sum(RSSI)/count(*)) as mean_rssi,
  (sum(distance)/count(*)) as mean_distance,
  beaconid,
  gatewayid 
from archive 
group by UNIX_TIMESTAMP(`datetime`) / 60*5,
  beaconid,
  gatewayid

also use sqlfiddle or rextester it is much easier to help you if you use those...

Group table into 15 minute intervals

;with cte_max as 
(
   select dateadd(mi, -15, max(StatusEndDateTime)) as EndTime, min(StatusSetDateTime) as StartTime
   from AgentActivityLog
), times as
(
    select StartTime as Time from cte_max
    union all
    select dateadd(mi, 15, c.Time)
    from times as c
        cross join cte_max as cm
    where c.Time <= cm.EndTime
)
select
    t.Time, A.UserID, A.Status,
    case
        when t.Time = A.StatusEndDateTime then 0
        else A.StatusDuration / (count(*) over (partition by A.StatusSetDateTime, A.UserID, A.Status) - 1)
    end as Duration
from AgentActivityLog as A
    left outer join times as t on t.Time >= A.StatusSetDateTime and t.Time <= A.StatusEndDateTime

sql fiddle demo

Is there a way to group by intervals of 15 min in DuckDB?

Add an expression for the quarter hour:

(extract(minute from createdat) / 15)::integer

to your columns:

select
  sum(tickets) AS total,
  extract(year from createdat),
  extract(month from createdat),
  extract(day from createdat),
  extract(hour from createdat),
  (extract(minute from createdat) / 15)::integer
from counter
where id = ?
group by
  extract(year from createdat),
  extract(month from createdat),
  extract(day from createdat),
  extract(hour from createdat),
  (extract(minute from createdat) / 15)::integer

Casting to integer truncates the fractional part of the division result.

---

Related Topics