Retrieve rows from last 24 hours

Question

I have a table with the following (with other fields removed)

CREATE TABLE if NOT EXISTS request_audit (
   user_id text,
   request_body text,
   lookup_timestamp TIMESTAMP
   PRIMARY KEY ((user_id), lookup_timestamp)
) WITH CLUSTERING ORDER BY ( lookup_timestamp DESC);

I create a record with the following

INSERT INTO request_audit (user_id, lookup_timestamp, request_body) VALUES (?, ?, toTimestamp(now()))

I am trying to retrieve all rows within the last 24 hours, but I am having trouble with the timestamp, I have tried

SELECT * from request_audit WHERE user_id = '1234' AND lookup_timestamp > toTimestamp(now() - "1 day" ) and various other ways of trying to take a day away from the query.

Answer 1

Cassandra has a very limited date operation support. What you need is a custom function to do date math calculation.

Inspired from here. How to get Last 6 Month data comparing with timestamp column using cassandra query?

you can write a UDF (user defined function) to date operation.

CREATE FUNCTION dateAdd(date timestamp, day int)     
CALLED ON NULL INPUT     
RETURNS timestamp     
LANGUAGE java     
AS 
$$java.util.Calendar c = java.util.Calendar.getInstance();
c.setTime(date);
c.add(java.util.Calendar.DAY_OF_MONTH, day);
return c.getTime();$$ ;

remember that you would have to enable UDF in config. Cassandra.yml. Hope that is possible.

enable_user_defined_functions: true

once done this query works perfectly.

SELECT * from request_audit WHERE user_id = '1234' AND lookup_timestamp > dateAdd(dateof(now()), -1)

Answer 2

You couldn't do it directly from CQL, as it doesn't support this kind of expressions. If you're running this query from cqlsh, then you can try to substitute the desired date with something like this:

date --date='-1 day' '+%F %T%z'

and execute this query.

If you're invoking this from your program, just use corresponding date/time library to get date corresponding -1 day, but this depends on the language that you're using.