Different time zones

[dellsters]

We have 2 groups of users that are divided when it comes to looking at time. One group prefers looking at data in PST time and the other group in EST time. What is the best way to model this requirement? Would I put 2 sets of columns in the time and day dimensions in both time zones and let the users choose which one they want to look at??

[ngalemmo]

The common way to deal with multiple time zones is similar to dealing with multiple currencies. You identify a standard time (usually UTC, aka GMT) and store the standard time and a local time (the time used in the transaction). If you need to restate time, you base it off the standard time. Your database can hold a simple table with time zones and offsets from UTC to convert, or, in many cases, database systems have functions that will do that for you.

[dellsters]

With the Oracle function to translate time zones, is it even worth saving it in both local and UTC? I could just store one as timestamp with time zone column and translate with the Oracle function?

[ngalemmo]

If you are going to store one value, and you need to deal with different time zones, the best option is to use UTC. You will not need to store a time zone, unless you want to know the local time, and UTC doesn't change... there is no daylight savings time and it isn't subject to political interpretation.