ohlc() functions
ToolkitTimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale Cloud. Click to learn more.Introduction
Perform analysis of financial asset data. These specialized hyperfunctions make it easier to write financial analysis queries that involve open, high, low, and closing prices.
They help you answer questions such as:
- What are the opening and closing prices of these stocks?
- When did the highest price occur for this stock?
deprecation
The candlestick_agg
functions provide a better API for financial analysis that allows you to include trading volume data. ohlc
is deprecated in favor of these functions.
Related hyperfunction groups
warning
This function group includes some experimental functions. Experimental functions might change or be removed in future releases. We do not recommend using them in production. Experimental functions are marked with an Experimental tag.
Aggregate
- ohlc
- ExperimentalAggregate financial asset data into an intermediate form for further calculation
Accessor
- close
- ExperimentalGet the closing price from an OHLC aggregate
- close_time
- ExperimentalGet the timestamp corresponding to the closing time from an OHLC aggregate
- high
- ExperimentalGet the high price from an OHLC aggregate
- high_time
- ExperimentalGet the timestamp corresponding to the high time from an OHLC aggregate
- low
- ExperimentalGet the low price from an OHLC aggregate
- low_time
- ExperimentalGet the timestamp corresponding to the low time from an OHLC aggregate
- open
- ExperimentalGet the opening price from an OHLC aggregate
- open_time
- ExperimentalGet the timestamp corresponding to the opening time from an OHLC aggregate
Rollup
- rollup
- ExperimentalRoll up multiple OHLC aggregates
ohlc(ts TIMESTAMPTZ,price DOUBLE PRECISION) RETURNS OpenHighLowClose
This is the first step for performing financial calculations on asset
prices. Use ohlc
to create an intermediate aggregate from your price data. This intermediate form can then be used by one or more accessors in this
group to compute final results.
Optionally, multiple such intermediate aggregate objects can be combined
using rollup()
before an accessor is applied.
Required arguments
Name | Type | Description |
---|---|---|
ts | TIMESTAMPTZ | Timestamp associated with stock price |
price | DOUBLE PRECISION | Stock quote price at the given time |
Returns
Column | Type | Description |
---|---|---|
agg | OpenHighLowClose | An object storing (timestamp, value) pairs for each of the opening, high, low, and closing prices. |
Examples
Create a continuous aggregate on some stock data. Store the asset prices for each bucket:
CREATE MATERIALIZED VIEW ohlcWITH (timescaledb.continuous) ASSELECT time_bucket('1 minute'::interval, "time") AS ts,symbol,toolkit_experimental.ohlc("time", price)FROM stocks_real_timeGROUP BY ts, symbol
close(ohlc OpenHighLowClose) RETURNS DOUBLE PRECISION
Get the closing price from an OHLC aggregate.
Required arguments
Name | Type | Description |
---|---|---|
ohlc | OpenHighLowClose | An OpenHighLowClose aggregate. |
Returns
Column | Type | Description |
---|---|---|
close | DOUBLE PRECISION | The closing price |
close_time(ohlc OpenHighLowClose) RETURNS TIMESTAMPTZ
Get the timestamp corresponding to the closing time from an OHLC aggregate.
Required arguments
Name | Type | Description |
---|---|---|
ohlc | OpenHighLowClose | An OpenHighLowClose aggregate. |
Returns
Column | Type | Description |
---|---|---|
close_time | TIMESTAMPTZ | The time at which the closing price occurred |
high(ohlc OpenHighLowClose) RETURNS DOUBLE PRECISION
Get the high price from an OHLC aggregate.
Required arguments
Name | Type | Description |
---|---|---|
ohlc | OpenHighLowClose | An OpenHighLowClose aggregate. |
Returns
Column | Type | Description |
---|---|---|
high | DOUBLE PRECISION | The high price |
high_time(ohlc OpenHighLowClose) RETURNS TIMESTAMPTZ
Get the timestamp corresponding to the high time from an OHLC aggregate.
Required arguments
Name | Type | Description |
---|---|---|
ohlc | OpenHighLowClose | An OpenHighLowClose aggregate. |
Returns
Column | Type | Description |
---|---|---|
high_time | TIMESTAMPTZ | The time at which the high price occurred |
low(ohlc OpenHighLowClose) RETURNS DOUBLE PRECISION
Get the low price from an OHLC aggregate.
Required arguments
Name | Type | Description |
---|---|---|
ohlc | OpenHighLowClose | An OpenHighLowClose aggregate. |
Returns
Column | Type | Description |
---|---|---|
low | DOUBLE PRECISION | The low price |
low_time(ohlc OpenHighLowClose) RETURNS TIMESTAMPTZ
Get the timestamp corresponding to the low time from an OHLC aggregate.
Required arguments
Name | Type | Description |
---|---|---|
ohlc | OpenHighLowClose | An OpenHighLowClose aggregate. |
Returns
Column | Type | Description |
---|---|---|
low_time | TIMESTAMPTZ | The time at which the low price occurred |
open(ohlc OpenHighLowClose) RETURNS DOUBLE PRECISION
Get the opening price from an OHLC aggregate.
Required arguments
Name | Type | Description |
---|---|---|
ohlc | OpenHighLowClose | An OpenHighLowClose aggregate. |
Returns
Column | Type | Description |
---|---|---|
open | DOUBLE PRECISION | The opening price |
open_time(ohlc OpenHighLowClose) RETURNS TIMESTAMPTZ
Get the timestamp corresponding to the opening time from an OHLC aggregate.
Required arguments
Name | Type | Description |
---|---|---|
ohlc | OpenHighLowClose | An OpenHighLowClose aggregate. |
Returns
Column | Type | Description |
---|---|---|
open_time | TIMESTAMPTZ | The time at which the opening price occurred |
rollup(ohlc OpenHighLowClose) RETURNS OpenHighLowClose
Combine multiple intermediate OHLC aggregates, produced by ohlc
, into a single intermediate OHLC aggregate. For example, you can use rollup
to combine OHLC aggregates from 15-minute buckets into daily buckets.
Required arguments
Name | Type | Description |
---|---|---|
ohlc | OpenHighLowClose | The aggregate produced by an ohlc call |
Returns
Column | Type | Description |
---|---|---|
ohlc | OpenHighLowClose | A new OHLC aggregate produced by combining the input OHLC aggregates |
Combine stock tick data into one-minute buckets. Return the asset prices and timestamps for each minute:
WITH ohlc AS (SELECT time_bucket('1 minute'::interval, ts) AS minute_bucket,symbol,toolkit_experimental.ohlc(ts, price)FROM stocks_real_timeGROUP BY minute_bucket, symbol)SELECT minute_bucket,symbol,toolkit_experimental.open_time(ohlc),toolkit_experimental.open(ohlc),toolkit_experimental.high_time(ohlc),toolkit_experimental.high(ohlc),toolkit_experimental.low_time(ohlc),toolkit_experimental.low(ohlc),toolkit_experimental.close_time(ohlc)toolkit_experimental.close(ohlc)FROM ohlc;
Roll up a by-minute continuous aggregate into hourly buckets and return the OHLC prices:
SELECT time_bucket('1 hour'::interval, ts) AS hourly_bucket,symbol,toolkit_experimental.open(toolkit_experimental.rollup(ohlc)),toolkit_experimental.high(toolkit_experimental.rollup(ohlc)),toolkit_experimental.low(toolkit_experimental.rollup(ohlc)),toolkit_experimental.close(toolkit_experimental.rollup(ohlc)),FROM ohlcGROUP BY hourly_bucket, symbol;
Roll up a by-minute aggregate into a daily aggregate and return the OHLC prices:
WITH ohlc AS (SELECT time_bucket('1 minute'::interval, ts) AS minute_bucket,symbol,toolkit_experimental.ohlc(ts, price)FROM stocks_real_timeGROUP BY minute_bucket, symbol)SELECT time_bucket('1 day'::interval , bucket) AS daily_bucketsymbol,toolkit_experimental.open(toolkit_experimental.rollup(ohlc)),toolkit_experimental.high(toolkit_experimental.rollup(ohlc)),toolkit_experimental.low(toolkit_experimental.rollup(ohlc)),toolkit_experimental.close(toolkit_experimental.rollup(ohlc))FROM ohlcGROUP BY daily_bucket, symbol;
Found an issue on this page?
Report an issue!Keywords