I don't think thats solves my problem. The question really is why can't we use ranges for both time columns when they are part of the primary key. They are on 1 row after all. Is this just a CQL limitation?
-Raj On Sat, Feb 14, 2015 at 3:35 AM, DuyHai Doan <doanduy...@gmail.com> wrote: > "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 >> > >
