> 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"}}}}}}}}}}}
```
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://docs.koinju.io/data/ohlcv.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.