> For the complete documentation index, see [llms.txt](https://docs.koinju.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.koinju.io/data/ohlcv.md). # OHLCV This parameterized view provides on-the-fly aggregation of raw 1-minute candles into customizable time intervals (e.g., 5min, 1hr, 4hr). ## SQL API ### `api.ohlcv` Aggregated OHLCV candles with dynamic resolution — on-the-fly aggregation of raw 1-minute candles into customizable time intervals. #### 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 | Universal 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) | *** #### Data Access by Tier | Data | Free | Developer | Professional | Business | Enterprise | | ------------------------------------ | --------------- | -------------- | ------------ | ------------ | ------------ | | Daily/Hourly OHLCV | Full history | Full history | Full history | Full history | Full history | | Spot 1-min OHLCV | Rolling 1 month | Rolling 1 year | Full history | Full history | Full history | | Futures OHLCV (1-min and aggregates) | Rolling 1 month | Rolling 1 year | Full history | Full history | Full history | | Options OHLCV (1-min and aggregates) | Rolling 1 month | Rolling 1 year | Full history | Full history | Full history | {% hint style="info" %} A request that partially overlaps your tier's window returns only the in-window data. On the REST API (`/ohlcv`), a request whose **entire** range is older than your window returns **HTTP 422** with a working `example_url` and a `discord_url` instead of an empty response. See [Pricing](/pricing.md) for details. {% endhint %} *** #### Performance 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! *** ### Example Queries #### 1. Basic Usage: 7 days of daily ETH Candles (Binance) ```sql 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' ``` `market` symbols are quote-specific and not unified across venues: `BTC-USD` (Coinbase/Kraken/Bitstamp/Gemini/Bitfinex) and `BTC-USDT` (Binance/OKX/Bybit/KuCoin/Gate.io) are distinct markets. #### 2. Exchange Volume Leaderboard Total BTC volume this month per exchange ```sql SELECT exchange, sum(volume) AS total_volume FROM api.ohlcv(candle_duration_in_minutes=1) WHERE market IN ('BTC-USD', 'BTC-USDT') AND start BETWEEN toStartOfMonth(now()) AND now() GROUP BY exchange ORDER BY total_volume DESC ``` USD- and USDT-quoted books are distinct markets, so a cross-exchange leaderboard must include both symbols. Functions used: [`toStartOfMonth`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofmonth), [`now`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#now). *Output:* ``` ┌─exchange───┬─total_volume─┐ │ binance │ 1542032.11 │ │ kraken │ 892384.75 │ │ coinbase │ 784291.03 │ └────────────┴──────────────┘ ``` #### 3. Volatility Analysis: 4hr ATR ```sql WITH candles AS ( SELECT *, high - low AS true_range FROM api.ohlcv(candle_duration_in_minutes = 240) -- 4hr candles WHERE market IN ('BTC-USD', 'BTC-USDT') 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 ``` USD- and USDT-quoted BTC are distinct markets; scope to both (or pin a single venue with `exchange = '…'`) to avoid mixing books. Functions used: [`avg() OVER`](https://clickhouse.com/docs/sql-reference/window-functions). *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: ```sql 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); ``` Functions used: [`WITH FILL`](https://clickhouse.com/docs/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier). *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 > 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.
```json {"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[{"apiKeyAuth":[]}],"components":{"securitySchemes":{"apiKeyAuth":{"type":"apiKey","in":"header","name":"x-api-key","description":"Required for /ohlcv and /trade endpoints.\nPass your API key in the x-api-key header.\nPublic /market/* endpoints do not require authentication.\n"}}},"paths":{"/ohlcv":{"get":{"summary":"Get OHLCV for a Market","description":"This endpoint retrieves OHLCV (Open, High, Low, Close, Volume) data for a specific market.\n\nIt includes spot, future, and option markets.\n\nThis endpoint is limited to 10000 candles per request.\n","tags":["market_data"],"parameters":[{"name":"exchange","in":"query","required":true,"description":"The name of the exchange to filter by","schema":{"type":"string"}},{"name":"market","in":"query","required":true,"description":"The universal market symbol to filter by","schema":{"type":"string"}},{"name":"candle_duration_in_minutes","in":"query","required":true,"description":"The time interval for the candles in minutes.\nThe value can be any amount of minutes:\n- 1: 1 minute\n- 5: 5 minutes\n- 60: 1 hour\n- 1440: 1 day\n","schema":{"type":"integer","minimum":1,"maximum":1440}},{"name":"start_datetime","in":"query","required":true,"description":"The start time for the OHLCV data. Accepts any format parseable by\n`parseDateTime64BestEffort` — e.g. a plain date (`2024-06-01`, treated\nas midnight UTC) or a full ISO 8601 timestamp (`2024-06-01T12:00:00Z`).\n","schema":{"type":"string","format":"date-time"}},{"name":"end_datetime","in":"query","required":true,"description":"The end time for the OHLCV data. Accepts any format parseable by\n`parseDateTime64BestEffort` — e.g. a plain date (`2024-06-08`, treated\nas midnight UTC) or a full ISO 8601 timestamp (`2024-06-08T12:00:00Z`).\n","schema":{"type":"string","format":"date-time"}}],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"type":"object","properties":{"start":{"type":"string","format":"date-time","description":"Start time of the candle"},"end":{"type":"string","format":"date-time","description":"End time of the candle"},"duration_minutes":{"type":"integer","description":"Duration of the candle in minutes"},"open":{"type":"number","format":"decimal","description":"Opening price of the candle"},"high":{"type":"number","format":"decimal","description":"Highest price during the candle period"},"low":{"type":"number","format":"decimal","description":"Lowest price during the candle period"},"close":{"type":"number","format":"decimal","description":"Closing price of the candle"},"volume":{"type":"number","format":"decimal","description":"Volume traded during the candle period"},"count":{"type":"integer","description":"Number of trades during the candle period"}}}}}}},"422":{"description":"Empty result because the entire requested range is older than your tier's data window; the body carries a working example_url and a Discord invite.","content":{"application/json":{"schema":{"type":"object","properties":{"message":{"type":"string","description":"Human-readable explanation of the empty result"},"example_url":{"type":"string","description":"A ready-to-run query within your tier's window"},"discord_url":{"type":"string","description":"Invite to the Koinju Discord for help"}}}}}}}}}}} ```