Session: Time Series Querying

To build a time series LINQ query, start with session.Query and extend it using LINQ expressions.
Here is a simple LINQ query that filters users by their age and retrieves their HeartRate time series, and the RQL equivalent for this query.

// Query - LINQ format
using (var session = store.OpenSession())
{
    var baseline = new DateTime(2020, 5, 17, 00, 00, 00);

    IRavenQueryable<TimeSeriesRawResult> query =
        (IRavenQueryable<TimeSeriesRawResult>)session.Query<User>()
            .Where(u => u.Age < 30)
            .Select(q => RavenQuery.TimeSeries(q, "HeartRates")
            .Where(ts => ts.Tag == "watches/fitbit")
            .ToList());

    var result = query.ToList();
}

// May 17 2020, 00:00:00
var baseline = new DateTime(2020, 5, 17, 00, 00, 00);

// Raw query with no aggregation - Select syntax
IRawDocumentQuery<TimeSeriesRawResult> nonAggregatedRawQuery =
    session.Advanced.RawQuery<TimeSeriesRawResult>(@"
        from Users as u where Age < 30                            
        select timeseries (
            from HeartRates
        )");

var nonAggregatedRawQueryResult = nonAggregatedRawQuery.ToList();

session.Query Definition:

IRavenQueryable<T> Query<T>(string indexName = null,
        string collectionName = null, bool isMapReduce = false);

Learn more about session.Query here.

Return Value:

• IRavenQueryable<TimeSeriesAggregationResult> for aggregated data.
  When the query aggregates time series entries, the results are returned in an aggregated array.
• IRavenQueryable<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.

• Open a session
• Call session.Query.
  • Run a document query to locate documents whose time series you want to query.
  • Extend the query using LINQ expressions to find and project time series data.
    Start with Select to choose a time series.
• Retrieve the results using -
  TimeSeriesAggregationResult for aggregated data
  -or-
  TimeSeriesRawResult for non-aggregated data
  

• In this sample, we select a three-days range from the HeartRate time series.
  

  IRavenQueryable<TimeSeriesRawResult> query =
      (IRavenQueryable<TimeSeriesRawResult>)session.Query<User>()
          .Where(u => u.Age < 30)
          .Select(q => RavenQuery.TimeSeries(q, "HeartRates", baseline, baseline.AddDays(3))
          .ToList());
  
  var result = query.ToList();

• The first occurance of Where in the following example, filters documents.
  The second occurance of Where filters entries.

  IRavenQueryable<TimeSeriesRawResult> query =
      (IRavenQueryable<TimeSeriesRawResult>)session.Query<User>()
  
          // Choose user profiles of users under the age of 30
          .Where(u => u.Age < 30)
  
          .Select(q => RavenQuery.TimeSeries(q, "HeartRates", baseline, baseline.AddDays(3))
  
          // Filter entries by tag.  
          .Where(ts => ts.Tag == "watches/fitbit")
  
          .ToList());
  
  var result = query.ToList();

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

  • Native
  • Named

  // Same query, only unnamed
  using (var session = store.OpenSession())
  {
      IRavenQueryable<TimeSeriesRawResult> query =
          session.Query<Company>()
          .Where(c => c.Address.City == "New York")
          .Select(q => RavenQuery.TimeSeries(q, "StockPrices", baseline, baseline.AddDays(3))
              .Where(ts => ts.Tag == "companies/kitchenAppliances")
              .ToList());
  
      var result = query.ToList()[0];
  
      day1Volume = result.Results[0].Values[4];
      day2Volume = result.Results[1].Values[4];
      day3Volume = result.Results[2].Values[4];
  }

  // Named Values Query
  using (var session = store.OpenSession())
  {
      IRavenQueryable<TimeSeriesRawResult<StockPrice>> query =
          session.Query<Company>()
          .Where(c => c.Address.City == "New York")
          .Select(q => RavenQuery.TimeSeries<StockPrice>(q, "StockPrices", baseline, baseline.AddDays(3))
              .Where(ts => ts.Tag == "companies/kitchenAppliances")
              .ToList());
  
      var result = query.ToList()[0];
  
      day1Volume = result.Results[0].Value.Volume;
      day2Volume = result.Results[1].Value.Volume;
      day3Volume = result.Results[2].Value.Volume;
  }

• Here, 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 into an IRavenQueryable<TimeSeriesAggregationResult> array.
  

  IRavenQueryable<TimeSeriesAggregationResult> 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());
  
  var result = query.ToList();

To send a raw RQL query to the server, use session.Advanced.RawQuery.
RawQuery transmits queries to the server without checking or altering their contents, time series contents or otherwise

• session.Advanced.RawQuery

  • Definition
    

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

  • Parameters

    Parameters	Type	Description
    query	string	Raw RQL Query

  • Return Value:

    • IRawDocumentQuery<TimeSeriesAggregationResult> for aggregated data.
      When the query aggregates time series entries, the results are returned in an aggregated array.
    • IRawDocumentQuery<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.

• Open a session
• Call session.Advanced.RawQuery
  Pass it your query
• Retrieve the results into
  TimeSeriesAggregationResult for aggregated data
  -or-
  TimeSeriesRawResult for non-aggregated data
  

• In this sample, a raw RQL query retrieves 24 hours of HeartRate data from users under the age of 30.
  The query does not aggregate data, so we retrieve its results using a TimeSeriesRawResult list.
  We define an offset, to adjust retrieved results to the client's local time-zone.

  • Declare-Syntax
  • Select-Syntax

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

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

• In this sample, the query aggregates 7 days of HeartRate entries into 1-day groups.
  From each group, two values are selected and projected to the client: the min and max hourly HeartRate values.
  The aggregated results are retrieved using a TimeSeriesAggregationResult array.

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