CODEWITHSUNDEEP
mysql
ComputerUser
ComputerUser

Reputation: 4888

MySQL Where date is greater than one month?

I have a datetime column called 'last_login'.

I want to query my database to select all records that haven't logged in within the last month. How do I do this?

This is what I have currently:

$query = $this->query("SELECT u.id, u.name, u.email, u.registered, g.name as group_name FROM `:@users` AS u LEFT JOIN `:@groups` AS g on u.group_id = g.id WHERE u.last_login = ...... LIMIT {$limit_start}, {$limit_end}");

:@ = database prefix

Upvotes: 23

Views: 31249

Answers (3)

Matt Healy
Matt Healy

Reputation: 18531

Try using date_sub

where u.last_login < date_sub(now(), interval 1 month)

(Similar to the first answer but in my mind it is more "natural" to use positive integers)

Upvotes: 40

delta5
delta5

Reputation: 9

matthewh was almost correct, except the > should have been a right one.

where u.last_login > date_sub(now(), interval 1 month)

Upvotes: -1

T.J. Crowder
T.J. Crowder

Reputation: 1074555

You can use date_add combined with now:

...where u.last_login < date_add(now(), interval -1 month)

Naturally, as both are MySQL-specific this limits you to MySQL backends. Alternately, you can figure out what the date was a month ago with PHP (I'm not a PHP person, but I'm guessing DateTime::sub would help with that) and then include that date in your query in the normal way you would any other date/time field.

Upvotes: 13

Related Questions