Read my latest article: Ezra Zygmuntowicz -- Farewell, Friend. (posted Mon, 01 Dec 2014 17:53:00 GMT)

Using BETWEEN for SQL comparisons

Posted by Sat, 14 Nov 2009 20:55:00 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

Put Your Controllers on a Diet already!

Posted by Tue, 19 Jun 2007 13:16:00 GMT

If you’re working with Ruby on Rails and are looking for ways to improve your existing code base, I would encourage you all to read the following blog posts.

Hopefully… you’ve already read each of them and as a result… put your controllers on a diet.

Those that Tend the Store need Dialogue

Posted by Thu, 04 Jan 2007 23:18:00 GMT

I’ve been keeping my eye on a series of blog posts by Chad Fowler, which he calls The Big Rewrite.

Today, Chad posted an entry titled, Who’s Tending the Store? He writes…

“the experts keep the old system running while the new system is being built. So, who builds the new system? Not the experts, that’s who. Usually, it’s people like me: technology experts. And while we’re banging away at the existing system’s UI, trying to figure out what needs to be coded, the domain experts are doing their jobs. Unfortunately, this means the domain experts aren’t watching the Big Rewrite very closely. Regardless of how good the team, if communication is impaired between the domain experts and the technology experts, things are going to move slowly, and wrong software is going to be created.”

I wanted to follow up on this issue as it’s an area of great interest to me.

I feel like this issue runs deeper and while it’s important to be mindful of the communication between domain and technology experts, it’s a good idea for each of us to take a break every few days (or everyday) to assess our perceptions in all areas of the project. More specifically, I’m suggesting that in order for us to be effective in our communication, we must make time to refactor our perceptions about the state of a project. From the design, to the development, to team communication, to the schedule, and all the way to customer satisfaction… or what Martin Fowler calls, Customer Affinity. These things are not static and we must see them as extremely dynamic variables… much more dynamic than our wonderful language of choice.

When Brian Ford and I started discussing Dialogue-Driven Development (d3), we were initially focused on an area that always seems to come up in projects. Client communication. From managing expectations to delivering the right product, d3 has become an essential tool in our team’s tool belt. We refactored our entire Design and Development process (and it’s always evolving) to focus on the things that we felt were the most important to a successful project. Clients come to us in search of expertise and guidance so that we can build them innovative solutions. When it comes to this process, clients deserve simplicity.

For starters, we’re misguided

If there is one thing that I have learned, it is that our initial perceptions are often misguided. We have to work really hard to think critically, not only of the problem we’re trying to build a solution for, but also of how we, ourselves, are actually looking at the problem. It’s easy to fall victim to tunnel vision. I often find myself having to take a step back from problems on a very regular basis. While I have no scientific proof to back this, it seems to feels natural for us to keep firing once we pull the trigger. It’s important to re-aim.

Chad raises a very important topic and leaves readers to think about the problem. After thinking about this, it’s my opinion that in order for the domain and technology experts to be effective, they need healthy collaboration. But, I feel like this applies to many other areas of our process as well.

Is there even a problem?

So, what is the solution? Better yet, what is the problem? Is there even a problem?

How can we avoid situations where communication becomes impaired? We’ve all been there. We know how to spot impaired communication, but how can we spot it… before we perceive it as too late? How can we recover from it? What causes the communication to break down? What if… it were possible to repair the situation?

These questions don’t have easy answers. These are complicated problems that reach far beyond the development community. These are the same problems that all members of organizations, communities, countries, and planets all face, each and every day.

Take action!

While it’s important to make sure we’re engaging in healthy dialogue through a project, bad things will happen. They are inevitable. As Agilists, we’re accepting this as a fact of (project) life and should be prepared to take action. If you see communication being impaired, it’s time to step up and help the team out. Otherwise, you’re only hurting yourself… and your colleagues.

“Be the change you wish to see in the world.”—Mahatma Gandhi

If these sorts of topics are of interest to you, I encourage you to join the Dialogue-Driven community and help us figure this stuff out!

Ruby on Rails, Refactoring Databases, and the Legacy System

Posted by Tue, 11 Apr 2006 01:11:00 GMT

1 comment Latest by Frederick Ros Wed, 12 Apr 2006 05:14:01 GMT

To avoid feeling like I have neglected my blog lately… I wanted to point out a few things.

First of all… there is a nice new book that was sitting at Powells technical bookstore just a few hours ago… and is now sitting on my desk. I read a few pages on the bus ride home today of Refactoring Database: Evolutionary Database Design. It’s a Martin Fowler signature book and you can read more by Scott W. Ambler (one of the authors) at Agile Data. So far it seems like a good book if you’re into database schemas and if your a fan of the Refactoring scene. :-)

In other news… Jeremy and I are preparing for our journey up to Vancouver, B.C. this week. Jeremy will be presenting his talk about i18n and Ruby on Rails… and I’ll be presenting my talk about using Ruby on Rails with Legacy databases. I look forward to meeting those of you who are making the trip to Canada on Rails (which is sold-out!) and if you don’t catch us there… we will also be presenting at RailsConf 2006!

If you’re going to be in Vancouver this week and would like to meet us for some drinks, hacking, or whatever… stop by on #canadaonrails (irc.freenode.net) and let us know. :-)

Related Post(s)

Refactoring and the Pacific Ocean

Posted by Tue, 22 Nov 2005 12:59:00 GMT

I am currently sitting in a hotel about 40 miles north of San Francisco. One more day on-site and then I fly home to Portland, so I can drive to Seattle for a day of rest. I might be back down here in a week or so.

I took a photo of the luggage of Jeremy... it was too funny to pass up.

Jeremy Refactors Luggage

He takes great pride in his packing… just like he does with the code that he designs. It was great that we fit in a few minutes for him to take his first real look at the Pacific Ocean.

jvoorhis sees the Pacific

I’ve been meaning to post those… so there you have them. :-)

Refactoring Rails... coming soon

Posted by Thu, 27 Oct 2005 19:30:00 GMT

As you may have heard earlier, Jeremy Voorhis and I are working on a top-secret project together. We’re going to keep things quiet for just a bit longer while we get an initial site together. In the meantime, sign up on our mailing list to be notified when we launch it.

We present to you…. Refactoring Rails.

Jeremy posted a short teaser on his blog and we’ll just keep you in suspense… but keep an eye out in the coming week(s). :-)

For more information, bookmark: http://www.refactoringrails.com

“Remove ambiguities and convert to specifics”Brian Eno, Oblique Strategies

Older posts: 1 2