Apache Cassandra COPY FROM, datetime entered incorrectly

Question

I can see a strange issue with COPY FROM command in Cassandra with datetime values.

My timezone and my server's timezone is same. IST (GMT+5:30)

First i tried inserting a value with INSERT query.

INSERT INTO activity (home_id, datetime, event, code_used) VALUES ('H01474777', '2014-05-21 07:32:16', 'alarm set', '5599');

It gave me the below row.

 home_id   | datetime                        | code_used | event
-----------+---------------------------------+-----------+-----------
 H01474777 | 2014-05-21 02:02:16.000000+0000 |      5599 | alarm set

Here Cassandra is showing the time value in GMT by removing +5:30

But when i tried to insert the below via the COPY FROM command and you can see that it added +5:30 when showing the GMT value, its like when adding the row it added 11 hours to the time. See file, query and output below respectively.

home_id|datetime|event|code_used
H02257222|2014-05-21 05:29:47|alarm set|1566
H01474777|2014-05-21 07:32:16|alarm set|5599

Query:

COPY activity (home_id, datetime, event, code_used) FROM '/home/cass/events.csv' WITH HEADER = TRUE AND DELIMITER = '|';

Result:

 home_id   | datetime                        | code_used | event
-----------+---------------------------------+-----------+-----------
 H01474777 | 2014-05-21 13:02:16.000000+0000 |      5599 | alarm set
 H01474777 | 2014-05-21 02:02:16.000000+0000 |      5599 | alarm set --Old row from insert query.
 H02257222 | 2014-05-21 10:59:47.000000+0000 |      1566 | alarm set

Here the first 2 rows are same data and the first 2 columns of the table are primary key but still another row has been created where as there should have been 2 rows only.

Nama Nama · Accepted Answer · 2020-02-14T08:34:04

I was able to replicate the scenario you have mentioned.

My server timezone EST .

I ran the insert that you provided and used the file you provided to load the data with copy command

INSERT INTO activity (home_id, datetime, event, code_used) VALUES ('H01474777', '2014-05-21 07:32:16', 'alarm set', 5599);

home_id   | datetime                        | code_used | event
-----------+---------------------------------+-----------+-----------
H01474777 | 2014-05-21 07:32:16.000000+0000 |      5599 | alarm set

COPY activity (home_id, datetime, event, code_used) FROM 'temp.csv' WITH HEADER = TRUE AND DELIMITER = '|'

 home_id   | datetime                        | code_used | event
-----------+---------------------------------+-----------+-----------
 H01474777 | 2014-05-21 02:32:16.000000+0000 |      5599 | alarm set
 H01474777 | 2014-05-21 07:32:16.000000+0000 |      5599 | alarm set
 H02257222 | 2014-05-21 00:29:47.000000+0000 |      1566 | alarm set

In the file you did not mention any timezone for datetime column. so it considering local zone as its zone. In my case it is EST so -0500 and your case it is IST so +0530

your file will be like

home_id|datetime|event|code_used
H02257222|2014-05-21 05:29:47.000+0530|alarm set|1566
H01474777|2014-05-21 07:32:16.000+0530|alarm set|5599

If you modify you csv file as below. Then you will only see one row for H01474777.

home_id|datetime|event|code_used
H02257222|2014-05-21 05:29:47.000+0000|alarm set|1566
H01474777|2014-05-21 07:32:16.000+0000|alarm set|5599

Hope this helps! Let me know if you have any questions.

Apache Cassandra COPY FROM, datetime entered incorrectly

1 Answers