question

yashwanth.kondeti@verizon.com avatar image
yashwanth.kondeti@verizon.com asked smadhavan commented

How do I check if a column's date is older than 30 days?

Hi,

We have a table with 3 columns (Order_ID, Item_Description, Order_Date). We add about 1000-5000 records to this table everyday. Everyday, we run a script that ensures that orders older than 30 days are deleted. We want to retain all the rows whose order_date is less than a month old and delete the rows that are older than 30 days.

Before deleting, we need want to select the data and validate it. So we would like to check which records satisfy the condition - Order_Date < (today's_date)-30. How do we achieve this? What can be the syntax? I understand that date(now) gives us today's date, but how do we get the date from 1 month ago?

I tried the following syntax, but it throws error. Can you please suggest the right syntax?

select * from Employees where Date_Joined<(date(now()-30)) allow filtering;

Should the column be defined as unix timestamp? Please share your thoughts.

Thanks,

Yashwanth.

cql
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

2 Answers

smadhavan avatar image
smadhavan answered smadhavan commented

I am posting the answer as a comment as this is already closed and there is no other way to answer to this original question. Also, since you've not posted the full table schema, I do not know what are the actual primary key, etc., so I am making a guess here.

Hello @yashwanth.kondeti@verizon.com, you could do something like as following,

 SELECT *
 FROM employees
 WHERE order_date = currentDate() - 30d;

See the following documentation for additional information:

2 comments Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

yashwanth.kondeti@verizon.com avatar image yashwanth.kondeti@verizon.com commented ·

Thanks a lot @smadhavan ..

"WHERE order_date = currentDate() - 30d;" -- this one worked perfectly.

I was always trying with "currentDate() - 30;" without the d so it was throwing errors.

Really appreciate your response.

0 Likes 0 ·
smadhavan avatar image smadhavan ♦ yashwanth.kondeti@verizon.com commented ·

You're very welcome. Glad it worked you @yashwanth.kondeti@verizon.com!

0 Likes 0 ·
Erick Ramirez avatar image
Erick Ramirez answered Erick Ramirez converted comment to answer

You won't be able to do it natively in CQL. You will need to implement it in your code.

In any case, the range query you're proposing is an expensive operation in Cassandra since it requires a full table scan. You need to use Apache Solr, Elasticsearch or Apache Spark to do that kind of complex query.

If you need assistance or have follow up questions, please log a ticket with DataStax Support since this is beyond what we can provide in a Q&A forum. Cheers!

1 comment Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

yashwanth.kondeti@verizon.com avatar image yashwanth.kondeti@verizon.com commented ·

Hi Erik @Erick Ramirez ,

Thank you for sharing your inputs. I understand that the query I posted is not optimal. I am not going to use the same query I posted here. Actually, my main objective is to find a function to get a date that is 30 days older than today's date. Is there a function that can give me the date from 30 days old?


Thanks,

Yashwanth.

0 Likes 0 ·