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);
}
