TimescaleDB: How to use time_bucket_gapfill in CASE statement

Question

I have a hypertable with measurements which looks like this

| time (timestamp with time zone) | subsystem (text) | metric (text) | value (double precision) | live (boolean) |
|---------------------------------|------------------|---------------|--------------------------|----------------|
| 2021-07-01 02:09:55.766783+00   | "hwmon"          | "t_pcb"       | 28.576568603515625       | true           |
| 2021-07-01 02:10:05.759777+00   | "hwmon"          | "t_pcb"       | 28.576568603515625       | true           |
| 2021-07-01 02:10:15.760297+00   | "hwmon"          | "t_pcb"       | 28.576568603515625       | true           |
| ...                             | ...              | ...           | ...                      | ...            |

and a table with settings which looks like this

| key (text)                       | value(text) |
|----------------------------------|-------------|
| "telemetry_interval_live_v5v"    | "10s"       |
| "telemetry_interval_offline_v5v" | "2m"        |
| "telemetry_interval_live_i5v"    | "10s"       |
| "telemetry_interval_offline_i5v" | "2m"        |

What I'm trying to achieve is that if I'm looking at

A big time range: time_bucket_gapfill() should be used to reduce the number of data points requested from the DB to increase the performance and in addition have the data gaps filled with NULL so that there is no line across the gap and the gap can be seen in the plot.
A small time range: all points are shown with their original unmodified timestamps by not using time_bucket_gapfill()

I got this working already with the following query (within Grafana)

SELECT
  CASE
    WHEN INTERVAL '$__interval' < GREATEST('$interval_live_t_pcb'::INTERVAL,'$interval_offline_t_pcb'::INTERVAL) THEN
      time
    ELSE 
      time_bucket_gapfill('$__interval',time,start => '${__from:date:iso}',finish => '${__to:date:iso}')
  END 
  AS "time",
  metric AS "metric",
  avg(value) AS "value"
FROM $satellite
WHERE
  $__timeFilter("time") AND
  live IN ($telemetry_type) AND
  "metric" = 't_pcb'
GROUP BY 1,2
ORDER BY 1,2

which is turned into the following query sent to PostgreSQL

SELECT
  CASE
    WHEN INTERVAL '2m' < GREATEST('10s'::INTERVAL,'2m'::INTERVAL) THEN
      time
    ELSE 
      time_bucket_gapfill('2m',time,start => '2021-06-30T01:24:56.045Z',finish => '2021-07-02T03:40:47.173Z')
  END 
  AS "time",
  metric AS "metric",
  avg(value) AS "value"
FROM ghzlab
WHERE
  "time" BETWEEN '2021-06-30T01:24:56.045Z' AND '2021-07-02T03:40:47.173Z' AND
  live IN (true,false) AND
  "metric" = 't_pcb'
GROUP BY 1,2
ORDER BY 1,2

In the if-then-else statement I compare the displayable interval provided by Grafana ($__interval) with the expected interval between data points (sampling rate) provided via Grafana variables (constants) and based on that use time_bucket_gapfill or the unmodified data.

To make it easier to manage all the settings, I want to have the time interval settings in a table instead of Grafana variables. But if I replace replace the interval variables in the conditional expression of the if-then-else with sub queries like this

SELECT
  CASE
    WHEN INTERVAL '$__interval' < GREATEST((SELECT value::INTERVAL from settings where key='telemetry_interval_offline_t_pcb'),
                                           (SELECT value::INTERVAL from settings where key='telemetry_interval_live_t_pcb')) THEN
      time
    ELSE 
      time_bucket_gapfill('$__interval',time,start => '${__from:date:iso}',finish => '${__to:date:iso}')
  END 
  AS "time",
  metric AS "metric",
  avg(value) AS "value"
FROM $satellite
WHERE
  $__timeFilter("time") AND
  live IN ($telemetry_type) AND
  "metric" = 't_pcb'
GROUP BY 1,2
ORDER BY 1,2

which is sent to PostgreSQL as

SELECT
  CASE
    WHEN INTERVAL '2m' < GREATEST((SELECT value::INTERVAL from settings where key='telemetry_interval_offline_t_pcb'),
                                  (SELECT value::INTERVAL from settings where key='telemetry_interval_live_t_pcb')) THEN
      time
    ELSE 
      time_bucket_gapfill('2m',time,start => '2021-06-30T01:24:56.045Z',finish => '2021-07-02T03:40:47.173Z')
  END 
  AS "time",
  metric AS "metric",
  avg(value) AS "value"
FROM ghzlab
WHERE
  "time" BETWEEN '2021-06-30T01:24:56.045Z' AND '2021-07-02T03:40:47.173Z' AND
  live IN (true,false) AND
  "metric" = 't_pcb'
GROUP BY 1,2
ORDER BY 1,2

then I get the following error message: "no top level time_bucket_gapfill in group by clause"

What do I need to do to prevent this error? If I replace the time_bucket_gapfill() with something else like for example with time + INTERVAL '1 day'

SELECT
  CASE
    WHEN INTERVAL '2m' < GREATEST((SELECT value::INTERVAL from settings where key='telemetry_interval_offline_t_pcb'),
                                  (SELECT value::INTERVAL from settings where key='telemetry_interval_live_t_pcb')) THEN
      time
    ELSE 
      time + INTERVAL '1 day'
  END 
  AS "time",
  metric AS "metric",
  avg(value) AS "value"
FROM ghzlab
WHERE
  "time" BETWEEN '2021-06-30T01:24:56.045Z' AND '2021-07-02T03:40:47.173Z' AND
  live IN (true,false) AND
  "metric" = 't_pcb'
GROUP BY 1,2
ORDER BY 1,2

it is working without errors.

TimescaleDB: How to use time_bucket_gapfill in CASE statement

0 Answers