24

I am trying to UPDATE an existing item in my cassandra DB using cqlsh:

$ > UPDATE allEvents SET "isLastEvent" = True WHERE "websiteId" = 'sd-8231'
AND "purchaser" = False
AND "currentTime" = '2016-04-06 13:06:11.534000';

And I got this:

InvalidRequest: code=2200 [Invalid query] message="Unable to coerce '2016-04-06 13:06:11.534000' to a formatted date (long)"

In case it can help:

$ > show version
[cqlsh 5.0.1 | Cassandra 3.0.2 | CQL spec 3.3.1 | Native protocol v4]
apesa
  • 12,163
  • 6
  • 38
  • 43
farhawa
  • 10,120
  • 16
  • 49
  • 91
  • Do you need the extra precision? Cassandra's timestamp type will only support milliseconds. If you need a higher precision you could use the timeuuid type (UUIDv1) which is accurate to 100 nanosecond intervals. – fromanator Apr 06 '16 at 20:08

2 Answers2

28

That's because Cassandra timestamp types only support milliseconds. Your currentTime has too much precision. Trim off the last three zeros, and that should work:

UPDATE allEvents SET "isLastEvent" = True 
WHERE "websiteId" = 'sd-8231'
      AND "purchaser" = False 
      AND "currentTime" = '2016-04-06 13:06:11.534';
Aaron
  • 55,518
  • 11
  • 116
  • 132
  • Thanks for your response, but why this doesn't matter when I inserted it? – farhawa Apr 06 '16 at 15:05
  • 2
    @farhawa I am guessing that whatever client you're using to insert data into Cassandra is treating insert differently than updates and silently removing the extra precision. – fromanator Apr 06 '16 at 20:05
4

There is a reason for the "unable to coerce '2016-04-06 13:06:11.534000' to a formatted date (long)" error, although, the timestamp column actually stores milliseconds in all versions, there seem to be some differences in how you can query based on the version:

Cassandra <=2.1 does not seem to support milliseconds in query: yyyy-mm-dd'T'HH:mm:ssZ https://docs.datastax.com/en/cql/3.1/cql/cql_reference/timestamp_type_r.html

Whereas >=3.0 supports it: yyyy-mm-dd'T'HH:mm:ss.ffffffZ https://docs.datastax.com/en/cql/3.3/cql/cql_reference/timestamp_type_r.html

I verified being able to select/insert in a newer cluster but not in old cluster using my IDE connected on 9160 Thrift port, have not yet tried on cqlsh:

INSERT INTO "sp.status"("ams", "load_start_time")
    VALUES('RRG', '2018-05-01T16:57:18.123+0200')
;

-- same with select, works on new cluster but not old
SELECT * FROM sp.status WHERE ams = 'RRG' AND load_start_time='2018-05-01T16:57:18.123+0200'
;

The driver seems to be able to map a java date and store milliseconds in both old and new cluster though.

=Cassandra 2.1 cqlsh uses native binary protocol (9042), previous versions use thrift (9160) although this should not change.

kisna
  • 2,869
  • 1
  • 25
  • 30