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
