PostgreSQL, time series - Looking for a way to omit timestamps if a row contains one or more zeroes

Sorry for the bump, I am still searching for a method to omit rows based on specific conditions though. Is it even possible?

I'm tinkering in a tool called Grafana with the aim of one day creating useful visualizations, tapping into a database (I haven't set up myself) containing temperature reads from various sources in a factory.

The problem: Once in a while a sensor fails to report its value, and becomes recorded as zero instead. Note: They are only shown as blanks/nulls in the output table because of condition WHERE value <> 0. However, regardless of zero/null, they are still wrong and cause problems in graphs and calculations. I therefore want all affected row gone. Losing an occational timestamp is less bad than including one with errors.

So I'm looking for a query condition/method to say that if a row contains zeroes in one (or more) of its columns, then the entire timestamp should be omitted.

Currently my query looks like this:

SELECT 
  time_bucket('20 sec', time) AS time, --20 second time interval
  part AS metric, --AS metric makes the column labels look neat (somehow)
  MAX(value) --pick max value of part within each interval
FROM temp 
WHERE
  temp.machineno = '$MachineNo' --grafana dashboard variable -> select machine from drop menu
    AND $__timeFilter(time) --grafana dashboard filter -> select time window from drop menu
      AND value <> 0 
GROUP BY time, part
ORDER BY time

It outputs a table like this:Table The names and numbers of columns vary depending on which machine is selected, so a method where I don't have to know or specify anything about the columns would be preferable. (Note: machines are selected from a drop-down menu in grafana, and is what '$MachineNo' refers to.)

As a graph it looks like this: Graph We can see the two blank from the table as gaps in the graph. Now Grafana has a button to smooth such gaps over vizually, but it doesn't remove them from table - which means they are still there to cause problems if I were to run calculations with these numbers.

Instead of a table like this: Unfiltered table

I'd like a filtered table like this: Filtered table

I can't really account for everything I've tried. Have mostly read a lot, and tried a few things now and then, but nothing that has worked. I am able to find info on how to omit columns, but very little pertaining to rows. I tried using DELETE once... Not sure if it would have worked, but luckily it did not... I don't have authoritiy to delete anything from the database, and I don't want to either. I just want to filter out obvious mistakes from the output table.

Solution

You can try following query:

with bucketed as (
  SELECT time_bucket('20 sec', time) AS time,   --20 second time interval
         part                        AS metric, --AS metric makes the column labels look neat (somehow)
         MAX(value)                  AS val     --pick max value of part within each interval
    FROM temp 
   WHERE temp.machineno = '$MachineNo' --grafana dashboard variable -> select machine from drop menu
     AND $__timeFilter(time)           --grafana dashboard filter -> select time window from drop menu
   GROUP BY time, part
)
SELECT t1.* FROM bucketed t1
WHERE NOT EXISTS (
  SELECT 1 
    FROM bucketed t2 
   WHERE t1.time = t2.time
     AND t2.val = 0
)

Here I get values put into time buckets into CTE, and then use this CTE in conjunction with NOT EXISTS to filter out all timestamps where any of the values is 0.

Please notice that I'm not familiar with time series implementation in Postgres, and couldn't find clear documentation for time_bucket functions, so simply assumed that it returns same value for all metrics. If this is not the case, line WHERE t1.time=t2.time might need some adjustment.