[SOLVED] How to get Last 6 Month data comparing with timestamp column using cassandra query?

How to get Last 6 Month data comparing with timestamp column using cassandra query?

How to get Last 6 Month data comparing with timestamp column using cassandra query? I need to get all account statement which belongs to last 3/6 months comparing with updatedTime(TimeStamp column) and CurrentTime. For example in SQL we are using DateAdd() function tor this to get. i dont know how to proceed this in cassandra. If anyone know,reply.Thanks in Advance.

Solution

Cassandra 2.2 and later allows users to define functions (UDT) that can be applied to data stored in a table as part of a query result.

You can create your own method if you use Cassandra 2.2 and later UDF

CREATE FUNCTION monthadd(date timestamp, month int)
    CALLED ON NULL INPUT
    RETURNS timestamp
    LANGUAGE java
    AS $$java.util.Calendar c = java.util.Calendar.getInstance();c.setTime(date);c.add(java.util.Calendar.MONTH, month);return c.getTime();$$

This method receive two parameter

date timestamp: The date from you want add or subtract number of month
month int: Number of month you want to or add(+) subtract(-) from date

Return the date timestamp

Here is how you can use this :

SELECT * FROM ttest WHERE id = 1 AND updated_time >= monthAdd(dateof(now()), -6) ;

Here monthAdd method subtract 1 mont from the current timestamp, So this query will data of last month

Note : By default User-defined-functions are disabled in cassandra.yaml - set enable_user_defined_functions=true to enable if you are aware of the security risks