Time Series Querying

Query usage

• Open a session
• Call session.Query:
  • Extend the query using LINQ expressions
  • Provide a Where query predicate to locate documents whose time series you want to query
  • Use Select to choose a time series and project time series data
  • Execute the query
• Results will be in the form:
  • TimeSeriesRawResult for non-aggregated data, or -
  • TimeSeriesAggregationResult for aggregated data
• Note:
  The RavenDB client translates the LINQ query to RQL before transmitting it to the server for execution.

Query examples

• This LINQ query filters users by their age and retrieves their HeartRates time series.
  The first occurence of Where filters the documents.
  The second Where filters the time series entries.

  • Query
  • RQL

  using (var session = store.OpenSession())
  {
      // Define the query:
      var query = session.Query<User>()
               // Filter the user documents
              .Where(u => u.Age < 30)
               // Call 'Select' to project the time series entries
              .Select(q => RavenQuery.TimeSeries(q, "HeartRates")
                   // Filter the time series entries    
                  .Where(ts => ts.Tag == "watches/fitbit")
                   // 'ToList' must be applied here to the inner time series query definition
                   // This will not trigger query execution at this point
                  .ToList());
      
      // Execute the query:
      // The following call to 'ToList' will trigger query execution
      List<TimeSeriesRawResult> result = query.ToList();
  }

  from "Users" as q
  where q.Age < 30
  select timeseries(from q.HeartRates where (Tag == "watches/fitbit"))

• In this example, we select a three-day range from the HeartRates time series.
  

  • Query
  • RQL

  var baseTime = new DateTime(2020, 5, 17, 00, 00, 00);
  
  var query = session.Query<User>()
      .Select(q => RavenQuery
          .TimeSeries(q, "HeartRates", baseTime, baseTime.AddDays(3))
          .ToList());
  
  List<TimeSeriesRawResult> result = query.ToList();

  from "Users" as q
  select timeseries(from q.HeartRates between "2020-05-17T00:00:00.0000000" and "2020-05-17T00:03:00.0000000")

• In this example, we retrieve a company's stock trade data.
  Note the usage of named values, so we may address trade Volume by name.
  

  • Native
  • Named

  using (var session = store.OpenSession())
  {
      var query = session.Query<Company>()
          .Where(c => c.Address.City == "New York")
          .Select(q => RavenQuery.TimeSeries(q, "StockPrices", baseTime, baseTime.AddDays(3))
              .Where(ts => ts.Tag == "companies/kitchenAppliances")
              .ToList());
  
      List<TimeSeriesRawResult> queryResults = query.ToList();
      
      TimeSeriesEntry[] tsEntries = queryResults[0].Results;
      
      double volumeDay1 = tsEntries[0].Values[4];
      double volumeDay2 = tsEntries[1].Values[4];
      double volumeDay3 = tsEntries[2].Values[4];
  }

  using (var session = store.OpenSession())
  {
      var query =
          session.Query<Company>()
          .Where(c => c.Address.City == "New York")
           // Use the StockPrice type in the time series query
          .Select(q => RavenQuery.TimeSeries<StockPrice>(q, "StockPrices", baseTime, baseTime.AddDays(3))
              .Where(ts => ts.Tag == "companies/kitchenAppliances")
              .ToList());
  
      List<TimeSeriesRawResult<StockPrice>> queryResults = query.ToList();
      
      var tsEntries = queryResults[0].Results;
  
      double volumeDay1 = tsEntries[0].Value.Volume;
      double volumeDay2 = tsEntries[1].Value.Volume;
      double volumeDay3 = tsEntries[2].Value.Volume;
  }

• In this example, we group heart-rate data of people above the age of 72 into 1-day groups,
  and retrieve each group's average heart rate and number of measurements.
  The aggregated results are retrieved as List<TimeSeriesAggregationResult>.

  var query = session.Query<User>()
      .Where(u => u.Age > 72)
      .Select(q => RavenQuery.TimeSeries(q, "HeartRates", baseline, baseline.AddDays(10))
          .Where(ts => ts.Tag == "watches/fitbit")
          .GroupBy(g => g.Days(1))
          .Select(g => new
          {
              Avg = g.Average(),
              Cnt = g.Count()
          })
          .ToList());
  
  List<TimeSeriesAggregationResult> result = query.ToList();

