candlestick_agg() functions

Introduction

Perform analysis of financial asset data. These specialized hyperfunctions make it easier to write financial analysis queries that involve candlestick data.

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?

This function group uses the two-step aggregation pattern. In addition to the usual aggregate function, candlestick_agg, it also includes the pseudo-aggregate function candlestick. candlestick_agg produces a candlestick aggregate from raw tick data, which can then be used with the accessor and rollup functions in this group. candlestick takes pre-aggregated data and transforms it into the same format that candlestick_agg produces. This allows you to use the accessors and rollups with existing candlestick data.

Related hyperfunction groups

• ohlc()

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

candlestick_agg

ExperimentalAggregate tick data into an intermediate form for further calculation

Pseudo aggregate

candlestick

ExperimentalTransform pre-aggregated candlestick data into the correct form to use with candlestick_agg functions

Accessor

close

ExperimentalGet the closing price from a candlestick aggregate

close_time

ExperimentalGet the timestamp corresponding to the closing time from a candlestick aggregate

high

ExperimentalGet the high price from a candlestick aggregate

high_time

ExperimentalGet the timestamp corresponding to the high time from a candlestick aggregate

low

ExperimentalGet the low price from a candlestick aggregate

low_time

ExperimentalGet the timestamp corresponding to the low time from a candlestick aggregate

open

ExperimentalGet the opening price from a candlestick aggregate

open_time

ExperimentalGet the timestamp corresponding to the open time from a candlestick aggregate

volume

ExperimentalGet the total volume from a candlestick aggregate

vwap

ExperimentalGet the Volume Weighted Average Price from a candlestick aggregate

Rollup

rollup

ExperimentalRoll up multiple Candlestick aggregates

Function details

candlestick_agg()

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.12.0

Hide content

`

1. candlestick_agg(

`

1. ts TIMESTAMPTZ,
2. price DOUBLE PRECISION,
3. volume DOUBLE PRECISION

`

1. ) RETURNS Candlestick

`

This is the first step for performing financial calculations on raw tick data. Use candlestick_agg to create an intermediate aggregate from your tick 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.

If you’re starting with pre-aggregated candlestick data rather than raw tick data, use the companion candlestick() function instead. This function transforms the existing aggregated data into the correct form for use with the candlestick accessors.

Required arguments

Returns

candlestick()

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.12.0

Hide content

`

1. candlestick(

`

1. ts TIMESTAMPTZ,
2. open DOUBLE PRECISION,
3. high DOUBLE PRECISION,
4. low DOUBLE PRECISION,
5. close DOUBLE PRECISION,
6. volume DOUBLE PRECISION

`

1. ) RETURNS Candlestick

`

This function transforms pre-aggregated candlestick data into a candlestick aggregate object. This object contains the data in the correct form to use with the accessors and rollups in this function group.

If you’re starting with raw tick data rather than candlestick data, use candlestick_agg() instead.

Required arguments

Returns

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.12.0

Hide content

`

1. close(

`

1. candlestick Candlestick

`

1. ) RETURNS DOUBLE PRECISION

`

Get the closing price from a candlestick aggregate.

Required arguments

Returns

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.12.0

Hide content

`

1. close_time(

`

1. candlestick Candlestick

`

1. ) RETURNS TIMESTAMPTZ

`

Get the timestamp corresponding to the closing time from a candlestick aggregate.

Required arguments

Returns

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.12.0

Hide content

`

1. high(

`

1. candlestick Candlestick

`

1. ) RETURNS DOUBLE PRECISION

`

Get the high price from a candlestick aggregate.

Required arguments

Returns

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.12.0

Hide content

`

1. high_time(

`

1. candlestick Candlestick

`

1. ) RETURNS TIMESTAMPTZ

`

Get the timestamp corresponding to the high time from a candlestick aggregate.

Required arguments

Returns

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.12.0

Hide content

`

1. low(

`

1. candlestick Candlestick

`

1. ) RETURNS DOUBLE PRECISION

`

Get the low price from a candlestick aggregate.

Required arguments

Returns

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.12.0

Hide content

`

1. low_time(

`

1. candlestick Candlestick

`

1. ) RETURNS TIMESTAMPTZ

`

Get the timestamp corresponding to the low time from a candlestick aggregate.

Required arguments

Returns

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.12.0

Hide content

`

1. open(

`

1. candlestick Candlestick

`

1. ) RETURNS DOUBLE PRECISION

`

Get the opening price from a candlestick aggregate.

Required arguments

Returns

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.12.0

Hide content

`

1. open_time(

`

1. candlestick Candlestick

`

1. ) RETURNS TIMESTAMPTZ

`

Get the timestamp corresponding to the open time from a candlestick aggregate.

Required arguments

Returns

volume()

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.12.0

Hide content

`

1. volume(

`

1. candlestick Candlestick

`

1. ) RETURNS DOUBLE PRECISION

`

Get the total volume from a candlestick aggregate.

Required arguments

Returns

vwap()

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.12.0

Hide content

`

1. vwap(

`

1. candlestick Candlestick

`

1. ) RETURNS DOUBLE PRECISION

`

