Postgresql query to select different materialized views depending on time range

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
    time_bucket(60, ts) as ts,
    MIN(temperature) as min_temperature,
    MAX(temperature) as max_temperature
  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?


I think we have an older blog post that is exactly what you’re looking for. Grafana doesn’t allow you to use a variable in a query for logic switching, but you can use the interval and a well-crafted WHERE clause to do the same thing (essentially stopping the other queries from executing.)

Let me know if you have questions after looking at this.

1 Like

I somehow didn’t find this post – sorry about that.
It’s exactly what i was looking for.

1 Like

No worries at all. Glad it’s helpful!