MySQL SELECT before or after a datetime

Filtering a MySQL SELECT query based on a DateTime being before, after, within or older than is a common need. It can be done a few ways with plenty of flexibility.

All you need is a DateTime type column. This could be when that row was inserted, updated or a value that is foreign.

To select results from now through to 1 week old:

SELECT * FROM `tablename` WHERE `datecol` < (NOW() - INTERVAL 1 WEEK);

NOW(); is the current MySQL servers date and time, whilst INTERVAL is the value for calculation.

This can be many measures of time type from seconds to minutes, days, weeks months and years.

Examples for intervals are:

INTERVAL 12 HOUR
INTERVAL 1 DAY
INTERVAL 3 MONTH
INTERVAL 1 YEAR
INTERVAL 3 DAY
INTERVAL 1 HOUR

Another method is to use an actual DateTime value:

SELECT * FROM `tablename` WHERE `datecol` > '2020-01-20 11:00:00';

This query returns all values where datecol is past or after 2020-01-20 11:00:00 meaning a value such as ‘2020-01-20 11:04:52’ will be included.

Switching > for < flips the results to be older/before than like ‘2020-01-20 10:58:04’.

Selecting data from between two dates:

SELECT * FROM `tablename` WHERE `datecol` BETWEEN '2020-01-20 00:00:00' AND '2020-01-21 23:59:59';

The query above will fetch values where their DateTime is in-between 2020-01-20 00:00:00 and 2020-01-21 23:59:59 such as 2020-01-21 08:51:44 or 2020-01-20 15:10:24