Cassandra timestamp column query

Question

I wanted to query the cassandra timestamp column such as this hour, last hour, yesterday.

I have the same queries executed in mysql like for yesterday data

Select * from table where time>=CURDATE() - INTERVAL 1 DAY AND time < CURDATE();

Answer 1

Cassandra 4.0 supports date/time arithmentic, but syntax a bit different. You can write something like this:

SELECT * FROM myTable WHERE date >= currentDate() - 2d;

But please take into account that performance will heavily dependent on the actual data model, so if you run this query without partition key, then it will scan the whole database, and most probably will timeout. I recommend to read about data modeling for Cassandra - although language looks like SQL, it's not SQL.

Answer 2

You are trying to broader your search in Cassandra, It needs multiple nodes before it shares the results. Most likely it will time out. You need to reconsider your data flow where you know absolutely about the partition key contains your date and need to fetch the data with the partition key.

    Select * from table where time='2022-25-01';
    Select * from table where time='2022-24-01';

You need to manage the results. As you are fetching your results based on the partition key.

You can consider bucketing for better performance.