Session: Time Series Querying

To build a time series LINQ query, start with session.Query or session.Advanced.DocumentQuery and extend it using LINQ expressions.
Here is a simple LINQ query that chooses users by their age and retrieves their HeartRates 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

session.Query Definition:

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

Learn more about session.Query here.

Document Query

The session Document Query accessible at session.Advanced can be extended with several useful time series methods. To access these methods, begin with the LINQ 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:

• 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 or session.Advanced.DocumentQuery.
  • 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
  

Session.Query

• In this sample, we select a three-day range from the HeartRates 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();

Document Query

• A Document Query using only the From() method.
  

  var query = session.Advanced.DocumentQuery<User>()
      .SelectTimeSeries(builder => builder
          .From("Heartrate")
          .ToList());

• A Document Query using Between().
  

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

• Two Document Queries using FromFirst() and FromLast(). These return the first three days of the 'HeartRates' time series, and the last three days, respectively.
  

  var query1 = session.Advanced.DocumentQuery<User>()
      .SelectTimeSeries(builder => builder
          .From("Heartrate")
          .FromFirst(x => x.Days(3))
          .ToList());
  
  var query2 = session.Advanced.DocumentQuery<User>()
      .SelectTimeSeries(builder => builder
          .From("Heartrate")
          .FromLast(x => x.Days(3))
          .ToList());

• A Document Query that filters time series entries by tags of the type Monitor. That is, the tags are document IDs of entities of type Monitor.
  

  • Query
  • Class

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

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

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