Skip to content
Advertisement

How to use timebucket_gapfill when rows can have null values?

I have a time series table where measurements are recorded into “wide” rows. Rows may contain all measurements or only some. The other columns are then set to NULL.

I would like to use timebucket_gapfill() to “clean” this table and make sure that every row in the output has data in all columns, even if the underlying dataset has some null values for some of the columns.

This is how I prepare the table with some data (schema from the getting started guide):

And this is how I query it:

The output is:

  • I understand why the first row is empty – no data in the dataset.
  • At 5:02:17, interpolation is working fine when there are no rows in the dataset.
  • However, at 5:02:15 and 5:02:19, where the underlying rows are “partial”, the database did not use values from the previous and next rows to interpolate a result for respectively humidity and temperature.

How do I write the query to return an interpolated value for all measurement columns?

Advertisement

Answer

Timescaledb does not consider NULL as missing values. I have to rewrite the query to avoid the rows with NULL values, that means doing multiple queries with timebucket_gapfill and joining the results together.

This works and does what I wanted:

Output:

Got some help on the timescaledb slack – thanks gayathri.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement