Cassandra cqlsh - how to show microseconds/milliseconds for timestamp columns?

Question

I'm inserting into a Cassandra table with timestamp columns. The data I have comes with microsecond precision, so the time data string looks like this:

2015-02-16T18:00:03.234+00:00

However, in cqlsh when I run a select query the microsecond data is not shown, I can only see time down to second precision. The 234 microseconds data is not shown.

I guess I have two questions:

1) Does Cassandra capture microseconds with timestamp data type? My guess is yes?

2) How can I see that with cqlsh to verify?

Table definition:

create table data (   datetime timestamp,   id text,   type text,   data text,   primary key (id, type, datetime) )  with compaction = {'class' : 'DateTieredCompactionStrategy'}; 

Insert query ran with Java PreparedStatment:

insert into data (datetime, id, type, data) values(?, ?, ?, ?); 

Select query was simply:

select * from data; 

Answer 1

In an effort to answer your questions, I did a little digging on this one.

1. Does Cassandra capture microseconds with timestamp data type?

Microseconds no, milliseconds yes. If I create your table, insert a row, and try to query it by the truncated time, it doesn't work:

aploetz@cqlsh:stackoverflow> INSERT INTO data (datetime, id, type, data)  VALUES ('2015-02-16T18:00:03.234+00:00','B26354','Blade Runner','Deckard- Filed and monitored.'); aploetz@cqlsh:stackoverflow> SELECT * FROM data  WHERE id='B26354' AND type='Blade Runner' AND datetime='2015-02-16 12:00:03-0600';   id | type | datetime | data ----+------+----------+------  (0 rows) 

But when I query for the same id and type values while specifying milliseconds:

aploetz@cqlsh:stackoverflow> SELECT * FROM data  WHERE id='B26354' AND type='Blade Runner' AND datetime='2015-02-16 12:00:03.234-0600';   id     | type         | datetime                 | data --------+--------------+--------------------------+-------------------------------  B26354 | Blade Runner | 2015-02-16 12:00:03-0600 | Deckard- Filed and monitored.  (1 rows) 

So the milliseconds are definitely there. There was a JIRA ticket created for this issue (CASSANDRA-5870), but it was resolved as "Won't Fix."

2. How can I see that with cqlsh to verify?

One possible way to actually verify that the milliseconds are indeed there, is to nest the timestampAsBlob() function inside of blobAsBigint(), like this:

aploetz@cqlsh:stackoverflow> SELECT id, type, blobAsBigint(timestampAsBlob(datetime)),  data FROM data;   id     | type         | blobAsBigint(timestampAsBlob(datetime)) | data --------+--------------+-----------------------------------------+-------------------------------  B26354 | Blade Runner |                           1424109603234 | Deckard- Filed and monitored.  (1 rows) 

While not optimal, here you can clearly see the millisecond value of "234" on the very end. This becomes even more apparent if I add a row for the same timestamp, but without milliseconds:

aploetz@cqlsh:stackoverflow> INSERT INTO data (id, type, datetime, data) VALUES ('B25881','Blade Runner','2015-02-16T18:00:03+00:00','Holden- Fine as long as nobody unplugs him.'); aploetz@cqlsh:stackoverflow> SELECT id, type, blobAsBigint(timestampAsBlob(datetime)),                   ...     data FROM data;   id     | type         | blobAsBigint(timestampAsBlob(datetime)) | data --------+--------------+-----------------------------------------+---------------------------------------------  B25881 | Blade Runner |                           1424109603000 | Holden- Fine as long as nobody unplugs him.  B26354 | Blade Runner |                           1424109603234 |               Deckard- Filed and monitored.  (2 rows) 

Answer 2

You can configure the output format of datetime objects in the .cassandra/cqlshrc file, using python's 'strftime' syntax.

Unfortunately, the %f directive for microseconds (there does not seem to be a directive for milliseconds) does not work for older python versions, which means you have to fall back to the blobAsBigint(timestampAsBlob(date)) solution.