0

I am willing to collect candle price stock data on specific time intervals using timescaledb continous aggregations, such as 37 minutes and my intervals will start every day at 00:00.

So for example, I am willing to have an interval starts each day at 00:00, 00:37, 01:14... until 23:26, and then start all over from 00:00, 008:37... and so forth until 23:26 again. So a data for example will be:

06/09 00:00 interval candle

06/09 00:37 interval candle

06/09 01:14 interval candle

...

06/09 23:26 interval candle

07/09 00:00 interval candle

07/09 00:37 interval candle

...

And so on

Of course that I would like to make them at the same view and not create every day a new hyper table for this.

How can i make this up using timescaledb?

I have looked it up at the docs and wasn't sure what should I use... Is it refresh policies? Origin?

Of course I can use bucket of 37 minutes, but my problem starts after the first day. The last candle of the day will be at 23:26 and the following candle afterwards will be at 00:03 instead of 00:00 and I will lose sync

Thank you very much, your help is super appreciated!

Ohad Koren
  • 1
  • 1

1 Answers1

0

You can use time_bucket('37 min', time_column) in your SELECT statement. For example if you have minute-level data, and want to generate candlestick data in 37-min intervals, you'd do something like this:

SELECT 
time_bucket('37 min', time) AS bucket
, FIRST(price_open, time) AS open_price
, LAST(price_close, time) AS close_price
, MIN(price_low) AS lowest
, MAX(price_high) AS highest
, SUM(trading_volume) AS volume
, symbol 
FROM stocks_intraday 
WHERE symbol = 'TSLA'
GROUP BY bucket, symbol
ORDER BY bucket desc

Result:

2021-08-31 20:00:00|  733.8901|      734.0|733.8901|   734.0|   4816|TSLA  |
2021-08-31 19:23:00|    733.98|      734.0|  733.64|   734.0|   8950|TSLA  |
2021-08-31 18:46:00|     734.0|     733.55|  733.55|   734.2|   4737|TSLA  |
2021-08-31 18:09:00|     734.5|      733.8|   733.8|   734.7|   4231|TSLA  |
2021-08-31 17:32:00|  734.6899|     734.47|  733.86|734.6899|   7902|TSLA  |
2021-08-31 16:55:00|     736.2|      734.5|  734.05|   736.2| 294596|TSLA  |
etc...

Then use this to create the continuous aggregate. (without the ORDER BY)

Attila Toth
  • 187
  • 1
  • 2
  • 13
  • Thanks atila for your response, I did time bucket of 37 minutes, ther problem was that after one day, my times got unsynchronized (so for example the laat candle od the say is 23:26, and then at the next day the first candle will be 00:03 instead of 00:00. I want that the next candle will be at 00:00, do yiu know how to do it? (So that every day I will have the same candle hours) – Ohad Koren Sep 07 '21 at 10:32
  • @OhadKoren Unfortunately this is not supported yet by time_bucket. Maybe you could create 30 min buckets? – Attila Toth Sep 07 '21 at 16:39
  • Probably, you're looking for the extra arguments that time_bucket has: https://docs.timescale.com/api/latest/hyperfunctions/time_bucket/#optional-arguments See that the `origin` timestamp will allow you to use your daily basis shift. – jonatasdp Sep 08 '21 at 12:02