CODEWITHSUNDEEP
cassandratimestampcql
Tobe
Tobe

Reputation: 23

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)"

Upvotes: 2

Views: 2976

Answers (1)

Alex Ott
Alex Ott

Reputation: 87069

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.

Upvotes: 3

Related Questions