Different time zones

View previous topic View next topic Go down

Different time zones

Post  dellsters on Thu Mar 25, 2010 10:10 am

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


Last edited by dellsters on Thu Apr 08, 2010 12:24 pm; edited 1 time in total

dellsters

Posts : 39
Join date : 2009-02-11

View user profile

Back to top Go down

Re: Different time zones

Post  ngalemmo on Thu Mar 25, 2010 11:44 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Different time zones

Post  dellsters on Thu Mar 25, 2010 4:19 pm

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?

dellsters

Posts : 39
Join date : 2009-02-11

View user profile

Back to top Go down

Re: Different time zones

Post  ngalemmo on Fri Mar 26, 2010 11:52 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Dimentional and fact Table model based on TimeZoneInfo (.Net Function)

Post  william.hullsiek on Thu Mar 31, 2011 11:33 am

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);

william.hullsiek

Posts : 2
Join date : 2011-03-28
Location : Saint Paul, Minnesota

View user profile

Back to top Go down

Re: Different time zones

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum