Time to Number value

Stu5677

I have tried to find a few answers on this but nothing has come up - is there a way to take a time column (00:02:01) and SUM ALL the values for the total Hours? I have tried SUM(HOUR('TALK TIME')) but that is not giving me the correct summarized number of hours. I also tried to do in the ETL but the column keeps coming up as blank. Is there an easier way or another article I can reference?

GrantSmith

I'd recommend looking into TIME_TO_SEC function to convert your string into total seconds then you can divide that number by 3600 to convert to it to hours and then SUM it.

TIME_TO_SEC
Returns an elapsed number of seconds for all values in a date/time column.
TIME_TO_SEC('DateCol')

Garrett_Kohler

https://community-forums.domo.com/main/discussion/comment/64021#Comment_64021

One problem I've had are discrpancies between the calculated number of seconds using TIME_TO_SEC and using EXTRACT() for getting the (Hour/3600) + (Minute/60) to give the time in seconds.

rco

If your goal is to get a result that is a time string like your input, I believe you want something like CAST(SEC_TO_TIME(SUM(TIME_TO_SEC(`TALK TIME`))) AS CHAR)

If you only want the hours (as a floating-point number), it would be SUM(TIME_TO_SEC(`TALK TIME`)) / 3600

If you want integer hours, invoke the DIV function instead of using the / operator: DIV(SUM(TIME_TO_SEC(`TALK TIME`)), 3600)

Magic ETL doesn't currently support EXTRACT or any of the functions in that family (e.g. HOUR, MINUTE) for time columns representing amounts of time, like you have in this case. They only work on calendric data like dates and timestamps.

Note that care must be taken when converting from text to time. These three common formats are treated these three ways:

1. '100' is treated as 100 seconds, i.e. 00:01:40

2. '100:59' is 100 hours and 59 minutes, i.e. 100:59:00

3. '100:59:59' is 100 hours, 59 minutes, and 59 seconds.

The 00:00 format is particularly troublesome, as it isn't always clear whether it was originally intended to be hours and minutes (which is the way it will be interpreted) or minutes and seconds (in which case you'd need to prepend '00:' to it for it to parse correctly).