how to do arithmetics with datetimes in CQL

Question

My objective, is to remove a fixed amount of time, from now(). So I always get the last five minutes, or the last five hours.

How can I achieve it ?

The documentation on cassandra says:

A duration can be added (+) or substracted (-) from a timestamp or a date 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.

inside cqlsh, show version; gives me:

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

I test with the following table:

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

The following query works:

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

The followings don't:

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()) [-]...)

As you can see the error is always about the - when trying to substract the duration. (By the way, the result is the same with +)

What do I do wrong? there might be a way to achieve it, but I can't figure it out!

solution

For the record, as suggested by @Ashraful-Islam my solution has been to create a UDF to do the job. see below:

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

Arithmetic Operators introduced in Cassandra 4.0

• Add support for arithmetic operators (CASSANDRA-11935)

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

---

Edited

If your cassandra version less than 4.0 then you have to do it from the application layer or create a user define function (UDF). Check the below link

Creating user-defined function in Cassandra