Unable to force '2012/11/11' to formatted date (long)
I'm 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 COPY command to CQL table from a file in CSV format, and I get the following error: Bad Request: unable to coerce '2012/11/11' to a formatted date (long)
.
How do I change the column with cql
so that it takes the date from my file CSV
?
source to share
as Brian said, there is a CQL timestamp type to execute a CQL query. It looks pretty weird sometimes! I have the same problem a few weeks ago with a date time input like this one:
INSERT INTO my_table (id,lastvisitdate) VALUES (1682221,'2012-03-25 02:26:04');
I got this error: Invalid request: Failed to force '2012-03-25 02:26:04' to formatted date (long) ! mmmm ... so bad that the date time seems right!
After many tries and before you go crazy, I 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 don't forget the timezone in your date values, it might be helpful !; -)
source to share
There is no direct way to do this from CQLSH.
There is a specific set of string date formats that you can enforce. See the CQL Timestamp type documentation page for some examples, for example:
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 can modify your CSV file to customize the date format and then import it. (In your case, this might be as simple as "yyyy / mm / dd" → "yyyy-mm-dd".)
source to share