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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive