Using BETWEEN for SQL comparisons
Recently, Carlos, suggested that I should start sharing some basic SQL tips that help with performance and/or general usage. I recently came across some code that I didn’t like to read and/or write. For example, let’s take the following…
SELECT * FROM brochures WHERE published_at <= now() AND archived_at >= now()
Essentially, this is pulling back some data WHERE the the brochures are considered published. (We have a project that allows people to manage their brochure launch dates ahead of time.) In fact, in this project, we have no less than 6-8 dates in the database that we’re comparing data on and it’s easy to get lost in the logic when trying to understand it.
Now, there isn’t anything inheriently wrong with how this condition is constuctued. As a matter of personal taste, I find it annoying to mentally parse. Also, I find having to write now() more than once in a WHERE clause to feel like I’m repeating myself.
Read it outloud…
“WHERE the brochures published at date is less than and/or equal to right now AND the archived date is greater than and/or equal to now.”
Who talks like that?
Luckily, there is a better and in my opinion, a more readable way to express this is with the BETWEEN construct in SQL. (postgresql docs, mysql docs)
SELECT * FROM brochures WHERE now() BETWEEN published_at AND archived_at
Let’s read this outloud…
“WHERE the current date is between the published at and archived at dates.”
This sounds more natural to me.
Additionally, you can also do the inverse with NOT.
SELECT ... WHERE now() NOT BETWEEN brochures.published_at AND brochures.archive_at
Remember kids, “code is for humans first and computers second.”—Martin Fowler
