Skip to content

    • Magic ETL

    Magic ETL

    Rounding a date/time field to the nearest half hour

    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!

     

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • 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

    • 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
    • Member

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

    • 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
    • Member

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

      Capture.JPG

    • 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
    • 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?

    • Contributor

      No problem.  us/pacific

       

      Enjoy!


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

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

    • 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

       

       

    • 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
    • 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 ?

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories