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)