[Openroad-users] eClient &Timezones-CORRECTION KB415539not KB415439
Bill Allan
wgallan at tpg.com.au
Fri Apr 11 23:34:54 EST 2008
Ki Darren,
Just a clarification on the DATE('now') function and a couple of other items,
The DATE('now') function returns GMT time into an underlying date datatype variable. The II_TIMEZONE_NAME setting is not relevant at this stage.
If you us an assignment within OpenROAD such as:
timestamp_var = DATE('now'); /* timestamp_var is declared as a date datatype */
OpenROAD will go to the Client OS and get the GMT value on the client PC.
If you do a select to set the value such as:
SELECT timestamp_var = DATE('now');
OpenROAD will send the select to the database server and the DBMS server will go to the OS there and get the GMT value. The DBMS server will pass back the GMT value to the Client.
NOTE: If you need to have consistency between client applications you should always use the SELECT DATE('now') to get the GMT from the DBMS server otherwise the DATE('now') on different Client machines will vary because each client machine will have a slightly different time.
Now to II_TIMEZONE_NAME. The timezone name is used only when converting a Ingres DATE to a character string or converting a character string to an Ingres DATE.
If you have an OpenROAD entyrfield which has a DATE datatype then there are effectively 2 peices of memory used. One piece which you do not see contains the underlying Ingres Date in GMT format. The other piece of memory is a piece of character memory which is used to display the date onto the form. When the underlying GMT date has a value set you need a refresh of some sort and at that stage the Timezone setting is used to determine the time to display which is then formatted according to FormatString.
This process is exactly the same as integers, floats, decimals and money. If you have an entry field for floats and declare the FormatString as ----------n.n and you assign a value of 0.95 to the field what will be displayed? '1.0'. The underlying value is still 0.95 and if you multiply it by 10.0 you will get a displayed value of? '9.5'.
The timezone setting is also used to convert a character string with a valid date/time format into the underlying GMT value. You will have noticed that the character string you type into an entry field will be converted to the underlying Ingres Date format in GMT and then reconverted to the display character string according to the Format String.
Note: If no format string is actually specified then the defaults for input and for the display character field will depend on the II_DATE_FORMAT setting.
When an OpenROAD application (or ABF or ISQl etc) connect to the database they pass the II_TIMEZONE_NAME and the II_DATE_FORMAT and also the II_DATE_CENTURY_BOUNDARY to the DBMS server which uses these for that connected session. This is necessary so that the conversion of a character string to an Ingres Date is consistent whether it is done in the client or the server. Remember Ingres will do implicit data type conversions where possible. Check these 3 different methods to store a Character date value into a Date column in the database.
1. timestamp_var = DATE('03/10/08 10:15:00'); /* timestamp_var is declared as a date data type. */
INSERT into db_table (date_data_column) values (timestamp_var);
2. INSERT into db_table (date_data_column) values (DATE'('03/10/08 10:15:00''));
2. INSERT into db_table (date_data_column) values ('03/10/08 10:15:00'');
Method 1 above does the conversion from a character string to GMT using the client side II_TIMEZONE_NAME and the II_DATE_FORMAT and also the II_DATE_CENTURY_BOUNDARY and the Ingres Date in GMT format is passed to the DBMS server.
Method 2 and 3 do the conversion from teh character string value at the DBMS server using II_TIMEZ..... values passed accross at the session initiation time.
Notice that the actual character string can have a number of interpretations depending on the setting of the variables. It is important that the client and the server are using the same variable settings.
If you are using an eClient connecting to an Application server then passing an Ingres date datatype accross will have the same GMT value in the eClient and the App Server. If however you pass a character string between the eClient and the App server and either one needs to convert it to an Ingres Date then you will get different results if any of the 3 variables (II_TIMEZ.....) are different on the client and the server.
I know this doesn't directly answer the original query but it may help an understanding.
Bill Allan.
----- Original Message -----
From: Darren Mason
To: 'International OpenROAD Users'
Sent: Friday, April 11, 2008 5:49 PM
Subject: Re: [Openroad-users]eClient &Timezones-CORRECTION KB415539not KB415439
Hi Bodo,
The nature of our application is if an employee is scheduled to start work at a specific time then that is the time - it does not change depending on which timezone you are viewing the data from. I am rostered to start a shift at 7:00 and finish at 15:00, then these times are static. If this is in Perth and I view the data in Sydney then I still want to see that the employee is rostered to start at 7:00 and finish at 15:00 - not for the times to alter.
What compounds this is that many employees using electronic timeclocks to record their start and finish times and these is gathered by the OpenROAD server and loaded - bypassing the local client. They arrive unaltered so the if the employee started in Perth as 6:42 then that is how it needs to be recognised so it will correctly match to the start time of their shift - the OpenROAD server in Sydney needs to know the Perth 07:00 shift start time is, in fact, 07:00.
It is this nature of the application that makes it very different to a scheduling tool where you want the Web conference to reflect different times based on the local timezone.
So yes, in hindsight with these learnings, storing them in a varchar column would have been potentially a better way to go (although we would have lost access to the neat date handling functions we currently utilise). Unfortunately much of the system is built around the Ingres Date usage and date functions provided by Ingres and OpenROAD, and the time to convert the system to use Varchar columns is significant, and then the upgrade of the application to the client base makes this commercially not viable.
So, back to the approach of running the clients and the servers on the same timezone.
If Date ('now') gives the system date on the PC rather than the timezone adjusted view of the current time then this will be perfect, however a previous e-mail of yours indicates date('now') applies the II_Timezone_Name applied during application startup - so would that give the current time in Sydney regardless of the location of the client if all clients were set to the same timezone as the server? IF so, back to the win32 SDK because we would have all clients on the same timezone as the server.
Darren's original timezone question was interestingly different because we need to adjust the times on the clocks accordingly, as we experience daylight savings changes to times between states,. We know the timzone each clock resides in, we just need to know what time to set them to when they are due to change, or if a user simply wants to reset the clock time. The system controls this centrally so we need to process the clocks in 'timezone groups' - but how do we know what time to set them to? We have a group of clocks in Perth, a group in Adelaide, a group in Melbourne, and another group in Brisbane - all in different timezones . You provided a solution where we could, as we process each group, SET the II_Timezone variable through another application / image, and return to the calling up so the times can be processed based on the newly set timezone, repeating this for each timezone. This appears to be an excellent solution to our problem and we will be implementing this.
Durwin's neat enhancement thought, however, would be sensational, having the facility to request a date time for a timezone, either passing no date and a timezone (DATE(ingresdatevalue, ii_timezone_name) to return the current date time for the timezone) - OR-- passing a datetime in the current timezone and it is returned adjusted to the timezone passed into the function. This would address many issues with a simple coding construct, I expect, but way beyond our technical capabilities!
Anyway - thank you all for your contributions. we certainly understand much, much more about date handling and the implication of various scenarios on our solution. Through these discussions we benefit greatly - just hope others have not been too bored along the way!
Regards
Darren Mason
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Bodo Bergmann
Sent: Friday, 11 April 2008 4:42 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] eClient &Timezones-CORRECTION KB415539not KB415439
Darren,
You wrote:
If a Sydney user then access the Perth times, the 9:00am will then be displayed as 11:00am because it is applying the Sydney - NSW timezone. The reverse is true if a user in Perth needed to view times from a Sydney employee. 11:00 am will be displayed as 9:00am.
We fear needing to introduce text fields to store the times that need to be displayed on each screen
I think the handling is what most users that use timezone-aware software would expect - e.g. if I plan a Web conference
for a distinct time (e.g. 4pm) in Germany I want the other attendees take part in it at the correct time, not at 4pm of their local time.
Does this mean that you do not want to have any timezone specific handling at all?
All your clients in all timezones would see the same date/time for dates entered by any client.
Then why don't you just pass the dates as VARCHAR from the clients and store them into a VARCHAR column?
Anyway, for the "remaining SELECT DATE('now') problem" you described:
You just have to pass the client's DATE('now') as a parameter to the OpenROAD server,
which then uses it rather then selecting it from the DB
(e.g. by setting a global variable first, so you don't have to pass it along procedures and methods).
Comments to Durwin:
DATE LITERALS and Timezones
You can actually pass a timezone aware date literal between the client and OpenROAD Server, e.g.
by using the date_gmt() function rather than simpe varchar() conversion.
DATE(ingresdatevalue, ii_timezone_name)
I had already provided a solution for the problem in a former email thread "Timezone Question"
(which was also started by Darren Harvey). This solution can be applied on both client and server.
Bodo.
Bodo Bergmann
Senior Software Engineer
OpenROAD Worldwide Development
Ingres Corp.
------------------------------------------------------------------------------
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Durwin Wright
Sent: Friday, April 11, 2008 3:03 AM
To: International OpenROAD Users
Subject: Re: [Openroad-users] eClient &Timezones-CORRECTION KB415539not KB415439
Hello Chris,
Good point Chris. I have a question. Are you talking about an INGRES DATE or an INGRES DATE LITERAL. Darren wants the value in Perth and Sydney to have the same display value.
I believe what Darren actually wants is some like DATE(ingresdatevalue, ii_timezone_name). Alas, this does not exist. It would be a pretty cool enhancement!
Durwin Wright | Sr. Architect | Durwin.Wright at ingres.com | Ingres | 500 Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA +1 650-587-5523 | fax: +1 650-587-5550
------------------------------------------------------------------------------
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Chris Wallace
Sent: Thursday, April 10, 2008 5:58 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] eClient & Timezones-CORRECTION KB415539not KB415439
Darren,
Storing a 'local' view of the time is only good if you don't care / aren't impacted by daylight savings changes. Particularly, since Perth, Tassie and Sydney/Melbourne are all change to or from daylight savings on different dates.
Regards
Chris Wallace
------------------------------------------------------------------------------
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Darren Mason
Sent: Friday, 11 April 2008 07:39
To: 'International OpenROAD Users'
Subject: Re: [Openroad-users] eClient & Timezones -CORRECTION KB415539not KB415439
Thank you Durwin and Bill,
It seems the consensus is to store a copy of each date field into a text field that represents the 'local' view of the time and then use this for display purposes.
What concerns me is the effort required by us to address the issue in this way. As we are developers of Time & Attendance software, as you would expect, the software is a heavy user of date fields. What compounds this is the implication of rolling such a change out to 100+ clients. Not pretty in a commercial world.
Where our thoughts have evolved to is deploying the eClient so it runs the same Timezone as the server, so Perth WA in effect, is on the Sydney NSW Timezone. This will ensure all data is viewed consistently - I submit 9:00am in Perth it will be displayed as 9:00am in Sydney, etc. Are there any gotchas with this, is this heresy, or a viable approach?
This leaves us with cases where we have used SELECT date('now'). My understanding is this gets the current date and time from the server so bypasses input from the client. So if a user clicks on a button to say they are starting or finishing work it will apply the server view of 'now', which if I was in Perth and the server in Sydney would be out by 2 hours.
To address this I expect we need to get the current system time from the local client and use it (where appropriate). Our audit logs that record when changes were made by users will need to use this alternate approach.
We have not used any Windows API calls to get the time on the client PC previously but I expect it is simple enough.
If anyone can see any 'gotchas' with what we are doing we will gladly hear them!
Thanks.
Regards
Darren Mason
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Durwin Wright
Sent: Friday, 11 April 2008 1:25 AM
To: International OpenROAD Users
Subject: Re: [Openroad-users] eClient & Timezones - CORRECTION KB415539not KB415439
Hello Darren,
It looks like you are dealing with an artifact of the Ingres implementation of the INGRES DATE datatype. Your description of how the INGRES DATE is stored as GMT in the database is accurate. When a local client retrieves an INGRES DATE datatype, it flows across the wire in the GMT normalized form. Internally in the local client it retrains it's GMT form. When it is time to convert the date into a DATE LITERAL is when the timezone offsets are applied.
We tend to think of the INGRES DATE and the INGRES DATE LITERAL as being equivalent. In many cases they do get treated identically.
Maybe you can try introducing an additional column in which you store the the INGRES DATE LITERAL value evaluated on the local OpenROAD eClient by using he VARCHAR(ingresdate) expression. If you have an Ingres database (or EA) connection this expression will evaluate to the same value since the DBMS server will put the session that the client is using into the same timezone name. If you have an eClient that uses an OpenROAD Server connection, the same is not true.
If the Sydney client stores the VARCHAR(ingresdate) value in the database where the time is 11:00 AM, then when the Perth client retrieves this value they will also see 11:00 AM. This can be converted back into an INGRES DATE value by using the DATE(ingres date liternal) expression. One caution, is that when you use this technique, you are effectively using an implicit INGRES DATE WITHOUT TIMEZONE value. Do not forget that the INGRES DATE semantic always assume that the they will be normalized to GMT when they are passed to the Ingres Server.
When the eClient passes an INGRES DATE to the OpenROAD Server, it get's passed also in a GMT normalized form. The only difference, is that when the OpenROAD Server passes the date into an Ingres database, it applies the timezone offset that the OpenROAD Server is set to instead of the timezone offset of the cleint.
Durwin Wright | Sr. Architect | Durwin.Wright at ingres.com | Ingres | 500 Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA +1 650-587-5523 | fax: +1 650-587-5550
------------------------------------------------------------------------------
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Darren Mason
Sent: Thursday, April 10, 2008 2:39 AM
To: 'International OpenROAD Users'
Subject: Re: [Openroad-users] eClient & Timezones - CORRECTION KB415539not KB415439
(on behalf of Darren Harvey)
Thanks for the advice Durwin,
That gives as some ideas on how to ensure we set the correct timezone at each of our interstate sites. Our immediate thought is to check for an .ini file on the client PC that will contain the local timezone value, and if it does not exists then prompt the user to select a timezone. Once this has been done the first time we should be ok from then on. We will start putting ths in place. The problem we are trying to resolve leads down the path of viewing the data after it has been saved. The following is based on the Australian timezones.
Let's use Perth in WA where the is at 9:00am , Sydney in NSW where the time is 11:00am, and GMT being 1:00 at the same time.
What we will be doing is enabling our Perth users to view the time as 9:00am and save it accordingly - all is good.
Our Sydney users will view their times as 11:00am, and saving it accordingly so all is good.
Our understanding is that the times are saved in the db as GMT times, so 9:00am in Perth and 11:00am in Sydney will both be saved as 1:00am GMT. Now we start introducing an issue.
If a Sydney user then access the Perth times, the 9:00am will then be displayed as 11:00am because it is applying the Sydney - NSW timezone. The reverse is true if a user in Perth needed to view times from a Sydney employee. 11:00 am will be displayed as 9:00am.
We fear needing to introduce text fields to store the times that need to be displayed on each screen, and I expect reporting will become an issue as it will be reporting on a collective set of date fields without knowing the source?
Any advice on how to address this would be greatly appreciated.
Regards
Darren Mason
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Durwin Wright
Sent: Thursday, 10 April 2008 5:59 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] eClient & Timezones - CORRECTION KB415539 not KB415439
Are you using the eClient ENVIRONMENT= directive in your eclient in the INSTALL4GL.TXT file that you use to package the eClient application? An excerpt fron the readme.txt for the eCleint is as follows,
ENVIRONMENT
This line can appear ZERO or more times. The string to the right of
the first "=" sign is treated as an environment variable specification.
These environment variables will be set in the eClient runtime process
before the OpenROAD runtime is initialized.
The variable name is separated from the variable value by the second
"=" sign, similar to the syntax for using "set" in a command window.
Variable values can refer to other variables using the standard %xxx%
syntax (such as in the %II_ECLIENT_APPDIR% example shown above).
The following "built-in" variables can be referenced by your own
definitions:
II_ECLIENT_APPDIR
This will be set to the current eClient application's
installation directory, which is also the current directory
when the OpenROAD runtime executes this eClient application.
II_ECLIENT_ROOT
This will be set to the parent directory of the application's
installation directory.
II_ECLIENT_LIBDIR
This will be set to the eClient shared library installation
directory.
(Other predefined variables are described in a later section.)
Variable definitions are processed in two passes. First the variables
that do not reference any other variables (i.e., those that do not
contain any "%" characters) are set. In the second pass, the variables
that contain "%" references are expanded using the current environment
settings, and then set.
The order in which variables are processed within each pass is not
defined. Therefore, you should not attempt to use more than one level
of nesting in your variable definitions.
Normally the II_TIMEZONE_NAME is retrieved from the SYMBOL.TBL for the nstallation. The eClient does not use a SYMBOL.TBL. If there is an Ingres installation on the machine where the eClient is deployed, then you could use the ENVIRONMENT directive to set II_SYSTEM to an explicit value.
An alternative to the SYMBOL.TBL is to explicitly use the SET command to specific an II_TIMEZONE_NAME vlaue. This environment variable cannot be set after the eClient starts the underlying OpenROAD runtime. The ENVIRONMENT directive is processed prior to the eClient OpenROAD runtime initializing and performs a SET on behalf of the eClient prior to the initialization of the eClient OpenROAD runtime.
Take a look at Ingres KB415539, "How to run an eClient application using different settings for II_W4GLAPPS_DIR to use different image versions". It specifically refers to II_W4GLAPPS_DIR but the technique cold easily be generalized to having a previously Windows environment variable set that contains the local value of II_TIMEZONE_NAME.
Durwin Wright | Sr. Architect | Durwin.Wright at ingres.com | Ingres | 500 Arguello Street | Suite 200 | Redwood City | CA | 94063 | USA +1 650-587-5523 | fax: +1 650-587-5550
------------------------------------------------------------------------------
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Darren Harvey
Sent: Wednesday, April 09, 2008 7:17 PM
To: 'International OpenROAD Users'
Subject: [Openroad-users] eClient & Timezones
I have a question re the handling of Timezones within an eClient environment.
Assume the time in NSW is 11:00 and Western Australia is 09:00.
We have built the Cab file through the OR Web-Publisher and specified an environment parameter of 'II_TIMEZONE_NAME=AUSTRALIA-NSW'. This works fine in NSW, however when users in Western Australia run the eClient, they see the 'current' time as 11:00 instead of the correct local time of 09:00. (I assume this is correct from an OR point of view? But unfortunately it isn't what we require.)
Does this mean we need to build a separate Web application for Western Australian users, that specifies a parameter of 'II_TIMEZONE_NAME=AUSTRALIA-WEST'? And that this would then show the 'current' time as 09:00.
I hope this makes sense.
Thanks
Darren
Regards
Darren Harvey
------------------------------------------------------------------------------
________________________________________________________________
OpenROAD-Users mailing list
You can maintain your subscription here:
http://www.peerlessit.com/mailman/listinfo/openroad-users
To unsubscribe click on this link
mailto:openroad-users-unsubscribe at peerlessit.com&subject=unsubscribe
To subscribe click on this link
mailto:openroad-users-subscribe at peerlessit.com&subject=subscribe
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.peerlessit.com/pipermail/openroad-users/attachments/20080411/fadbe3df/attachment-0001.html
More information about the Openroad-users
mailing list