Query syntax

• The session.Query syntax is available here.

• To define a time series query use RavenQuery.TimeSeries within the query Select clause.

• RavenQuery.TimeSeries overloads:

  public static ITimeSeriesQueryable TimeSeries(object documentInstance,
      string name)

  public static ITimeSeriesQueryable TimeSeries(object documentInstance,
      string name, DateTime from, DateTime to)

  Parameter	Type	Description
  documentInstance	object	Document Instance
  name	string	Time Series Name
  from (optional)	DateTime	Range Start Default: DateTime.Min
  to (optional)	DateTime	Range End Default: DateTime.Max

• RavenQuery.TimeSeries can be extended with the following time series methods:

  Offset(TimeSpan offset);
  Scale(double value);
  FromLast(Action<ITimePeriodBuilder> timePeriod);
  FromFirst(Action<ITimePeriodBuilder> timePeriod);
  LoadByTag<TEntity>();
  GroupBy(string s);
  GroupBy(Action<ITimePeriodBuilder> timePeriod);
  Where(Expression<Func<TimeSeriesEntry, bool>> predicate);

DocumentQuery usage

• Open a session
• Call session.Advanced.DocumentQuery:
  • Extend the query using RavenDB's fluent API methods
  • Provide a WhereEquals query predicate to locate documents whose time series you want to query
  • Use SelectTimeSeries to choose a time series and project time series data
  • Execute the query
• Results will be in the form:
  • TimeSeriesRawResult for non-aggregated data, or -
  • TimeSeriesAggregationResult for aggregated data
• Note:
  The RavenDB client translates query to RQL before transmitting it to the server for execution.

DocumentQuery examples

• A DocumentQuery using only the From() method.
  The query returns all entries from the 'HeartRates' time series.

  // Define the query:
  var query = session.Advanced.DocumentQuery<User>()
      .SelectTimeSeries(builder => builder
          .From("HeartRates")
           // 'ToList' must be applied here to the inner time series query definition
           // This will not trigger query execution at this point
          .ToList());
  
      
  // Execute the query:
  // The following call to 'ToList' will trigger query execution
  List<TimeSeriesRawResult> results = query.ToList();

• A DocumentQuery using Between().
  The query returns only entries from the specified time range.

  var query = session.Advanced.DocumentQuery<User>()
      .SelectTimeSeries(builder => builder
          .From("HeartRates")
          .Between(DateTime.Now, DateTime.Now.AddDays(1))
          .ToList());
  
  List<TimeSeriesRawResult> results = query.ToList();

• A DocumentQuery using FromFirst().
  The query returns the first three days of the 'HeartRates' time series.
  

  var query = session.Advanced.DocumentQuery<User>()
      .SelectTimeSeries(builder => builder
          .From("HeartRates")
          .FromFirst(x => x.Days(3))
          .ToList());
  
  List<TimeSeriesRawResult> results = query.ToList();

• A DocumentQuery using FromLast().
  The query returns the last three days of the 'HeartRates' time series.
  

  var query = session.Advanced.DocumentQuery<User>()
      .SelectTimeSeries(builder => builder
          .From("HeartRates")
          .FromLast(x => x.Days(3))
          .ToList());
  
  List<TimeSeriesRawResult> results = query.ToList();

• A DocumentQuery that loads the related Monitor documents that are specified in the time entries tags.
  The results are then filtered by their content.

  • Query
  • Class

  var query = session.Advanced.DocumentQuery<User>()
      .SelectTimeSeries(builder => builder
          .From("HeartRates")
          .LoadByTag<Monitor>()
          .Where((entry, monitor) => entry.Value <= monitor.Accuracy)
          .ToList());
  
  List<TimeSeriesRawResult> results = query.ToList();

  public class Monitor
  {
      public double Accuracy { get; set; }
  }

DocumentQuery syntax

The session DocumentQuery, which is accessible from session.Advanced, can be extended with several useful time series methods. To access these methods, begin with method SelectTimeSeries():

IDocumentQuery SelectTimeSeries(Func<ITimeSeriesQueryBuilder, TTimeSeries> timeSeriesQuery);

SelectTimeSeries() takes an ITimeSeriesQueryBuilder. The builder has the following methods:

