Search code examples
javaormcassandratimestampdatastax

Cassandra CQL does not return anything within a timestamp range

I'm working with Cassandra using their Java API for interacting with it. I have entity classes that my mapper object uses to peform CRUD operations. I needed a custom query to retrieve all my Purchase objects from a specific timespan. However when I run my query below, I never get anything in return. Fun fact though, after more extensive testing, the query does work on my colleague's Mac running Cassandra 3.11.2. My machine is running Windows and Cassandra 3.9.0.

String query = String.format("SELECT * FROM purchase WHERE timestamp >=  %s AND timestamp <= %s ALLOW FILTERING;", startTimestamp, endTimestamp);
                purchases = session.execute(query);

I have also tried using the IN operation, however I can't find any information on what it actually does and though it doesn't throw any exception, it won't find any purchases at all:

String query = String.format("SELECT * FROM purchase WHERE timestamp IN (%s , %s);", startTimestamp, endTimestamp);
Solution

  • I finally managed to solve. Turns out, if you store something with the Cassandra timestamp data type, you cannot select the item with a long anymore. You have to use a date format on a string. Solved it like this:

       startDate = simpleDateFormat.format(Long.valueOf(startTimestamp));
   endDate = simpleDateFormat.format(Long.valueOf(endTimestamp));

   query = String.format("SELECT * FROM purchase WHERE timestamp >= '%s' AND timestamp <= '%s' ALLOW FILTERING;", startDate, endDate);