Dealing with Cassandra Timestamp

Question

Recently I have started working on cassandra and I have some issues dealing with cassandra timestamp using cql and Java.

Below is my sample cassandra table schema.

CREATE TABLE emp (
    empid int,
    create_date timestamp,
    deptid int,
    PRIMARY KEY (empid, create_date)
)

Here are my questions below:

1) Actually I need only date(I am not worried about time), In my table schema I have used timestamp datatype, Is there any datatype like date to store only date instead timestamp.

2) If there is no alternative to timestamp datatype below is how I am inserting record into table manually

Insert into emp(empId, deptId, create_date) values(1,2,'2016-03-15 00:00:00+0000');

When I am trying to query to retrieve record using cql as below

select * from emp where create_date='2016-03-15' and empid=1;

It's not returning any record so I have use below query

 select * from emp where create_date='2016-03-15 00:00:00+0000' and empid=1;

above query returned record, So how to query so that I need to get records only with date.

3) I am using datastax 2.1 JAVA API to communicate to cassandra, Below is my code how I am retrieving timestamp column from Java.

row.getDate("create_date")

getDate method is returning java.util.date (later versions returning com.datastax.driver.core.LocalDate)

When I try to get date from code its returning one day less suppose if record in my table is 2016-03-15 00:00:00+0000, but from my code it shows something like 2016-03-14 EDT (one day less is this because of timezone issue), If its timezone issue should I set timezone in my Java code while retrieving

4) If I want to query and get record from my Java code based on empId and create_date how to make exact pattern like 2016-03-15 00:00:00+0000 in Java code, As it is not retrieving if I provide only date in where condition or is there any better way in retrieving using Java code.

Solution

Getting back to your example:

Insert into emp(empId, deptId, create_date) values(1,2,'2016-03-15 00:00:00+0000');

The statement above will create a timestamp value for 2016-03-15 00:00 UTC.

select * from emp where create_date='2016-03-15' and empid=1;

The select will match against a timestamp created from the given date in your local timezone. What you have to do here is to query specifically by UTC, using create_date='2016-03-15Z'.

As you're also having timezone related issues in Java the easiest option would be to simply use a string representation of the date.

Comments

[Shabarinath Volam]

Thanks @stefan podkowinski for your time, I have some questions 1) As you have suggested "simply use a string representation of the date." does it mean I need to use string datatime instead of timestamp in table schema 2) As you have suggested querying using "create_date='2016-03-15Z' " is working perfectly using cqlsh but how to do this using java (some how I have managed to get correct date by using joda datetime and setting timezone as UTC)