Cassandra query timestamp column

Question

Using Cassandra 2.28, Drive 3, Sparks2. I have a timestamp column in Cassandra I need to query it by the date portion only. If I query by date: .where("TRAN_DATE= ?", "2012-01-21" : it does not bring any result. If I include the time portion it says Invalid Date. My data (as I can read in cqlsh) is: 2012-01-21 08:01:00+0000

param: "2012-01-21" > No error but no result

param: "2012-01-21 08:01:00" > Error : Invalid Date

param: "2012-01-21 08:01:00+0000" > Error : Invalid Date

SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy/mm/dd"); TRAN_DATE = DATE_FORMAT.parse("1/19/2012"); Have used the bulk loader/SSLoader to load the table

Data in table:

tran_date                | id
--------------------------+-------
 2012-01-14 08:01:00+0000 |    ABC
 2012-01-24 08:01:00+0000 |    ABC
 2012-01-23 08:01:00+0000 |    ALM
 2012-01-29 08:01:00+0000 |    ALM
 2012-01-13 08:01:00+0000 |    ATC
 2012-01-15 08:01:00+0000 |    ATI
 2012-01-18 08:01:00+0000 |    FKT
 2012-01-05 08:01:00+0000 |    NYC
 2012-01-11 08:01:00+0000 |    JDU
 2012-01-04 08:01:00+0000 |    LST

How do I solve this.

Thanks

Answer 1

If you insert data into timestamp column without providing timezone like this one :

INSERT INTO timestamp_test (tran_date , id ) VALUES ('2016-12-19','TMP')

Cassandra will choose coordinator timezone

If no time zone is specified, the time zone of the Cassandra coordinator node handing the write request is used. For accuracy, DataStax recommends specifying the time zone rather than relying on the time zone configured on the Cassandra nodes.

If you execute select with Datastax Driver, you Need To Convert the String date into java.util.Date and set the time zone of coordinator node, In my case it was GMT+6

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date date = dateFormat.parse("2012-01-21");
dateFormat.setTimeZone(TimeZone.getTimeZone("GMT+6")); //Change this time zone

Now You can query with

QueryBuilder.eq("TRAN_DATE", date)

Here is a complete demo :

try (Cluster cluster = Cluster.builder().addContactPoints("127.0.0.1").withCredentials("username", "password").build(); Session session = cluster.connect("tests")) {
    session.execute("INSERT INTO test_trans(tran_date , id ) VALUES ('2016-12-19','TMP')");

    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    dateFormat.setTimeZone(TimeZone.getTimeZone("GMT+6"));
    Date date = dateFormat.parse("2016-12-19");
    System.out.println(date);
    for (Row row : session.execute(QueryBuilder.select().from("timestamp_test").where(QueryBuilder.eq("tran_date", date)))) {
        System.out.println(row);
    }
}

Source : https://docs.datastax.com/en/cql/3.0/cql/cql_reference/timestamp_type_r.html