为什么此CQL返回空结果，开始和结束日期范围都是相同的值 [英] Why does this CQL return empty results with beginning and ending date range are the same value

问题描述

我编写的参数化CQL语句的行为不符合我的预期。我试图返回一个日期范围的记录列表，在这个实例中，对于开始和结束点使用相同的日期。

I am writing a parameterized CQL statement that is not behaving as I would expect. I am trying to return a list of records for a range of dates that in this instance are using the same date for the beginning and end points.

表定义为如下：

CREATE TABLE sometable (
partition_key varchar,
cluster_start_date timestamp,
other_cluster_key varchar,
data varchar,
PRIMARY KEY((partition_key), cluster_start_date, other_cluster_key)
) WITH CLUSTERING ORDER BY(cluster_start_date ASC, other_cluster_key ASC);

准备的语句如下：

SELECT * FROM sometable WHERE partition_key = 'xxx' AND cluster_start_date  >= ? AND cluster_start_date <= ?;

当参数化日期不同时，语句返回正确的数据。但是当参数化日期相同时，则不返回任何值。

When the parameterized dates are different, the statement returns the correct data. But when the parameterized dates are the same, then no values are returned.

有人能告诉我为什么这个语句不能使用相同的参数，如果是，

Could someone tell me why this statement does not work with identical parameters, and if so, what can be done to get the expected behavior?

推荐答案

感谢所有可以帮助的人。方案

解决方案

您没有显示如何在查询中指定时间戳记值，但我想您正在指定它在​​几秒钟内。

You didn't show how you are specifying the timestamp value in your query, but I'm guessing you are specifying it in seconds. Internally the resoultion of timestamp is higher than seconds.

如果你以秒为单位指定一个时间戳，那么你的下限将被舍入到000毫秒，而你的上限bound也将向下舍入到000毫秒。它可能是你希望看到的行没有000毫秒，因此它在你指定的微小的1毫秒宽的范围之外。例如：

If you specify a timestamp in seconds, then your lower bound would be rounded down to 000 for the milliseconds, and your upper bound would also be rounded down to 000 milliseconds. It's likely the row you expect to see does not have 000 for the milliseconds, and so it is outside the tiny 1 millisecond wide range you specified. For example:

SELECT * from sometable ;

 partition_key | cluster_start_date       | other_cluster_key | data
---------------+--------------------------+-------------------+------
             a | 2015-08-22 06:16:38-0400 |                 a | row1
             a | 2015-08-22 06:17:09-0400 |                 b | row2
             a | 2015-08-22 06:17:31-0400 |                 c | row3

SELECT * FROM sometable WHERE partition_key = 'a' 
    and cluster_start_date  >= '2015-08-22 06:17:09-0400' 
    and cluster_start_date  <= '2015-08-22 06:17:09-0400';

 partition_key | cluster_start_date | other_cluster_key | data
---------------+--------------------+-------------------+------

(0 rows)

但是如果你将时间戳指定为毫秒精度，并在上边界上使用999毫秒，它将找到行：

But if you specify the timestamp to millisecond precision, and use 999 for the milliseconds on the upper bound, it finds the row:

SELECT * FROM sometable WHERE partition_key = 'a' 
    and cluster_start_date  >= '2015-08-22 06:17:09.000-0400'
    and cluster_start_date  <= '2015-08-22 06:17:09.999-0400';

 partition_key | cluster_start_date       | other_cluster_key | data
---------------+--------------------------+-------------------+------
             a | 2015-08-22 06:17:09-0400 |                 b | row2

或者你可以删除等于上限，说小于下一秒，像这样：

Or you could drop the equals on the upper bound and say less than the next second like this:

SELECT * FROM sometable WHERE partition_key = 'a' 
    and cluster_start_date  >= '2015-08-22 06:17:09-0400' 
    and cluster_start_date  < '2015-08-22 06:17:10-0400';

 partition_key | cluster_start_date       | other_cluster_key | data
---------------+--------------------------+-------------------+------
             a | 2015-08-22 06:17:09-0400 |                 b | row2

这篇关于为什么此CQL返回空结果，开始和结束日期范围都是相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！