see on GitHub

Querying: Choosing Time Series Range



Choosing Query Range

In an RQL query, use between and and to specify a range of time series entries to query. The entries are chosen by their timestamps, in UTC format.

from Users as jog where Age < 30
select timeseries(
   from HeartRate 
   between 
      '2020-05-27T00:00:00.0000000Z'
     and 
      '2020-06-23T00:00:00.0000000Z'
  • between '2020-05-27T00:00:00.0000000Z' and '2020-06-23T00:00:00.0000000Z'
    Retrieve all entries between these two timestamps.

    You can use the Studio to try these queries.
    Using the studio, you can use parameters for a clearer query.
    E.g. -

    $from = '2020-05-27T00:00:00.0000000Z'
    $to = '2020-06-23T00:00:00.0000000Z'
    
    from Users as jog where Age < 30
    select timeseries(
       from HeartRate 
       between $from and $to
    )

Client Usage Samples

You can run queries from your client using raw RQL and LINQ.

  • Learn how to run a LINQ time series query here.
  • Learn how to run a raw RQL time series query here.


Choosing a Range Using LINQ

To choose a range as part of a LINQ query, pass RavenQuery.TimeSeries a from and a to DateTime values.
Omitting these values will load the entire series.

  • RavenQuery.TimeSeries Definitions

    public static ITimeSeriesQueryable TimeSeries(object documentInstance, 
        string name)
    public static ITimeSeriesQueryable TimeSeries(object documentInstance, 
        string name, DateTime from, DateTime to)
  • Parameters

    Parameters Type Description
    documentInstance object Document Instance
    name string Time Series Name
    from (optional) DateTime Range Start
    Default: DateTime.Min
    to (optional) DateTime Range End
    Default: DateTime.Max
  • 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();

Choosing a Range Using Raw RQL

To choose a range as part of a raw RQL query, use the between and and keywords.

In this sample, a raw RQL query chooses the profiles of users under the age of 30 and retrieves a 24-hours range from each.
An offset is defined, adding two hours to retrieved timestamps to adjust them 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();