Is MySQL "between" clause not inclusive?

sql

datetime

timestamp

date

byAlex Kataev·Sep 11, 2024

Certainly, the MySQL BETWEEN clause is indeed inclusive. However, when dealing with DATETIME fields, these are by default initialized to 00:00:00 if no specific time is determined. So, to consider the full day (time included), specify 'YYYY-MM-DD 23:59:59' as your end value.

Here's an example that includes the full end date:

# Here's to catching all those midnight thoughts
SELECT * FROM thoughts_table
WHERE thought_datetime BETWEEN '2023-01-01' AND '2023-01-31 23:59:59';

Beyond the basics: Unveiling `between` & dates

Grasping the nuances of how MySQL handles the BETWEEN clause with DATE and DATETIME fields can be the ultimate game-changer in your data querying endeavors. It’s quite a breeze with DATE fields, but the plot thickens with DATETIME or TIMESTAMP fields.

Let's talk inclusive `between`

As per the MySQL official docs, BETWEEN is inclusive. It's simply a short-form for >= AND <=.

Decoding datetime

DATETIME or TIMESTAMP fields come with a time component that alters the between game. You can truncate the time or extend the end date to cover the whole day:

For whole day inclusivity, slap 23:59:59 to the end date.
If you want to focus only on the dates, go ahead and cast the datetime to a date.

Here's how you can truncate datetime:

# Casting away the time—like in fishing, but less exciting
SELECT * FROM users
WHERE CAST(dob AS DATE) BETWEEN '2023-01-01' AND '2023-01-31';

Time is of the essence

If time precision down to the last second is what you need, tagging 23:59:59 on your end date is your best bet. But handle time rounding or truncating functions with care, so you don't miss out on that precious second.

Nailing accurate time ranges

Datetime and Timestamp are two fields that could throw a wrench in your MySQL queries if not handled with precision. Here are some pro-tips for crusading these temporal terrains:

The timestamp precision game

When you want every tick of time accountable within your date range, accurately set the time to 23:59:59.

Datetime castaway

If time is your adversary and it seems irrelevant, go with the CAST. A DATE is simple to manage and eliminates risks of accidentally losing your end date.