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!

Find more posts tagged with

Accepted answers

cwolman

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`

All comments

cwolman

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.

rollthejewels

Thanks Chris. It's actually stored in DOMO as a Date-Time field - will this still work?

cwolman

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`

rollthejewels

Thanks - almost there but the hours don't seem to be correct.

cwolman

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`

rollthejewels

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?

cwolman

No problem. us/pacific

Enjoy!

rollthejewels

Got everything to work as desired, thank you for your help!

rollthejewels

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.

cwolman

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`

rollthejewels

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 ?