Integral function

[mayku00]

Hi, I asked in the slack about how to get the function integral (like in influxDB)

The answer:
“Time weighted average def does that! Sounds like we should be exposing that! can you create an issue here: https://github.com/timescale/timescaledb-toolkit/issues? I think we have what we need in time_weight but just need to expose it the right way, so probably would be relatively quick to implement, and will just be an accessor, so if you want to, say, create continuous aggregates you can use the time_weight aggregate and when we make this accessor, you'll just be able to use it on that.”

so open is!!

[jerryxwu]

@mayku00 thank you for the feature request! I think I have a general idea of what you are asking for. It would be great to learn your specific use case for this function. It can help us design the function better.

[mayku00]

Hi! Sure.
I have an hypertable with 3 colums: timestamp, upload_bitrate, download_bitrate. I use the table to save the bitrate that I get from a modem every x seconds.

I use the table as the historical register for the bitrate, but also I need to calculate the volume consumed in a period of time.

As I have a line/curve of values ordered by the timestamp, I calculate the volume as the area under this line/curve:

On Influx I was using: https://docs.influxdata.com/flux/v0.x/stdlib/universe/integral/

I asked for a similar function

KR

[davidkohn88]

Thanks! Leaving as much context here is awesome because the links to Slack eventually break because we don't have full history there! So feel free to edit or add more if you think of anything. But I think time_weight('Linear', time, value) -> integral(unit, start_time, end_time) would be a good start for what you're looking for here where unit is the unit of time to use to calculate the integral, all of those could have defaults, ('1s', NULL, NULL), where null values would just use the starting and ending times seen in the data set.

[jerryxwu]

Having integral built on top of time_weight definitely makes sense. A few ideas and comments:

1. Users wanting to compute integral are more likely familiar with numeric integration lingo, so we should document 'Linear' means trapezoidal rule and LOCF means left-hand rule. Even better, allowing time_weight to accept trapezoidal as an alternative to Linear.
2. It's unlikely that integral can accept start_time and end_time, only unit makes sense
3. interpolated_integral(...) analogous to interpolated_average should also be implemented

[jerryxwu]

There's another request from the community slack channel.

[syvb]

Assigning myself as discussed in the standup.

[andrey-skat]

Another use case is calculating power consumption from raw time/watt data.
It can also be done with lag window function, but it can't be used in continuous aggregations.

[davidkohn88]

That's an awesome use case, was actually just using that to explain it, essentially you want to go from kW to kWh. We'll definitely want to make sure we have interpolated_integral as well as that will be really useful there!

[rtwalker]

Closed by #526