Skip to content

Calculated fields on timescale

I’m trying to add calculated fields to my hypertable with sensor data, e.g., I would like to add the derivative of the volume readings to get the volume flow.

I have tried to do this with a continuous aggregate, but I get invalid SELECT query for continuous aggregate.

Is this because of the lead function or is it because there has to be an aggregation function in a continuous aggregate? What would be best practice for this? I could make a job that inserts the data in the original table every minute, but then I wouldn’t catch if new data is inserted back in time (it’s a rather large table, so I can’t run through the whole table every minute).

Advertisement

Answer

Both the windowing function, and the UNION will prevent continuous aggregates from working.

In this case, since the computation being performed per-row is so low, an index on time and a regular view may provide the needed performance.

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