# Dates with mySQL

**URL:** <https://forum.kirupa.com/t/dates-with-mysql/59713>\
**Category:** Uncategorized\
**Created:** [August 3, 2004, 8:13am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713 "2004-08-03T08:13:45Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![The\_Vulcan](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/the_vulcan/32/145_2.png) [@The\_Vulcan](https://forum.kirupa.com/u/The_Vulcan)\
**Post date:** [August 3, 2004, 8:13am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/1 "2004-08-03T08:13:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 8:23am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/2 "2004-08-03T08:23:06Z")

</div>

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](http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html)

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 8:28am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/3 "2004-08-03T08:28:37Z")

</div>

ha ha I am already on that page… 🙂

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…

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 8:32am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/4 "2004-08-03T08:32:04Z")

</div>

Can I do something like

delete where (post\_date \> curdate + 30days)

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 8:33am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/5 "2004-08-03T08:33:58Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 8:34am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/6 "2004-08-03T08:34:30Z")

</div>

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’

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 8:38am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/7 "2004-08-03T08:38:26Z")

</div>

lol…

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

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 8:49am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/8 "2004-08-03T08:49:37Z")

</div>

I am clue less…

This is what I am thinking…

DELETE FROM users WHERE (last\_login \> (CURDATE() + 30 DAYs));

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 8:54am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/9 "2004-08-03T08:54:05Z")

</div>

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(…).

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 8:59am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/10 "2004-08-03T08:59:24Z")

</div>

_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 😃

thanks.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 3:58pm UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/11 "2004-08-03T15:58:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 3, 2004, 4:22pm UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/12 "2004-08-03T16:22:18Z")

</div>

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:

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 4, 2004, 7:28am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/13 "2004-08-04T07:28:08Z")

</div>

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.

```php
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]

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 4, 2004, 3:52pm UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/14 "2004-08-04T15:52:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 4, 2004, 4:29pm UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/15 "2004-08-04T16:29:15Z")

</div>

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

```php

 <?
 $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!

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 4, 2004, 5:33pm UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/16 "2004-08-04T17:33:31Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 5, 2004, 5:46am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/17 "2004-08-05T05:46:43Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 5, 2004, 5:59am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/18 "2004-08-05T05:59:47Z")

</div>

> 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 😃

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/kirupa/original/3X/6/2/621e5c11736f46532526e61be85940af4230f3e5.png) [@system](https://forum.kirupa.com/u/system)\
**Post date:** [August 5, 2004, 7:22am UTC](https://forum.kirupa.com/t/dates-with-mysql/59713/19 "2004-08-05T07:22:10Z")

</div>

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