Convert tick data to candlestick (OHLC) with SQL

Question

I have a table with columns:

timeInMilliseconds | price

And I want to create OHLC (Open, High, Low, Close) candles from it. That is basically group all the prices from some period of time (let's say every 1 minute) and select the min, max and first and last price of them.

I have created this query so far:

SELECT
  MIN(price) as low,
  MAX(price) as  high,
  FLOOR(timeInMilliseconds/(1000*60)) as open_time
FROM ticks
GROUP BY FLOOR(timeInMilliseconds/(1000*60))

And that works, but the problem is the Open (first) and Close (last) price.
Is there someway to get them in the same query (efficiently)?

Nick Nick · Accepted Answer · 2019-11-11T03:40:21

You need to find the maximum and minimum times for each time period and then JOIN your table to them to get the price values for those times:

SELECT t1.price AS open,
       m.high,
       m.low,
       t2.price as close,
       open_time
FROM (SELECT MIN(timeInMilliseconds) AS min_time,
             MAX(timeInMilliseconds) AS max_time,
             MIN(price) as low,
             MAX(price) as high,
             FLOOR(timeInMilliseconds/(1000*60)) as open_time
      FROM ticks
      GROUP BY open_time) m
JOIN ticks t1 ON t1.timeInMilliseconds = min_time
JOIN ticks t2 ON t2.timeInMilliseconds = max_time

Demo on dbfiddle

Convert tick data to candlestick (OHLC) with SQL

1 Answers