"I am trying to get the state as of a particular transaction_time"
--> In that case you should probably define your primary key in another order for clustering columns PRIMARY KEY (weatherstation_id,transaction_time,event_time) Then, select * from temperatures where weatherstation_id = 'foo' and event_time >= '2015-01-01 00:00:00' and event_time < '2015-01-02 00:00:00' and transaction_time = 'xxxx' On Sat, Feb 14, 2015 at 3:06 AM, Raj N <raj.cassan...@gmail.com> wrote: > Has anyone designed a bi-temporal table in Cassandra? Doesn't look like I > can do this using CQL for now. Taking the time series example from well > known modeling tutorials in Cassandra - > > CREATE TABLE temperatures ( > weatherstation_id text, > event_time timestamp, > temperature text, > PRIMARY KEY (weatherstation_id,event_time), > ) WITH CLUSTERING ORDER BY (event_time DESC); > > If I add another column transaction_time > > CREATE TABLE temperatures ( > weatherstation_id text, > event_time timestamp, > transaction_time timestamp, > temperature text, > PRIMARY KEY (weatherstation_id,event_time,transaction_time), > ) WITH CLUSTERING ORDER BY (event_time DESC, transaction_time DESC); > > If I try to run a query using the following CQL, it throws an error - > > select * from temperatures where weatherstation_id = 'foo' and event_time > >= '2015-01-01 00:00:00' and event_time < '2015-01-02 00:00:00' and > transaction_time < '2015-01-02 00:00:00' > > It works if I use an equals clause for the event_time. I am trying to get > the state as of a particular transaction_time > > -Raj >
