GMT to local time conversion

[rcoon]

Hello,

I have data that includes dates/times stored as GMT timestamps. I would like to be able to display this data in the local timezone. To that end, I am following an approach similar to what has been outlined in https://www-304.ibm.com/support/docview.wss?uid=swg21339907 which basically has you look up the user's timezone ID, and map it to a number of hours that should be added or substracted from the GMT time.

All good, however, this method seems to only cover the case where all users have set their own personal timezones, and I would like to be able to also cover what is actually the default case, where the user has simply stayed with the default (server) timezone. Is there a server-wide property that is similar to account.personalInfo.timeZoneID ?

With no server timeZoneID property that I know of, I decided to take another approach. I am getting the current_timestamp, and then parsing it to identify the time zone offset, which can then be added/substracted to the GMT times in my data, building local timestamps. I've got this working, but the results are incorrect due to the current_timestamp not handling daylight savings properly. If I call current_timestamp right now, I get the result 'April 12, 2011 5:12:50.526 PM GMT-08:00' (I'm in CA). This is not entirely correct. The time is right, but the time zone offset it wrong. While the offset for PST is -08:00, due to DST, my current timezone is PDT, and the offset should be -07:00.  Is there any way to get the correct timezone offset? Of course, if a server wide timeZoneID property is identified, this last part becomes moot.

Thanks for your time.

rob

[rcoon]

On further examination, the per-user timezone solution from the IBM article doesn't handle the daylight savings properly either. Ugh. So what I need is a way to properly identify the timezone offset, or at least find a way to recognized if DST is in effect, so I can compensate for it.

[Lynn]

You might consider a database solution for this. It is possible to derive the start and end of daylight savings time in SQL (for the current year), but it is messy and I don't recommend it. I did it at a previous client because the awful (horrible, lazy, difficult, nasty) DBA refused to create a table with the data needed to simply model in FM for appropriate conversion.

The table structure would be:

TZ_ID (surrogate key for uniqueness)
userTZ (value EST or PST or whatever timezones you want to support that will be returned per the property noted in the IBM article)
actualTZ (value indicating EDT or EST depending on the date, useful for display purposes)
TZoffset (the integer value to be added or subtracted from the GMT stored value)
startDate (date/time of the beginning of a period)
endDate (date/time of the end of a period)

This would be pre-populated much like a date dimension:

Code Select Expand


1 GMT GMT  0 Jan 01 1900 0000 Dec 31 2099 2359
2 EST EST -5 Nov 07 2010 0200 Mar 13 2011 0159
3 EST EDT -4 Mar 13 2011 0200 Nov 06 2011 0159
4 PST PST -8 Nov 07 2010 0200 Mar 13 2011 0159
5 PST PDT -7 Mar 13 2011 0200 Nov 06 2011 0159


Model this in FM with your date in the data table between the start and end date. Also include a prompt macro for the user timezone to ensure that only one row will be returned. You can base this off the user's TZ property and also specify a default value if there isn't one set. You can click the ellipsis in the relationship dialog to enter the expression such as

Code Select Expand


[YourDataTable].[Date] between [TZTable].[StartDate] and [TZTable].[EndDate]
and
[TZTable].[userTZ] = #prompt ... blah blah ...#


In your model query subject you could expose the GMT value as well as a local value that is defined with an expression that adds the TZoffset to your date. 

As for your default, I don't see the value in going through a lot of hoops for that. If they don't have their own personal value set then perhaps GMT would be a good enough default? Why go to the trouble of using the server timezone which could be just as arbitrary as GMT (from a user's perspective)?

Sorry for the rambling on response but hopefully a little food for thought somewhere in here :)