Reputation: 371
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
Upvotes: 2
Views: 565
Reputation: 59
The simplest way to do what you want is:
SELECT x, toUnixTimestamp(date_1) - toUnixTimestamp(date_2) as age FROM y WHERE a = 'z';
Upvotes: 3