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

by

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;

Leave a Comment