Different time zones

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??

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.

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?

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.

I used the TimeZoneInfo class from .Net library as the basis for my design. All of our dates (operationally) are stored in UTC, but need to be presented to the user in the TimeZone of their choice.

Consider developing two tables: DimTimeZoneInfo, and FactTimeZoneInfo. DimTimeZoneInfo - identifies the timezones, where FactTImeZoneInfo contains the offset and abbreviation for the various transitions. These can be populated by calling the .Net functions. (I wrote a Powershell script to handle that).

The Clustered index for FactTimeZoneInfo is based on { TimeZoneKey, utcLb, utcUb } where the Utc is inclusive. I use the DateTime2 structure - so the granularity goes down to 100 nano-seconds.

The business rule for the TimeZone fact table is that you can have zero or multiple transitions per year. The facts changes based on the timezone. For the US Market, the table is only 840 rows to cover 1970 through 2035.

An example of the code to convert a UTC value into a Timezone based value is as follows:

Select
@TimeZoneKey = DimTimeZoneInfo.TimeZoneKey
, @StandardUtcOffset = DimTimeZoneInfo.StandardUtcOffset
, @DaylightUtcOffset = DimTimeZoneInfo.DaylightUtcOffset
, @SupportsDaylightSavingTime = DimTimeZoneInfo.SupportsDaylightSavingTime
, @StandardAbbreviation = DimTimeZoneInfo.StandardAbbreviation
, @DaylightAbbreviation = DimTimeZoneInfo.DaylightAbbreviation
From
DimTimeZoneInfo With(Nolock, FORCESEEK, FASTFIRSTROW)
Where
DimTimeZoneInfo.[Uid] = dbo.ufnTimeZoneSingleCriteria(@TimeZoneSingleCriteria);

If @SupportsDaylightSavingTime Is Null or @SupportsDaylightSavingTime = 0 Begin
Set @Offset = @StandardUtcOffset
Set @Abbreviation = @StandardAbbreviation
End
Else Begin
Select
@IsStandardTime = FactTimeZoneInfo.IsStandardTime
, @Offset = FactTimeZoneInfo.TimeZoneOffset
, @Abbreviation = FactTimeZoneInfo.TimeZoneAbbreviation
From
FactTimeZoneInfo With(Nolock, FORCESEEK, FASTFIRSTROW)
Where
FactTimeZoneInfo.TimeZoneKey = @TimeZoneKey
And @utc Between FactTimeZoneInfo.utcLb and FactTimeZoneInfo.utcUb
End;

-- Convert the UTC to local time.
Declare @dtm DateTime2 = Cast(SwitchOffset(@utc, @offset) As DateTime2);
If @IncludeMilliseconds = 1
Return(Convert(nvarchar(32), @dtm, 121) + ' ' + @Abbreviation);

Return(Convert(nvarchar(32), @dtm, 120) + ' ' + @Abbreviation);