3

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
0

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.

  • I tried this but i am confused to know that how new dates will be available there on future? i mean i will need all future time for left join right? – Uttam Jan 20 '18 at 13:32
  • Yes you should generate new dates. Googling a bit you can easily find scripts to generate calendar tables or to generate table values between two dates. – McNets Jan 20 '18 at 15:09
0

If you had MariaDB, you could use a "sequence" table for auto-generating the dates.

I prefer to do something like this before JOINing to calendar:

SELECT
        FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(Date) / (5*60))
                      * (5*60)) AS chunk_start,
        SUM(...)
    ...
    GROUP BY chunk_start

That does the arithmetic earlier in the process, hence is somewhat faster.

  • I am using MySQL database and i updated my query sample on my question, i think i have that syntax that you gave me but i still miss that gaps for missing dates. – Uttam Jan 20 '18 at 13:23
  • The technique is to RIGHT JOIN to a table that has all the dates, including missing days. – Rick James Jan 20 '18 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.