CODEWITHSUNDEEP
mysqlsqltimestamp
somejkuser
somejkuser

Reputation: 9040

MySQL Determine if user has logged in within X days

I am creating a sql query that determines if the user has been a member for one year and if so than sends an email. My logic tells me I should also ensure that they have been active within that past year of service. Here is my sql query:

SELECT * FROM users WHERE FROM_UNIXTIME(date_created,'%Y-%m-%d') + INTERVAL 365 DAY = CURRENT_DATE() AND FROM_UNIXTIME(last_login,'%Y-%m-%d') + INTERVAL 365 DAY >= CURRENT_DATE()

My assumption is that if the last_login + 365 day is greater than or equal to the current date, that means they've logged in within the 365 day.

Last Login is a TIMESTAMP. Is that a correct SQL Query?

Upvotes: 0

Views: 108

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78433

Your query is currently returning the set of users who were created precisely a year ago and who logged in since. Don't you want the users who were created up to a year ago and who logged in? i.e. >= CURRENT_DATE() instead of = CURRENT_DATE().

Upvotes: 1

tadman
tadman

Reputation: 211560

The only way to know if it's a valid query is to test it, and the only way to test it is to create data that straddles the boundary conditions.

Do make a point to see what happens on February 29th, 2014 as well, or to people who last logged in on that day.

Upvotes: 0

Related Questions