Cassandra query with equals operator on timestamp column not working

Question

I have created a table as follows in Cassandra:

CREATE TABLE sp.status(
ams text,
load_start_time timestamp,
file_path text,
file_timestamp timestamp,
host text,
load_end_time timestamp,
records_ignored int,
records_imported int,
records_in_file int,
status text,
PRIMARY KEY (ams, load_start_time)
) WITH CLUSTERING ORDER BY (load_start_time DESC)

I want to select a row from a specific date. When I use the > operator everything works as expected. But if I use the = operator I get no data back.

SELECT * FROM sp.status WHERE ams = 'RRG' AND load_start_time='2016-01-20 10:10:27' allow filtering;

There is in the database a row with that value for load_start_time and it is returned if in the above query I replace = with > for load_start_time.

Can someone explain why would this be the case ?

I actually need this for a DELETE statement and there I cannot use range operators, only =.

Okun

Answer 1

Did you INSERT the rows using dateOf(now()) or with another method containing millisecond precision? TIMESTAMPs will store milliseconds, but will not display it. Check this example:

CREATE TABLE stackoverflow.timestamptest (
    userid text,
    activetime timestamp,
    value text,
    PRIMARY KEY (userid, activetime)
) WITH CLUSTERING ORDER BY (activetime ASC)


INSERT INTO timestamptest (userid,activetime,value) VALUES ('d',dateof(now()),'value1');
INSERT INTO timestamptest (userid,activetime,value) VALUES ('d','2016-01-22 14:57:54+0000','value2');
SELECT userid, value, activetime, blobAsBigint(timestampAsBlob(activetime)) 
  FROM timestamptest WHERE userid='d';


 userid | value  | activetime               | system.blobasbigint(system.timestampasblob(activetime))
--------+--------+--------------------------+---------------------------------------------------------
      d | value2 | 2016-01-22 14:57:54+0000 |                           1453474674000
      d | value1 | 2016-01-22 14:57:54+0000 |                           1453474674912

(2 rows)

By wrapping activetime in the blobAsBigint(timestampAsBlob()) nested functions, I can see that there are milliseconds present for the time entered via dateOf(now()) (912), but not for the time entered as text. As you are finding out, this can become problematic when needing to accomplish an in-place update or delete.

Try using blobAsBigint(timestampAsBlob()) to see if the milliseconds are there, and then add those milliseconds to the timestamp value for your DELETE operation. ex:

DELETE FROM timestamptest WHERE userid='d' AND activetime='2016-01-22 14:57:54.912+0000';

Ensure

You are right. I also thought milliseconds were the problem at start. I am using Dbeaver to access Cassandra. I inspected my timestamp field and it did not show any sign that it stores milliseconds. But your method showed that it does as you said. I added the ms and it worked. Thanks – Okun

@Okun Awesome! Glad I could help. – Ensure

Your answer totally saved my day in a case where 'SELECT * FROM ... LIMIT N' would show results, but asking for a specific entry returned nothing. – Preconize

Answer 2

I bet it's caused by different time zones. Try to include your zone in the statement like ...AND load_start_time='2016-01-20 10:10:27.000+0200'

Examination

I thought of this but i tested with the > operator on the second level so I know exactly what date is correct to input – Okun

Answer 3

Regarding the "unable to coerce '2016-04-06 13:06:11.534000' to a formatted date (long)" error, although, the timestamp column actually stores milliseconds in all versions, there seem to be some differences in how you can query based on the version:

Cassandra <=2.1 does not seem to support milliseconds in query: yyyy-mm-dd'T'HH:mm:ssZ https://docs.datastax.com/en/cql/3.1/cql/cql_reference/timestamp_type_r.html

Whereas >=3.0 supports it: yyyy-mm-dd'T'HH:mm:ss.ffffffZ https://docs.datastax.com/en/cql/3.3/cql/cql_reference/timestamp_type_r.html

I verified being able to select/insert in a newer cluster but not in old cluster using my IDE connected on 9160 Thrift port, have not yet tried on cqlsh:

INSERT INTO "sp.status"("ams", "load_start_time")
    VALUES('RRG', '2018-05-01T16:57:18.123+0200')
;

-- same with select, works on new cluster but not old
SELECT * FROM sp.status WHERE ams = 'RRG' AND load_start_time='2018-05-01T16:57:18.123+0200'
;

The driver seems to be able to map a java date and store milliseconds in both old and new cluster though.

=Cassandra 2.1 cqlsh uses native binary protocol (9042), previous versions use thrift (9160) although this should not change.

Luxurious