From(string name);
Between(DateTime start, DateTime end);
FromLast(Action<ITimePeriodBuilder> timePeriod);
FromFirst(Action<ITimePeriodBuilder> timePeriod);
LoadByTag<TTag>();
//LoadByTag is extended by a special version of Where():
Where(Expression<Func<TimeSeriesEntry, TTag, bool>> predicate);

FromLast() and FromFirst() take an ITimePeriodBuilder, which is used to represent a range of time from milliseconds to years:

public interface ITimePeriodBuilder
{
    Milliseconds(int duration);
    Seconds(int duration);
    Minutes(int duration);
    Hours(int duration);
    Days(int duration);
    Months(int duration);
    Quarters(int duration);
    Years(int duration);
}

Return Value:

• List<TimeSeriesAggregationResult> for aggregated data.
  When the query aggregates time series entries, the results are returned in an aggregated array.

• List<TimeSeriesRawResult> for non-aggregated data.
  When the query doesn't aggregate time series entries, the results are returned in a list of time series results.

RawQuery usage

• Open a session
• Call session.Advanced.RawQuery, pass it the raw RQL that will be sent to the server
• Results will be in the form:
  • TimeSeriesRawResult for non-aggregated data, or -
  • TimeSeriesAggregationResult for aggregated data
• Note:
  The raw query transmits the provided RQL to the server as is, without checking or altering its content.

RawQuery examples

• In this example, we retrieve all HearRates time series for all users under 30.

  • RawQuery

  // Raw query with no aggregation - Select syntax
  var query = session.Advanced.RawQuery<TimeSeriesRawResult>(@"
          from Users where Age < 30
          select timeseries (
              from HeartRates
          )");
  
  List<TimeSeriesRawResult> results = query.ToList();

• In this example, a raw RQL query retrieves 24 hours of heart rate data from users under the age of 30.
  The query does not aggregate data, so results are in the form of a TimeSeriesRawResult list.
  We define an offset, to adjust retrieved results to the client's local time-zone.

  • Declare-Syntax
  • Select-Syntax

  var baseTime = new DateTime(2020, 5, 17, 00, 00, 00); // May 17 2020, 00:00:00
  
  // Raw query with no aggregation - Declare syntax
  var query =
      session.Advanced.RawQuery<TimeSeriesRawResult>(@"
          declare timeseries getHeartRates(user) 
          {
              from user.HeartRates 
                  between $from and $to
                  offset '02:00'
          }
          from Users as u where Age < 30
          select getHeartRates(u)
          ")
      .AddParameter("from", baseTime)
      .AddParameter("to", baseTime.AddHours(24));
  
  List<TimeSeriesRawResult> results = query.ToList();

  var baseline = new DateTime(2020, 5, 17, 00, 00, 00); // May 17 2020, 00:00:00
  
  // Raw query with no aggregation - Select syntax
  var query =
      session.Advanced.RawQuery<TimeSeriesRawResult>(@"
          from Users as u where Age < 30
          select timeseries (
              from HeartRates 
                  between $from and $to
                  offset '02:00'
          )")
      .AddParameter("from", baseline)
      .AddParameter("to", baseline.AddHours(24));
  
  var results = query.ToList();

• In this example, the query aggregates 7 days of HeartRates entries into 1-day groups.
  From each group, two values are selected and projected to the client:
  the min and max hourly HeartRates values.
  The aggregated results are in the form of a TimeSeriesAggregationResult list.

  var baseline = new DateTime(2020, 5, 17, 00, 00, 00); // May 17 2020, 00:00:00
  
  // Raw Query with aggregation
  var query =
      session.Advanced.RawQuery<TimeSeriesAggregationResult>(@"
          from Users as u
          select timeseries(
              from HeartRates 
                  between $start and $end
              group by '1 day'
              select min(), max()
              offset '03:00')
          ")
      .AddParameter("start", baseline)
      .AddParameter("end", baseline.AddDays(7));
  
  List<TimeSeriesAggregationResult> results = query.ToList();

RawQuery syntax

IRawDocumentQuery<T> RawQuery<T>(string query);

Return Value:

• List<TimeSeriesAggregationResult> for aggregated data.
  When the query aggregates time series entries, the results are returned in an aggregated array.

• List<TimeSeriesRawResult> for non-aggregated data.
  When the query doesn't aggregate time series entries, the results are returned in a list of time series results.