MYSQL now-DateX

[Nassauer]

Hi,

in a MYSQL-Database table 'users' I have stored data of users of a message board.
There is a user_lastlogon field and a user_posts field.

I want to delete all users, who have not logged on to the board for more than 6 month and have 0 posts.

So I tried a query like that:

DELETE FROM users WHERE now()-user_lastlogon>.... AND user_posts=0

But I've problems with the date's format.
The lastlogon is e.g. 1061834640 for 25.08.2003 20:04. So I cannot substract that from now() properly.

Can you help me out? (I'd like to use just a query not php or such stuff)

 

[sleipnir214]

What column-type is user_lastlogon, and what is the source of values in that column?

If your user_lastlogon is some kind of Unix typestamp, you might use something like unix_timestamp (now())

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Nassauer]

The column-type is

user_lastlogon int(11)

 

[sleipnir214]

and what is the source of values in that column?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Nassauer]

There is a php-function I've no access to that enters the values in that column

 

[sleipnir214]

Without knowing what those values are, it's difficult to advise you.

If those values are Unix Current Epoch time ticks, then you can use unix_timestamp(now()) (

http://www.mysql.com/doc/en/Date_and_time_functions.html)

in your query.

But before you implement by advice, verify the format of the values in user_lastlogon. I would do this by using PHP's date() function with values from user_lastlogon to see if the values retured are reasonable.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Nassauer]

Thanx a lot! unix_timestamp -> that's it!

 

[Nassauer]

unix_timestamp was correct, but when I use the following query

SELECT * FROM phpbb_users WHERE unix_timestamp(now())-user_regdate > unix_timestamp('1970- 06 -01 01:00:00') AND user_posts=0

that shoul return all users that have 0 posts on the message board AND HAVE REGISTERED 6 month ago, the query returns ALL users with 0 posts.....

Has anyone got an idea why that is?