Updating a database record every month

Question

I need to update some user records in my database every month (specifically, give them credits that belong to their subscription), starting the day 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.

Preferably in Java, since direct access to MySQL is cumbersome. — Bart van Heukelom, Jul 19 '11 at 09:08

score 1 · Answer 1 · answered Jul 19 '11 at 09:03

1

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.

answered Jul 19 '11 at 09:03

Bozho

588,226
146
1,060
1,140

What's happening at 10 am on Feb 28th with this Expression `@Scheduled(cron="0 10 30 * *")` – powerMicha Jul 19 '11 at 09:07
That would make the method execute every month, yes, but without any record of it in the database. See my comment on JohnIdol why that's undesirable. – Bart van Heukelom Jul 19 '11 at 09:07
@powerMicha I copy-pasted the cron expr. from somewhere, not sure if it's correct. – Bozho Jul 19 '11 at 09:08
@Bart van Heukelom that thing must run in a transaction, which will solve all such issues. – Bozho Jul 19 '11 at 09:08

score 0 · Answer 2 · answered Jul 19 '11 at 09:02

0

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.

answered Jul 19 '11 at 09:02

JohnIdol

48,899
61
158
242

That's not reliable enough. The process may crash halfway or not run at all for some reason. When it runs next day, it should pick up correctly without giving people double credits or skipping them a month. – Bart van Heukelom Jul 19 '11 at 09:04
Well you could always - I don't know - make it reliable? Trust me, *there are ways* :) – JohnIdol Jul 19 '11 at 10:25

KaKa · Answer 3 · 2011-07-19T09:12:05.080

0

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.

edited Jul 19 '11 at 09:12

answered Jul 19 '11 at 09:03

KaKa

1,543
12
18

That would work. One issue I can see is that if I ever need to change the program so that it updates once a week, for example, it would take a month before every user benefits. – Bart van Heukelom Jul 19 '11 at 09:11
have a last_updated_time column too, if you need to change frequency then updating the 'next_update_time' column accordingly is a matter of a sql query. – KaKa Jul 19 '11 at 09:15
or can have only last_updated_time and cronjob should take care of when to update next..select * from tab where last_updated_time > timestampadd(days, n ,now()) (or month,1 if needed in months) – KaKa Jul 19 '11 at 09:17

score 0 · Accepted Answer · answered Jul 19 '11 at 09:08

0

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

answered Jul 19 '11 at 09:08

Talha Ahmed Khan

15,043
10
42
49

My solution can be seen here: http://stackoverflow.com/questions/6844061/calculate-month-difference-in-joda-time I'm using Joda-Time's version of getting the maximum day of the month. – Bart van Heukelom Jul 27 '11 at 12:34

score 0 · Answer 5 · answered Jul 19 '11 at 09:12

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.

Updating a database record every month

5 Answers5