sec_to_time() Is adding an unnecessary period to durations

nickcorona · May 7

I'm using sec_to_time(`SessionTime`) to convert an integer of seconds into a duration however when a SessionTime should end in a single digit amount of seconds say, 5 seconds "12:45:05" the beastmode field is displayed as "12:45:5." This creates an unnecessary period and shows an inconsistent format. The result should show hh:mm:ss every time, not hh:mm:s.

Is there a way around this or is this a bug?

david_cunningham · May 7

So I tested using your example data, and wasn't able to replicate your output.

My gut tells me that there might be something going on due to rounding or microseconds. I would suggest rounding your seconds before doing sec_to_time() and see if that fixes your issue.

david_cunningham · May 7

@nickcorona I've been playing around with this, and I'm not able to replicate your output. Is there anything that is different about the data in those rows where this is occurring? Is it just those 2 rows / how widespread is the problem?

Really not sure what's going on here. My gut still tells me there is something going on with rounding/repeating numbers causing weird behavior. For example, I was trying to recreate the SEC_TO_TIME() functionality manually to see if I could figure out where the problem was originating, and saw that one of your rows had this output when generating the seconds multiplier decimal.

Anyways, this would be my recommended list of things to try. Just spit-balling here with how I would go about troubleshooting.

In your ETL, wrap TotalSessionTime in a ROUND function and test that (rather than doing it in the beast mode). If that doesn't work, try CEIL or FLOOR and see if that makes a difference.
Rather than rounding in your beast mode, try this.
- CAST(TotalSessionTime AS INT) and then test TIME_TO_SEC on that

If none of those work. I would recommend opening a ticket with domo support. Hopefully you won't have to 🤞, but if you do please let me know what the issue ended up being once resolved.

Alternatively, if you need a temporary fix for this. I went ahead and built a beast mode that manually creates 2 different versions of the sec_to_time output. Though I would stress that this hasn't been thoroughly tested, and should be considered more of a temporary patch. The proper course of action is to figure out what is causing the behavior in the first place.

This beast mode…

CONCAT(

    SUBSTRING_INDEX(`TotalSessionTime`/60/60,'.',1),'h ',

    SUBSTRING_INDEX(CAST(CONCAT('.',SUBSTRING_INDEX((`TotalSessionTime`/60/60),'.',-1)) AS FLOAT)*60,'.',1),'m ',

    ROUND(CONCAT('.',SUBSTRING_INDEX(CAST(CONCAT('.',SUBSTRING_INDEX((`TotalSessionTime`/60/60),'.',-1)) AS FLOAT)*60,'.',-1))*60),'s'
  )

Generates the circled output

And this beast mode matches the formatting from sec_to_time()

CONCAT(

    /*Hours*/

    SUBSTRING_INDEX(`TotalSessionTime`/60/60,'.',1),':',

    /*Minutes*/

    CASE 

      WHEN LENGTH(SUBSTRING_INDEX(CAST(CONCAT('.',SUBSTRING_INDEX((`TotalSessionTime`/60/60),'.',-1)) AS FLOAT)*60,'.',1)) = 2 

        then SUBSTRING_INDEX(CAST(CONCAT('.',SUBSTRING_INDEX((`TotalSessionTime`/60/60),'.',-1)) AS FLOAT)*60,'.',1)

      else CONCAT('0',SUBSTRING_INDEX(CAST(CONCAT('.',SUBSTRING_INDEX((`TotalSessionTime`/60/60),'.',-1)) AS FLOAT)*60,'.',1))

    end,':',



    /*Seconds*/

    CASE 

      WHEN LENGTH(ROUND(CONCAT('.',SUBSTRING_INDEX(CAST(CONCAT('.',SUBSTRING_INDEX((`TotalSessionTime`/60/60),'.',-1)) AS FLOAT)*60,'.',-1))*60)) = 2 

        then ROUND(CONCAT('.',SUBSTRING_INDEX(CAST(CONCAT('.',SUBSTRING_INDEX((`TotalSessionTime`/60/60),'.',-1)) AS FLOAT)*60,'.',-1))*60)

      else CONCAT('0',ROUND(CONCAT('.',SUBSTRING_INDEX(CAST(CONCAT('.',SUBSTRING_INDEX((`TotalSessionTime`/60/60),'.',-1)) AS FLOAT)*60,'.',-1))*60))

    end


  )

And generates this output (circled).

david_cunningham · May 7