Rounding a date/time field to the nearest half hour

rollthejewels
rollthejewels Member
in Magic ETL

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

  • cwolman
    cwolman Contributor
    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`


    -----------------
    Chris

Answers

  • cwolman
    cwolman Contributor

    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.

     

    datetime_rounding.PNG


    -----------------
    Chris
  • rollthejewels
    rollthejewels Member

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

  • cwolman
    cwolman Contributor

    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`

     


    -----------------
    Chris
  • rollthejewels
    rollthejewels Member

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

    Capture.JPG

  • cwolman
    cwolman Contributor

    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`


    -----------------
    Chris
  • rollthejewels
    rollthejewels Member

    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
    cwolman Contributor

    No problem.  us/pacific

     

    Enjoy!


    -----------------
    Chris
  • rollthejewels
    rollthejewels Member

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

  • rollthejewels
    rollthejewels Member

    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.Capture.JPG

     

     

  • cwolman
    cwolman Contributor
    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`


    -----------------
    Chris
  • rollthejewels
    rollthejewels Member

    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 ?

Categories