OHLCV

SQL API

api.ohlcv

Aggregated OHLCV Candles with Dynamic Resolution This parameterized view provides on-the-fly aggregation of raw 1-minute candles into customizable time intervals (e.g., 5min, 1hr, 4hr).

1. Basic Usage: 7 days of daily ETH Candles (Binance)

SELECT
  start,
  open,
  high,
  low,
  close,
  volume  
FROM api.ohlcv(candle_duration_in_minutes = 1440)  
WHERE market = 'ETH-USDT'
  AND exchange = 'binance'
  AND start >= '2024-06-01'
  AND start <= '2024-06-08'

Parameters

Parameter	Type	Description
candle_duration_in_minutes	Int32	Aggregation window (1, 5, 15, etc.)

---

Columns

Column	Type	Description
start	DateTime	Candle open time (UTC)
end	DateTime	Candle close time (UTC)
exchange	String	Exchange name (e.g., "binance")
market	String	Normalized symbol (e.g., "BTC-USDT")
open	Decimal(76,20)	First price in interval
high	Decimal(76,20)	Highest price in interval
low	Decimal(76,20)	Lowest price in interval
close	Decimal(76,20)	Last price in interval
volume	Decimal(76,20)	Total base asset volume
count	Int32	Number of trades
duration_minutes	Int32	Candle duration (matches input parameter)

---

⚠️ Critical Performance Warning

The underlying public_data.candle_1m table exceeds 4 TB. Always include:

1. Time filters (e.g., start BETWEEN ...)

2. Market/exchange filters

3. Reasonable aggregation windows

Unfiltered queries will be rejected by the query killer!

---

Advanced Example Queries

Exchange Volume Leaderboard

Total BTC-USD volume this month per exchange

SELECT 
  exchange, 
  sum(volume) AS total_volume 
FROM api.ohlcv(candle_duration_in_minutes=1) 
WHERE market = 'BTC-USD'
  AND start BETWEEN 
      toStartOfMonth(now()) AND 
      now()
GROUP BY exchange
ORDER BY total_volume DESC

Output:

┌─exchange───┬─total_volume─┐
│ binance    │   1542032.11 │
│ kraken     │    892384.75 │
│ coinbase   │    784291.03 │
└────────────┴──────────────┘

3. Volatility Analysis: 4hr ATR

WITH candles AS (
  SELECT 
    *,
    high - low AS true_range  
  FROM api.ohlcv(candle_duration_in_minutes = 240)  -- 4hr candles
  WHERE market = 'BTC-USD'
    AND exchange = 'kraken'
    AND start >= now() - INTERVAL 7 DAY
)
SELECT 
  start,
  true_range,
  avg(true_range) OVER (ORDER BY start ROWS 14 PRECEDING) AS atr_14_period
FROM candles

Output:

┌───────────────start─┬─true_range─┬──────atr_14_period─┐
│ 2025-08-08 16:00:00 │      876.5 │              876.5 │
│ 2025-08-08 20:00:00 │      630.1 │  753.3000000000001 │
│ 2025-08-09 00:00:00 │      333.1 │  613.2333333333333 │
│ 2025-08-09 04:00:00 │      549.5 │              597.3 │
│ 2025-08-09 08:00:00 │     1106.2 │             699.08 │
│ 2025-08-09 12:00:00 │      494.8 │  665.0333333333334 │
│ 2025-08-09 16:00:00 │      396.2 │  626.6285714285714 │
│ 2025-08-09 20:00:00 │      686.9 │  634.1624999999999 │
│ 2025-08-10 00:00:00 │     2012.7 │  787.3333333333335 │
│ 2025-08-10 04:00:00 │     1211.8 │             829.78 │
│ 2025-08-10 08:00:00 │      823.7 │  829.2272727272727 │

4. Fill gaps

Low volume candles are not backfilled by default. This can be done on query:

SELECT
  start,
  open,
  high,
  low,
   close,
  volume  
FROM api.ohlcv(candle_duration_in_minutes = 1)  
WHERE market = 'BTC-MXN'
  AND exchange = 'binance'
  AND start >= '2024-06-01'
  AND start <= '2024-06-02'
  order by start
  WITH FILL
  STEP interval 1 minute  
  INTERPOLATE(open as close, high as close, low as close , close as close);

Output

┌───────────────start─┬────open─┬────high─┬─────low─┬───close─┬───volume─┐
│ 2024-06-01 00:18:00 │ 1155300 │ 1155300 │ 1155300 │ 1155300 │ 0.000214 │
│ 2024-06-01 00:19:00 │ 1155300 │ 1155300 │ 1154806 │ 1154806 │ 0.000921 │
│ 2024-06-01 00:20:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
│ 2024-06-01 00:21:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
│ 2024-06-01 00:22:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
│ 2024-06-01 00:23:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
│ 2024-06-01 00:24:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │
│ 2024-06-01 00:25:00 │ 1154806 │ 1154806 │ 1154806 │ 1154806 │        0 │

REST API

Get OHLCV for a Market

get

This endpoint retrieves OHLCV (Open, High, Low, Close, Volume) data for a specific market.

It includes spot, future, and option markets.

This endpoint is limited to 10000 candles per request.

Authorizations

basicAuth

AuthorizationstringRequired

Use Basic Authentication with:

• Username: Your API Key
• Password: Your API Secret

Query parameters

exchangestringRequired

The name of the exchange to filter by

marketstringRequired

The universal market symbol to filter by

candle_duration_in_minutesinteger · min: 1 · max: 1440Required

The time interval for the candles in minutes. The value can be any amount of minutes:

• 1: 1 minute
• 5: 5 minutes
• 60: 1 hour
• 1440: 1 day

start_datetimestring · date-timeRequired

The start time for the OHLCV data in ISO 8601 format (e.g., '2023-01-01T00:00:00Z') or any format that can be parsed by the parseDateTime64BestEffort

end_datetimestring · date-timeRequired

The end time for the OHLCV data in ISO 8601 format (e.g., '2023-01-02T00:00:00Z') or any format that can be parsed by the parseDateTime64BestEffort

Responses

200

OK

application/json

get

/ohlcv

HTTPcURLJavaScriptPython

GET /ohlcv?exchange=text&market=text&candle_duration_in_minutes=1&start_datetime=2025-12-20T07%3A52%3A10.669Z&end_datetime=2025-12-20T07%3A52%3A10.669Z HTTP/1.1
Host: api.koinju.io
Authorization: Basic username:password
Accept: */*

200

OK

[
  {
    "start": "2023-01-01T00:00:00Z",
    "end": "2023-01-01T00:01:00Z",
    "duration_minutes": 1,
    "open": 100,
    "high": 105,
    "low": 99,
    "close": 104,
    "volume": 1500,
    "count": 25
  },
  {
    "start": "2023-01-01T00:01:00Z",
    "end": "2023-01-01T00:02:00Z",
    "duration_minutes": 1,
    "open": 104,
    "high": 106,
    "low": 103,
    "close": 105.5,
    "volume": 1200,
    "count": 20
  }
]