Trying to Split a Text Field with Time data (HH:MM:SS) into usable data

psmith

I am trying to split up a column using the ETL. The field is formatted as TEXT and has both HH:MM:SS data and MM:SS data (not uniform). I need to convert it to a uniform format so I can break it down into Hours, minutes and seconds of time (End result I am going to add it all up to seconds). After splitting I will convert each field to integer format. Any help to making the data uniform?

MarkSnodgrass

@psmith it turned out to be not too difficult to create samples for you in both versions of Magic ETL. Here is what they look like

Version 1

In the 1st string operations tile, extract the seconds by choosing Right 2. Extract the minutes by choosing right 5. (You're initially grabbing more than the minutes, but we will take care of that in the next tile.)

In the 2nd string operations tile, extract the minutes by choosing Left 2.

In the Replace Text tile, choose the minutes field you just created, enter : (colon) for the term to search for and leave the 3rd box blank in what to replace it with.

In the Set Column Type tile, choose your seconds and minutes fields you created and set them to type of integer.

Version 2

Use the Add Formula Tile and use the following formulas:

Seconds: RIGHT(`Time`,2)

Minutes: REPLACE(LEFT(RIGHT(`Time`,5),2),':','')

Use the Alter Columns tile to change the data type to integer for each of those. Results should look like this based on my sample data:

GrantSmith

Hi @psmith

Regular expressions are one way to solve your problem. Assuming your text is ONLY HH:M:SS OR MM:SS you can utilize a replace text tile (Magic ETL 1.0 or 2.0) to pad your MM:SS formatted strings to be 00:MM:SS

Here's the regular expression you can copy and past:

^(\d{2}):(\d{2})$

And here's the Replacement string:

00:$1:$2

Alternatively you can copy and paste this code into your Magic ETL dataflow and it should* populate the tile automatically for you. You'll just need to select the correct column storing your string and attach the input.

{"contentType":"domo/dataflow-actions","data":[{"name":"Replace Text","id":"a75b422e-7cb5-4c7f-a166-932ed27bd321","type":"ReplaceString","gui":{"x":420,"y":168},"dependsOn":["d0858ff2-48dc-4f30-8151-ba3426ba302a"],"removeByDefault":false,"notes":[],"fields":[{"inStreamName":"MMSS","useRegex":true,"replaceString":"^(\\d{2}):(\\d{2})$","replaceByString":"00:$1:$2","wholeWord":false,"caseSensitive":false}]}]}

MarkSnodgrass

@psmith are you using the new Magic ETL 2.0? If so, the solution will be simpler. If not, you can still accomplish this, but will require a few more steps. Let me know so I can provide the proper solution.

MarkSnodgrass

@psmith it turned out to be not too difficult to create samples for you in both versions of Magic ETL. Here is what they look like

Version 1

In the 1st string operations tile, extract the seconds by choosing Right 2. Extract the minutes by choosing right 5. (You're initially grabbing more than the minutes, but we will take care of that in the next tile.)

In the 2nd string operations tile, extract the minutes by choosing Left 2.

In the Replace Text tile, choose the minutes field you just created, enter : (colon) for the term to search for and leave the 3rd box blank in what to replace it with.

In the Set Column Type tile, choose your seconds and minutes fields you created and set them to type of integer.

Version 2

Use the Add Formula Tile and use the following formulas:

Seconds: RIGHT(`Time`,2)

Minutes: REPLACE(LEFT(RIGHT(`Time`,5),2),':','')

Use the Alter Columns tile to change the data type to integer for each of those. Results should look like this based on my sample data:

GrantSmith

Hi @psmith

Regular expressions are one way to solve your problem. Assuming your text is ONLY HH:M:SS OR MM:SS you can utilize a replace text tile (Magic ETL 1.0 or 2.0) to pad your MM:SS formatted strings to be 00:MM:SS

Here's the regular expression you can copy and past:

^(\d{2}):(\d{2})$

And here's the Replacement string:

00:$1:$2

Alternatively you can copy and paste this code into your Magic ETL dataflow and it should* populate the tile automatically for you. You'll just need to select the correct column storing your string and attach the input.

{"contentType":"domo/dataflow-actions","data":[{"name":"Replace Text","id":"a75b422e-7cb5-4c7f-a166-932ed27bd321","type":"ReplaceString","gui":{"x":420,"y":168},"dependsOn":["d0858ff2-48dc-4f30-8151-ba3426ba302a"],"removeByDefault":false,"notes":[],"fields":[{"inStreamName":"MMSS","useRegex":true,"replaceString":"^(\\d{2}):(\\d{2})$","replaceByString":"00:$1:$2","wholeWord":false,"caseSensitive":false}]}]}

jaeW_at_Onyx

@GrantSmith and @MarkSnodgrass i love both of your solutions (especially the regex!)

if it were me, I would have done something like,

CASE

WHEN <text contains 3 colons> then text

WHEN <text contains 2 colons> then concat 00:

END

then use TIME_To_SEC

psmith

Thanks everyone! I used a combination of both answers 1 & 2 to complete what I wanted to do. We seem to be on Magic ETL 1.0 right now (Just started in Domo at our workplace). Thanks @MarkSnodgrass @GrantSmith

There is no formula button so thats why I assume we are on 1.0 (no place to use case)

jaeW_at_Onyx

contact your CSM / Domo Account Representative or support@domo.com and ask them to push the Magic 2.0 beta. it's free and available if you ask for it.

psmith

Thanks @jaeW_at_Onyx . We actually have a meeting tomorrow with our account rep so I'll bring it up.

SM17

Thanks! this has been very helpful to me… say we already converted both to integers for minutes and seconds, how do we sum both and convert to hours?