Date Arithmetic: Cassandra 4

Question

The data I am loading into Cassandra contains dates(timestamp type). I need to do calculations with these dates to calculate, for example the difference between a given date and now() or between two date(timestamp type) columns.

I tried:

SELECT x, date_1 - date_2 as age  FROM y WHERE a = 'z';

I tried parentheses, the 'AS' clause and casting the timestamps to a date type, but received errors.

I also tried creating a UDF(User Defined Function):

CREATE OR REPLACE FUNCTION x.age_calc (tounixtimestamp(date_1) int, tounixtimestamp(date_2) int) RETURNS NULL ON NULL INPUT RETURNS int LANGUAGE java AS 'return Integer.valueOf(Math.abs(tounixtimestamp(date_1) - tounixtimestamp(date_2)))';

I can see the above UDF has incorrect syntax but don't quite know how/where to fix it.

I wish for instance to get a difference in milliseconds and convert it into years or months.(the conversion part is easy, I hope)

I am sure I am close to getting there but my syntax in all examples tried is incorrect.

Web searches over a number of days have yielded no really similar examples.

I am guessing Cassandra 4 can do this.

Versions: Cassandra Version: 4.0-beta2, cqlsh 5.0.1

Answer 1

The simplest way to do what you want is:

 SELECT x, toUnixTimestamp(date_1) - toUnixTimestamp(date_2) as age  FROM y WHERE a = 'z';