Time Series Querying


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


Query


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.

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.

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.

using (var session = store.OpenSession())
{
    var 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());

    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];
}
// Named Values Query
using (var session = store.OpenSession())
{
    var 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());

    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

session.Query Definition:

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

Learn more about session.Query here.

DocumentQuery


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.

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

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.

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);
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 a related document. 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:

  • 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


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.

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

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 $start and $end
                offset '02:00'
        }
        from Users as u where Age < 30
        select getHeartRates(u)
        ")
    .AddParameter("start", baseTime)
    .AddParameter("end", 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 $start and $end
                offset '02:00'
        )")
    .AddParameter("start", baseline)
    .AddParameter("end", 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);
Parameter Type Description
query string The RQL query string

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.