Tycoon Talk
Become a Big fish!
The number 1 forum for online business!
Post topics, ask questions, share your knowledge.
Tycoon Talk is part of Freelancer.com - find skilled workers online at a fraction of the cost.

The Database Forum


You are currently viewing our The Database Forum as a guest. Please register to participate.
Login



Closed Thread
Select rows older than 3 months ( based on last used field )
Old 11-12-2009, 06:55 AM Select rows older than 3 months ( based on last used field )
lynxus's Avatar
Awesomeo-Maximo

Posts: 1,615
Location: UK
Trades: 1
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
__________________

Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


lynxus is offline
View Public Profile Visit lynxus's homepage!
 
 
Register now for full access!
Old 11-12-2009, 07:02 AM Re: Select rows older than 3 months ( based on last used field )
chrishirst's Avatar
Missing! presumed drunk.

Posts: 41,519
Name: Chris Hirst
Location: Blackpool. UK
Trades: 0
use the DATEDIFF function.
__________________
Chris. ->> Links are advertising NOT optimising!! <<-
A foolish consistency is the hobgoblin of little minds
Thought for today:- I SEO the only industry where all the cowboys are Indians?
chrishirst is online now
View Public Profile Visit chrishirst's homepage!
 
Old 11-12-2009, 09:02 AM Re: Select rows older than 3 months ( based on last used field )
lynxus's Avatar
Awesomeo-Maximo

Posts: 1,615
Location: UK
Trades: 1
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
__________________

Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


lynxus is offline
View Public Profile Visit lynxus's homepage!
 
Old 11-12-2009, 09:12 AM Re: Select rows older than 3 months ( based on last used field )
chrishirst's Avatar
Missing! presumed drunk.

Posts: 41,519
Name: Chris Hirst
Location: Blackpool. UK
Trades: 0
Quote:
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()) > 
Should work (not tested)
__________________
Chris. ->> Links are advertising NOT optimising!! <<-
A foolish consistency is the hobgoblin of little minds
Thought for today:- I SEO the only industry where all the cowboys are Indians?
chrishirst is online now
View Public Profile Visit chrishirst's homepage!
 
Old 11-12-2009, 09:39 AM Re: Select rows older than 3 months ( based on last used field )
lynxus's Avatar
Awesomeo-Maximo

Posts: 1,615
Location: UK
Trades: 1
Ahhhhh
I seee

Thanks for your help, ill give it a try.
__________________

Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


lynxus is offline
View Public Profile Visit lynxus's homepage!
 
Old 11-13-2009, 08:47 AM Re: Select rows older than 3 months ( based on last used field )
lynxus's Avatar
Awesomeo-Maximo

Posts: 1,615
Location: UK
Trades: 1
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
__________________

Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE



Last edited by lynxus; 11-13-2009 at 08:51 AM..
lynxus is offline
View Public Profile Visit lynxus's homepage!
 
Old 11-13-2009, 08:56 AM Re: Select rows older than 3 months ( based on last used field )
chrishirst's Avatar
Missing! presumed drunk.

Posts: 41,519
Name: Chris Hirst
Location: Blackpool. UK
Trades: 0
print the full query to screen and paste into a post
__________________
Chris. ->> Links are advertising NOT optimising!! <<-
A foolish consistency is the hobgoblin of little minds
Thought for today:- I SEO the only industry where all the cowboys are Indians?
chrishirst is online now
View Public Profile Visit chrishirst's homepage!
 
Old 11-13-2009, 09:07 AM Re: Select rows older than 3 months ( based on last used field )
lynxus's Avatar
Awesomeo-Maximo

Posts: 1,615
Location: UK
Trades: 1
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>
__________________

Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


lynxus is offline
View Public Profile Visit lynxus's homepage!
 
Old 11-13-2009, 09:29 AM Re: Select rows older than 3 months ( based on last used field )
chrishirst's Avatar
Missing! presumed drunk.

Posts: 41,519
Name: Chris Hirst
Location: Blackpool. UK
Trades: 0
Ok so it's in MySQL then.

SELECT username FROM admins WHERE DATEDIFF(lastused,now()) > 90 ;
__________________
Chris. ->> Links are advertising NOT optimising!! <<-
A foolish consistency is the hobgoblin of little minds
Thought for today:- I SEO the only industry where all the cowboys are Indians?
chrishirst is online now
View Public Profile Visit chrishirst's homepage!
 
Old 11-13-2009, 09:36 AM Re: Select rows older than 3 months ( based on last used field )
lynxus's Avatar
Awesomeo-Maximo

Posts: 1,615
Location: UK
Trades: 1
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.
Code:
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)
__________________

Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE



Last edited by lynxus; 11-13-2009 at 09:37 AM..
lynxus is offline
View Public Profile Visit lynxus's homepage!
 
Old 11-13-2009, 09:50 AM Re: Select rows older than 3 months ( based on last used field )
chrishirst's Avatar
Missing! presumed drunk.

Posts: 41,519
Name: Chris Hirst
Location: Blackpool. UK
Trades: 0
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
__________________
Chris. ->> Links are advertising NOT optimising!! <<-
A foolish consistency is the hobgoblin of little minds
Thought for today:- I SEO the only industry where all the cowboys are Indians?
chrishirst is online now
View Public Profile Visit chrishirst's homepage!
 
Old 11-13-2009, 10:17 AM Re: Select rows older than 3 months ( based on last used field )
lynxus's Avatar
Awesomeo-Maximo

Posts: 1,615
Location: UK
Trades: 1
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
__________________

Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


Please login or register to view this content. Registration is FREE

Please login or register to view this content. Registration is FREE


lynxus is offline
View Public Profile Visit lynxus's homepage!
 
Closed Thread     « Reply to Select rows older than 3 months ( based on last used field )
 

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off





   
RSS Feed  Feeds: RSS   JS   XML
RSS Feed  Feeds for this forum: RSS   JS   XML



Page generated in 0.34661 seconds with 12 queries