CODEWITHSUNDEEP
javamysqlspringjpa
julien
julien

Reputation: 634

Delete records from more than 1 year ago

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);


    }

Upvotes: 18

Views: 30625

Answers (4)

Alexius DIAKOGIANNIS
Alexius DIAKOGIANNIS

Reputation: 2584

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);
     }

Upvotes: 25

julien
julien

Reputation: 634

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);


    }

Upvotes: 7

Alexander Heim
Alexander Heim

Reputation: 154

If you want the current time you could use

System.getCurrentTimeMillis()

Take the time from the old date and subtract it from the current one then you just have to compare it with the duration of one year. Maybe you should add something for leap-years.

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691745

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

DELETE FROM Tweetpost t WHERE t.createdAt < :nowMinusOneYear

Upvotes: 3

Related Questions