[SOLVED] Date Ranges?

[random1]

Hi All,

 

Im pretty new to MySQL. My web app currently has a table of users and my interface has a date range picker: 1st is now <- 30days, 2nd is now -> 30days (two select lists).

 

How in mysql can you do date range filtering?

 

e.g.

 

SELECT `user_name` FROM `user`
WHERE `user_registration_datetime` > NOW() - 1 week
AND `user_registration_datetime` < NOW() + 1 week

 

(I'd like to select the info for `user` where the user registration datetime is 1 week back or 1 week forward)

[corbin]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

 

DATE_ADD and DATE_SUB should do the trick.

[random1]

Thanks,

 

I ended up with:

 

SELECT UTC_TIMESTAMP() + INTERVAL 1 MONTH AS `now + 1 month`;

SELECT UTC_TIMESTAMP() - INTERVAL 1 MONTH AS `now - 1 month`;

 

and

 

SELECT * FROM `user`
WHERE `user_datetimelastlogin`
BETWEEN UTC_TIMESTAMP() - INTERVAL 1 MONTH 
AND UTC_TIMESTAMP();

 

People that don't use UTC timestamps need to replace UTC_TIMESTAMP() with NOW().

[corbin]

Uh....  That's much slower (well, it is if you're using it how I think you are), but ok.

 

 

Also, you might want to use BETWEEN in a situation where you're saying between x and y.