Cassandra time comparison

Question

As shown in the image request with the exact timestamp (2013-08-01 15:02:56), the result is not returned, although a row with this timestamp exists, but it returns the results with this string when prompted for

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

Is this common behavior in Kassandra?

Answer 1

Yes, this is the expected behavior.

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

When you insert your data, you add a millisecond value with a higher degree of detail than your "2013-08-01 15:02:56" (millisecond now value versus a few seconds and 0 milliseconds). The EQ statement will never match IF your inserted timestamp has 0 milliseconds.

It will work

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

So, when you request it through cqlsh, your datetime is converted to an integer (in milliseconds) that just differs from the value you originally entered. Your inserted value will be a few milliseconds AFTER "2013-08-01 15:02:56". You request EXACTLY "2013-08-01 15:02:56" (and 0 milliseconds). Using a GT or LT statement will match, an EQ statement will not.

Hope this helps!

Answer 2

Like the almighty, I think your problem is that the timestamp is stored with milliseconds> 0.

To see that the following query is being run:

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

Then check the last numbers, which are milliseconds.

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

So, you have two options:

• Delete milliseconds when saving data
• Request with ranges, something like ..

... give me the events after 15:02:56, but until 15:02:57:

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

Answer 3

Recently, I also faced the same problem, and here's how I solve it.

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