Referencing timezones in postgres

Question

I need to store timezones for users in Postgres 9.3.

A logical solution would be to create a table of timezone names and offsets, and then store an FK reference in my user table.

However, postgres already has a view pg_timezone_names which displays the timezone names Postgres knows about. It would seem to make sense just to reference those names, rather than creating my own table of the exact same names. However, pg_timezone_names is a view and therefore can't be referenced.

Is there a system table holding these timezone names which is safe to reference, or is it simply a bad idea to do so?

Answer 1

Rather than a FK reference, you should probably just denormalize. Store the string name of the time zone with the user, using an IANA time zone identifier, such as 'America/Los_Angeles'. These IDs are used by Postgres, and are also interchangeable with other systems.

Keep in mind that a time zone cannot be solely represented by an offset. Read more in the section "Time Zone != Offset" of the timezone tag wiki.