0

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!

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.