Unable to coerce to a formatted date - Cassandra timestamp type

3
votes

I have the values stored for timestamp type column in cassandra table in format of 2018-10-27 11:36:37.950000+0000 (GMT date). I get Unable to coerce '2018-10-27 11:36:37.950000+0000' to a formatted date (long) when I run below query to get data.

select create_date from test_table where create_date='2018-10-27 11:36:37.950000+0000' allow filtering;

How to get the query working if the data is already stored in the table (of format, 2018-10-27 11:36:37.950000+0000) and also perform range (>= or <=) operations on create_date column?

I tried with create_date='2018-10-27 11:36:37.95Z', create_date='2018-10-27 11:36:37.95' create_date='2018-10-27 11:36:37.95'too.

Is it possible to perform filtering on this kind of timestamp type data?

P.S. Using cqlsh to run query on cassandra table.

2
cassandracqlcassandra-3.0cqlshspring-data-cassandra

2 Answers

4
votes

In first case, the problem is that you specify timestamp with microseconds, while Cassandra operates with milliseconds - try to remove the three last digits - .950 instead of .950000 (see this document for details). The timestamps are stored inside Cassandra as 64-bit number, and then formatted when printing results using the format specified by datetimeformat options of cqlshrc (see doc). Dates without explicit timezone will require that default timezone is specified in cqlshrc.

Regarding your question about filtering the data - this query will work only for small amounts of data, and on bigger data sizes will most probably timeout, as it will need to scan all data in the cluster. Also, the data won't be sorted correctly, because sorting happens only inside single partition.

If you want to perform such queries, then maybe the Spark Cassandra Connector will be the better choice, as it can effectively select required data, and then you can perform sorting, etc. Although this will require much more resources.

I recommend to take DS220 course from DataStax Academy to understand how to model data for Cassandra.

-1
votes

This is works for me

var datetime = DateTime.UtcNow.ToString("yyyy-MM-dd HH:MM:ss");
                var query = $"SET  updatedat = '{datetime}' WHERE ...