Hi Guys,

I have a lastused field, How do I select rows that are OLDER than 3 months " so i can mail the user saying, Use it or you will be deleted "

The field is a timestamp in this format 2009-11-08 12:56:31


Thanks in advance.
Graham

use the DATEDIFF function.

That seems ok if i know the dates though? Or am i missing something..

I was looking for something like
select * from blaa where lastused >= 3months;

Thanks
G

That seems ok if i know the dates though

??

You know the "last used" date and you obviously know (or the server does) the current date with the date() or now() functions so

PHP Code:

SELECT id FROM table WHERE DATEDIFF("m",lastused,now()) > 3 ; 


Should work (not tested)

Ahhhhh
I seee

Thanks for your help, ill give it a try.

Hi There,
Just gave it a try an this is what happens:
SELECT username FROM admins WHERE DATEDIFF("m",lastused,now()) > 3 ;


SQL query: ?
SELECT username
FROM admins
WHERE DATEDIFF( "m", lastused, now( ) ) >3
LIMIT 0 , 30
MySQL said: ?
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'now()) > 3
LIMIT 0, 30' at line 1


Any thoughts?

My SQL is terrible when i comes to more than a simple insert select etc


Thanks
G

print the full query to screen and paste into a post

im not sure what you mean by print to screen?

Heres an output from the sql cli.

mysql> SELECT username FROM admins WHERE DATEDIFF("m",lastused,now()) > 3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'now()) > 3' at line 1
mysql>

Ok so it's in MySQL then.

SELECT username FROM admins WHERE DATEDIFF(lastused,now()) > 90 ;

Ah ok, That works, but doesnt return any rows.

Im assuming 90 is days?

Ive changed it to lower to even 1 where I know for afact theres plenty of rows that have last used dates longer than that but still 0 rows?

Any thoughts?

heres all the last used rows.

mysql> SELECT lastused FROM admins order by lastused desc;
+---------------------+
| lastused            |
+---------------------+
| 2009-11-13 13:37:17 | 
| 2009-11-13 13:37:16 | 
| 2009-11-13 13:37:15 | 
| 2009-11-12 05:05:31 | 
| 2009-11-10 22:57:44 | 
| 2009-11-10 11:14:25 | 
| 2009-11-10 01:17:36 | 
| 2009-11-09 06:33:28 | 
| 2009-11-09 01:53:15 | 
| 2009-11-08 22:55:48 | 
| 2009-11-08 22:37:43 | 
| 2009-11-08 22:34:01 | 
| 2009-11-08 12:56:31 | 
| 2009-11-08 05:47:40 | 
| 2009-11-08 04:13:12 | 
| 2009-11-08 03:25:42 | 
| 2009-11-08 00:09:42 | 
| 2009-11-05 17:21:02 | 
| 2009-11-05 00:19:10 | 
| 2009-11-05 00:18:48 | 
| 2009-11-04 16:39:59 | 
| 2009-11-03 14:05:34 | 
| 2009-11-02 17:30:48 | 
| 2009-10-31 17:27:29 | 
| 2009-10-31 13:53:37 | 
| 2009-10-30 19:14:01 | 
| 2009-10-29 08:51:39 | 
| 2009-10-28 00:02:46 | 
| 2009-10-27 23:25:03 | 
| 2009-10-27 23:13:11 | 
| 2009-10-27 22:24:31 | 
| 2009-10-27 19:21:23 | 
| 2009-10-25 02:16:41 | 
| 2009-10-24 20:06:15 | 
| 2009-10-23 21:28:18 | 
| 2009-10-21 19:56:56 | 
| 2009-10-21 19:42:04 | 
| 2009-10-21 14:30:23 | 
| 2009-10-20 22:51:19 | 
| 2009-10-16 03:48:50 | 
| 2009-10-14 17:47:02 | 
| 2009-10-11 02:03:30 | 
| 2009-10-11 00:49:11 | 
| 2009-10-10 18:53:08 | 
| 2009-10-07 14:21:07 | 
| 2009-10-07 07:37:24 | 
| 2009-10-06 22:28:09 | 
| 2009-10-05 16:15:54 | 
| 2009-10-01 20:18:28 | 
| 2009-10-01 16:34:55 | 
| 2009-09-29 20:34:18 | 
| 2009-09-28 20:56:35 | 
| 2009-09-26 19:07:59 | 
| 2009-09-26 13:30:33 | 
| 2009-09-25 17:27:30 | 
| 2009-09-25 16:22:52 | 
| 2009-09-22 12:13:41 | 
| 2009-09-20 18:14:13 | 
| 2009-09-18 05:42:05 | 
| 2009-09-16 19:56:05 | 
| 2009-09-13 10:34:26 | 
| 2009-09-13 01:10:56 | 
| 2009-09-12 11:13:48 | 
| 2009-09-07 07:03:23 | 
| 2009-09-06 06:00:21 | 
| 2009-09-05 19:07:11 | 
| 2009-09-05 07:21:54 | 
| 2009-09-01 02:17:34 | 
| 2009-08-31 21:26:58 | 
| 2009-08-31 10:22:58 | 
| 2009-08-26 16:29:40 | 
| 2009-08-26 11:52:33 | 
| 2009-08-25 05:42:42 | 
| 2009-08-25 00:53:33 | 
| 2009-08-22 23:02:11 | 
| 2009-08-22 16:02:04 | 
| 2009-08-20 16:56:30 | 
| 2009-08-19 22:26:55 | 
| 2009-08-19 08:04:13 | 
| 2009-08-18 03:34:41 | 
| 2009-08-17 03:10:21 | 
| 2009-08-16 04:21:48 | 
| 2009-08-14 09:46:52 | 
| 2009-08-12 16:28:09 | 
| 2009-08-11 20:58:04 | 
| 2009-08-09 15:00:28 | 
| 2009-08-09 14:54:26 | 
| 2009-07-28 12:38:39 | 
| 2009-07-26 06:28:08 | 
| 2009-07-26 00:49:01 | 
| 2009-07-25 12:31:33 | 
| 2009-07-24 21:08:15 | 
| 2009-07-24 19:59:12 | 
| 2009-07-23 02:39:51 | 
| 2009-07-21 21:29:59 | 
| 2009-07-20 13:02:12 | 
| 2009-07-20 12:57:22 | 
| 2009-07-20 08:58:16 | 
| 2009-07-18 11:35:50 | 
| 2009-07-18 00:22:13 | 
| 2009-07-17 13:34:59 | 
| 2009-07-15 18:47:12 | 
| 2009-07-15 18:16:22 | 
| 2009-07-15 18:04:36 | 
| 2009-07-11 08:21:47 | 
| 2009-07-09 01:31:40 | 
| 2009-07-08 11:21:32 | 
| 2009-07-06 06:42:25 | 
| 2009-07-01 16:08:44 | 
| 2009-06-29 16:52:28 | 
| 2009-06-28 05:25:46 | 
| 2009-06-25 15:38:14 | 
| 2009-06-24 03:57:30 | 
| 2009-06-23 05:28:45 | 
| 2009-06-21 23:34:30 | 
| 2009-06-15 12:18:53 | 
| 2009-06-11 11:19:20 | 
+---------------------+
117 rows in set (0.00 sec)

No idea off the top of my head. I don't have your all of data or your server to test with.

try reversing the operator and use < instead

Ah ha,
Think ive figured it:

SELECT username FROM admins WHERE DATEDIFF(now(),lastused) > 90 ;

I swapped round the NOW() and the lastused.

Thanks for your help

-G