Dealing with data series with different frequency

Grafana PostgreSQL
,
1

  • What Grafana version and what operating system are you using?
    8.3.6

  • What are you trying to achieve?
    I have incremental data coming from sensors (A) every 5’, and sensors (B) updating every 10". I cannot control that or even when they start sending the data, so I have them in different tables in the database. I’d like to calculate the area of v(A) - v(B) in, let’s say, 5’ steps.

  • How are you trying to achieve it?
    I can get some variables from the data I have. With the following query I can get the energy produced (5’ sensor) on a variable:

SELECT
  time AS "time",
  pvenergytotal-lag(pvenergytotal) OVER(ORDER BY time) as solar_produced
FROM t040504
WHERE
  $__timeFilter(time)
ORDER BY 1

While with the following query I can get the energy pulled from the network and injected into it (10" sensor):

SELECT
  time AS "time",
  energy_t1_in + energy_t2_in - lag(energy_t1_in) OVER(ORDER BY time) - lag(energy_t2_in) OVER(ORDER BY time) as network_in,
  energy_t1_out + energy_t2_out - lag(energy_t1_out) OVER(ORDER BY time) - lag(energy_t2_out) OVER(ORDER BY time) as network_out
FROM electricity
WHERE
  $__timeFilter(time)
ORDER BY 1

I can plot these two on a panel and call it a day, but I am wondering how can I plot my consumption (given by solar_produced + network_in - network_out), considering I cannot simply join the tables because the timestamps (primary key on the database) are guaranteed not to match?

I am considering keeping a reading buffer on the service receiving those readings, so that such service would take care of the timestamp matching, but before starting such a task I am wondering if there is any known strategy to deal with this situation? I can hardly think I am the first one.

2

@violetcucumber I’m not a postgres expert, but have you tried using the query option for min_interval to set the min interval on sensor A to match the frequency of sensor B?