[Openroad-users] eClient & Timezones

Fri Apr 11 10:43:30 EST 2008

Hello Darren and Fabian,

Darren, you had it figured out in your first email!  I have always
thought that the INGRES DATE datatype has some of the strangest
behaviors that I have ever seen.  I am gong to repeat some of the
information that has been given in the previous thread.  

Your requirements dictate that you do not want store the DATETIME values
of an INGRES DATE as an INGRES DATE in the database.  You want to store
the DATETIME values as a INGRES DATE LITERAL value.

There is some confusion between the INGRES DATE datatype and the INGRES
DATE LITERAL semantics.  INGRES DATE datatypes are always stored
internally as GMT normalized values.  Only when it is needed to be
displayed are the TIMEZONE offsets applied.  These offsets are applied
identically on both the Client an the Ingres Server.  An INGRES DATE
LITERAL is exactly that -- a literal string value.  No time zone offsets
are applied to the INGRES DATE LITERAL.  The DATE() function will turn
the INGRES DATE LITERAL into a local time.  

What you are running into is the fact that the INGRES DATE datatype is
heavily overloaded.  It is simultaneously a DATE, TIMESTAMP (with
timezone), a TIME (sort of/kind of) and an INTERVAL.  You can have a
single column in which each row has a different semantic meaning.  Throw
in the fact that Ingres also supports the bizarre notion of an "Empty
Date" (which is different from a Null Date) and you begin to see the
issues. 

Fundamentally, the Ingres runtime assumes that INGRES DATE datatypes are
encoded internally as normalized GMT values.  When a client connects to
the Ingres Server it passes a GCA connection parameter that passes the
TIMEZONE_NAME of the client to the Ingres Server.  The Ingres Server
uses this to initialize the Ingres session control block associated with
that client.  You may wonder why would we jump through all of these
hoops...Well what Ingres is trying to do is to make sure that any
expression evaluation done by the Ingres Abstract Data Facility (ADF) to
convert DATE LITERAL values behaves the same way on the client as the
server.

Let's use an example.  It is 900 AM GMT.  In New York the time will be
4:00 AM.  In Los Angeles the time will be 1:00 AM.  If you have the
Ingres server that is located in New York, then a client in New York
that inserts an INGRES DATE that is 4:00 AM will have the internal date
stored as 9:00 AM GMT.  The same will be true for the Los Angeles client
that inserts an INGRES DATE or 1:00 AM.

You get this weird effect in which the local time that the client uses
is always normalized to a GMT value before it is stored in the database.
This is how this works.  I do not pretend to understand the reasons why
but this is what we have to live with.  

In the case of the query "select date('now')" it depends on what
timezone that the client is located.  The passing of the TIMEZONE NAME
from the client to the server always ensures that the value returned is
the local time of the current GMT time.  Confusing?  

In the case of the OpenROAD Server we always pass the INGRES DATE values
as normalized GMT values.  As long as no attempt is made to use a DATE
LITERAL everything is okay.  An OpenROAD eClient will not pass it's
local timezone name to the OpenROAD Server, because fundamentally, there
is nothing that the OpenROAD Server can do.  It already has a connection
to an Ingres Database and has thus passed it's TIMEZONE NAME to the
Server.  Changing the clients TIMEZONE NAME would cause the two to get
out of sync.  You could disconnect from the database, set the TIMEZONE
NAME to a different value and then reconnect, but then you would lose
the benefits of OpenROAD Server connection pooling.

What we have today has a lot of strange behaviors.  The reason that we
have not changed these behaviors is that we realize that there are
existing applications that depend on the existing behaviors.

In summary, if you really want to save a DATETIME value in the database
from clients in multiple time zone locations and guarantee that they all
will display the same value, then you cannot store the values as INGRES
DATE datatypes.  They must be stored as string literal values.

Durwin Wright | Sr. Architect | Durwin.Wright at ingres.com
<blocked::mailto:Durwin.Wright at ingres.com>  | Ingres | 500 Arguello
Street | Suite 200 | Redwood City | CA | 94063 | USA
<blocked::http://maps.google.com/maps?q=500+arguello+street,+94063&ll=37
.487297,-122.233200&spn=0.004602,0.012771&t=k&hl=en>   +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 Fabian
Anderson
Sent: Thursday, April 10, 2008 4:56 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] eClient & Timezones-CORRECTION KB415539not
KB415439

Hi Darren,

            Using the Win32 SDK to get the timezone from the control
panel is the way it would work normally (for any other distributed
app)...so I think the same approach would be needed for eClient.  

            I find it odd though that the eClient doesn't already offer
a function that gets this information, but rather provides a means to
pass the environment from the server.

            I would proceed with the Win32 SDK idea, but only for
display/conversion purposes.  You should not need to store an additional
date/varchar though.

Cheers,

Fabian Anderson

Systems Analyst

Fintechnix Pty Ltd

________________________________

From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Darren Mason
Sent: Friday, 11 April 2008 9:39 AM
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

MyWorkplace Solutions Pty Limited

Level 5, 11 Queens Road

Melbourne Victoria 3004

Ph.   1300 733 731

Mob. 0419 337 170

Fax.  03 9710 1112

Making Service our Priority

www.MyWorkplace.com.au

If you receive this email by mistake, please notify us and do not make
any use of the email. We do not waive any privilege, confidentiality or
copyright associated with it.

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
<mailto:Durwin.Wright at ingres.com>  | Ingres | 500 Arguello Street |
Suite 200 | Redwood City | CA | 94063 | USA
<http://maps.google.com/maps?q=500+arguello+street,+94063&ll=37.487297,-
122.233200&spn=0.004602,0.012771&t=k&hl=en>   +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

MyWorkplace Solutions Pty Limited

Level 5, 11 Queens Road

Melbourne Victoria 3004

Ph.   1300 733 731

Mob. 0419 337 170

Fax.  03 9710 1112

Making Service our Priority

www.MyWorkplace.com.au

If you receive this email by mistake, please notify us and do not make
any use of the email. We do not waive any privilege, confidentiality or
copyright associated with it.

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
<mailto:Durwin.Wright at ingres.com>  | Ingres | 500 Arguello Street |
Suite 200 | Redwood City | CA | 94063 | USA
<http://maps.google.com/maps?q=500+arguello+street,+94063&ll=37.487297,-
122.233200&spn=0.004602,0.012771&t=k&hl=en>   +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

MyWorkplace Solutions Pty Limited

Level 5, 11 Queens Road

Melbourne Victoria 3004

Ph.       1300 733 731

Mob.     0400 398 188

Fax.      03 9710 1112

Making Service our Priority

www.MyWorkplace.com.au

If you receive this email by mistake, please notify us and do not make
any use of the email. We do not waive any privilege, confidentiality or
copyright associated with it.

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________

Specialist providers of back and front office systems for the financial
services industry.
Featuring: Fintechnix(r)
Disclaimer: 
Notice: This message contains privileged and confidential information
intended only for the use of the addressee named above. If you are not
the intended recipient of this message you are hereby notified that you
must not disseminate, copy or take any action in reliance on it.
Any views expressed in this message are those of the individual
sender,except where the sender specifically states them to be the views
of Fintechnix Pty Ltd.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.peerlessit.com/pipermail/openroad-users/attachments/20080410/ccb68517/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 1436 bytes
Desc: image001.jpg
Url : http://www.peerlessit.com/pipermail/openroad-users/attachments/20080410/ccb68517/attachment.jpe 