2

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

Aaron
  • 55,518
  • 11
  • 116
  • 132
jlb333333
  • 371
  • 2
  • 13
  • Date arithmetic is only allowed in the `WHERE` clause, and then only with an appropriate `duration` type, based on https://issues.apache.org/jira/browse/CASSANDRA-11936. – Aaron Nov 02 '20 at 16:43

1 Answers1

3

The simplest way to do what you want is:

 SELECT x, toUnixTimestamp(date_1) - toUnixTimestamp(date_2) as age  FROM y WHERE a = 'z';
Benjamin Lerer
  • 59
  • 1
  • 1
    Thank you @benjamin-lerer. Worked perfectly. I think I tried this in Cassandra3 and failed but not again in Cassamdra4. I had pretty much given up. So thanks again. Cassandra date arithmetic would be a great subject for a blog post. – jlb333333 Nov 06 '20 at 07:30