Storing a user’s timezone in Postgres can be an interesting task in apps. Often, this is accomplished by generating a hardcoded list of every timezone in the backend (or worse the frontend JavaScript) and storing an item from that list in the database for each user. This hardcoded magic list is both difficult to build accurately and hard to maintain. Timezones change more frequently than you would imagine ...but less frequently than time itself.
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 pg_timezone_names.
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)
FROM events
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.
This article was originally published on Magistrate. Head over there if you like this post and want to read others like it.
Top comments (0)