Convert a UTC timezone in postgresql to EST (local time)

Here in London, we are currently 1 hour ahead of UTC. So - if I take your timezone without timestamp and say it is in UTC I will get it printed for my local timezone.

richardh=> SELECT ((timestamp '2015-10-24 16:38:46') AT TIME ZONE 'UTC');        timezone        ------------------------ 2015-10-24 17:38:46+01(1 row)

But you want "EST" which seems to be somewhere in the Americas, judging by the value returned. You can wrap the expression in a little SQL function if you wanted to.

richardh=> SELECT ((timestamp '2015-10-24 16:38:46') AT TIME ZONE 'UTC') AT TIME ZONE 'EST';      timezone       --------------------- 2015-10-24 11:38:46(1 row)

Edit: how to do it in a query

SELECT ((stored_timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'EST') AS local_timestampFROM my_table;

You will probably want to get an introductory book on SQL if this sort of thing is causing you problems.

Similarly execute

SELECT '2015-10-24 16:38:46'::timestamp AT time zone 'EST';timezone------------------------ 2015-10-24 21:38:46+00(1 row)

I usually leave everything in UTC and convert when it is time to show.I use something like:

SELECT my_date_utc AT time zone 'utc' at time zone 'est' From ....