How to configure CQL timestamp to %d-%m-%Y %H:%M:%S%z

I use Cassandra on Windows 10 Pro. I have created the following table schema:

CREATE TABLE testee (
               serialno int,
               changeDate timestamp,
               value text,
               devicename text,
               PRIMARY KEY ((serialno, changeDate), changeDate)
) WITH CLUSTERING ORDER BY (changeID DESC);

The given date format (timestamp) is: %d-% m-%-Y% H:%M:%S%z

The Cassandra default timestamp is: %Y-% m-%-d% H:%M:%S%z

For the conversion of the date format I have followed the instructions of Datastax:

https://docs.datastax.com/en/cql/3.3/cql/cql_reference/cqlshUsingCqlshrc.html

Now the cqlshrc file is under:

C:\Users\tlq\ .cassandra\cqlshrc

According to this solved question Cassandra Timestamp data type, 'time_format' must be inserted in the [ui] section of the cqlshrc file. I did this:

[ui]

;; Used for displaying timestamps (and reading them with COPY)
datetimeformat = %d-%m-%Y %H:%M:%S%z

;;Used for used displaying timestamps generell

time_format = %d-%m-%Y %H:%M:%S%z

By using the insert command:

INSERT INTO foo(serialno, changedate, devicename) VALUES(2,'07-12-2011 13:56:20','fooname',34);

The following message still appears

InvalidRequest: Error from server: code=2200 [Invalid query] message="Unable to >coerce '07-12-2014 13:56:20' to a formatted date (long)"

Solution

The options that you specify in the cqlshrc file is for diplaying of data, not for input strings. Cassandra accepts data in following formats, described in documentation:

yyyy-mm-dd[(T| )HH:MM:SS[.fff]][(+|-)NNNN]

So you need to specify date in correct order: year-month-day...

P.S. When you don't specify the timezone, it's taken from client's configuration - you need to specify your default timezone by adding the timezone parameter to cqlshrc file (same [ui] section), like this:

timezone = Etc/UTC

See also documentation that talks about installing optional packages like pytz.