I have had a request made as follows:
What is average value for a tag between 0300 and 0400 on a weekly basis.
What they mean is retrieve every value for the tag between 0300 and 0400 over the course of a week and give me the average; they also want it every week for the past 6 months.
I have been unable to find an elegant solution and as such am using the following:
SELECT tag, time, value
WHERE tag = 'sinusoid'
AND time between '*-180d' AND '*'
AND HOUR(time) >= 3
AND HOUR(time) <4
This produced four values per day over the 180 days. I am then putting it into excel to get the week number then pivoting the data grouped by Week Number and averaging the value.
I have to do this on over 7000 points.
Is there a better way?