Take the 2-minute tour ×

I've the following question:

This is my Create-table:

CREATE TABLE `mod_users` (
  `u_id` int(11) NOT NULL auto_increment,
  `u_date` datetime NOT NULL,
  `u_type` int(1) NOT NULL default '',
  `u_adddate` datetime NOT NULL,
  PRIMARY KEY  (`u_id`)

u_type contains an int (doh) which represent a type of interval.

1 = one week
2 = two weeks
3 = one month
4 = one year

I want to select the first date after the current date which falls into the interval of the u_type - one day (so one week becomes 6 days, 2 weeks becomes 13 days, one month becomes: 1 month - 1 day, one year becomes: one-year -1 day) with a start of u_date. Sounds confusing right.

An example: If an user has type 1 (one week) And u_date 2010-11-01 00:00:00 (Four days ago) I want to get the first occurrence after one week. In this case 'SELECT DATE_ADD(u_date,INTERVAL 6 DAYS) from mod_users WHERE u_id = 1'; (which gives 2010-11-07 00:00:00) How can i do this for u_type 3 or 4? something like 'SELECT DATE_ADD(u_date,INTERVAL 1 MONTH - 1 DAYS) from mod_users WHERE u_id = 1';?? This is not a valid query.

Also if the current date would be 2010-11-08 I Want to get the first occurrence after this date. In this case 2010-11-14. 00:00:00. This reject this query 'SELECT DATE_ADD(u_date,INTERVAL 6 DAYS) from mod_users WHERE u_id = 1'; Cause 6 days is not the right interval at this query.

Hope some of you can help? I can use php and mysql so these queries are just used as an example. I'm really stuck here so just hints or clues would be appreciated.

share|improve this question
Meanwhile I've got the first problem tackled with SELECT u_date - INTERVAL 1 DAY + INTERVAL 1 MONTH AS C from mod_users WHERE u_id = 1 –  Tim Nov 5 '10 at 10:07

1 Answer 1

up vote 1 down vote accepted

In MySQL you can add intervals directly to a datetime field, so u_date + INTERVAL 1 MONTH - INTERVAL 1 DAY should work fine.

share|improve this answer
I found out. See my comment a couple of minutes ago :) –  Tim Nov 5 '10 at 10:12

Your Answer


