Formula for rounding time to nearest minute
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 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.
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?
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
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive