Compare dates in MySQL

Handling date comparisons efficiently

Need to compare a date range? Lean on the trusty BETWEEN. It simplifies your life by reducing multiple AND conditions:

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';


This commandeers all the events happening in January 2023. Both ends included, no man left behind!

Precision matters: Date vs. Datetime

Watch the road for DATETIME and TIMESTAMP fields potholes when you only need date parts compared. Here DATE() rides to your rescue:

SELECT * FROM purchases WHERE DATE(sale_timestamp) = '2023-04-01';


Our hero fetches all purchases made on April 1st, 2023, no matter the time of day. Time Busters, who you gonna call?

Common traps in date comparison

Lost in the maze of mixing DATETIME with DATE? It leads to unwanted surprises. If you compare DATETIME directly to a date string, it assumes 00:00:00 as the time component:

-- Here lies pitfalls, comparing datetimes to date strings, unattended times included.
SELECT * FROM table WHERE datetime_column = '2023-04-01';


Avoid this trap by always extracting the date part for your comparisons.

Working with time intervals

Working with time intervals? Consider INTERVAL your newest best buddy. No need for headache-inducing logic for next day or three months ago:

SELECT * FROM log WHERE entry_date > CURDATE() - INTERVAL 1 DAY;


That simply gathers all log entries since yesterday.

Need more refined control? Take the interval by the hour, minute, and second:

-- From the creators of "Back to the Future", now presenting "Messages from the last hour".
SELECT * FROM messages WHERE sent_time > NOW() - INTERVAL 1 HOUR;


This reels in messages sent in the last 60 minutes.

Keeping date formatting consistent

Keep the style police away and use DATE_FORMAT(). It ensures your dates are dressed in the format you desire:

SELECT DATE_FORMAT(dob, '%Y-%m-%d') as formatted_date FROM users;


This chic query will fetch the dob column all dolled up in ISO format of 'YYYY-MM-DD'.