Updating a database record every month

I need to update some user records in my database every month (specifically, give them credits that belong to their subscription), starting the da开发者_开发知识库y they sign up. I thought of doing it this way:

Once every day...

• Find all users that have been updated a month ago (or more, in case the process fails for a day)
• Update them
• Increase their last update day by a month

Sounds simple, but simpler than it is. The problem is that months don't always have the same number of days. For example, if somebody signs up march 31, when should he be updated? April 30, may 1? Or should I simply restrict the range of update days to the lowest common range, 28? I could of course just do it on the 1st, for everyone, though that's less ideal.

Do you have experiences to share with situations like this? I'm using MySQL 5 from Java+JPA+Hibernate. If it helps, I'm already using JodaTime for something else.

If using spring, Spring scheduling is what you can take a look at. You can have a method like this:

@Scheduled("0 0 1 * *") // cron expression
public void executeOnceAMonth() {
}

and just have <task:annotation-driven /> (more details on configuration - in the linked document)

This is actually an abstraction over Quartz, so you can also use a Quartz job to do it.

Get the maximum day of the month by following code.

    Calendar calendar = Calendar.getInstance();     
    java.sql.Date userSignupdate = ...;
    calendar.setTimeInMillis( userSignupdate );

    // maxDay will hold the maximum date of the month. {28,29,30,31}
    int maxDay = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);

Can u just have a scheduled job running the 20th of each month (or whatever) and calculate credits for each user so far?

I would not complicate the situation with offsets and stuff if not strictly necessary.

Instead have a column 'next_update_time', so when a user signs up the column value should be updated with the time after a month

Calendar cal = Calendar.getInstance();
cal.add(Calendar.MONTH, 1);
cal.getTime();

run a cronjob (invoke another small java code) every few hours to check for columns which have next_update_time >= current time, if yes,update the records and set the next_update_time value to next month.

I don't see why doing it on the first of the month is less ideal. Especially if you give a partial credit to bring each user up to the same date

The simplest way of handling this in my experience is to "normalise" each user to the first of the month when they join. So if somebody joins on the 15th June they get a

1-(15/30) x monthly_credits credit.

From that point on it is the 1st of the month.

NB you can use a similar method to assign users to different days (1-28) if you want to spread them out for a particular reason.