Mysql – Grouping data into 5 minute intervals within a time range

chart;database-designgroup byMySQL

I am trying to prepare data for a candle stick chart. I have some records on MySQL database as shown below:

| Rate     | Date                |
|----------|---------------------|
| 0.001111 | 2017-11-12 19:13:05 |
| 0.001214 | 2017-11-12 19:14:11 |
| 0.001612 | 2017-11-12 19:15:42 |
| 0.001231 | 2017-11-12 19:16:23 |
| 0.001514 | 2017-11-12 19:17:23 |
| 0.001611 | 2017-11-12 19:26:33 |
| 0.001421 | 2017-11-12 19:27:25 |
| 0.001214 | 2017-11-12 19:28:27 |
| 0.001611 | 2017-11-12 19:29:55 |
|----------|---------------------|

I want to group below data as shown below:

| Rate     | Date                |
|----------|---------------------|
| 0.002325 | 2017-11-12 19:10:00 |
| 0.004357 | 2017-11-12 19:15:00 |
| 0.004357 | 2017-11-12 19:20:00 |  # Auto generated record of missing record.
| 0.005857 | 2017-11-12 19:25:00 |
|----------|---------------------|

I was trying to understand calendar table, but I wasn't able to do with that.

I also followed this link https://stackoverflow.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range but still failed.

UPDATE:

This is my sample query that I am using currently to get data with date sorting and grouping.

SELECT 
SUM(exchange_rat) AS totalAmount, 
DATE_FORMAT(from_unixtime(unix_timestamp(created_at) - unix_timestamp(created_at) mod 300), '%Y-%m-%d %H:%i:00') as createdAt 
from payments group by createdAt

Best Answer

This is a very basic implementation of a calendar table. As you cans see, it simply group dates using intervals.

drop table if exists calendar;
drop table if exists intrv;

create table calendar(id int, dt timestamp);

insert into calendar values 
(1, '2017-11-12 19:10:00'), 
(1, '2017-11-12 19:15:00'),
(1, '2017-11-12 19:20:00'),
(1, '2017-11-12 19:25:00'),
(1, '2017-11-12 19:30:00');

create table intrv (Rate decimal(18,6), Dt timestamp);

insert into intrv values
(0.001111, '2017-11-12 19:13:05'),
(0.001214, '2017-11-12 19:14:11'),
(0.001612, '2017-11-12 19:15:42'),
(0.001231, '2017-11-12 19:16:23'),
(0.001514, '2017-11-12 19:17:23'),
(0.001611, '2017-11-12 19:26:33'),
(0.001421, '2017-11-12 19:27:25'),
(0.001214, '2017-11-12 19:28:27'),
(0.001611, '2017-11-12 19:29:55');

select    c.dt as dt1,
          sum(i.Rate) 
from      calendar c
left join intrv i
on        i.dt >= c.dt 
and       i.dt < date_add(c.dt, interval 5 minute)
group by  c.dt;

| dt1                 | rate     |
|---------------------|----------|
| 12.11.2017 19:10:00 | 0,002325 |
| 12.11.2017 19:15:00 | 0,004357 |
| 12.11.2017 19:20:00 | NULL     |
| 12.11.2017 19:25:00 | 0,005857 |
| 12.11.2017 19:30:00 | NULL     |

rextester here

For the second part of the question, you should explain how to auto-generate the desired values.

Related Solutions

Group data by non-unique keys by distinct time range

Below a complete solution for sql-server.

I was trying analytical functions, but didn't succeed. I'm tempted to repost the question to SO requiring a solution using analytical functions. I think there must be one.

And here is my working solution. I'm proud not to have used cursors.

set language us_english
Set Nocount ON

create table #t( PROJ_NBR char(4),  STATUS char(2), START_DT datetime);

insert into #t values ('AL20',     'AC', '1/14/2010 4:31');  
insert into #t values ('AL20',     'AC', '1/14/2010 4:32');  
insert into #t values ('AL20',     'AC', '1/14/2010 4:32');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:34');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'AC', '3/4/2010 4:31');   
insert into #t values ('AL20',     'AC', '3/4/2010 4:39');   
insert into #t values ('AL20',     'AC', '3/21/2010 13:24'); 
insert into #t values ('AL20',     'AC', '2/4/2011 13:54');  
insert into #t values ('AL20',     'AC', '2/4/2011 14:14');  
insert into #t values ('AL20',     'AC', '2/4/2011 14:27');  
insert into #t values ('AL20',     'DE', '2/11/2011 16:24'); 


select
    distinct PROJ_NBR, STATUS, [start], [end]
from(
select 
*,
(select MIN(START_DT) 
    from #t t1 
    where t.STATUS = t1.STATUS 
    and t.START_DT >= t1.START_DT 
    and not exists ( select * from #t t2 
                    where t.STATUS <> t2.STATUS  
                    and  t.START_DT >= t2.START_DT
                    and t1.START_DT <= t2.START_DT)
) [start],
(select Max(START_DT) 
    from #t t1 
    where t.STATUS = t1.STATUS 
    and t.START_DT <= t1.START_DT 
    and not exists ( select * from #t t2 
                    where t.STATUS <> t2.STATUS  
                    and  t.START_DT <= t2.START_DT
                    and t1.START_DT >= t2.START_DT)
) [end]

from #t t
) t1
order by [start]

I think besides column and table names this can easily adapted to Oracle too.

Sql-server – Grouping over multiple data dimensions (pivot)

The PIVOT operator may be of help. Take a look at Books Online

For example:

SELECT 
    DtStart AS Date, 
    ISNULL([1], 0) AS [Server 1], 
    ISNULL([2], 0) AS [Server 2], 
    ISNULL([3], 0) AS [Server 3]
FROM
(
    SELECT 
        DtStart, 
        nServerID, 
        NDirection 
    FROM dbo.events 
) AS SourceTable
PIVOT
(
    SUM(NDirection)
    FOR nServerID IN ([1], [2], [3])
) AS PivotTable
ORDER BY 
    DtStart ASC

Best Answer

Related Solutions

Group data by non-unique keys by distinct time range

Sql-server – Grouping over multiple data dimensions (pivot)

Related Question