Rounding a date/time field to the nearest half hour
We have a date/time field that I'd like to accomplish the following with:
- Split the date and time into two separate columns (while also maintaining the original column)
- Round the time down to the nearest half hour increment (for instance, 8:43 would round to 8:30, 8:18 would round to 8:00, etc.)
Issues to be considered: the date and timestamp isn't in a standard format - so text functions like pad, trim, etc. won't necessarily work. For instance, we have values such as:
- 8/19/2019 4:38:51 AM
- 8/19/2019 10:04:42 PM
- 8/8/2019 8:30:00 AM
- 12/5/2019 9:14:12 PM
Anybody have any ideas on how to accomplish this?
Thanks!
Best Answer
-
Sorry about that - I rounded up. Here is the formula for rounding down.
sec_to_time((time_to_sec(time(convert_tz(`date_time`,'utc','us/pacific'))) div 1800)*1800) as `rounded_down`
-----------------
Chris0
Answers
-
Hello,
I worked under the assumption that your datetime column is a string and utilized the str_to_date function to convert the value. Hopefully this example will be helpful.
-----------------
Chris0 -
Thanks Chris. It's actually stored in DOMO as a Date-Time field - will this still work?
0 -
Yes. Actually it is even easier because you do not need to use the str_to_date() function.
The sql would be as follows replacing the `test` field with your field:
select `test` as orig_value
, date(`test`) as `date`
, time(`test`) as `time`
, sec_to_time(floor((time_to_sec(time(`test`))+900)/1800)*1800) as `rounded_time`
from `data`
-----------------
Chris0 -
Thanks - almost there but the hours don't seem to be correct.
0 -
Ahh, time zone conversion in Domo. I have lost many hours on this issue in the past. It looks like you are in mountain time so I will use that for my example.
select `date_time` as orig_value
, date(convert_tz(`date_time`,'utc','us/mountain')) as `date`
, time(convert_tz(`date_time`,'utc','us/mountain')) as `time`
, sec_to_time(floor((time_to_sec(time(convert_tz(`date_time`,'utc','us/mountain')))+900)/1800)*1800) as `rounded_time`
from `data`
-----------------
Chris1 -
Thanks Chris. Sorry to ask for such a simple solution here but I can't seem to find it on my own - I'm actually in the Pacific time zone - can you tell me the code for that?
0 -
No problem. us/pacific
Enjoy!
-----------------
Chris0 -
Got everything to work as desired, thank you for your help!
1 -
Actually, sorry - I was a little hasty in my joy. Looks like some of them are rounding up - any idea why that'd happen? It looks like if it goes over the 15 or 45 minute mark it rounds up - I still need those to round down.
0 -
Sorry about that - I rounded up. Here is the formula for rounding down.
sec_to_time((time_to_sec(time(convert_tz(`date_time`,'utc','us/pacific'))) div 1800)*1800) as `rounded_down`
-----------------
Chris0 -
Got it, thank you. Also - turns out I didn't need the timezone conversion in there....some wonkiness going on in the DOMO ETL stuff there. Thanks for your help ?
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive