Get Date Range for Cassandra - Select timeuuid with IN returning 0 rows

cassandra, cql

Solution

The `IN` condition is used to specify multiple keys for a SELECT query. To run a date range query for your table, (you're close) but you'll want to use greater-than and less-than.

Of course, you can't run a greater-than/less-than query on a partition key, so you'll need to flip your keys for this to work. This also means that you'll need to specify your `id` in the WHERE clause, as well:

CREATE TABLE teste6 (
  time timeuuid,
  id text,
  checked boolean,
  email text,
  name text,
  PRIMARY KEY ((id), time)
)

INSERT INTO teste6 (time,id,checked,email,name)
VALUES (now(),'B26354',true,'rdeckard@lapd.gov','Rick Deckard');

SELECT * FROM teste6 
WHERE id='B26354'
AND time >= minTimeuuid('2013-01-01 00:05+0000')
AND time <= now();

 id     | time                                 | checked | email             | name
--------+--------------------------------------+---------+-------------------+--------------
 B26354 | bf0711f0-b87a-11e4-9dbe-21b264d4c94d |    True | rdeckard@lapd.gov | Rick Deckard

(1 rows)

Now while this will technically work, partitioning your data by `id` might not work for your application. So you may need to put some more thought behind your data model and come up with a better partition key.

Edit:

Remember with Cassandra, the idea is to get a handle on what kind of queries you need to be able to fulfill. Then build your data model around that. Your original table structure might work well for a relational database, but in Cassandra that type of model actually makes it difficult to query your data in the way that you're asking.

Take a look at the modifications that I have made to your table (basically, I just reversed your partition and clustering keys). If you still need help, Patrick McFadin (DataStax's Chief Evangelist) wrote a really good article called Getting Started with Time Series Data Modeling. He has three examples that are similar to yours. In fact his first one is very similar to what I have suggested for you here.

Problem

Original source