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;

推荐答案

为了回答您的问题，我对这个问题进行了一些挖掘.

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

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屋！