Time Zones

Reply
Yellow Belt

Time Zones

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! 


Accepted Solutions
Highlighted
Yellow Belt

Re: Time Zones

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. 

View solution in original post


All Replies
Highlighted
Moderator

Re: Time Zones

Does anyone have any ideas on how to do this?

Highlighted
Green Belt

Re: Time Zones

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

 

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Highlighted
Yellow Belt

Re: Time Zones

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. 

Highlighted
Yellow Belt

Re: Time Zones

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. 

View solution in original post

Highlighted
White Belt

Re: Time Zones

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

Highlighted
Yellow Belt

Re: Time Zones

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

 

 

Announcements
Win free lodging at Domoapalooza! We want you to share you favorite Domo tips and tricks. Click here for more details!