Skip to content

    • Magic ETL

    Magic ETL

    Time Zones

    Member
    in Magic ETL

    How do I change the time stamps in my postgresql data from UTC to EST? Saw the convert_tz() syntax from Domo but that's for mysql. Not sure where I should run this and how. 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

    • Member
      Answer ✓

      Domo Support has given me a better string of code to run this. running select convert_timezone('EST', timestamp) should give us the correct time in EST. 

    Answers

    • Member

      Does anyone have any ideas on how to do this?

    • Member

      You can do this in the Postgresql query before the data enters Domo using the "AT TIME ZONE" construct.

       

      Example:

       

      SELECT now() AT TIME ZONE 'EST';

       

      SELECT MyDateTime AT TIME ZONE 'EST';

       

      If you want to see it in action try it with another time zone to make sure it changes.

       

      SELECT now() AT TIME ZONE 'MST';

      SELECT now() AT TIMEZONE 'PST';

       

    • Member

      Running the syntax in my query simple adds 5 hours on all the timestamps in my data. I would like to know how to convert my timestamp, which is stored as UTC, to EST. The adjustments made should be 4 hours behind since EST is 4 hours behind UTC. Thanks in advance. 

    • Member
      Answer ✓

      Domo Support has given me a better string of code to run this. running select convert_timezone('EST', timestamp) should give us the correct time in EST. 

    • Member

      When I run your example it says CONVERT_TIMEZONE does not exist.

    • Member

      I tried the below to convert from IST to EST, not sure if this is correct

      case when (HOUR(`Date/Time Opened`)+10)<24
      then (HOUR(`Date/Time Opened`)+10)
      else (HOUR(`Date/Time Opened`)-15)
      end

       

       

    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