Calculating average time from DateTime field in MySql might come in handy in cases like getting the average arrival time, departure time, service sampling etc. In my case, I have a database with records on my arrival to work, and I wanted to calculate time average time that I arrive by the office, and then calculate how much time I spend in traffic, depending on when I leave my home.
In this post, I’ll show how I calculate the arrival time to the office, as a part of calculating my average daily commute to the office.
Averaging DateTime in MySql
When we deal with the time average, the most logical operation is to average the time part of the date, like hours and minutes. Cross dates average might be useful in some cases, but for my application, only the time of day is what I need.
The query below takes the column “stime” which is a DateTime type and extracts from it hours, minutes and seconds. Hours and Minutes are converted into Seconds for the average calculation. After that, an avg() function is run on the seconds, and finally converts to readable hours and minutes, that represents the time of day
|
1 2 3 4 |
SELECT TIME_FORMAT(SEC_TO_TIME(avg(hour(stime) * 3600 + (minute(stime) * 60) + second(stime))),'%H:%i') as AvgArrivalTime from MyProject.LocationTable where placeID = 2 and month (stime)=7; |
For the simplicity of the query, I assigned the place (2 is work) and the month, 7, for July.
Table with arrival times
Average Arrival Time after MySql Calculation
I’ll show in future posts how I calculate the average commute to the office by the time of day, let me know in the comments below if this is something that you find interesting.