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.
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.