Rounding a date/time field to the nearest half hour

rollthejewels

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!

 

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`

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 ?