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

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

Solution 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.

Solution 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)

Solution 3:

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 ....

Solution 4:

If you have problem accessing with your zone, you can simply pass your zone interval also. To convert timestamp from IST to UTC.

SELECT '2020-12-14 06:38:46'::timestamp AT time zone INTERVAL '+05:30';

timezone
------------------------
2015-10-24 11:38:46+00
(1 row)

To convert timestamp from UTC to IST.

SELECT '2020-12-14 06:38:46'::timestamp AT time zone INTERVAL '-05:30';

timezone
------------------------
2020-12-14 12:08:46+00
(1 row)