I need to query a table that contains time series records and filter the results to return one reading every nth second.
For example, return records between StartDate and EndDate but don’t return every record, return one record for every 3,600 seconds so one reading per hour.
In .NET I can do this using code like this.
var TimeStepResults = allResults.Aggregate((x, y) => Math.Abs(x.From.Ticks - counter) < Math.Abs(y.From.Ticks - counter) ? x : y);
However I don’t want to return millions of records and then pull out one record every hour or 30 minutes, I’d prefer to just return filtered records from the stored procedure.
Can anyone suggest how this could be done?