Time Zones

user00341

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! 

user00341

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. 

KaLin

Does anyone have any ideas on how to do this?

DBandrew

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

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

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

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

user056243

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