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
 1 APAC User Group
 12 Welcome
 36 Domo News
 9.6K Using Domo
 1.9K Dataflows
 2.4K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 339 Workbench
 252 Domo Best Practices
 11 Domo Certification
 461 Domo Developer
 47 Domo Everywhere
 101 Apps
 705 New to Domo
 84 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 23 お知らせ
 63 Kowaza
 297 仲間に相談
 649 ひらめき共有