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

 

About Author

Author Image
Md Imroz Alam

Md. Imroz Alam is a bright Web App Developer, he has good knowledge of Java, J2SE, Jsp, Servlet, jdbc. His hobbies are watching movie, playing carom.

Request for Proposal

Name is required

Comment is required

Sending message..