CODEWITHSUNDEEP
datetimecassandratimestampcqlcqlsh
Victor A Chavez
Victor A Chavez

Reputation: 191

how convert datetime to timestamp on cqlsh

I'm new in cassandra and specially to the cql syntax. But I have a created a column that is timestamp which gets me datetime as this '2018-05-18 03:08:58.246000+0000' but where I filter by created I get this error 

InvalidRequest: Error froim server: code=2200 [Invalid query] message="Unable to coerce '2018-05-18 03:08:58.246000+0000' to formatted date (long)"

Which lead me to think that I either need to convert the datetime into a tick or do some sort of casting.

how convert datetime to timestamp on cqlsh in a where clause?

Upvotes: 1

Views: 1801

Answers (1)

Pedro Vidigal
Pedro Vidigal

Reputation: 422

Although Cassandra stores timestamp fractions using the .ffffff format defined by the ISO 8601 standard as you mentioned. 

cqlsh:test_keyspace> select * from timestamp_table ;

timestamp                       | other_field
---------------------------------+---------------
2018-05-18 03:08:58.246000+0000 | Other content
2018-05-18 03:08:58.000000+0000 | Other content

When interacting with the database (ie. INSERT, SELECT, ...) you need to use the .fff format like so:

cqlsh:test_keyspace> select * from timestamp_table WHERE timestamp='2018-05-18 03:08:58.123+0000';

timestamp                       | other_field
---------------------------------+---------------
2018-05-18 03:08:58.123000+0000 | Other content

Otherwise you will get the error you mentioned.

Error when Reading

 cqlsh:test_keyspace> select * from timestamp_table WHERE timestamp='2018-05-18 03:08:58.123000+0000';
InvalidRequest: Error from server: code=2200 [Invalid query] message="Unable to coerce '2018-05-18 03:08:58.123000+0000' to a formatted date (long)"

Error when writing

cqlsh:test_keyspace> INSERT INTO timestamp_table (timestamp , other_field ) VALUES ( '2018-05-18 03:08:58.123456+0000', 'Other content');
InvalidRequest: Error from server: code=2200 [Invalid query] message="Unable to coerce '2018-05-18 03:08:58.123456+0000' to a formatted date (long)"

Upvotes: 4

Related Questions