Dates with mySQL

Hey, another mySQL question.

I have a message board with login_date.

I am trying to create a function where you can delete users from the data base based on when they last loged in. ie (1week, 1month, 1year ago) kinda thing.

DELETE FROM users WHERE (users login_date was over 1 month ago)

Note:
I am trying to figure it out on my own now, however I figured some mySQL wiz on here who can pull the answer out of their hat, might be able to provide the answer before I can figure it out.

Since you are trying to figure it out by yourself I won’t tell you, but this link will definitely get you on your way: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

ha ha I am already on that page… :slight_smile:

I have been looking for others, basicly an example of what I am trying to do but couldn’t find any… So now I am on that page…

Can I do something like

delete where (post_date > curdate + 30days)

There’s

ADDDATE(expr,days)
When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL argument, see the discussion for DATE_ADD().

mysql> SELECT DATE_ADD(‘1998-01-02’, INTERVAL 31 DAY);
-> '1998-02-02’
mysql> SELECT ADDDATE(‘1998-01-02’, INTERVAL 31 DAY);
-> ‘1998-02-02’

As of MySQL 4.1.1, the second syntax is allowed, where expr is a date or datetime expression and days is the number of days to be added to expr.

mysql> SELECT ADDDATE(‘1998-01-02’, 31);
-> ‘1998-02-02’

ADDDATE(expr,days)
When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL argument, see the discussion for DATE_ADD().

mysql> SELECT DATE_ADD(‘1998-01-02’, INTERVAL 31 DAY);
-> '1998-02-02’
mysql> SELECT ADDDATE(‘1998-01-02’, INTERVAL 31 DAY);
-> ‘1998-02-02’

As of MySQL 4.1.1, the second syntax is allowed, where expr is a date or datetime expression and days is the number of days to be added to expr.

mysql> SELECT ADDDATE(‘1998-01-02’, 31);
-> ‘1998-02-02’

Though that’s one month from now, not one month ago. Lemme see if there’s a SUBTRACTDATE or something too.

Here you go:

