Time() function return Null

Question

I have a table having a 'duration' column which stores the duration in time format i.e. the datatype is 'time' . When i calculate the average of this 'duration' column based on certain group by condition ,it gives me following output :

Following is the query :

select avg(Cactive_duration) as 'duration'
from table_name
group by column_name;

But when I try to convert them to time format following is the output:

Following is the query :

select time(avg(Cactive_duration)) as 'duration'
from table_name
group by column_name;

Any help is greatly appreciated. The database is MySQL.

(1) Tag your question with the database you are using. (2) What is the data type of `duration`? (3) Your query mentions two columns but your data only has one. — Gordon Linoff, May 22 '20 at 11:06
@GordonLinoff i have made the required changes. Also 1. The database is MySQL. 2) The Type is 'TIME'. 3.) The query mentions only 1 column — Aman Mishra, May 22 '20 at 11:14
Provide table's CREATE TABLE script, its data INSERT INTO script, and show desired output for provided data. — Akina, May 22 '20 at 11:39
`AVG()` processes the data in any numeric format only. So time value is converted to numeric. So calculated average value is wrong. `AVG()` is not applicable for desired action. — Akina, May 22 '20 at 11:40

score 0 · Answer 1 · answered May 22 '20 at 11:44

The short answer is: MySQL cannot aggregate times.

Best would be, MySQL confessed this by raising an exception. Instead it converts the time to some number (the internal representation it seems) and deals with that, which results in a number nobody wants.

Convert your times to seconds instead, aggregate these, and then convert back to time:

select sec_to_time(avg(time_to_sec(cactive_duration))) as duration
from table_name
group by column_name;

Time() function return Null

1 Answers1