Converting time value stored as text
Hi,
I have a column of time value in the format of 'HH : MM : SS'  it is currently stored as text, I want to be able to run some calculation such as looking at the sum, or average of this column. How do I do that?
When I aggregate the data, I'd like it to still output as 'HH : MM : SS' on the graph
Comments

I was able to accomplish this with the following steps in MySQL:
SELECT
`Resolution Time`
,STR_TO_DATE(`Resolution Time`,'%H:%i:%s') as `Date field`
FROM table_nameThis will change your text column to a date time column. Then I changed the time to seconds, summed them and converted the seconds back to your format with this transform:
SELECT CONCAT_WS(
':',
(SUM(TIME_TO_SEC(`Date field`)) DIV 3600),
(SUM(TIME_TO_SEC(`Date field`)) MOD 3600) DIV 60,
(SUM(TIME_TO_SEC(`Date field`)) MOD 3600) MOD 60) as `Total Time`,
CONCAT_WS(
':',
(AVG(TIME_TO_SEC(`Date field`)) DIV 3600),
(AVG(TIME_TO_SEC(`Date field`)) MOD 3600) DIV 60,
(AVG(TIME_TO_SEC(`Date field`)) MOD 3600) MOD 60) as `Avg Time`
FROM transform_data_1This gives the Total Time and the Average Time:
______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman
______________________________________________________________________________________________0 
Hi,
Thanks for the suggestion. I have tried to use it but I noticed when the hours value is more than 24 hours, it does not generate.
The idea is to understand the number of hours/min/second on the specifc columns. So it is not considering date at all, but counting the number of hours. Based on the suggested string, I have an output that does not generate any values that are beyond 24 hours in the date field. Any suggestion to resolve?
First Response Time (in Hrs) Resolution Time (in Hrs) 3:00:10 5341:49:00 0:00:00 5147:52:43 9:00:00 5098:59:17 0:00:00 5053:03:48 0:00:00 5080:07:27 0:00:00 4962:48:40 0:00:00 4934:35:47 0:00:00 4894:35:53 0:00:00 4894:30:41 0:00:00 4893:53:05 0:00:00 4893:44:24 0:00:00 0:00:00 0:00:00 0:00:00 Thanks,
0
Categories
 10.7K All Categories
 13 Getting Started in the Community
 38 Beastmode & Analytics
 2.1K Data Platform & Data Science
 59 Domo Everywhere
 2.7K Charting
 2.5K Ideas Exchange
 1.3K Connectors
 362 Workbench
 303 Use Cases & Best Practices
 501 APIs
 118 Apps
 48 News
 753 Onboarding
 1.2K 日本支部
 Private Company Board