Querying: Choosing Time Series 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
  )

first and last

You can use the keywords first and last to specify a range of entries at the beginning or end of the time series. The range is specified using a whole number of one of these units:

• Seconds
• Minutes
• Hours
• Days
• Months
• Quarters
• Years

Within the select timeseries clause of the query, write e.g. first 1 second or last 3 quarters.

from Users
select timeseries(
   from HeartRates 
   last 1 day
)

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 example, 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, "HeartRates", baseline, baseline.AddDays(3))
          .ToList());
  
  var result = query.ToList();

FromFirst() and FromLast()

To select only the first or last entries, use the methods FromFirst() or FromLast() (only one of them can be used at a time).

ITimeSeriesQueryable FromLast(Action<ITimePeriodBuilder> timePeriod);

ITimeSeriesQueryable FromFirst(Action<ITimePeriodBuilder> timePeriod);

In this example, we select only the entries in the last 30 minutes of the time series "HeartRates".

var result = session.Query<Person>()
    .Select(p => 
    RavenQuery.TimeSeries(p, "HeartRates")
        .FromLast(g => g.minutes(30))
        .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 example, 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 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();

first and last

To select only the first or last entries, use the keywords first or last (only one of them can be used at a time). Within the select timeseries clause of the query, write e.g. first 1 second or last 3 quarters.

In this example, we select only the entries in the last 12 hours of the time series "HeartRates".

from People as doc
select timeseries(
    from doc.HeartRates 
    last 12h
    group by 1h
    select min(), max(), avg()
)