The simple, easy way to look at this is that you can use a range when the data will be contiguous.Only by allowing only the last clustering column to use a range can Cassandra be assured that the rows selected by the range will be contiguous (a "slice.") The point is that Cassandra is designed for high performance operations, and with the trade-off that low-performance operations are simply not supported.
DataStax Enterprise with Solr for search and Spark for analytics, or Lucene indexing of Cassandra data through Stratio or Stargate, will give you much more flexible query capabilities. -- Jack Krupansky On Sat, Feb 14, 2015 at 6:05 PM, Raj N <raj.cassan...@gmail.com> wrote: > 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 >>> >> >> >
