Formula for rounding time to nearest minute

jrtomici
jrtomici Member
in Magic ETL

Hi, I need to round times to the nearest minute. Not rounding up or down, but nearest minute. Any ideas on how to use formula ETL tile for this? Thanks!

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    What format are your times in? It it the number of seconds or is it an actual time format with seconds?

    You'll either be rounding up or rounding down. So if you have 1:04:30 AM what should that equate to? What about 1:04:55 AM?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jrtomici
    jrtomici Member
    edited September 2022

    Time is in this format

    07:32:39

    You brought up a good point and I need to rethink the problem.

    I have a dataset that looks like this:

    ID     Time
1      07:32:39
1      07:32:40
1      07:32:41
1      07:32:41
1      07:32:42

1      14:46:59
1      14:47:00
1      14:47:01

2      01:04:29
2      01:04:30
2      01:04:31
2      01:04:31

    I need only one from each section/time range. I can't just delete duplicates of ID, because in some cases a given ID can cover two separate time ranges. It doesn't matter which row is kept, as long as it's one from each range. Example of desired output:

    ID     Time
1      07:32:39
1      14:46:59
2      01:04:29

    Thank you. @GrantSmith

  • ST_-Superman-_
    ST_-Superman-_ Coach

    @jrtomici

    I have a question about your example. Wouldn't line 12 get rounded to a different minute then line 13? Line 12 should be rounded to 01:04 while lines 13-15 should be rounded to 01:05.

    Otherwise, I was able to use this formula to get to the nearest minute:

    "HH:MM"

    CONCAT(LPAD(trim(Trailing '.0' from FLOOR(ROUND((TIME_TO_SEC(`Time`) / 60),0)/60))
      ,2,0),':',LPAD(trim(trailing '.0' from (MOD(ROUND((TIME_TO_SEC(`Time`) / 60),0),60))),2,0))

    I then used a rank and window tile to get a row number for each "HH:MM" value from the above formula. Then I filtered the data for Row=1 to only return one line of data for each HH:MM.

    Snag_d9fa3f.png


    The idea behind the formula is to convert the time value to seconds. You then divide the number of seconds by 60 to get the number of minutes. This will now have a decimal for the fraction of the minute represented by seconds which can be rounded to get to the "nearest" minute.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • jrtomici
    jrtomici Member

    @ST_-Superman-_ that's a really smart solution thanks. But unfortunately yeah, my initial thought process wouldn't work in this problem because of the example I depicted in ID #2. I only need one row from that grouping.

  • ST_-Superman-_
    ST_-Superman-_ Coach

    Would you need to create groupings based on elapsed time then? Something like, if it's within 5 seconds of the initial time then keep it in the same group?

    Is it also per ID? What if you had data like this?

    image.png

    Would you expect one group for that time frame? or 2?


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    edited September 2022

    my approach would be to try to take a timestamp and date_format( hh:mm) that would always round down.

    to address round up, i'd use a CASE statement so if second() is 30 then hh:mm + 1 minute.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories