ohlc(open,high, low and close) record between two date range on different time internal(1,5,15,60,120) minute, 1 day even there are no record

Question

What I have

1. MY Domain "User_Order"

CREATE TABLE `user_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_date` datetime DEFAULT NULL,
  `fees` decimal(19,2) NOT NULL,
  `instrument` varchar(255) NOT NULL,
  `is_limit_order` bit(1) NOT NULL,
  `market_id` bigint(20) NOT NULL,
  `order_id` varchar(255) NOT NULL,
  `order_status` varchar(255) NOT NULL,
  `order_type` varchar(255) NOT NULL,
  `price` decimal(20,8) NOT NULL,
  `quantity` decimal(20,8) NOT NULL,
  `remaining_quantity` decimal(20,8) NOT NULL,
  `updated_date` datetime DEFAULT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_t5ah1x4wm9314qclf90dy0lyu` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. we have following record in my table.

| id | created_date        | fees   | instrument | is_limit_order | market_id | order_id         | order_status | order_type | price       | quantity   | remaining_quantity | updated_date        | user_id |
+----+---------------------+--------+------------+----------------+-----------+------------------+--------------+------------+-------------+------------+--------------------+---------------------+---------+
|  1 | 2017-09-15 05:08:57 | 0.0100 | INBTCINR   |               |         1 | 12:05:52-0000001 | EXECUTED     | BUY        | 10.00000000 | 2.00000000 |         0.00000000 | 2017-09-15 05:08:57 |       3 |
|  2 | 2017-09-15 05:09:34 | 0.0100 | INBTCINR   |               |         1 | 12:25:43-0000001 | EXECUTED     | SELL       | 10.00000000 | 2.00000000 |         0.00000000 | 2017-09-15 05:09:34 |       4 |
|  3 | 2017-09-15 05:11:18 | 0.0100 | INBTCINR   |               |         1 | 12:05:52-0000002 | CANCELLED    | BUY        |  2.00000000 | 1.00000000 |         1.00000000 | 2017-09-15 05:11:18 |       3 |
|  4 | 2017-09-15 05:12:43 | 0.0100 | INBTCINR   |               |         1 | 12:05:52-0000003 | EXECUTED     | BUY        |  4.00000000 | 2.00000000 |         0.00000000 | 2017-09-15 05:12:43 |       3 |
|  5 | 2017-09-15 05:23:10 | 0.0100 | INBTCINR   |               |         1 | 12:25:43-0000002 | EXECUTED     | SELL       |  4.00000000 | 2.00000000 |         0.00000000 | 2017-09-15 05:23:10 |       4 |
|  6 | 2017-09-15 08:34:17 | 0.0100 | INBTCINR   |               |         1 | 12:25:43-0000003 | OPEN         | BUY        |  2.00000000 | 1.00000000 |         1.00000000 | 2017-09-15 08:34:17 |       4 |
|  7 | 2017-09-15 08:36:32 | 0.0100 | INBTCINR   |               |         1 | 12:05:52-0000004 | CANCELLED    | SELL       |  1.00000000 | 2.00000000 |         2.00000000 | 2017-09-15 08:36:32 |       3 |
|  8 | 2017-09-15 09:24:22 | 0.0100 | INBTCINR   |               |         1 | 12:25:43-0000004 | OPEN         | BUY        |  2.00000000 | 1.00000000 |         1.00000000 | 2017-09-15 09:24:22 |       4 |
|  9 | 2017-09-15 09:30:19 | 0.0100 | INBTCINR   |               |         1 | 12:25:43-0000005 | OPEN         | BUY        |  1.00000000 | 2.00000000 |         2.00000000 | 2017-09-15 09:30:19 |       4 |
| 10 | 2017-09-15 09:32:10 | 0.0100 | INBTCINR   |               |         1 | 12:05:52-0000005 | CANCELLED    | SELL       |  1.00000000 | 2.00000000 |         2.00000000 | 2017-09-15 09:32:10 |       3 |
| 11 | 2017-09-15 10:02:57 | 0.0100 | INBTCINR   |               |         1 | 12:05:52-0000006 | OPEN         | SELL       |  2.00000000 | 4.00000000 |         4.00000000 | 2017-09-15 10:02:57 |       3 |
| 12 | 2017-09-15 10:16:19 | 0.0100 | INBTCINR   |               |         1 | 12:25:43-0000006 | OPEN         | BUY        |  4.00000000 | 2.00000000 |         2.00000000 | 2017-09-15 10:16:19 |       4 |

3. I am writing below query for every 5(300 in second) minute interval record.
start date = '2017-09-11 00:00:00' end date = '2017-09-15 23:59:59'

select  FROM_UNIXTIME(floor(min(UNIX_TIMESTAMP(created_date))/300)*300)
                 as timestampDate,
        sum(quantity) as volume,sum(price*quantity)/sum(quantity) as wavg_price,
        substring_index(min(concat('created_date','_',price)),
                '_',-1) as open,
        max(price) as high,
        min(price) as low,
        substring_index(max(concat ('created_date','_',price)),
                '_',-1) as 'close'
    from  user_order
    where  created_date between '2017-09-11 00:00:00' AND '2017-09-15 23:59:59'
    group by  floor(unix_timestamp(created_date)/300)
    order by  created_date;

result for above query:-

 timestampDate       | volume     | wavg_price              | open        | high        | low         | close       |
+---------------------+------------+-------------------------+-------------+-------------+-------------+-------------+
| 2017-09-15 05:05:00 | 4.00000000 | 10.00000000000000000000 | 10.00000000 | 10.00000000 | 10.00000000 | 10.00000000 |
| 2017-09-15 05:10:00 | 3.00000000 |  3.33333333333333333333 | 2.00000000  |  4.00000000 |  2.00000000 | 4.00000000  |
| 2017-09-15 05:20:00 | 2.00000000 |  4.00000000000000000000 | 4.00000000  |  4.00000000 |  4.00000000 | 4.00000000  |
| 2017-09-15 08:30:00 | 1.00000000 |  2.00000000000000000000 | 2.00000000  |  2.00000000 |  2.00000000 | 2.00000000  |
| 2017-09-15 08:35:00 | 2.00000000 |  1.00000000000000000000 | 1.00000000  |  1.00000000 |  1.00000000 | 1.00000000  |
| 2017-09-15 09:20:00 | 1.00000000 |  2.00000000000000000000 | 2.00000000  |  2.00000000 |  2.00000000 | 2.00000000  |
| 2017-09-15 09:30:00 | 4.00000000 |  1.00000000000000000000 | 1.00000000  |  1.00000000 |  1.00000000 | 1.00000000  |
| 2017-09-15 10:00:00 | 4.00000000 |  2.00000000000000000000 | 2.00000000  |  2.00000000 |  2.00000000 | 2.00000000  |
| 2017-09-15 10:15:00 | 2.00000000 |  4.00000000000000000000 | 4.00000000  |  4.00000000 |  4.00000000 | 4.00000000  |
+---------------------+------------+-------------------------+-------------+-------------+-------------+-------------+

4. Above result give only table persent record even i need all record with default value or pervious record value ( open, close, high, low and their timestamp) for every interval

expected output :-
date range= '2017-09-11 00:00:00' and '2017-09-15 23:59:59'

 timestampDate       | volume     | wavg_price              | open        | high        | low         | close       |
+---------------------+------------+-------------------------+-------------+-------------+-------------+-------------+
| 2017-09-11 00:05:00 | 4.00000000 | 10.00000000000000000000 | 10.00000000 | 10.00000000 | 10.00000000 | 10.00000000 |
| 2017-09-11 00:10:00 | 3.00000000 |  3.33333333333333333333 | 2.00000000  |  4.00000000 |  2.00000000 | 4.00000000  |
| 2017-09-11 00:15:00 | 2.00000000 |  4.00000000000000000000 | 4.00000000  |  4.00000000 |  4.00000000 | 4.00000000  |
................................................
.................................................
.................................................
| 2017-09-15 23:05:00 | 1.00000000 |  2.00000000000000000000 | 2.00000000  |  2.00000000 |  2.00000000 | 2.00000000  |
| 2017-09-15 23:10:00 | 1.00000000 |  2.00000000000000000000 | 2.00000000  |  2.00000000 |  2.00000000 | 2.00000000  |
..........................................................
..........................................................
..........................................................

| 2017-09-15 23:50:00 | 1.00000000 |  2.00000000000000000000 | 2.00000000  |  2.00000000 |  2.00000000 | 2.00000000  |
| 2017-09-15 23:55:00 | 1.00000000 |  2.00000000000000000000 | 2.00000000  |  2.00000000 |  2.00000000 | 2.00000000  |

What i want

output give only table persent record even i need all record with default value or pervious record value ( open, close, high, low and their timestamp) for every interval
case a)
Here, there is no record on '2017-09-11 00:05:00'. in that case open,close,high, low value will be set as 0(zero).
case b)
Here, there is no record on '2017-09-15 05:25:00', '2017-09-15 05:30:00' and extra but it is lie between the two record '2017-09-15 05:20:00' and 2017-09-15 08:30:00. in that case ohlc value of '2017-09-15 05:25:00' should be previous date time('2017-09-15 05:15:00') ohlc value setted.

Thanks

score 0 · Answer 1 · answered Sep 30 '18 at 05:09

To get missing records, you need to

Create an extra table with all the needed values
LEFT JOIN from the extra table to the query you already have
Do something about the NULLs that will show up in the output.

If you are using MariaDB, there is a handy "sequence" generator. for step 1. In MySQL, I recommend you pre-build a table of, say, the numbers from 0 to a million, then create the 5-minute datetimes from that using + INTERVAL ... SECOND

ohlc(open,high, low and close) record between two date range on different time internal(1,5,15,60,120) minute, 1 day even there are no record

1 Answers1