Choosing Time Series Range



Choose range in a query

Specify range

  • Provide 'from' & 'to' DateTime values to the time series query to retrieve entries only from that range (inclusive). Omitting these parameters will retrieve the entire series.

  • The provided DateTime values are treated by the server as UTC.
    The client does Not perform any conversion to UTC prior to sending the request to the server.

  • Note: calling 'offset' will only adjust the timestamps in the returned results to your local time (optional).

  • In this example, we specify a 10-minute range from which to retrieve "HeartRates" entries for UK employees.

// Define the time series query part (expressed in RQL):
const tsQueryText = `
    from HeartRates
    between "2020-05-17T00:00:00.0000000"
    and "2020-05-17T00:10:00.0000000"
    offset "03:00"`;

// Define the query:
const query = session.query({ collection: "employees" })
    .whereEquals("Address.Country", "UK")
    .selectTimeSeries(b => b.raw(tsQueryText), TimeSeriesRawResult);

// Execute the query:
const results = await query.all();

// Access entries results:
rawResults = results[0];
assert.equal((rawResults instanceof TimeSeriesRawResult), true);

const tsEntry = rawResults.results[0];
assert.equal((tsEntry instanceof TimeSeriesEntry), true);

const tsValue = tsEntry.value;
const from = new Date("2020-05-17T00:00:00.0000000");
const to = new Date("2020-05-17T00:10:00.0000000");

// Define the time series query part (expressed in RQL):
const tsQueryText = `
    from HeartRates
    between $from and $to
    offset "03:00"`;

// Define the query:
const query = session.query({ collection: "employees" })
    .whereEquals("Address.Country", "UK")
    .selectTimeSeries(b => b.raw(tsQueryText), TimeSeriesRawResult)
    .addParameter("from", from)
    .addParameter("to", to);

// Execute the query:
const results = await query.all();

// Access entries results:
rawResults = results[0];
assert.equal((rawResults instanceof TimeSeriesRawResult), true);

const tsEntry = rawResults.results[0];
assert.equal((tsEntry instanceof TimeSeriesEntry), true);

const tsValue = tsEntry.value;
const rql = `
    from "Employees" as employee
    where employee.Address.Country == "UK"
    select timeseries(
        from employee.HeartRates
        between "2020-05-17T00:00:00.0000000"
        and "2020-05-17T00:10:00.0000000"
        offset "03:00"
    )`;

const query = session.advanced.rawQuery(rql, TimeSeriesRawResult);

const result = await query.all();
const rql = `
    from "Employees" as employee
    where employee.Address.Country == "UK"
    select timeseries(
        from employee.HeartRates
        between $from and $to
        offset "03:00"
    )`;

const from = new Date("2020-05-17T00:00:00.0000000");
const to = new Date("2020-05-17T00:10:00.0000000");

const query = session.advanced.rawQuery(rql, TimeSeriesRawResult)
    .addParameter("from", from)
    .addParameter("to", to);

const result = await query.all();
// RQL:
from "employees" as employee
where employee.Address.Country == "UK"
select timeseries(
    from employee.HeartRates
    between "2020-05-17T00:00:00.0000000"
    and "2020-05-17T00:10:00.0000000"
    offset "03:00"
)

// RQL with parameters:
from "employees"
where Address.Country = $p0
select timeseries(
    from HeartRates
    between $from and $to
    offset "03:00"
)
{
  "p0":   "UK",
  "from": "2020-05-17T00:00:00.0000000",
  "to":   "2020-05-17T00:10:00.0000000"
}

Retrieve first or last entries

  • Use first to specify the time frame from the start of the time series.
    Use last to specify the time frame from the end of the time series.
    Only one of them can be used in the same query function.

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

// Define the time series query part (expressed in RQL):
const tsQueryText = `
    from HeartRates
    last 30 min
    offset "03:00"`;

// Define the query:
const query = session.query({ collection: "employees" })
    .selectTimeSeries(b => b.raw(tsQueryText), TimeSeriesRawResult);

// Execute the query:
const results = await query.all();
const rql = `
    from "Employees" as employee
    select timeseries(
        from employee.HeartRates
        last 30 min
        offset "03:00"
    )`;

const query = session.advanced.rawQuery(rql, TimeSeriesRawResult);

const result = await query.all();
from "Employees" as e 
select timeseries(
    from e.HeartRates
    last 30 min
    offset "03:00"
)

Choose range - RQL syntax

between and and


  • Use the between and and keywords to retrieve time series entries from the specified range (inclusive).
    Provide the timestamps in UTC format. E.g.:

    from "Employees"
    where Address.Country == "UK"
    select timeseries(
        from HeartRates
        between "2020-05-17T00:00:00.0000000Z" // start of range
        and "2020-05-17T01:00:00.0000000Z"     // end of range
    )
    
    // Results will include only time series entries within the specified range for employees from UK.
    declare timeseries getHeartRates(employee)
    {
        from HeartRates
        between "2020-05-17T00:00:00.0000000Z" // start of range
        and "2020-05-17T01:00:00.0000000Z"     // end of range
    }
    
    from "Employees" as e
    where e.Address.Country == "UK"
    select getHeartRates(e) 
    
    // Results will include only time series entries within the specified range for employees from UK.
  • RQL queries can be run from the query view in the Studio.
    Using the Studio, you can use parameters in the following way for a clearer query.

    $from = "2020-05-17T00:00:00.0000000Z"
    $to = "2020-05-17T01:00:00.0000000Z"
    
    from "Employees"
    where Address.Country == "UK"
    select timeseries(
        from HeartRates
        between $from and $to  // using parameters
    )

first and last


  • Use first to specify the time frame from the start of the time series.
    Use last to specify the time frame from the end of the time series.
    Only one of them can be used in the same query function. E.g.:

    // Retrieve all entries from the last day, starting from the end of time series "HeartRates"
    from "Employees"
    select timeseries(
        from HeartRates
        last 1 day
    )

    // Retrieve the first 10 minutes of entries from the beginning of time series "HeartRates"
    from "Employees"
    select timeseries(
        from HeartRates
        first 10 min
    )
  • The range is specified using a whole number of one of the following units.

    • seconds ( seconds/ second / s )
    • minutes ( minutes / minute / min )
    • hours ( hours / hour / h )
    • days ( days / day / d )
    • months ( months / month / mon / mo )
    • quarters ( quarters / quarter / q )
    • years ( years / year / y )
    • Note: milliseconds are currently not supported by 'first' and 'last' in a time series query.