Cassandra cqlsh - 如何显示时间戳列的微秒/毫秒？ [英] Cassandra cqlsh - how to show microseconds/milliseconds for timestamp columns?

问题描述

我插入到带有时间戳列的Cassandra表中。数据的微秒精度，所以时间数据字符串看起来像这样：

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

然而，在cqlsh当我运行选择查询微秒数据没有显示，我只能看到时间下降到第二精度。 234 微秒数据未显示。

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.

我想我有两个问题：

1）Cassandra是否捕获具有时间戳数据类型的微秒？我的猜测是吗？

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

2）我怎么能看到用cqlsh验证？

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

表格定义：

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

使用Java PreparedStatment执行插入查询：

Insert query ran with Java PreparedStatment:

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

选择查询简单：

select * from data;

推荐答案

为了回答您的问题，

1. Cassandra是否会捕获具有时间戳记数据类型的微秒数？

微秒数，毫秒是。如果我创建你的表，插入一行，并尝试查询它的截断时间，它不工作：

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)

但是当我查询相同的 id 和 type 值，同时指定毫秒：

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)

所以毫秒绝对存在。为此问题创建了一个JIRA凭单（ CASSANDRA-5870 ），但已解决为Won 't Fix。

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. 我如何通过cqlsh验证？

一个可能的方法来实际验证毫秒确实存在，是嵌套 timestampAsBlob（） blobAsBigint（），如下所示：

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)

虽然不是最佳的，毫秒值234就在结束。如果我为相同的时间戳添加一行，但是没有毫秒，这变得更加明显：

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)

这篇关于Cassandra cqlsh - 如何显示时间戳列的微秒/毫秒？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！