Timescale DB interpolation on JSON fields

Question

I am currently working on a project that stores IOT information as JSON in Postgresql, and I decided to use timescale extension because I require some functionality like interpolation for when a component goes down or so for an hour or so. I am trying to divide my data across 1 hour buckets and and the field value is one of the keys in the JSON field but to no avail. Can you please help I am getting very unpredictable time buckets when I use the time_bucket_gapfill function Here is my query

SELECT
    time_bucket_gapfill(
        '1 hour ', module_timestamp,
        start => '2020-04-20 15:00',
        finish => '2020-04-21 17:00') AS hour,
    interpolate(CAST(component_data->'common'->'bat' AS INT)) AS avg_val
FROM main_componentmessage order by hour;

and here are is the result I am getting

          hour          | avg_val 
------------------------+---------      
 2020-03-10 09:00:00+00 |      30
 2020-04-20 17:00:00+00 |        
 2020-04-20 18:00:00+00 |      50

I do not understand why the start parameter did not act as an upper limit and I do not understand why the middle row did not interpolate to 40

score 3 · Accepted Answer · answered Apr 21 '20 at 10:38

time_bucket_gapfill is meant to be used with an aggregate, since you're bucketing the values into the specified bucket width. I'm not sure why it's not throwing errors. But regarding your query you can use it and interpolate in the following manner:

SELECT 
  time_bucket_gapfill(
    '1 hour', time, 
    start=> '2020-04-20 15:00', 
    finish => '2020-04-20 17:00') as hour,
  interpolate(
    avg((val->'common'->'bat')::integer)
  )
FROM main_componentmessage
GROUP BY 1;

Note that the interpolation will not result in 40 for the 2020-04-20 17:00:00+00 bucket. The interpolation takes into account the previous value available (30) and the next (50), but also takes into account the time distance between them (~a month). So you would get an interpolation of 40 if the bucket is in the middle between 20th April and 10th March. But since the bucket is very close to 20th April 18:00 the interpolated value will be very close to 50.

Check out the API https://docs.timescale.com/latest/api#interpolate to see how you can specify your own calculation for the previous and next values so you can tweak it as you like

Timescale DB interpolation on JSON fields

1 Answers1