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 =.

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';

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'