MySQL Script to identify old accounts

Question

Need help with creating a MySQL query to create a list of users that have not logged into the site within the past 6 months.

Needs to contain Username, Email address and last login date.

Will use the list to send email regarding upcoming site purge.

Can use the CP and see them, but need to export to a XLS or CSV file.

Thanks!

Answer 1

Using 60 seconds × 60 minutes × 24 hours × 30 days × 6 months:

SELECT username, email, last_visit
FROM exp_members
WHERE (UNIX_TIMESTAMP(NOW()) - last_visit) > (60*60*24*30*6)
ORDER BY username
LIMIT 2000

Using 600000000 as MDDHHMMSS:

SELECT username, email, last_visit
FROM exp_members
WHERE ((NOW() + 0) - (FROM_UNIXTIME(last_visit) + 0)) > 600000000
ORDER BY username
LIMIT 2000

I used NOW() + 0 to convert dates like 2007-12-15 23:50:26 to 20071215235026.000000.

It possible to have little differences on the results of both queries, due to its precision, but it shouldn't be a problem if you want to run them more than one time.

If you customized the table prefix, you gonna need to adapt the query.

You can use this query to create a template which lists this users.