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

Question

I am new to PostgreSQL and I was wondering if there is a direct way to just convert the timestamp values in a table to a different timezone using a function. In my case it is UTC to EST.

These are the values for example that I need to convert to EST (not just one value but all the values in the table)

date ------------------- 2015-10-24 16:38:46 2016-01-19 18:27:00 2016-01-24 16:14:34 2016-02-09 23:05:49 2016-02-11 20:46:26 

Answer 1

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_timestamp FROM my_table; 

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

Answer 2

Similarly execute

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