Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

bharat.asnani_190772 avatar image
bharat.asnani_190772 asked Erick Ramirez edited

Exception: InvalidRequest: Error from server: code=2200 [Invalid query] message="Unable to coerce '2020-08-18 14:06:12.000000+0000' to a formatted date (long)"

Hi,


I am using Cassandra database with CQL version 3.4.5


When I search for a timestamp column then I get result in milliseconds with more precision.


For eg 2020-08-18 14:06:12.000000+0000


Now, when I use this timestamp for filter condition then it is providing following error:


InvalidRequest: Error from server: code=2200 [Invalid query] message="Unable to coerce '2020-08-18 14:06:12.000000+0000' to a formatted date (long)"


Sample query:

SELECT * FROM "Account" WHERE "appId" = 'CAMR-IGAManagedApplication' AND "nativeType" = 'Account' AND "tenantId" = 'Default' AND "ownerId" = 'PMATHUR' AND "updateTime" < '2020-08-18 14:06:12.000000+0000' ORDER BY "updateTime" DESC LIMIT 10;


Please let me know how can we resolve this error.


dse
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

bettina.swynnerton avatar image
bettina.swynnerton answered

Hi @bharat.asnani_190772,

Cassandra stores millisecond-precision timestamps.

However, cqlsh displays a microsecond format: yyyy-mm-dd'T'HH:mm:ss.ffffffZ

You will need to remove the last three 0s when querying with the timestamp.

Here is a quick example:

cqlsh> select * from community_7788.comments where created_at = '2020-08-18 14:40:26.950+0000' allow filtering;

 id    | created_at
-------+---------------------------------
 test1 | 2020-08-18 14:40:26.950000+0000

I hope this helps.

Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.