To search between dates in MySQL, you can use the BETWEEN or comparison operators (>=).
For example, if you want your SQL query to return all posts that were published in a given month, you could do something like this:
SELECT title, date FROM news WHERE (date BETWEEN '2023-12-01' AND '2023-12-31');The SQL statement above uses the BETWEEN operator to return all rows that are dated between December 1st and December 31st, 2023.
Alternatively, you can also use MySQL’s greater than, equal, and less than operators:
SELECT title, date FROM news WHERE date >= '2024-01-01' AND date <= '2024-12-31';The statement above returns results within a given date range. In this case, we are searching for posts that were published in 2024.
To do this, we return rows that:
- Bigger than or equal to 2024-01-01 and
- Less than or equal to 2024-12-31.
Note that this will only work if you are storing your dates and timestamps in a DATE or DATETIME column. Using TEXT, CHAR, or VARCHAR will not work, as MySQL does not recognize text types as dates.
If you have made this mistake, then you will need to change the column type to the correct format before proceeding.
Time gets larger with each second. Consequently, a future date will be “larger” than a date that occurred in the past. This is an important concept to grasp if you plan on using comparison operators to compare or search between dates.
For example, the unix timestamp for December 21, 2024, is going to be smaller than the timestamp for December 22, 2024.
You can also limit your search results to a specific timeframe. The following SQL statement will return all rows that have a time between 1 p.m. and 2 p.m.:
SELECT title, date FROM news WHERE (date BETWEEN '2023-10-04 13:00:00' AND '2023-10-04 14:00:00');The query above selects all posts that were published between 1 p.m. and 2 p.m. on October 4th, 2023. As you can see, all timestamps are in a 24-hour format.