Using time_bucket and joining multiple tables in timescaledb

Question

I have a simple database schema (Timescaledb) with a few tables. Each user has one main sensor with multiple metrics, each metric has its own table with user_id and timestamp.

Schema

Table 1

| user_id | timestamp | val1 | val... |:---- |:------:| -----:| -----: Table ...

user_id	timestamp	val1	val...

Question

I want to know how can I merge these tables in a view with the time_bucket function. So, if I sample it every 10 seconds to show something like View

user_id	timestamp	agg val from table 1	agg val from table 2	agg val from table X

Answer 1

Yes! it's possible!

You can try it by using regular SQL. Here is some example:

select time_bucket('1m', a.time) as bucket_a,
          a.symbol || '/' || b.symbol as pair,
          LAST(a.price, a.time) as last_price_a,
          LAST(b.price, b.time) as last_price_b
          FROM ticks a
          LEFT JOIN ticks b
          ON time_bucket('1m', a.time) = time_bucket('1m', b.time)
            AND a.symbol = 'SYMBOL' and b.symbol = 'ANOTHER'
          GROUP BY 1, 2;

Note that I'm joining the table ticks twice, you can just join another table with the other metrics.

Another detail is that instead of filtering the symbol on where clause, it's now part of the JOIN clause.