Skip to content

    • Datasets

    Datasets

    UTC to Eastern time zone adjustment

    Member
    edited March 2023 in Datasets

    My question for the Domo Brillance Bar when arriving at the detroit user group:

     

    We have a connection from 5/9s system to Domo with a timestamp column in the dataset.  We created cards with this dataset and realized that the data was not reporting in the correct time zone.  We added this dataset as in input in a dataflow and added a transform to convert the timestamp field to a date/time instead of text.  

     

    This is the code we added in our dataflow:

     

    DATE_FORMAT(str_to_date(`TIMESTAMP`, '%a, %e %b %Y %T'),'%m/%d/%Y %k:%i:%s') AS ConvertedTimeStamp

     

    Then we used the new field ConvertedTimeStamp in our cards to report the correct time zone for the calls that come in to our call center.  

     

    Any questions please feel free to reach out to me.

     

    Thank you

     

    Kevin

    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 ✓

      @kmaurer

      Hi.

       

      UTC to Eastern

      convert_tz(`TimeStamp`,'UTC','EST')

       

      DATE_FORMAT(convert_tz(str_to_date(`TIMESTAMP`, '%a, %e %b %Y %T'),'UTC','EST'),'%m/%d/%Y %k:%i:%s') AS ConvertedTimeStamp

    Answers

    • Contributor
      Answer ✓

      @kmaurer

      Hi.

       

      UTC to Eastern

      convert_tz(`TimeStamp`,'UTC','EST')

       

      DATE_FORMAT(convert_tz(str_to_date(`TIMESTAMP`, '%a, %e %b %Y %T'),'UTC','EST'),'%m/%d/%Y %k:%i:%s') AS ConvertedTimeStamp

    • Coach

      Hi! I use ETL for my dataflows, not SQL. Is there a way to do this conversion inside an ETL dataflow? I have the Scripting beta that allows me to include Python or R, but I don't know what to do next. Anyone willing to lend a hand? 

       

      Field is EVENT_DATE_TIME and I need to make 6/21/2016 3:30:00 PM change to 7:30 PM, for example. 

       

      Thanks!

      Broadway + Data
    • Contributor

      The only way in found to do in ETL is to add or subtract hours, not ideal unfortunately.

      • Convert Date/Time to decimal
      • Add or subtract the hours you need (1 = 1 milisecond)
      • Convert back to Date and Time 

    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