How to find average of time in Zoho Reports?
In Zoho reports, we have only Date data type that is used to store both date and time. We cannot store Time value only. Only way to store only time in Date column is to specify the format of column with only time in it like hh:mm:ss a, HH:mm etc. But when we try to find average of the only time value in Date field then it does not allow us to find its average directly. For this purpose, we have some solutions like to type cast the values as Float value in Query. But this also does not work for zoho reports. It shows error on casting the value to Float as Float is not a data type in zoho reports.
To solve this problem, the simplest way is to convert the time in seconds and finding the average of seconds and lastly convert it back to time.
We will have to use three functions of zoho reports here:-
SELECT SEC_TO_TIME(AVG(TIME_TO_SEC("Date Created"))) as "Average Time", "User" FROM "Example Table" GROUP BY "User"
This Query is finding the average time of each user of table “Example Table”.
“Date Created” is the column name here which is of data type “Date” but contains time only.
“TIME_TO_SEC” function taking “Date Created” as argument and is converting the time to seconds.
“AVG()” function is used to finding the average of the seconds retrieved from “TIME_TO_SEC” function.
“SEC_TO_TIME()” function is used to converting the average we get from AVG function to time again.