cassandra - 如何在 CQL 中对日期时间进行算术运算

Question

我的目标是从现在()开始删除固定的时间。所以我总是能得到最后五分钟或最后五个小时。

我怎样才能实现它？

The documentation在 Cassandra 上说:

A duration can be added (+) or substracted (-) from a timestamp or adate to create a new timestamp or date. So for instance:
SELECT * FROM myTable WHERE t = '2017-01-01' - 2d
will select all the records with a value of t which is in the last 2 days of 2016.

在cqlsh内，show version;给我:

[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL spec 3.4.4 | Native protocol v4]

我用下表进行测试:

cqlsh:> CREATE TABLE t (
    ...   ts        timestamp,
    ...   PRIMARY KEY (ts)
    ... )
    ... WITH compression = {'class': 'LZ4Compressor'}
    ... AND gc_grace_seconds = 60;

以下查询有效:

SELECT (float)1.55 FROM t WHERE (ts <= toTimestamp(now()));

以下情况不会:

cqlsh:> SELECT (float)1.55 FROM t WHERE (ts <= toTimestamp(now() - 1d));
SyntaxException: line 1:57 mismatched input '-' expecting ')' (...ts <= toTimestamp(now() [-]...)
cqlsh:> SELECT (float)1.55 FROM t WHERE (ts <= toTimestamp(now()) - 1d);
SyntaxException: line 1:58 mismatched input '-' expecting ')' (... <= toTimestamp(now()) [-]...)
cqlsh:> SELECT (float)1.55 FROM t WHERE (ts <= toTimestamp(now()) - 1m);
SyntaxException: line 1:58 mismatched input '-' expecting ')' (... <= toTimestamp(now()) [-]...)
cqlsh:> SELECT (float)1.55 FROM t WHERE ts <= toTimestamp(now()) - 1m;
SyntaxException: line 1:57 mismatched input '-' expecting EOF (... <= toTimestamp(now()) [-]...)
cqlsh:> SELECT (float)1.55 FROM t WHERE ts = toTimestamp(now()) - 1m;
SyntaxException: line 1:56 mismatched input '-' expecting EOF (... = toTimestamp(now()) [-]...)

正如您所看到的，当尝试减去持续时间时，错误始终与 - 有关。 (顺便说一句，结果与+相同)

我做错了什么？可能有办法实现它，但我不知道!

解决方案

郑重声明，正如 @Ashraful-Islam 所建议的，我的解决方案是创建一个 UDF 来完成这项工作。见下文:

CREATE FUNCTION IF NOT EXISTS timeAgo(minutes int) 
  CALLED ON NULL INPUT 
  RETURNS timestamp
  LANGUAGE java AS '
    long now = System.currentTimeMillis();
    if (minutes == null)
      return new Date(now);
    return new Date(now - (minutes.intValue() * 60 * 1000));
  ';

-- So I can do
SELECT timeAgo(60) FROM t;

Answer 1

Cassandra 4.0 中引入的算术运算符

• Add support for arithmetic operators (CASSANDRA-11935)

来源:https://github.com/apache/cassandra/blob/trunk/CHANGES.txt#L124

<小时/>

已编辑

如果您的 cassandra 版本低于 4.0，那么您必须从应用程序层执行此操作或创建用户定义函数 (UDF)。检查下面的链接

Creating user-defined function in Cassandra