I noticed several threads that were 4+ years old regarding the handling of data from different time zones including the use of DST in some cases.

We are faced with a situation where we will be using data from each timezone in the U.S. (and some out) from a single WebFocus installation which will be hosted in the Central Time Zone (also does observe DST). We plan on using EPOCH dates and then converting appropriately for InfoAssist(+) use based on the logged in users time zone.

In addition, we would like report caster to properly translate time based on the logged in user. For example, if a user logs in from the eastern time zone and schedules a report for 7:00 PM we would like the actual report to be scheduled at 8:00 Central on the Webfocus server.

Has anyone developed a clever way to address either one of these problems? Is there perhaps built in WebFocus functions to help with this that we are not aware of?

Thanks in advance for any assistance!

This message has been edited. Last edited by: <Emily McAllister>,

We use the below for determining time zone offsets, may come in handy:

SQL SQLMSS PREPARE SQLOUT FOR
SELECT
	GETDATE() - GETUTCDATE() AS DIFF_UTC
,	GETDATE() AS DATE_TZ
,	GETUTCDATE() AS DATE_UTC
;
END

DEFINE FILE SQLOUT
-* DIFF_UTC returns a datetime based at 1900 instead of 1970
	EPOCH_AT_TZ/HYYMDs	= HADD(DIFF_UTC, 'yy', 70, 8, EPOCH_AT_TZ);
	TZ_OFFSET_M/D12	= HDIFF(DATE_TZ, DATE_UTC, 'MINUTE', TZ_OFFSET_M);
END
TABLE FILE SQLOUT
PRINT
	EPOCH_AT_TZ
	TZ_OFFSET_M

END

Jamie,

At this point in time WF does not support time zone datatypes. Last year I opened a NFR to have improvements made in regarding to Time zones. Last I heard (April of this year), a possible resolution is being targeted for WF7706/WF7707. But I haven't heard anything more on it. The case number is 20293018. I would suggest opening up an NFR with IBI. Supposedly these things need strength in numbers.... I mean disregard the fact that the US has 9 time zones that may or may not observe daylight savings time...

Sorry rant over.

In my NFR I asked for the following:
- Timezone to be added to the Date Time data type
- Additional function for converting time zones
- The ability to alter the session time zone

As for what you can do now...

For the infoassist issue. The best suggestion that I can come up with is to add a MFD profile for the Master File with the EPOCH dates. Assuming that your database supports timestamps with local timezone (Oracle does), I would store the datetime values in that format, then create a stored procedure that would alter the session timezone. The challenge here would be in how to identify what timezone to change it to. I don't know how you would get the users timezone in InfoAssist. If I were building a web page to launch a report, I would write a java script function to get the users current time and compare it against DST, then apply that offset to the alter session command. If you have a custom login page, you might be able to put that javascript function into a header variable and then define that header variable to a global variable in the custom settings of the Administration Console. By altering the session, the Date/Time should be adjusted automatically according to what you defined in your alter session.

For your issue with report caster, that would be a nice feature. I don't think this is available in report caster. The only thing I can think of would be a clunky work around. you could create a database with each user and their offset. You would probably then need to build a process that would interrogate the last created Jobs and adjust the execution time accordingly. I don't actually suggest this method though.... a lot of things could go wrong. Probably best to raise it as a NFR with IBI and hope that they add something like that in.