unable to coerce '2012/11/11' to a formatted date (long)

Question

I am new to Cassandra cql (cqlsh 4.1.1, Cassandra 2.0.8.39, CQL spec 3.1.1, Thrift protocol 19.39.0) - using the cql COPY command to a table from a CSV formatted file and I get the following error:
Bad Request: unable to coerce '2012/11/11' to a formatted date (long).
How do I change a column using cql so that it accepts the date from my CSV file?

Anamorphosis

Answer 1

as Brian said, there are CQL timestamp type to follow to get CQL query running. Sometimes it looks like quite weird indeed ! I've got the same issue few weeks ago with a date time insert like this one :

INSERT INTO my_table (id,lastvisitdate) VALUES (1682221,'2012-03-25 02:26:04');

I got this error : Bad Request: unable to coerce '2012-03-25 02:26:04' to a formatted date (long) ! mmmm... so bad as the date time seems to be correct !

After many tries and before going nuts, I've just added a Z at the end of the time, Z stands for Zulu time which is also UTC and GMT :

INSERT INTO my_table (id,lastvisitdate) VALUES (1682221,'2012-03-25 02:26:04Z');

Yessss ! It works ! So do not forget the timezone in your date time values, it could be helpful ! ;-)

Cockerham

Answer 2

There is not a direct way to do that from within CQLSH.

There are a certain set of string date formats which can be coerced. See the CQL Timestamp type documentation page for some examples like:

yyyy-mm-dd HH:mm
yyyy-mm-dd HH:mm:ss
yyyy-mm-dd HH:mmZ
yyyy-mm-dd HH:mm:ssZ
yyyy-mm-dd'T'HH:mm
yyyy-mm-dd'T'HH:mmZ
yyyy-mm-dd'T'HH:mm:ss
yyyy-mm-dd'T'HH:mm:ssZ
yyyy-mm-dd
yyyy-mm-ddZ

As a workaround you could modify your CSV file to adjust the date format, then import it. (In your case it may be as simple as "yyyy/mm/dd" -> "yyyy-mm-dd".)

Audwin