Formula for rounding time to nearest minute

jrtomici

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!

GrantSmith

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?

jrtomici

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

@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.

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.

jrtomici

@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

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?

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

jaeW_at_Onyx

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.