Cassnadra - Update/Delete based on timestamp datatype

user_4816131 0
Viewed 33

I have below table structure which houses failed records.

CREATE TABLE if not exists dummy_plan (
id uuid,
payload varchar,
status varchar,
bucket text,
create_date timestamp,
modified_date timestamp,
primary key ((bucket), create_date, id))
WITH CLUSTERING ORDER BY (create_date ASC) 
         AND COMPACTION = {'class': 'TimeWindowCompactionStrategy', 
                       'compaction_window_unit': 'DAYS', 
                       'compaction_window_size': 1};

My table looks like below

| id | payload | status | bucket     | create_date              | modified_date |
| abc| text1   | Start  | 2021-02-15 | 2021-02-15 08:07:50+0000 |               |

Table and records are created and inserted successfully. However after processing, we want to update (if failed) and delete (if successful) record based on Id. But am facing problem with timestamp where I tried giving same value but it still doesn't deletes/updates. Seems Cassandra doesn't works with EQ with timestamp. Please guide.

Thank you in advance.

cassandra timestamp
1 Answers

Cassandra works just fine with the timestamp columns - you can use equality operation on that. But you need to make sure that you include milliseconds into the value, otherwise it won't match:

cqlsh> insert into test.dummy_service_plan_contract (id, create_date, bucket)
   values (1, '2021-02-15T11:00:00.123Z', '123');
cqlsh> select * from test.dummy_service_plan_contract;
 bucket | create_date                     | id | modified_date | payload | status
--------+---------------------------------+----+---------------+---------+--------
    123 | 2021-02-15 11:00:00.123000+0000 |  1 |          null |    null |   null

(1 rows)
cqlsh> delete from test.dummy_service_plan_contract where bucket = '123' and 
  id = 1 and create_date = '2021-02-15T11:00:00Z';
cqlsh> select * from test.dummy_service_plan_contract;
 bucket | create_date                     | id | modified_date | payload | status
--------+---------------------------------+----+---------------+---------+--------
    123 | 2021-02-15 11:00:00.123000+0000 |  1 |          null |    null |   null

(1 rows)
cqlsh> delete from test.dummy_service_plan_contract where bucket = '123' and 
  id = 1 and create_date = '2021-02-15T11:00:00.123Z';
cqlsh> select * from test.dummy_service_plan_contract;
 bucket | create_date | id | modified_date | payload | status
--------+-------------+----+---------------+---------+--------

(0 rows)

If you don't see the milliseconds in your output in the cqlsh, then you need to configure datetimeformat setting in the .cqlshrc

Alex_Ott_186270
Related