Using BETWEEN for SQL comparisons
10 comments Latest by ed hardy sunglass Wed, 10 Mar 2010 08:12:30 GMT
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
Enjoying the content? Be sure to subscribe to my RSS feed.
Indeed, much nicer. When you provide a Range to ActiveRecord’s :conditions it generates a BETWEEN.
From http://api.rubyonrails.org/classes/ActiveRecord/Base.html
Is there a rails statement, function for between?
I just learned some sub-selects using GROUP BY. Still amazed a week later. Where have I been all these years?
This was an excellent read. I am glad you shred it with us.
chi flat iron chi hair straightener chi hair irons chi hair tools
Thank you for your sharing. Looking For discount jordan shoes? The store online sells the air jordan shoes. Welcome to visit and buy michael jordan shoes.
Thank you this article I hope you have an enjoyable day. gucci shoes gucci handbags gucci handbags gucci belts
good article and enjoy it
good
Thank you for your post! Thant is great!