Get the Volume Weighted Average Price from a candlestick aggregate.

For Candlesticks constructed from data that is already aggregated, the Volume Weighted Average Price is calculated using the typical price for each period (where the typical price refers to the arithmetic mean of the high, low, and closing prices).

Required arguments

Returns

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.12.0

Hide content

`

1. rollup(

`

1. candlestick Candlestick

`

1. ) RETURNS Candlestick

`

Combine multiple intermediate candlestick aggregates, produced by candlestick_agg or candlestick, into a single intermediate candlestick aggregate. For example, you can use rollup to combine candlestick aggregates from 15-minute buckets into daily buckets.

Required arguments

Returns

Extended examples

Get candlestick values from tick data

Query your tick data table for the opening, high, low, and closing prices, and the trading volume, for each 1 hour period in the last day:

2. SELECT

4. time_bucket('1 hour'::interval, "time") AS ts,

6. symbol,

8. toolkit_experimental.open(toolkit_experimental.candlestick_agg("time", price, volume)),

10. toolkit_experimental.high(toolkit_experimental.candlestick_agg("time", price, volume)),

12. toolkit_experimental.low(toolkit_experimental.candlestick_agg("time", price, volume)),

14. toolkit_experimental.close(toolkit_experimental.candlestick_agg("time", price, volume)),

16. toolkit_experimental.volume(toolkit_experimental.candlestick_agg("time", price, volume))

18. FROM stocks_real_time

20. WHERE "time" > now() - '1 day'::interval

22. GROUP BY ts, symbol

24. ;

26. -- or

28. WITH cs AS (

30. SELECT time_bucket('1 hour'::interval, "time") AS hourly_bucket,

32. symbol,

34. toolkit_experimental.candlestick_agg("time", price, volume) AS candlestick

36. FROM stocks_real_time

38. WHERE "time" > now() - '1 day'::interval

40. GROUP BY hourly_bucket, symbol

42. )

44. SELECT hourly_bucket,

46. symbol,

48. toolkit_experimental.open(candlestick),

50. toolkit_experimental.high(candlestick),

52. toolkit_experimental.low(candlestick),

54. toolkit_experimental.close(candlestick),

56. toolkit_experimental.volume(candlestick)

58. FROM cs

60. ;

Create a continuous aggregate from tick data and roll it up

Create a continuous aggregate on your stock trade data:

2. CREATE MATERIALIZED VIEW candlestick

4. WITH (timescaledb.continuous) AS

6. SELECT time_bucket('1 minute'::interval, "time") AS ts,

8. symbol,

10. toolkit_experimental.candlestick_agg("time", price, volume) AS candlestick

12. FROM stocks_real_time

14. GROUP BY ts, symbol

16. ;

Query your by-minute continuous aggregate over stock trade data for the opening, high, low, and closing (OHLC) prices, along with their timestamps, in the last hour:

2. SELECT ts,

4. symbol,

6. toolkit_experimental.open_time(candlestick),

8. toolkit_experimental.open(candlestick),

10. toolkit_experimental.high_time(candlestick),

12. toolkit_experimental.high(candlestick),

14. toolkit_experimental.low_time(candlestick),

16. toolkit_experimental.low(candlestick),

18. toolkit_experimental.close_time(candlestick),

20. toolkit_experimental.close(candlestick)

22. FROM candlestick

24. WHERE ts > now() - '1 hour'::interval

26. ;

Roll up your by-minute continuous aggregate into daily buckets and return the Volume Weighted Average Price for AAPL for the last month:

2. SELECT

4. time_bucket('1 day'::interval, ts) AS daily_bucket,

6. symbol,

8. toolkit_experimental.vwap(toolkit_experimental.rollup(candlestick))

10. FROM candlestick

12. WHERE symbol = 'AAPL'

14. AND ts > now() - '1 month'::interval

16. GROUP BY daily_bucket

18. ORDER BY daily_bucket

20. ;

Roll up your by-minute continuous aggregate into hourly buckets and return the the opening, high, low, and closing prices and the volume for each 1 hour period in the last day:

2. SELECT

4. time_bucket('1 hour'::interval, ts) AS hourly_bucket,

6. symbol,

8. toolkit_experimental.open(toolkit_experimental.rollup(candlestick)),

10. toolkit_experimental.high(toolkit_experimental.rollup(candlestick)),

12. toolkit_experimental.low(toolkit_experimental.rollup(candlestick)),

14. toolkit_experimental.close(toolkit_experimental.rollup(candlestick)),

16. toolkit_experimental.volume(toolkit_experimental.rollup(candlestick))

18. FROM candlestick

20. WHERE ts > now() - '1 day'::interval

22. GROUP BY hourly_bucket

24. ;

Starting from already-aggregated data

If you have a table of pre-aggregated stock data, it might look similar this this format:

2. ts           │ symbol │  open  │  high  │  low   │ close  │  volume

4. ────────────────────────┼────────┼────────┼────────┼────────┼────────┼──────────

6. 2022-11-17 00:00:00-05 │ VTI    │ 195.67 │  197.9 │ 195.45 │ 197.49 │  3704700

