Converting time value stored as text

adelinek

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

ST_Superman

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_name

 

This 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_1

 

This gives the Total Time and the Average Time:

 

 

adelinek

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,