I have a requirement which requires to fetch all records older than N days. As Cassandra doesn't support the GETDATE() which is available in SQL and helps to achieve the same task, what could be the possible solution in CQL? The SQL query is given below for reference,
Select * from table where created_date < GETDATE() - 'NDays'
There is a native CQL function CURRENTDATE() which returns the system time in date format but there are no operators in the CQL grammar which allows that level of manipulation.
Our general recommendation is to perform the calculation within your application then feed the result to your CQL query.
As a side note, retrieving multiple partitions based on a range filter is a bad access pattern for Cassandra.
Cassandra is designed for high-velocity reads at internet scale, particularly retrieving single partitions really fast. If you need to retrieve multiple records in a query, there's a good chance that (a) you have an analytics use case (instead of OLTP), (b) you got your data model wrong, or (c) both. Cheers!