Timestamp comparison in cassandra

Question

As shown in the picture querying with exact timestamp(2013-08-01 15:02:56) is not returning any result though a row with that timestamp exists but it returns results with that row when queried for

timestamps > '2013-08-01 15:02:56'

Is this normal behavior in Cassandra?

Answer 1

Yes that is expected behavior.

According to the cassandra docs and here here, cassandra is storing timestamps as "milliseconds since the standard base time known as the epoch".

When you insert your data, you insert a millisecond value with higher granularity than your "2013-08-01 15:02:56" (milliseconds value of "now" vs just seconds and 0 milliseconds). A EQ operator will never match UNLESS your inserted timestamp has 0 milliseconds.

This will work

SELECT * FROM myTable WHERE timestamps >= '2013-08-01 15:02:56'
AND timestamps < '2013-08-01 15:02:57' 

So, when you query it through cqlsh your datetime is translated into an integer (of milliseconds) that is just different from the value you inserted originally. Your inserted value will be some milliseconds AFTER "2013-08-01 15:02:56". You query for EXACTLY "2013-08-01 15:02:56" (and 0 milliseconds). Using a GT or LT operator will match, an EQ operator will not.

Hope that helps!