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?

August

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!

Db

thanks @ominbear that field is a timestamp not a TimeUUID. I had also tried the query with time zones but results were same. Are there any rules according to which cassandra behaviour like this. Also I am just curious, why use timestamp at all when there is TimeUUID? – August

Answer 2

Like omnibear said I think your problem is that the timestamp is stored with milliseconds >0.

To see that launch the next query:

select  blobAsBigint(timestampAsBlob(timestamps)) where timestamps > '2013-08-01 15:02:56';

Then check the last numbers which are the milliseconds.

If the last numbers are >0 (which is what I expect) then this explains why your = assertion is false.

So you have two options:

1. Remove milliseconds when you store the data
2. Query with ranges, something like..

...give me events after 15:02:56 but before 15:02:57:

where timestamps >= '2013-08-01 15:02:56' and timestamps < '2013-08-01 15:02:57'

Kyl

Your answer deserves a lot more credit, since it's the only real answer I could find after spending a lot of time searching for it. – Cacique

Answer 3

I recently also faced the same problem and this is how I solve it.

Calculate long value using blobAsBigint(timestampAsBlob(timestamps)) and then use it in your where clause with '=' operator.

Olds