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
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
GET /ohlcv?exchange=text&market=text&candle_duration_in_minutes=1&start_datetime=2025-12-06T14%3A53%3A26.909Z&end_datetime=2025-12-06T14%3A53%3A26.909Z 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
  }
]

PreviousPublic tradesNextIntroduction

Last updated