Skip to content

ActiveRecord migrations helpers for Timescale

Create table is now with the hypertable keyword allowing to pass a few options to the function call while also using the create_table method:

create_table with the :hypertable option

hypertable_options = {
  time_column: 'created_at',
  chunk_time_interval: '1 min',
  compress_segmentby: 'identifier',
  compression_interval: '7 days'

create_table(:events, id: false, hypertable: hypertable_options) do |t|
  t.string :identifier, null: false
  t.jsonb :payload

The create_continuous_aggregate helper

This example shows a ticks table grouping ticks as OHLCV histograms for every minute.

First make sure you have the model with the acts_as_hypertable method to be able to extract the query from it.

class Tick < ActiveRecord::Base
  self.table_name = 'ticks'

Then, inside your migration:

hypertable_options = {
  time_column: 'created_at',
  chunk_time_interval: '1 min',
  compress_segmentby: 'symbol',
  compress_orderby: 'created_at',
  compression_interval: '7 days'
create_table :ticks, hypertable: hypertable_options, id: false do |t|
  t.string :symbol
  t.decimal :price
  t.integer :volume

query =<<~QUERY)
  time_bucket('1m', created_at) as time,
  FIRST(price, created_at) as open,
  MAX(price) as high,
  MIN(price) as low,
  LAST(price, created_at) as close,
  SUM(volume) as volume").group("1,2")

options = {
  with_data: false,
  refresh_policies: {
    start_offset: "INTERVAL '1 month'",
    end_offset: "INTERVAL '1 minute'",
    schedule_interval: "INTERVAL '1 minute'"

create_continuous_aggregate('ohlc_1m', query, **options)

If you need more details, please check this blog post.

If you're interested in candlesticks and need to get the OHLC values, take a look at the toolkit ohlc function that do the same but through a function that can be reusing candlesticks from smaller timeframes.

Disable ddl transactions in your migration to start with data

If you want to start with_data: true, remember that you'll need to disable_ddl_transaction! in your migration file.

class CreateCaggsWithData < ActiveRecord::Migration[7.0]

  def change
    create_continuous_aggregate('ohlc_1m', query, with_data: true)
    # ...