Convert an event time to a user's local time zone by state in postgres

local-timezone.sql

--Case statement covnert user generated events to the user's local time zone based on a user's state
--Best used when your date/time field is stored as a timestampz, time zone aware, value in postgres
--For states that have more than one time, the time zone with the largest population is used.
--Exmaple use case: figuring out what percentage of user generated events take place outside of buiness hours

--Replace 'state' and 'event_time' with the respective columns names from your postgres database.

case
	when state in ('CT','DE','DC','FL','GA','IN','KY','ME','MD','MA','MI','NH','NJ','NY','NC','ND','OH','PA','RI','SC','VT','VA','WV') then event_time at time zone 'us/eastern'
	when state in ('AR','IL','IA','KS','LA','MN','MS','MO','MT','NE','OK','SD','TN','TX','WI') then event_time at time zone 'us/central'
	when state in ('AZ','CO','ID','NM','UT','WY') then event_time at time zone 'us/mountain'
	when state in ('CA','NV','OR','WA') then event_time time zone 'us/pacific'
	when state in ('AK') then event_time at time zone 'us/alaska'
	when state in ('HI') then event_time at time zone 'us/hawaii'
	else null
end as event_time_local