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

var nonAggregatedRawQueryResult = nonAggregatedRawQuery.ToList();

Syntax

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);
Parameter Type Description
name string The name of the time series (in one or more documents) to query
start DateTime First parameter for Between(); the beginning of the time series range to filter.
end DateTime Second parameter for Between(); the end of the time series range to filter.
timePeriod Action<ITimePeriodBuilder> Expression returning a number of time units representing a time series range either at the beginning or end of the queried time series.
LoadByTag type parameter TTag Time series entry tags can be just strings, but they can also be document IDs, representing a reference to an entity. LoadByTag takes the type of the entity.
predicate Expression<Func<TimeSeriesEntry, TTag, bool>>  

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.

Usage Flow

  • 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

Examples

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.

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

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

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