DATE_SUB(date,INTERVAL expr type)
These functions perform date arithmetic. date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a `-’ for negative intervals. type is a keyword indicating how the expression should be interpreted. The INTERVAL keyword and the type specifier are not case sensitive. The following table shows how the type and expr arguments are related:
type Value Expected expr Format
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND 'SECONDS.MICROSECONDS’
MINUTE_MICROSECOND 'MINUTES.MICROSECONDS’
MINUTE_SECOND 'MINUTES:SECONDS’
HOUR_MICROSECOND 'HOURS.MICROSECONDS’
HOUR_SECOND 'HOURS:MINUTES:SECONDS’
HOUR_MINUTE 'HOURS:MINUTES’
DAY_MICROSECOND 'DAYS.MICROSECONDS’
DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE 'DAYS HOURS:MINUTES’
DAY_HOUR 'DAYS HOURS’
YEAR_MONTH 'YEARS-MONTHS’
The type values DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND, and MICROSECOND are allowed as of MySQL 4.1.1. The values QUARTER and WEEK are allowed as of MySQL 5.0.0. MySQL allows any punctuation delimiter in the expr format. Those shown in the table are the suggested delimiters. If the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts), the result is a DATE value. Otherwise, the result is a DATETIME value. As of MySQL 3.23, INTERVAL expr type is allowed on either side of the + operator if the expression on the other side is a date or datetime value. For the - operator, INTERVAL expr type is allowed only on the right side, because it makes no sense to subtract a date or datetime value from an interval. (See examples below.)

mysql> SELECT ‘1997-12-31 23:59:59’ + INTERVAL 1 SECOND;
-> '1998-01-01 00:00:00’
mysql> SELECT INTERVAL 1 DAY + ‘1997-12-31’;
-> '1998-01-01’
mysql> SELECT ‘1998-01-01’ - INTERVAL 1 SECOND;
-> '1997-12-31 23:59:59’
mysql> SELECT DATE_ADD(‘1997-12-31 23:59:59’,
-> INTERVAL 1 SECOND);
-> '1998-01-01 00:00:00’
mysql> SELECT DATE_ADD(‘1997-12-31 23:59:59’,
-> INTERVAL 1 DAY);
-> '1998-01-01 23:59:59’
mysql> SELECT DATE_ADD(‘1997-12-31 23:59:59’,
-> INTERVAL ‘1:1’ MINUTE_SECOND);
-> '1998-01-01 00:01:00’
mysql> SELECT DATE_SUB(‘1998-01-01 00:00:00’,
-> INTERVAL ‘1 1:1:1’ DAY_SECOND);
-> '1997-12-30 22:58:59’
mysql> SELECT DATE_ADD(‘1998-01-01 00:00:00’,
-> INTERVAL ‘-1 10’ DAY_HOUR);
-> '1997-12-30 14:00:00’
mysql> SELECT DATE_SUB(‘1998-01-02’, INTERVAL 31 DAY);
-> '1997-12-02’
mysql> SELECT DATE_ADD(‘1992-12-31 23:59:59.000002’,
-> INTERVAL ‘1.999999’ SECOND_MICROSECOND);
-> ‘1993-01-01 00:00:01.000001’

If you specify an interval value that is too short (does not include all the interval parts that would be expected from the type keyword), MySQL assumes that you have left out the leftmost parts of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to have days, hours, minutes, and seconds parts. If you specify a value like '1:10', MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds. In other words, '1:10' DAY_SECOND is interpreted in such a way that it is equivalent to '1:10' MINUTE_SECOND. This is analogous to the way that MySQL interprets TIME values as representing elapsed time rather than as time of day. If you add to or subtract from a date value something that contains a time part, the result is automatically converted to a datetime value:

mysql> SELECT DATE_ADD(‘1999-01-01’, INTERVAL 1 DAY);
-> '1999-01-02’
mysql> SELECT DATE_ADD(‘1999-01-01’, INTERVAL 1 HOUR);
-> ‘1999-01-01 01:00:00’

If you use really malformed dates, the result is NULL. If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:

mysql> SELECT DATE_ADD(‘1998-01-30’, INTERVAL 1 MONTH);
-> ‘1998-02-28’

lol…

Yeah I see all that… but not knowing what I can and can’t do with the correct syntax is the problem.

I am clue less…

This is what I am thinking…

DELETE FROM users WHERE (last_login > (CURDATE() + 30 DAYs));

Like this I’d say:

DELETE FROM users WHERE last_login > DATE_SUB(CURDATE(), INTERVAL 30 DAY);
*What was I smoking …

First try using SELECT instead of DELETE and run the query, to see if it works. Oh, and you may have to try using single quotes around the DATE_ADD(…).

Ha ha ha I wasn’t even close…

I will give yours a try when I get home…

I might as well say thankyou now :smiley:

thanks.

To delete all rows that are older than 30 days, it should be

delete from users where last_login < date_sub(curdate(), interval 30 day)

On DB2 (another database made by IBM) you can say
delete from users where last_login < current date - 30 days

I think that’s a bit more clear. But unfortunately it doesn’t work with MySQL.

Huh !? Did I really post

DELETE FROM users WHERE last_login > DATE_ADD(CURDATE(), INTERVAL 1 DAY);

?? Man, what was I smoking … I meant to say what Hans Killian said. :trout:

Well I couldn’t get it to work…

I have a database with 30 entries they are all 2004 except for 2.

post_date
2004-07-23
2003-07-23
2003-07-23

This is the code I tried
The blanked out line acting on users works so I know that it is sending and recieving.
I know that the guestbook connection works as I have 2 other functions in the same PHP file that work.

So if the line did select the 2 2003 entries it would return a value greater than 0 to Flash.

if ($all_posts_older_than == 1){
 
$sql = "SELECT FROM guestbook WHERE post_date < date_sub(curdate(), interval 30 day)"
 
//$sql = "SELECT username FROM users";
 
$allComments = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
$numallComments = mysql_num_rows($allComments);
 
print "&numallComments=$numallComments";
 
exit; }

Any ideas ???

Green functions are working, Red (date) ones I am working on now.
Delete:
[color=seagreen]user[/color]
[color=seagreen]all users[/color]
[color=seagreen]modorator[/color]
[color=seagreen]all modorators[/color]
[color=seagreen]all posts[/color]
[color=seagreen]all posts made by[/color]

[color=red]all posts older than[/color]
[color=red]all accounts unused for[/color]
[color=red]all accounts unactivated for[/color]

What does the script print if you run it from a browser?

One thing that may help is to ‘SELECT * FROM guestbook’. Right now you only have ‘SELECT FROM guestbook’. That’s not valid SQL AFAIK. You could also do ‘SELECT 1 FROM guestbook’ if you don’t need any of the column values.

You could do it alot more easier, by just doing the post_date with the mktime() function. Try this:


 <?
 $onemonthago = mktime()-2592000000; // 30 days ago... (miliseconds)
mysql_query("DELETE FROM users WHERE post_date < '$onemonthago'");
 ?>
 

I think that is much easier then the other ways… hope it helps!

I don’t think you can compare a date field to an integer like that.

So you have to convert the post_date field to a Unix timestamp like this:
mysql_query(“DELETE FROM users WHERE UNIX_TIMESTAMP(post_date) < $onemonthago”)

Note that you also have to delete the quotes around $onemonthago since you’re comparing numeric values.

Thanks for the response guys.

I figured out why my code wasn’t returning anything…

[color=darkgreen]$sql = "SELECT[/color] [color=royalblue]*[/color] [color=darkgreen]FROM guestbook WHERE post_date < date_sub(curdate(), interval 30 day)";[/color]

I had used this [color=darkgreen]$query = “DELETE FROM guestbook”;[/color]
which deletes all…

So I thought that ( nothing = all, so I never put * in…) doh…

One thing that may help is to ‘SELECT * FROM guestbook’. Right now you only have ‘SELECT FROM guestbook’. That’s not valid SQL AFAIK. You could also do ‘SELECT 1 FROM guestbook’ if you don’t need any of the column values.

Ha ha ha Hans you solved it for me and I didn’t even see…

I got my e-mail that said what does it print to browser? I didn’t see the bit below that, and while I was stuffing around getting that done I realised the * thing…

Thanks for your help with that :smiley:

Haha, well spotted, I didn’t even see that ! :trout: