I’ve been working on a project that required some date based MySQL queries. I’m not sure what it is, but I always find date based queries a little tricky. Seems that the data I need is never in the format I want or I’m always looking for data from a few days back and need to do some “date math” to get there. Unfortunately, I don’t work with MySQL everyday, so the necessary functions and data type restrictions to retrieve what I need aren’t in the forefront of my memory. Keeping that in mind, I thought I would post a quick MySQL tech-tip for anyone else that occasionally struggles with date centric MySQL queries.

MySQL query requirement

Here is what I need to do with my custom MySQL query: View all of the rows of data entered into a specific table 2 days ago. The starting point for 2 days back is always based on the current date. For example, if today is 1/16/2008, then I’m looking for data from 1/14/2008.

Data restrictions for the requirement

I’m working from a 10 column table that includes a timestamp column. This column is of type Date Time so I’ll need to leverage this column to extract the data from 2 days ago; however, since the column is of type Date Time it contains the date and the time for a particular data row. Also, I’m interested in all of the data from 2 days ago regardless of the time for a particular row of data. Therefore I’ll need to take that into consideration as I write my query.

MySQL functions needed for date manipulation

To start, I need a way to determine the current date. Thankfully, MySQL has a very simple and intuitive function called CURDATE(); however, CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE() and work exactly the same. And while CURDATE() is shorter and quicker to type, I plan to use CURRENT_DATE() for future readability.

Next, I need a way to do some “date math” so I can figure out the date for 2 days ago based on today. For that I’ll use DATE_SUB(). This function can also be called SUBDATE(), but again, for future readability I’ll use DATE_SUB() which takes two arguments: the date from which to start the calculations and the interval expression unit.

For example, if I wanted to calculate the date from 2 days ago from today (1/16/2008), then I’d type the following:

DATE_SUB('1/16/2008', INTERVAL 2 DAY)

Likewise, if I wanted to determine what the date was 2 months ago from today, then I’d type the following:

DATE_SUB('1/16/2008', INTERVAL 2 MONTH)

However, since my stating date is always based on today, then I’ll call this function in the following manner:

DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)

Extract the date from a column containing date and time

Finally, since I’m trying to match the data stored in my timestamp column, I need to extract just the date portion of the value stored. Because if I try to match just a “date” against “date and time,” my query won’t return any results. Therefore I’ll need to use the DATE() function in the following manner to extract just the date from this column and then perform my match:

DATE(timestamp)

Now, using the DATE(), DATE_SUB(), and CURRENT_DATE() functions I can view all of the transactions in my PURCHASES table from 2 days ago.

SELECT * FROM purchases WHERE DATE(timestamp) = DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY);

Date based queries are easier than you think

Queries that require some form of date manipulation are always a little strange (at least they are for me). Fortunately, MySQL offers several built in functions to make these types of queries much easier. For a complete list of the available functions along with further explanation on the allowed arguments (such as the ones allowed for DATE_SUB()), check out the MySQL online function reference and the MySQL date time type definitions.

Share, Bookmark, or Email this post

If you liked this post, subscribe to TechTraction's RSS feed or TechTraction's email feed

Filed under: How-To & Tech Tips