ohlc() functions

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.

Two-step aggregation

Hide content

This group of functions uses the two-step aggregation pattern.

Rather than calculating the final result in one step, you first create an intermediate aggregate by using the aggregate function.

Then, use any of the accessors on the intermediate aggregate to calculate a final result. You can also roll up multiple intermediate aggregates with the rollup functions.

The two-step aggregation pattern has several advantages:

  1. More efficient because multiple accessors can reuse the same aggregate
  2. Easier to reason about performance, because aggregation is separate from final computation
  3. Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
  4. Can perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result

To learn more, see the blog post on two-step aggregates.

Functions in this group

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

Function details

ohlc()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.10.1

Hide content

`

  1. ohlc(

`

  1.   ts TIMESTAMPTZ,
  2.   price DOUBLE PRECISION

`

  1. ) 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:

  2. CREATE MATERIALIZED VIEW ohlc
  4. WITH (timescaledb.continuous) AS
  6. SELECT time_bucket('1 minute'::interval, "time") AS ts,
  8.   symbol,
  10.   toolkit_experimental.ohlc("time", price)
  12. FROM stocks_real_time
  14. GROUP BY ts, symbol

close()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.10.1

Hide content

`

  1. close(

`

  1.   ohlc OpenHighLowClose

`

  1. ) 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()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.10.1

Hide content

`

  1. close_time(

`

  1.   ohlc OpenHighLowClose

`

  1. ) 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()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.10.1

Hide content

`

  1. high(

`

  1.   ohlc OpenHighLowClose

`

  1. ) 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()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.10.1

Hide content

`

  1. high_time(

`

  1.   ohlc OpenHighLowClose

`

  1. ) 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()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.10.1

Hide content

`

  1. low(

`

  1.   ohlc OpenHighLowClose

`

  1. ) 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()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.10.1

Hide content

`

  1. low_time(

`

  1.     ohlc OpenHighLowClose

`

  1. ) 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()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.10.1

Hide content

`

  1. open(

`

  1.   ohlc OpenHighLowClose

`

  1. ) 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()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.10.1

Hide content

`

  1. open_time(

`

  1.   ohlc OpenHighLowClose

`

  1. ) 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()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.10.1

Hide content

`

  1. rollup(

`

  1.   ohlc OpenHighLowClose

`

  1. ) 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

Extended examples

Calculate by-minute stock-tick prices

Combine stock tick data into one-minute buckets. Return the asset prices and timestamps for each minute:

  2. WITH ohlc AS (
  4.     SELECT time_bucket('1 minute'::interval, ts) AS minute_bucket,
  6.       symbol,
  8.       toolkit_experimental.ohlc(ts, price)
  10.     FROM stocks_real_time
  12.     GROUP BY minute_bucket, symbol
  14. )
  16. SELECT minute_bucket,
  18.   symbol,
  20.   toolkit_experimental.open_time(ohlc),
  22.   toolkit_experimental.open(ohlc),
  24.   toolkit_experimental.high_time(ohlc),
  26.   toolkit_experimental.high(ohlc),
  28.   toolkit_experimental.low_time(ohlc),
  30.   toolkit_experimental.low(ohlc),
  32.   toolkit_experimental.close_time(ohlc)
  34.   toolkit_experimental.close(ohlc)
  36. FROM ohlc;

Roll up multiple continuous aggregates

Roll up a by-minute continuous aggregate into hourly buckets and return the OHLC prices:

  2. SELECT time_bucket('1 hour'::interval, ts) AS hourly_bucket,
  4.     symbol,
  6.     toolkit_experimental.open(toolkit_experimental.rollup(ohlc)),
  8.     toolkit_experimental.high(toolkit_experimental.rollup(ohlc)),
  10.     toolkit_experimental.low(toolkit_experimental.rollup(ohlc)),
  12.     toolkit_experimental.close(toolkit_experimental.rollup(ohlc)),
  14. FROM ohlc
  16. GROUP BY hourly_bucket, symbol;

Roll up a by-minute aggregate into a daily aggregate and return the OHLC prices:

  2. WITH ohlc AS (
  4.     SELECT time_bucket('1 minute'::interval, ts) AS minute_bucket,
  6.       symbol,
  8.       toolkit_experimental.ohlc(ts, price)
  10.     FROM stocks_real_time
  12.     GROUP BY minute_bucket, symbol
  14. )
  16. SELECT time_bucket('1 day'::interval , bucket) AS daily_bucket
  18.   symbol,
  20.   toolkit_experimental.open(toolkit_experimental.rollup(ohlc)),
  22.   toolkit_experimental.high(toolkit_experimental.rollup(ohlc)),
  24.   toolkit_experimental.low(toolkit_experimental.rollup(ohlc)),
  26.   toolkit_experimental.close(toolkit_experimental.rollup(ohlc))
  28. FROM ohlc
  30. GROUP BY daily_bucket, symbol;