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;
In an effort to answer your questions, I did a little digging on this one.
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."
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)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With