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? 

 Time.PNG

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_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:

    1.png

     

     

  • 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:105341:49:00
    0:00:005147:52:43
    9:00:005098:59:17
    0:00:005053:03:48
    0:00:005080:07:27
    0:00:004962:48:40
    0:00:004934:35:47
    0:00:004894:35:53
    0:00:004894:30:41
    0:00:004893:53:05
    0:00:004893:44:24
    0:00:000:00:00
    0:00:000:00:00

     

     SQL.PNG

     

    Thanks,