4

I am new in Cassandra. I have a Cassandra( V: 3.11 ) table (data). It is having a column timeStampCol of timestamp type and I am inserting a value in it.

insert into data (timeStampCol) values('2017-05-02 17:33:03');

While accessing the data from table

select * from data;

I got result like -

@ Row 1
----------+------------------------------------
 timeStampCol | 2017-05-02 08:33:03.000000+0000

Inserted value and retrieved values are different for time. Reason might be timezone, how can I get it correct ?

DEV
  • 2,106
  • 3
  • 25
  • 40

2 Answers2

6

Your selected timestamp value is correct, it's just showing in different timezone.

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

insert into data (timeStampCol) values('2017-05-02 17:33:03');

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.

You Need To Convert the String date into java.util.Date and set the timezone 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

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

Ashraful Islam
  • 12,470
  • 3
  • 32
  • 53
  • 4
    Ok got it. if I use Z [ insert into data (timeStampCol) values('2017-05-02 17:33:03Z') ] its working fine without any conversion. – DEV Sep 25 '17 at 05:25
0

Cassandra will assume incoming data in the timezone it is set up. For example if you have Cassandra set up in IST, and even though incoming data is UTC, Cassandra will convert it back to UTC, considering data to be in IST.

You might have to set Cassandra coordinator timezone in code or calculate the time difference between the incoming data timezone and Cassandra timezone and add/subtract that from incoming data before it is written to Cassandra. This way you will have the exact timestamps written to Cassandra.

sam
  • 21
  • 5