UTC to local Hour Number

Hi All,

I have a transaction table with a datetime column  in UTC (2018-12-30T21:36:37Z) format. I also have Lat, Long, Address etc. Data is for all the stores spread across US. 

I want to creat a column with hour number (ex. 5, 23, 18) so that hour number represents the local time hour where transaction happend (example: if in NY, transaction happend at 4:23 pm est, it should show 4 and not UTC or any other time). Our aim is to analyze and compare  hours at which transactions happens in different states of the US. 

(Note - column should take care of daylighsavings)

I am struggling with this for quite sometime. I was looking at this video for help - https://www.youtube.com/watch?v=M1zquwmpnZE&t=84s , but maybe not helpful in my case. Please suggest.

Thanks!