Formula for rounding time to nearest minute
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!
Answers

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!**1 
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
0 
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 1315 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.
______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman
______________________________________________________________________________________________2 
@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.
0 
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?
______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman
______________________________________________________________________________________________1 
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"1
Categories
 7.3K All Categories
 13 Getting Started in the Community
 142 Beastmode & Analytics
 1.8K Data Platform & Data Science
 54 Domo Everywhere
 2K Charting
 1K Ideas Exchange
 904 Connectors
 237 Workbench
 342 APIs
 77 Apps
 19 Governance & Productivity
 235 Use Cases & Best Practices
 50 News
 473 Onboarding
 573 日本支部