How To Get ohlc Value In mysql

Posted By : Md Imroz Alam | 28-Sep-2017

OHLC stands for open high low close value for a trading market on different interval time.

1) what is trade open, high, low and close value

open value is the opening price of a selected date

high value is the highest value of the selected date

the low value is the lowest value of the selected date

close value is the last trade value of selected date

ohlc value provides us every time moment up and high trade market value.

2) We have following record of Trade table

price ,quantity, order_date
11       1       2017-09-15 05:23:00
10       1       2017-09-15 10:00:00
40       1       2017-09-15 12:34:00
30       1       2017-09-15 22:30:34
9        1       2017-09-15 22:30:34
11       1       2017-09-16 03:00:00

3) Expected result:-

open close low high date
11   9     9   40   2017-09-15 
11   11    11  11   2017-09-16

4 a) For the desired result , we have to add group by query and also subquery on table. Actually we want result in a single query

select c1.order_date as date,(SELECT c2.price FROM trade c2 WHERE c2.id = MIN(c1.id)) AS open,MAX(c1.price) AS high,MIN(c1.price) AS low,(SELECT c2.price FROM trade c2 WHERE c2.id = MAX(c1.id)) AS close FROM trade c1 where GROUP BY year(c1.order_date), month(c1.order_date), DAY(c1.order_date) ORDER BY c1.order_date ASC

4 b) when we want ohlc value on different time interval( for example:- 1 minute, 5 minute , 15 minute extra) we passing 1 minute time interval as 60 second

select  FROM_UNIXTIME(floor(min(UNIX_TIMESTAMP(order_date))/60)*60) as date,substring_index(min(concat(order_date,'_',price)),'_',-1) as open,max(price) as high,min(price) as low,substring_index(max(concat(order_date,'_',price)),'_',-1) as 'close' from trade group by floor(unix_timestamp(order_date)/60) order by order_date

Thanks

I hope this will be helpful