External lookup script for time of day cost metric

Elastic Stack Elasticsearch
1

Hi There,
I have an indexed document that contains time series usage data (kw) at say 10 second intervals. I am looking to create a query to return the cost of that energy usage with time of day metering. What this means is the cost varies by the time of day (peak, off-peak, shoulder), weekday, weekend and season (high or low). Typically the tariff structure is maintained in a SQL database.

The energy cost will vary depending on the time of day, day of the week and month of the year

off-peak: 12am to 9am --> cost 1
peak: 9am to 3pm --> cost 2
shoulder: 3pm to 8pm --> cost 3
off-peak: 8pm to 12pm --> cost 4

I was thinking along the lines of a plugin that can do the heavy lifting of calculating the cost over a specified time period and returning a single cost metric. That plugin needs access to external data or a lookup function to determine the cost during the period of time the energy usage occurred.

I have implemented such a query to do a "single pass" over the data. However it doesn't easily lend itself to querying over many days (e.g. 30, 90 or 365 days) and "second pass" aggregations such as cost during opening hours or a ranking of types based on cost without quickly becoming overly complicated.

Elastic Search aggregation query using range filters

Any suggestions much appreciated.

Thanks,

Mark

2

Can you not save the cost with the doc at the time of indexing?

3

Thanks for the response Mark. That's a good suggestion, however the cost isn't always 1:1 with the date range. In fact the cost can be a tiered structure depending on the energy usage for the month. The more energy consumed, the cheaper the cost. So a lookup function would be ideal. The other downside would be that if the cost changed, then everything would have to be re-indexed. It would be difficult to compare different rate structures for example.

4

After thinking about this some more I think this is the ideal way to solve this problem, thanks Mark!