查找时间序列数据时，Cassandra返回变量结果 [英] Cassandra returns variable results when looking for time series data

问题描述

当我在DataStax DevCenter中进行此查询时，将返回2行。

When I give this query in DataStax DevCenter, 2 rows are returned. The rows returned are foe Dec 30th as they should be.

SELECT * FROM abc.alerts_by_type_and_timestamp WHERE alert_type IN ('Permanent Fault', 'Temporary Fault') AND alert_timeStamp >= '2015-12-30T15:00+0000' AND alert_timeStamp <= '2015-12-31T15:00+0000'

但是在PreparedStatement中运行

But running in PreparedStatement like this

    SELECT * FROM abc.alerts_by_type_and_timestamp WHERE alert_type IN :alertTypes AND alert_timeStamp >= :minTimestamp AND alert_timeStamp <= :maxTimestamp

返回低于4行。

    17:52:48,587 INFO  [stdout] (default task-39) minTimestamp: 2015-12-30 15:00:00.0 - maxTimestamp : 2015-12-31 15:00:00.0
    17:52:50,904 INFO  [stdout] (default task-39) row : Row[Permanent Fault, Thu Dec 31 12:09:22 PST 2015, 2015, 365, .....]
    17:52:50,904 INFO  [stdout] (default task-39) row : Row[Permanent Fault, Thu Dec 31 12:08:14 PST 2015, 2015, 365, ....]
    17:52:50,905 INFO  [stdout] (default task-39) row : Row[Temporary Fault, Thu Dec 31 12:09:22 PST 2015, 2015, 365, ...]
    17:52:50,906 INFO  [stdout] (default task-39) row : Row[Temporary Fault, Thu Dec 31 12:08:14 PST 2015, 2015, 365, ...]

    17:52:50,906 INFO  [stdout] (default task-39) count is : 4

我相信这是由于时间转换。数据存储为GMT，但是以某种方式PreparedStatement在PST中传递了数据？

I believe this is due to time conversion. Data is stored as GMT, but somehow PreparedStatement is passing it in PST ??

我该如何解决此问题？

我也尝试过：

DateTime dateTime = new DateTime(minTimestamp.getTime(), DateTimeZone.UTC);
DateTime dateTime2 = new DateTime(maxTimestamp.getTime(), DateTimeZone.UTC);
BoundStatement stmtByAlertTypeAndTimestamp = pStmt.bind()
    .setTimestamp("minTimestamp", new Timestamp(dateTime.getMillis()))
    .setTimestamp("maxTimestamp", new Timestamp(dateTime2.getMillis()))
    .setList("Types", Types);

在日期时间打印时间：

 minTimestamp: 2016-07-19 17:00:00.0  
 maxTimestamp: 2016-07-26 00:00:00.0

谢谢

推荐答案

您应更改文件
$ CASSANDRA_HOME / pylib / cqlshlib / formatter.py

You should change the file $CASSANDRA_HOME/pylib/cqlshlib/formatter.py

将函数strftime更改为

change function strftime to

def strftime(time_format, seconds):
    tzless_dt = datetime_from_timestamp(seconds)
    return tzless_dt.replace(tzinfo=pytz.utc).astimezone(pytz.timezone('Asia/Kolkata')).strftime(time_format)

并导入pytz库

我这样做是为了将cqlsh输出更改为IST。您可以根据需要更改时区

i done this to change the cqlsh output to IST. You can change timezone according to your need

说明：实际上cassandra总是将数据存储在GMT中，而准备好的语句占用本地时区（系统时区）的时间，因此结果会有所不同在这两个查询中。

Explanation: Actually cassandra store the data always in GMT and prepared statements take the time in local timezone (system timezone) so your results differ in both query.

还有另一种解决方法，您可以在带时区的准备好的语句中传递数据时间，然后根据我的说法应该可以正常工作

There is one more work around that you can pass the data time in prepared statement with time zone then according to me it should work fine

希望有帮助

这篇关于查找时间序列数据时，Cassandra返回变量结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！