Timeseries: Include rows immediately adjacent to range query?

[Hugo José Pinto <hu...@inovaworks.com>]

Hi!

We're using cassandra to store a time series, using a table similar to:

CREATE TABLE timeseries (
source_id uuid,
tstamp timestamp,
value text,
PRIMARY KEY (source_id, tstamp)
) WITH CLUSTERING ORDER BY (tstamp DESC);

With that, we do a ranged query with tstamp > x and tstamp < y to gather
all events within a time window.

Now, due to the variable granularity of incoming data, we have no guarantee
that our data points are close to the requested interval. In order to
compute derived values, we'd need the values immediately before and after
the requested range.

Any ideas on what would be the best way to approach this in Cassandra CQL?

Many thanks for any help!

-- 
Hugo José Pinto

Re: Timeseries: Include rows immediately adjacent to range query?

[Eric Stevens <mi...@gmail.com>]

It seems like you should be able to solve it with two more queries
immediately after your first query:

SELECT * FROM timeseries WHERE tstamp < ${MIN(firstQuery.tstamp)} LIMIT 1
SELECT * FROM timeseries WHERE tstamp > ${MAX(firstQuery.tstamp)} LIMIT 1


On Tue, Jan 13, 2015 at 9:31 AM, Hugo José Pinto <hu...@inovaworks.com>
wrote:

> Hi!
>
> We're using cassandra to store a time series, using a table similar to:
>
> CREATE TABLE timeseries (
> source_id uuid,
> tstamp timestamp,
> value text,
> PRIMARY KEY (source_id, tstamp)
> ) WITH CLUSTERING ORDER BY (tstamp DESC);
>
> With that, we do a ranged query with tstamp > x and tstamp < y to gather
> all events within a time window.
>
> Now, due to the variable granularity of incoming data, we have no
> guarantee that our data points are close to the requested interval. In
> order to compute derived values, we'd need the values immediately before
> and after the requested range.
>
> Any ideas on what would be the best way to approach this in Cassandra CQL?
>
> Many thanks for any help!
>
> --
> Hugo José Pinto
>
>