public static QueriedMetricResult GetMetricValuesBetweenDates(SQLiteConnection connection, Metric metric, DateTime startDate, DateTime endDate, QueryResolution resolution) { if (connection == null) throw new ArgumentNullException("connection"); if (metric == null) throw new ArgumentNullException("metric"); // Impossible to have values when the start date is after the end date, so don't even try if (endDate < startDate) return new QueriedMetricResult { Metric = metric, Values = new KeyValuePair<DateTime, decimal>[0] }; var query = GenerateQueryAtResolution(resolution); var values = new List<KeyValuePair<DateTime, decimal>>(); using (var command = new SQLiteCommand(query, connection)) { command.Parameters.AddWithValue("@device", metric.Device); command.Parameters.AddWithValue("@category", metric.Category); command.Parameters.AddWithValue("@name", metric.Name); command.Parameters.AddWithValue("@start", startDate.ToUtcUnixTimestamp()); command.Parameters.AddWithValue("@end", endDate.ToUtcUnixTimestamp()); using (var reader = command.ExecuteReader()) while (reader.Read()) ReadQueryResult(resolution, reader, values); } return new QueriedMetricResult { Metric = metric, Values = values.ToArray() }; }
private static void ReadQueryResult(QueryResolution resolution, SQLiteDataReader reader, List<KeyValuePair<DateTime, decimal>> values) { switch (resolution) { case QueryResolution.All: var date = reader.GetDateTime(0); var value = reader.GetDecimal(1); values.Add(new KeyValuePair<DateTime, decimal>(date, value)); break; default: var rawDate = Convert.ToString(reader["GroupedDate"]); // Partial time resolutions will cause the raw date to have a minute value // of :0 instead of :00, which will cause parsing errors, so try and fix that if (rawDate.EndsWith(":0")) rawDate += "0"; var resolutionDate = DateTime.Parse(rawDate); var resolutionValue = reader.GetDecimal(reader.GetOrdinal("Value")); values.Add(new KeyValuePair<DateTime, decimal>(resolutionDate, resolutionValue)); break; } }
private static string GenerateQueryAtResolution(QueryResolution resolution) { const string fromClause = @"from MetricData "; const string orderByClause = @"order by Date "; const string whereClause = @"where MetricDevice = @device and MetricCategory = @category and MetricName = @name and Date between @start and @end "; // Different select clauses for the different resolutions const string allSelectClause = @"select strftime('%Y-%m-%dT%H:%M:%S', datetime(Date, 'unixepoch', 'localtime')) as 'Date', Value "; const string resolutionSelectClause = @"select {0} as 'GroupedDate', avg(Value) as 'Value' "; const string resolutionGroupByClause = @"group by {0} "; var resolutionDateParts = new Dictionary<QueryResolution, string> { {QueryResolution.Minute, "strftime('%Y-%m-%dT%H:%M:00.000', datetime(Date, 'unixepoch', 'localtime'))"}, {QueryResolution.Hour, "strftime('%Y-%m-%dT%H:00:00.000', datetime(Date, 'unixepoch', 'localtime'))"}, {QueryResolution.Day, "strftime('%Y-%m-%dT00:00:00.000', datetime(Date, 'unixepoch', 'localtime'))"}, {QueryResolution.Month,"strftime('%Y-%m-01T00:00:00.000', datetime(Date, 'unixepoch', 'localtime'))" }, {QueryResolution.Year,"strftime('%Y-01-01T00:00:00.000', datetime(Date, 'unixepoch', 'localtime'))" }, {QueryResolution.FifteenMinutes, "strftime('%Y-%m-%dT%H:', datetime(Date, 'unixepoch', 'localtime')) || ((strftime('%M', datetime(Date, 'unixepoch', 'localtime'))/15) * 15)"}, {QueryResolution.TenMinutes, "strftime('%Y-%m-%dT%H:', datetime(Date, 'unixepoch', 'localtime')) || ((strftime('%M', datetime(Date, 'unixepoch', 'localtime'))/10) * 10)"}, {QueryResolution.HalfHour, "strftime('%Y-%m-%dT%H:', datetime(Date, 'unixepoch', 'localtime')) || ((strftime('%M', datetime(Date, 'unixepoch', 'localtime'))/30) * 30)"}, }; // Build the query string query; string groupBy; switch (resolution) { case QueryResolution.All: query = allSelectClause; groupBy = string.Empty; break; default: var datePart = resolutionDateParts[resolution]; query = string.Format(resolutionSelectClause, datePart); groupBy = string.Format(resolutionGroupByClause, datePart); break; } return string.Format("{1}{0}{2}{0}{3}{0}{4}{0}{5}", Environment.NewLine, query, fromClause, whereClause, groupBy, orderByClause); }