How to fetch records between specific times but relative dates?

Recently I had to write a report for a client that goes out everyday. The query was required to contain results between 09 30 AM yesterday and 09 30 AM today. I’m no great shakes in SQL and so I set about to use Google to find my answer.

Believe it or not, I had some hard time finding pages that gave me this information. A lot of potential sites came up and I poured through a lot of information and finally came across what I wanted. I’m posting it here again so the next time you need something like this, you can stumble upon this more easily.

SELECT * FROM table_in_question
WHERE date_in_question >= TIMESTAMP(DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY), ’093000′)
AND date_in_question < TIMESTAMP(CURDATE(),'093000');

The query is pretty simple and self explanatory so I will give you a very short explanation. The TIMESTAMP function of MySQL with two arguments will return an object after concatenating the date and time part from the arguments. This object’s data type is datetime which is what most people use to store times in the database. From here on it is the usual comparison in the where clause. To break it down further:

TIMESTAMP(DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY), ’093000′) today will return 2011-03-18 09:30:00.000

and

TIMESTAMP(DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY), ’093000′) tomorrow will return 2011-03-19 09:30:00.000

When you write a query like this, you let the database do some heavy lifting very easily as compared to writing that code yourself in the business and just passing it as arguments to the query.

Happy St. Patrick’s day & cheers!