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; ```

Accepted Answer

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

- 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';

(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."

- 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;

(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;

(2 rows)
```

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

Solution

Problem