Querying: Choosing Time Series Range

• Queries can be performed over whole time series or over a chosen range of time series entries, e.g. only entries collected during the last 7 days.

• In this page:

  • Choosing Query Range
    • first and last
  • Client Usage Examples
    • Choosing a Range Using LINQ
    • Choosing a Range Using Raw RQL

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'
'2020-06-23T00:00:00.0000000Z'

sers as jog where Age < 30
timeseries(
m HeartRate 
ween $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
)

Client Usage Examples

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 example, we select a three-days range from the HeartRate 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();

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.

• Declare-Syntax
• Select-Syntax

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