How to perform query with cassandra's timestamp column as WHERE condition

Question

I have the following Cassandra table

cqlsh:mydb> describe table events;

CREATE TABLE mydb.events (
    id uuid PRIMARY KEY,
    country text,
    insert_timestamp timestamp
) WITH bloom_filter_fp_chance = 0.01
    AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}'
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy'}
    AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99.0PERCENTILE';
CREATE INDEX country_index ON mydb.events (country);
CREATE INDEX insert_timestamp_index ON mydb.events (insert_timestamp);

As you can see, index is already created on insert_timestamp column.

I had gone through https://stackoverflow.com/a/18698386/3238864

I though the following is the correct query

cqlsh:mydb> select * from events where insert_timestamp >= '2016-03-01 08:27:22+0000';
InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'insert_timestamp >= <value>'"

cqlsh:mydb> select * from events where insert_timestamp >= '2016-03-01 08:27:22+0000' ALLOW FILTERING;
InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'insert_timestamp >= <value>'"

But, query with country column as WHERE condition does work.

cqlsh:mydb> select * from events where country = 'my';

id                                   | country | insert_timestamp
--------------------------------------+---------+--------------------------
53167d6a-e125-46ff-bacf-f5b267de0258 |      my | 2016-03-01 08:27:22+0000

Any idea why query with timestamp as condition doesn't work? Is there anything wrong with my query syntax?

Possible duplicate of [Cassandra CQL range query rejected despite equality operator and secondary index](http://stackoverflow.com/questions/24894393/cassandra-cql-range-query-rejected-despite-equality-operator-and-secondary-index) — Cheok Yan Cheng, Mar 03 '16 at 03:10

score 9 · Answer 1 · answered Mar 01 '16 at 09:36

Any idea why query with timestamp as condition doesn't work? Is there anything wrong with my query syntax?

Native Cassandra secondary index is limited to = predicate. To enable inequality predicates you need to add ALLOW FILTERING but it will perform a full cluster scan :-(

If you can afford to wait for a couple of weeks, Cassandra 3.4 will be released with the new SASI secondary index which is much more efficient for range queries: https://github.com/apache/cassandra/blob/trunk/doc/SASI.md

This answer is so useful that my friend who couldn't upvote is asking me to upvote to show the gratitude :) — nizam.sp, Jan 09 '18 at 14:09

score 0 · Answer 2 · edited May 23 '17 at 12:25

Direct queries on secondary indices support only =, CONTAINS or CONTAINS KEY restrictions.

Secondary index queries allow you to restrict the returned results using the =, >, >=, <= and <, CONTAINS and CONTAINS KEY restrictions on non-indexed columns using filtering.

So your query will work once you add ALLOW FILTERING to it.

select * from events where insert_timestamp >= '2016-03-01 08:27:22+0000' ALLOW FILTERING;

The link that you have mentioned in your question has timestamp column as clustering key. Hence it is working there.

As per the comment RangeQuery on secondary index is not alllowed upto 2.2.x version

FYI: When Cassandra must perform a secondary index query, it will contact all the nodes to check the part of the secondary index located on each node. Hence it is considered as anti-pattern in cassandra to have index on high cardinality column like timestamp. You Should consider changing your data model to suit your queries.

I execute the `ALLOW FILTERING` version as yours. But I'm still get the same error. — Cheok Yan Cheng, Mar 01 '16 at 09:37

score 0 · Answer 3 · edited May 23 '17 at 12:25

Index in cassandra are quite different from index in relational DB. One of the difference is, range query in a cassandra index is not allowed at all. Usually, range query only works with clustering keys (it also could work with partition keys if ByteOrderPartitioner is used, but it is not common), meaning you have to design your columnfamilies carefully for your potential query patterns. There are already many discussions in StackOverflow for the same topic.

To understand when to use cassandra's index (it is designed for quite specific cases) and its limitations, this is a good post,

score 0 · Answer 4 · answered Dec 07 '17 at 00:29

Using cequel ORM

    now = DateTime.now
    today = DateTime.new(now.year, now.month, now.day, 0, 0, 0, now.zone) 
    tommorrow = today + (60 * 60 * 24);
    MyObject.allow_filtering!.where("done_date" => today..tommorrow).select( "*" )

Has worked for me.

How to perform query with cassandra's timestamp column as WHERE condition

4 Answers4

Linked