Querying by Date
In this final installment in this series on Dates and Times in MySQL, we'll be putting everything we've learned thus far into practice by writing SELECT queries to obtain date-related insights into our data.
Selecting a Date from a Datetime Column
One of the first challenges database practitioners encounter when trying to query with dates is that a good deal of temporal data is stored as DateTime and Timestamp data types. For example, the Sakila Sample Database stores the customer table's create_date column as a Datetime:
Hence, if we try to select customer records that were created on a specific date, we can't simply supply a date value:
One simple workaround is to convert the Datetime values to Dates by using the DATE() function:
Now any record whose date matches ours will be returned.
Obtaining the Difference Between Two Dates
It is extremely common to perform queries that determine how long ago something happened. In MySQL, the way to do that is to employ the DATEDIFF() function. It accepts two date values and returns the number of days between them. Here's a simple example using Navicat for MySQL 16:
Notice that, in the above example, DATEDIFF() is telling us that the first date is 10 days later than the second one. We can also use an earlier date for the first argument and it will return a negative value:
Calculating Periods Other than Days
For periods other than days, we need to do a little conversion. For example, we can divide by 7 to obtain the number of weeks between two dates. Rounding is also employed to show whole weeks in the results:
ROUND(DATEDIFF(end_date, start_date)/7, 0) AS weeksout
For other time periods, the TIMESTAMPDIFF() function may be of help. It accepts two TIMESTAMP or DATETIME values (DATE values will auto-convert in MySQL) as well as the unit of time we want to base the difference on. For instance, we can specify MONTH as the unit in the first parameter:
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-04') -- Outputs: 0 SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-05') -- Outputs: 1 SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-15') -- Outputs: 1 SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-12-16') -- Outputs: 7
A More Complex Example
Once you've got the hang of the DATEDIFF() function, you can using it in more advanced ways. Case in point, here's a query that uses the DATEDIFF() function to calculate the average number of days that customers keep their film rentals before returning them:
To do that, the results of the DATEDIFF() function is passed to the AVG() function and then rounded to 1 decimal place.
Series Conclusion
We've covered a lot of ground in this series on Dates and Times, including:
- MySQL's five temporal data types
- some important date/time-oriented functions
- how to create dates and times in MySQL
- querying by date
While there certainly is a lot more to working with temporal data in MySQL, hopefully this series gave you a good head start on your road to MySQL proficiency.
