In previous blog, we tabulated the average daily counts for a given column in SQL Server using Navicat for SQL Server. In today's follow-up, we're going to raise the difficulty factor slightly by calculating the daily average date/time interval that is based on start and end date columns. For demonstration purposes, I'll be working with MySQL using Navicat Premium.
Calculating Movie Rental Durations in Days
In the Sakila Sample Database's rental table there are two date fields that represent a time interval: they are the rental and return dates. These, of course, store the date and time that a film was rented, and when it was returned.
With that in mind, suppose that we needed to write a query that shows the average length of movie rentals for each day. The first step would be to calculate the length of all movie rentals. Here's what that query would look like in Navicat:
To convert the rental_date from a datetime to a pure date we can use the DATE() function. It accepts any valid date or datetime expression.
The number of days is calculated using the MySQL DATEDIFF() function. It returns the number of days between two dates or datetimes. Navicat can help us use the DATEDIFF() function by providing auto-complete. When you start to type a word, a popup list appears with suggestions for everything from schemas, tables/views, columns, as well as stored procedures and functions. Here is the DATEDIFF() function in the suggestion list:
After you select a function, it gets inserted into your code at the cursor position with tabbable, color-coded, input parameters for quick entry:
A Word about Times
For shorter timeframes, you can use TIMEDIFF() instead of DATEDIFF(). It returns the time difference in seconds. For longer intervals, you can divide by 60 for minutes and another 60 for hours.
Grouping Results by Day
The next step is to group results by day. This is done via the GROUP BY clause. It allows us to apply aggregate functions such as COUNT() and AVG() to the number of days that rentals were out for each rental_date. Here is the updated query with the GROUP BY clause:
You'll notice that I rounded the avg_days_rented to one decimal place. Otherwise, we'd get 4 points of precision, which may be a bit much for our purposes!
Conclusion
Thanks to MySQL's many date/time functions, calculating the daily average date/time interval based on start and end date columns is made a lot easier than it otherwise might be. Moreover, Navicat's feature-rich SQL Editor further simplifies query writing by providing auto-complete for just about any database entity, including schemas, tables, columns, as well as functions and stored procedures.
Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial organizations. In his spare time, Rob has become an accomplished guitar player and has released several CDs and digital singles. You can hire Rob by emailing him at This email address is being protected from spambots. You need JavaScript enabled to view it. .
