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.
Any suggestions much appreciated.