see on GitHub

Session: Time Series Querying


Learn more about time series queries in the section dedicated to this subject.


Time Series LINQ Queries

To build a time series LINQ query, run a document query using session.Query and extend it using LINQ expressions.
Here is a simple LINQ query that chooses 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, "HeartRate")
            //.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 HeartRate
        )");

var nonAggregatedRawQueryResult = nonAggregatedRawQuery.ToList();

Syntax

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.

Usage Flow

  • 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

Usage Samples

  • 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, "HeartRate", 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, "HeartRate", 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.

    // 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, "HeartRate", 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();

Client Raw RQL Queries

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

RQL Queries Syntax

  • 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.

RQL Queries Usage Flow

  • 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

RQL Queries Usage Samples

  • 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.

    // 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 getHeartRate(user) 
            {
                from user.HeartRate 
                    between $start and $end
                    offset '02:00'
            }
            from Users as u where Age < 30
            select getHeartRate(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 HeartRate 
                    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 HeartRate 
                    between $start and $end
                group by '1 days'
                select min(), max())
            ")
        .AddParameter("start", baseline)
        .AddParameter("end", baseline.AddDays(7));
    
    var aggregatedRawQueryResult = aggregatedRawQuery.ToList();