I’ll describe a somewhat contrived example for the purpose of illustrating the problem.
The way I will describe it will make it appear to be a purely postgres/sql question (and it may very well be the case that my sql-skills are simply lacking.) But I’m also totally open to an entirely different solution.
Say I have a timescale table with raw, time-series data, with one extra dimension:
ts: a unix timestamp (let’s say in seconds,)
temperature: an integer
location: for sake of simplicity, let’s say of type text
I’ll make some other assumptions for simplicity here: There are no duplicates (i.e. we are guaranteed that for any location there are at most one measurement per second (and I will ignore leap seconds just to keep this sane.)
I can now create a continuous aggregate materialized view using this as the source:
CREATE MATERIALIZED VIEW temperature_minutely WITH (timescaledb.continuous) AS
SELECT
time_bucket(60, ts) as ts,
MIN(temperature) as min_temperature,
MAX(temperature) as max_temperature
location
FROM temperature
GROUP BY ts, location;
If I create multiple of these materialized views at differing granularities (i.e. I called the above one temperature_minutely to reflect its time bucket size, I could create additional _hourly, _daily and _monthly ones,) conceptually it should be really simple to query the appropriate table given the time_bucket query input.
And that conditional should be relatively simple to write as part of the SQL query, especially if I have access to something like $__interval from grafana: Global variables | Grafana documentation
First, is the approach to have different granularity materialized views even reasonable? I don’t see why not, but I’m curious.
Second, am I missing some super-obvious way to “switch” on the table at query-time?