Cassandra CQL select query not returning records which have timestamp as clusterkey

Question

Cassandra CQL: Table created with composite key and cluster key. When I try to execute select * from partition key then I able to retrieve all data and it works for relational operator ( < or > ) too . But when I queried for particular cluster key using equal-to(=) operator with proper value it returns 0 rows.

Table:

CREATE TABLE entity_data (
received_date timestamp,
entity text,
received_time timestamp,
node int,
primary key ((received_date ,entity),received_time));

Data ( select * from entity):

received_date              | entity | received_time            | node_id
2014-09-24 00:00:00+0400   |     NA | 2014-09-24 18:56:55+0400 |       0  | 

with Conditional Query: -- here it does not work

select * from entity_data 
where received_date = '2014-09-24 00:00:00+0400' and entity = 'NA' 
and received_time='2014-09-24 18:56:55+0400';
(0 rows)

-- it returns 0 rows.

Davedaveda

Answer 1

I see what is going on. You are using now() to generate a time-UUID. But when you convert that to a timestamp using dateOf() you are truncating the milliseconds off of it. Therefore querying for a received_time equal to 2014-09-24 18:56:55+0400 will yield nothing, as the timestamp type is still stored with the milliseconds (you just can't see it due to your dateOf()).

The best way to go about this, is to store your times as timeuuids (NOTE: I left received_date as a timestamp just for purposes of the example). Then use the dateOf when you SELECT, and use the minTimeuuid() function for your WHERE clause:

CREATE TABLE entity_data2 (
    received_date timestamp,
    entity text,
    received_time timeuuid,
    node int,
PRIMARY KEY ((received_date, entity), received_time));

INSERT INTO entity_data2 (received_date, entity, received_time , node) 
VALUES ('2014-09-24 00:00:00+0400','NA',now(),0);

aploetz@cqlsh:stackoverflow> SELECT * FROM entity_data2 
    WHERE received_date = '2014-09-24 00:00:00+0400' AND entity = 'NA'  
    AND received_time>minTimeuuid('2014-10-08 08:13:53-0500') 
    AND received_time<minTimeuuid('2014-10-08 08:13:54-0500');

 received_date            | entity | received_time                        | node
--------------------------+--------+--------------------------------------+------
 2014-09-23 15:00:00-0500 |     NA | f3b548b0-4eec-11e4-9d05-7991a041665c |    0

(1 rows)

aploetz@cqlsh:stackoverflow> SELECT received_date, entity, dateof(received_time), node 
    FROM entity_data2 WHERE received_date = '2014-09-24 00:00:00+0400' AND entity = 'NA'
    AND received_time>minTimeuuid('2014-10-08 08:13:53-0500') 
    AND received_time<minTimeuuid('2014-10-08 08:13:54-0500');

 received_date            | entity | dateof(received_time)    | node
--------------------------+--------+--------------------------+------
 2014-09-23 15:00:00-0500 |     NA | 2014-10-08 08:13:53-0500 |    0

(1 rows)

Basically the dateOf() function was designed to be used for querying data, not storing it. Here is a blog posting that describes (in more detail) how to make this work:

Time series based queries in Cassandra 1.2+ and CQL3

Kilimanjaro

Thanks for your immediate reply.. But it is not working: – Davedaveda

for insert query I was using dateof(now()) because of this select operator not returning any value. Please tell me how to overcome this. – Davedaveda

@Davedaveda can you edit your question with your INSERT query, double-check your table schema to make sure it resembles what is in the question, and also provide your version number? It will be difficult to help you any further without that information. – Kilimanjaro

INSERT INTO entity_data (received_date, entity, received_time , node) VALUES ('2014-09-24 00:00:00+0400','NA',dateOf(now()),0); – Davedaveda

@Davedaveda Edit made. – Kilimanjaro

[cqlsh 4.1.1 | Cassandra 2.0.8.39 | CQL spec 3.1.1 | Thrift protocol19.39.0 CREATE TABLE entity_data2 ( received_date timestamp, entity text, received_time timeuuid, node int, PRIMARY KEY ((received_date, entity), received_time)); – Davedaveda