Postgres has a system view called
pg_timezone_names that has the list of timezones used by Postgres internally. We can use this view to generate a dropdown or other input type for the user to select their timezone. We can filter out some of the timezones in your query (like all the ones that start with “posix/“) because this list is quite large by default.
select name from pg_timezone_names where name not like 'posix%' and name not ilike 'system%' order by name;
Now, we need to figure out the best way to store the selection from our user. We can do this by adding a column to our users table to hold the timezone. The column is a text type because we are only storing the
name value from
alter table users add column timezone text;
Next, we want to query the user’s events in the timezone we have for the user. We store the
start_time for the events as UTC
timestamptz columns. This solution leverages the timezone function in Postgres.
AT TIME ZONE zone also works. The last
join in this query is optional but it is there in case you need to know the UTC offset or if the timezone is in DST.
SELECT events.name, timezone(users.timezone, start_time)
JOIN users ON events.user_id = users.id
JOIN pg_timezone_names ON users.timezone = pg_timezone_names.name;
This could be taken a step further by using the to_timestamp function in Postgres to format the dates, and keep the application from formatting dates at all.