Goal: Search all users who were updated between a certain time period (in our case one day).
Scenario: A system wide glitch caused a set of users to have all of their last names changed to Smith. Your manager wants to know how many records are affected.
MySQL offers 2 or more ways this can be accomplished. First using <= and >= the second using BETWEEN operator.
The between operator is identical to >= and <= and is inclusive. Notice the difference in order between the 2 queries.
In order for this query to work the field must be either datetime, timestamp, or date. If using date then remove the time portion from the query.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select * from users where updated_at <= '2014-12-25 23:59:59' AND updated_at >= '2014-12-25 00:00:00'; | |
| select * from users where updated_at BETWEEN '2014-12-25 00:00:00' AND '2014-12-25 23:59:59'; |
Demo on SQL Fiddle.
Share this Story
