Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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; 
like image 941
WillZ Avatar asked Feb 16 '15 18:02

WillZ


2 Answers

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

  1. 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) 
like image 137
Aaron Avatar answered Sep 19 '22 16:09

Aaron


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.

like image 23
Tom Avatar answered Sep 20 '22 16:09

Tom