Corresponding local time of UTC time

Question

I have a set of UTC times:

UTC_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

I want to know the corresponding local time of these times, I am in CDT timezone, which should be 5 or 6 hrs behind UTC . This is the result I need:

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

I tried UTC_date::timestamp AT time zone 'CDT', but this will do the opposite way.What PostgreSQL query I could use?

Answer 1

Assuming that utc_date is a timestamp without timezone, you can do:

select 
    utc_date, 
    utc_date at time zone 'utc' at time zone 'cdt' cdt_date 
from mytable

The logic is to first indicate Postgres that the timestamp is in utc, and then translate it to the target timezone.

Demo on DB Fiddle:

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