Versions:

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
NameTypeDescription
tsTIMESTAMPTZTimestamp associated with stock price
priceDOUBLE PRECISIONStock quote price at the given time
Returns
ColumnTypeDescription
aggOpenHighLowCloseAn 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 ohlc
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute'::interval, "time") AS ts,
symbol,
toolkit_experimental.ohlc("time", price)
FROM stocks_real_time
GROUP BY ts, symbol
close(
ohlc OpenHighLowClose
) RETURNS DOUBLE PRECISION

Get the closing price from an OHLC aggregate.

Required arguments
NameTypeDescription
ohlcOpenHighLowCloseAn OpenHighLowClose aggregate.
Returns
ColumnTypeDescription
closeDOUBLE PRECISIONThe closing price
close_time(
ohlc OpenHighLowClose
) RETURNS TIMESTAMPTZ

Get the timestamp corresponding to the closing time from an OHLC aggregate.

Required arguments
NameTypeDescription
ohlcOpenHighLowCloseAn OpenHighLowClose aggregate.
Returns
ColumnTypeDescription
close_timeTIMESTAMPTZThe time at which the closing price occurred
high(
ohlc OpenHighLowClose
) RETURNS DOUBLE PRECISION

Get the high price from an OHLC aggregate.

Required arguments
NameTypeDescription
ohlcOpenHighLowCloseAn OpenHighLowClose aggregate.
Returns
ColumnTypeDescription
highDOUBLE PRECISIONThe high price
high_time(
ohlc OpenHighLowClose
) RETURNS TIMESTAMPTZ

Get the timestamp corresponding to the high time from an OHLC aggregate.

Required arguments
NameTypeDescription
ohlcOpenHighLowCloseAn OpenHighLowClose aggregate.
Returns
ColumnTypeDescription
high_timeTIMESTAMPTZThe time at which the high price occurred
low(
ohlc OpenHighLowClose
) RETURNS DOUBLE PRECISION

Get the low price from an OHLC aggregate.

Required arguments
NameTypeDescription
ohlcOpenHighLowCloseAn OpenHighLowClose aggregate.
Returns
ColumnTypeDescription
lowDOUBLE PRECISIONThe low price
low_time(
ohlc OpenHighLowClose
) RETURNS TIMESTAMPTZ

Get the timestamp corresponding to the low time from an OHLC aggregate.

Required arguments
NameTypeDescription
ohlcOpenHighLowCloseAn OpenHighLowClose aggregate.
Returns
ColumnTypeDescription
low_timeTIMESTAMPTZThe time at which the low price occurred
open(
ohlc OpenHighLowClose
) RETURNS DOUBLE PRECISION

Get the opening price from an OHLC aggregate.

Required arguments
NameTypeDescription
ohlcOpenHighLowCloseAn OpenHighLowClose aggregate.
Returns
ColumnTypeDescription
openDOUBLE PRECISIONThe opening price
open_time(
ohlc OpenHighLowClose
) RETURNS TIMESTAMPTZ

Get the timestamp corresponding to the opening time from an OHLC aggregate.

Required arguments
NameTypeDescription
ohlcOpenHighLowCloseAn OpenHighLowClose aggregate.
Returns
ColumnTypeDescription
open_timeTIMESTAMPTZThe 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
NameTypeDescription
ohlcOpenHighLowCloseThe aggregate produced by an ohlc call
Returns
ColumnTypeDescription
ohlcOpenHighLowCloseA 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_time
GROUP 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 ohlc
GROUP 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_time
GROUP BY minute_bucket, symbol
)
SELECT time_bucket('1 day'::interval , bucket) AS daily_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 ohlc
GROUP BY daily_bucket, symbol;

Found an issue on this page?

Report an issue!

Keywords

Related Content