8. 2022-11-16 00:00:00-05 │ VTI    │ 199.45 │ 199.72 │ 198.03 │ 198.32 │  2905000

10. 2022-11-15 00:00:00-05 │ VTI    │  201.5 │ 202.14 │ 198.34 │ 200.36 │  4606200

12. 2022-11-14 00:00:00-05 │ VTI    │ 199.26 │ 200.92 │ 198.21 │ 198.35 │  4248200

14. 2022-11-11 00:00:00-05 │ VTI    │ 198.58 │  200.7 │ 197.82 │ 200.16 │  4538500

16. 2022-11-10 00:00:00-05 │ VTI    │ 194.35 │ 198.31 │ 193.65 │ 198.14 │  3981600

18. 2022-11-09 00:00:00-05 │ VTI    │ 190.46 │ 191.04 │ 187.21 │ 187.53 │ 13959600

20. 2022-11-08 00:00:00-05 │ VTI    │ 191.25 │ 193.31 │ 189.42 │ 191.66 │  4847500

22. 2022-11-07 00:00:00-05 │ VTI    │ 189.59 │ 190.97 │ 188.47 │ 190.66 │  3420000

24. 2022-11-04 00:00:00-04 │ VTI    │ 189.32 │  190.3 │ 185.75 │ 188.94 │  3584600

26. 2022-11-03 00:00:00-04 │ VTI    │  186.5 │ 188.09 │ 185.13 │ 186.54 │  3935600

28. 2022-11-02 00:00:00-04 │ VTI    │ 193.07 │ 195.27 │ 188.29 │ 188.34 │  4686000

30. 2022-11-01 00:00:00-04 │ VTI    │    196 │ 196.44 │ 192.76 │ 193.43 │  9873800

32. 2022-10-31 00:00:00-04 │ VTI    │ 193.99 │ 195.17 │ 193.51 │ 194.03 │  5053900

34. 2022-10-28 00:00:00-04 │ VTI    │ 190.84 │ 195.53 │ 190.74 │ 195.29 │  3178800

36. 2022-10-27 00:00:00-04 │ VTI    │ 192.46 │ 193.47 │ 190.61 │ 190.85 │  3556300

38. 2022-10-26 00:00:00-04 │ VTI    │ 191.26 │ 194.64 │ 191.26 │ 191.75 │  4091100

40. 2022-10-25 00:00:00-04 │ VTI    │ 189.57 │ 193.16 │ 189.53 │ 192.94 │  3287100

42. 2022-10-24 00:00:00-04 │ VTI    │ 188.38 │ 190.12 │ 186.69 │ 189.51 │  4527800

44. 2022-10-21 00:00:00-04 │ VTI    │ 182.99 │ 187.78 │ 182.29 │ 187.49 │  3381200

46. 2022-10-20 00:00:00-04 │ VTI    │ 184.54 │ 186.99 │ 182.81 │ 183.27 │  2636200

48. 2022-10-19 00:00:00-04 │ VTI    │ 185.25 │ 186.64 │ 183.34 │ 184.87 │  2589100

50. 2022-10-18 00:00:00-04 │ VTI    │ 188.14 │  188.7 │ 184.71 │ 186.46 │  3906800

You can use the candlestick function to transform the data into a form that you’ll be able pass to all of the accessors and rollup functions. To show that your data is preserved, this example shows how these accessors return a table that looks just like your data:

2. SELECT

4. ts,

6. symbol,

8. toolkit_experimental.open(candlestick),

10. toolkit_experimental.high(candlestick),

12. toolkit_experimental.low(candlestick),

14. toolkit_experimental.close(candlestick),

16. toolkit_experimental.volume(candlestick)

18. FROM (

20. SELECT

22. ts,

24. symbol,

26. toolkit_experimental.candlestick(ts, open, high, low, close, volume)

28. FROM historical_data

30. ) AS _(ts, symbol, candlestick);

32. ;

34. -- or

36. WITH cs AS (

38. SELECT ts

40. symbol,

42. toolkit_experimental.candlestick(ts, open, high, low, close, volume)

44. FROM historical_data

46. )

48. SELECT

51. ts

53. symbol,

55. toolkit_experimental.open(candlestick),

57. toolkit_experimental.high(candlestick),

59. toolkit_experimental.low(candlestick),

61. toolkit_experimental.close(candlestick),

63. toolkit_experimental.volume(candlestick)

65. FROM cs

67. ;

The advantage of transforming your data into the candlestick aggergate form is that you can then use other functions in this group, such as rollup and vwap.

Roll up your by-day historical data into weekly buckets and return the Volume Weighted Average Price:

2. SELECT

4. time_bucket('1 week'::interval, ts) AS weekly_bucket,

6. symbol,

8. toolkit_experimental.vwap(toolkit_experimental.rollup(candlestick))

10. FROM (

12. SELECT

14. ts,

16. symbol,

18. toolkit_experimental.candlestick(ts, open, high, low, close, volume)

20. FROM historical_data

22. ) AS _(ts, symbol, candlestick)

24. GROUP BY weekly_bucket, symbol

26. ;