Delete records from more than 1 year ago

Question

I'm saving twitter tweets in my database with spring JPA Repositories. The date of the tweet is saved as Datetime in the MySQL db. Now I want to delete all tweets that are older than one year. I saw there is the function CURRENT_TIME and I thought of something like CURRENT_TIME - 360. I know thats not the correct syntax but I have no idea how to do this. Here is what I have:

@Modifying
@Transactional
@Query("DELETE FROM Tweetpost t WHERE t.createdAt > ")
int removeOlderThan();

EDIT SOLVED:

Repository:

@Modifying
    @Transactional
    @Query("DELETE FROM Tweetpost m WHERE m.createdAt < :date")
    int removeOlderThan(@Param("date") java.sql.Date date);

Service:

public void removeOldItems() {
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DATE, -360);
        java.sql.Date oneYear = new java.sql.Date(cal.getTimeInMillis());
        tweetRepository.removeOlderThan(oneYear);
    }

Solution 1

For this you need 2 steps. First of all you need a method that will take as a parameter the date of which you want to delete the messages and you dont need tha @Query annotation at all.

So in your repository you must have something like

    @Modifying
    public void deleteByCreatedAtBefore(Date expiryDate);

Now in your service method, you will calculate the Date and pass it on like this

    public void performTweetCleanup(){
       //calculate date
       Calendar cal = Calendar.getInstance();
       Date today = cal.getTime();
       cal.add(Calendar.YEAR, -1);
       Date previousYear = cal.getTime();
       //call the method
       MyTweeterRepository.deleteByCreatedAtBefore(previousYear);
     }

Solution 2

SOLVED:

Repository:

@Modifying
    @Transactional
    @Query("DELETE FROM Tweetpost m WHERE m.createdAt < :date")
    int removeOlderThan(@Param("date") java.sql.Date date);

Service:

public void removeOldItems() {
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DATE, -360);
        java.sql.Date oneYear = new java.sql.Date(cal.getTimeInMillis());
        tweetRepository.removeOlderThan(oneYear);
    }

Solution 3

Compute the current time minus one year in Java, and then use the follwoing query:

DELETE FROM Tweetpost t WHERE t.createdAt < :nowMinusOneYear

Comments

[Strawberry]

WHEREt.createdAt < CURDATE() - INTERVAL 1 YEAR

[Strawberry]

So why is this tagged mysql?

[julien]

Because its a mysql db working in the background.

[Strawberry]

But you can't pass raw (my)sql to it?

[julien]

Not with Spring JPA Data, it throws an exception when I use "Interval".

[julien]

I already tried to compute a newDate - 360 days but it allways gives me an error..

[JB Nizet]

How could we possibly help you if you don't post the code used to compute it and the error you got?

[julien]

yes sorry here is what I have but there are alot of syntax errors when I try to create the new date "OneYear"... Calendar cal = Calendar.getInstance(); cal.setTime(dateInstance); cal.add(Calendar.DATE, -360); Date OneYear = cal.getTime();

[julien]

When I add this to the Repo it says: Invalid derived query! No property deleteTweetstbyCreatedAt found for type Tweetpost!

[JB Nizet]

Post that in the question, and tell what the error is.

[julien]

I did is it possible, that i can't create new dates in the repository?

[julien]

No problem I worked with your Example and solved the problem.

[Alexius DIAKOGIANNIS]

Thanks mate best of luck with spring data :)