4

I am new to timescaledb and I want to store one minute ohlcv ticks for each stock in the table. There are 1440 ticks generated daily for one stock and 43200 ticks a month. I have a 100 assets whose ticks I would like to store every month and basically have the tables divide every 30 days or so, so that I dont have to build complex logic for this division. Any suggestions on how this can be done with timescale DB.

Currently, the way I am doing it is

  • Take incoming tick (ex timestamp 1535090420)
  • Round its timestamp to the nearest 30 day period (1535090420/(86400 * 30)) = 592.241674383
  • Round this number to 592 and multiply by interval to get 1534464000 which is the nearest 30 day bucket inside which all the ticks should be stored So I create a table called OHLC_1534464000 if not exists and add the ticks there
  • Is there a better way to do this
PirateApp
  • 5,433
  • 4
  • 57
  • 90

1 Answers1

3

Unless I'm missing something, it sounds like you just want a table partitioned by 30 day intervals that will automatically know which table to put incoming data into based on the timestamp. This is exactly the functionality TimescaleDB's hypertables.

You can create your table using the following syntax:

SELECT create_hypertable('ohlc', 'timestamp', chunk_time_interval => interval '30 days');

This will create 30 day chunks (just Postgres tables under the hood) and transparently place each tick in the appropriate chunk based on its timestamp. It will automatically create a new chunk once a tick passing the current 30 day period comes in. You can just insert and query on the main table (ohlc) as though it were a single Postgres table.

Lee Hampton
  • 410
  • 5
  • 13