Using Last Recorded Values in InfluxDB for Reliable Time-Series Analysis

InfluxDB 2
, , , ,
1

Problem Statement:

I have an organization with multiple buckets in InfluxDB, each representing a machine. All machines start writing data at 7:00 AM, but they stop at different times:

  • Two machines write data from 7:00 AM to 7:00 PM (19:00).
  • One machine writes data only from 7:00 AM to 12:00 PM (Noon) and then stops.

Objective:

I need to generate an organization summary by fetching the field values from all buckets at every 30-second interval and performing the following calculations:

  1. Mean calculation: Compute the mean for four specific fields across all three machines.
  2. Sum calculation: Compute the sum for one specific field across all three machines.

Challenges:

  1. Different end times:
    One machine stops writing at 12:00 PM, while the others continue until 7:00 PM.
  2. Handling missing data for mean and sum calculation:
    If a machine has no data at a given time interval, use its last recorded value (before it stopped) instead of excluding it.
    This ensures that even at 3:00 PM or 5:00 PM, the calculations include all three machines, even though one stopped at 12:00 PM.

Expected Outcome:

A summary dataset where values are recorded every 30 seconds for all machines.
The mean of four fields at each interval, ensuring the stopped machine contributes using its last recorded value.
The sum of one field at each interval, ensuring all three machines are included.

Question:

How can this be efficiently implemented in an InfluxDB Flux query, ensuring that the last known value of the stopped machine is used for mean and sum calculations beyond 12:00 PM?

1 Like