Subtracting dates in WHERE clause

[deepatpaul]

I'm having trouble coming up with a WHERE condition for a query that returns all events based on user. I only want dates to appear that are less than 30 days minus the current date.

I'm running mySQL 4.0.24.

 

[sleepingkirby]

what format the dates written in? Can you post an example of the dates?

 

[deepatpaul]

Here is the query (some fields retrieved multiple times for date comparison purposes)

SELECT TIME_FORMAT(events.publish_up, '%l:%i %p') as starttime,
DATE_FORMAT(events.publish_up, '%M %D') as startdate,
DATE_FORMAT(events.publish_up, '%Y') as startyear,
DATE_FORMAT(events.publish_up, '%m-%d') as strdate,
TIME_FORMAT(events.publish_down, '%l:%i %p') as endtime,
DATE_FORMAT(events.publish_down, '%M %D') as enddate,
DATE_FORMAT(events.publish_down, '%Y') as endyear,
events.title, events.content, events.adresse_info, events.contact_info,
events.extra_info, events.id
FROM mos_events as events INNER JOIN mos_events_users as events_users
ON (events.id=events_users.eventID) INNER JOIN mos_makinmusic_teacher as teacher
ON (events_users.userID=teacher.userid) WHERE teacher.userid = '85'
ORDER BY startyear ASC, startdate ASC

 

[KarveR]

if you have a date filed as the basis for the query,

select * from blah WHERE date_sub(your_datefield,interval 30 day) and ... ;

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

I lied. over tired.

select * from blah where your_datefield >= date_sub(curdate(), interval 30 day);

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[deepatpaul]

My version of PHP doesn't have date_sub (or _add) built in yet, unfortunately. Thanks for the effort.

 

[KarveR]

its not a php function, its a mysql function.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[deepatpaul]

Er, sorry. That's what I meant. I need mySQL 4.1, but only have 4.0.24 installed.

 

[KarveR]

date_sub has been in since like forever - here it is in action in 3.23.56

[root@XXXXX fx]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4804 to server version: 3.23.56-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select date_sub(curdate(), interval 30 day);
+--------------------------------------+
| date_sub(curdate(), interval 30 day) |
+--------------------------------------+
| 2005-10-05                           |
+--------------------------------------+
1 row in set (0.00 sec)

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.