Time Zones

Options
user00341
user00341 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! 

Best Answer

  • user00341
    user00341 Member
    Answer ✓
    Options

    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

  • KaLin
    KaLin Member
    Options

    Does anyone have any ideas on how to do this?

  • DBandrew
    DBandrew Member
    Options

    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';

     

  • user00341
    user00341 Member
    Options

    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. 

  • user00341
    user00341 Member
    Answer ✓
    Options

    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. 

  • user03698
    user03698 Member
    Options

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

  • user056243
    user056243 Member
    Options

    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

     

     

Categories