[Openroad-users] Timezone Question
Bodo Bergmann
Bodo.Bergmann at ingres.com
Wed Mar 12 23:21:53 EST 2008
Unfortunately that wouldn't work.
SetEnv sets the Environment for the current process,
but as the OpenROAD runtime only reads and processes it at startup time,
DATE('NOW') would still return the time according to the II_TIMEZONE_NAME setting during application start.
That's why I used a different image, which is then started using the modified environment,
as CALL SYSTEM uses the environment of the starting process, within II_TIMEZONE_NAME had been changed.
Setenv does not write to symbol.tbl, so there are no permanent changes involved.
But setting an environment variable takes precedence to the symbol.tbl settings.
So, you only have to SetEnv II_TIMEZONE_NAME back to its original value, if you start other processes using
CALL SYSTEM that require the correct local timezone.
Bodo.
-----Original Message-----
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Pete Rabjohns
Sent: Wednesday, March 12, 2008 1:07 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] Timezone Question
Hi Bodo,
Interesting point - never thought about using the SetEnv method.
Could the code be re-written as:
PROCEDURE GetLocalTime (tz = VARCHAR(20) NOT NULL) =
DECLARE
Tzdate = DATE NOT NULL;
SavedTz = VARCHAR(20) NOT NULL;
BEGIN
SavedTz = CurSession.GetEnv ('II_TIMEZONE_NAME');
CurSession.SetEnv(envstring='II_TIMEZONE_NAME='+tz);
TzDate = DATE('now');
CurSession.SetEnv(envstring='II_TIMEZONE_NAME='+savedTz);
RETURN tzDate;
END;
Or does the SetEnv actually write back to symbol.tbl and only take effect when OpenROAD starts up? If so, won't the example you have given make permanent changes to the OpenROAD environment table?
Pete
-----Original Message-----
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Bodo Bergmann
Sent: 12 March 2008 11:35
To: International OpenROAD Users
Subject: Re: [Openroad-users] Timezone Question
Here is a pure OpenROAD Solution (which doesn't require an Ingres database):
1. Create an application 'tz' containing one procedure 'writecurtime' with the simple code:
procedure writecurtime() =
{
MESSAGE VARCHAR(DATE('NOW'));
}
2. Create the image of this application - tz.img - and place it into your II_W4GLAPPS_DIR directory
3. In your application where you need the current time create a 4GL procedure 'getLocalTime' with the following code:
PROCEDURE getLocalTime(
tz=VARCHAR(20) NOT NULL
)=
declare
str = StringObject;
enddeclare
{
CurSession.SetEnv(envstring='II_LOG=C:\temp'); // to specify the place where logfile is created
CurSession.SetEnv(envstring='II_TIMEZONE_NAME='+tz);
CALL SYSTEM 'w4glrun tz.img -Ltz.log';
str.FileHandle = 'C:\temp\tz.log';
RETURN str.Value;
}
Now you can use the 4GL procedure whereever you need like:
MESSAGE GetLocalTime(tz='AUSTRALIA-VICTORIA');
Hope this helps.
Bodo.
Bodo Bergmann
Senior Software Engineer
OpenROAD Worldwide Development
Ingres Corp.
-----Original Message-----
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Kitson, Charles
Sent: Wednesday, March 12, 2008 10:34 AM
To: 'International OpenROAD Users'
Subject: Re: [Openroad-users] Timezone Question
Hi Durwin, Darren,
I recall looking into this way back in the late 1990's when I worked on
Ingres DBMS at CA.
>From what I recall, unfortunately there's been a lot of water under the
bridge since then, the SQL timezone was a 3 character representation which
can use GMT as you found. It will also accept 'edt' and 'est' as
representing eastern daylight time and eastern standard time for North
America.
* select date('12-mar-2008 12:23 edt')
* \g
Executing . . .
+-------------------------+
│col1 │
+-------------------------│
│12/03/08 16:23:00 │
+-------------------------+
(1 row)
continue
* select date('12-mar-2008 12:23 est')
* \g
Executing . . .
+-------------------------+
│col1 │
+-------------------------│
│12/03/08 17:23:00 │
+-------------------------+
(1 row)
At the time I was corresponding with Bill(?) from Australia because he was
trying to use 'edt' for Australia but Ingres could not make any distinction
between 'edt' for America and 'edt' for Australia. I think it may have been
logged as an enhancement. As I no longer work for CA I do not have access to
those original emails, perhaps I created Startrak solution but don't know if
they became Ingres knowledge base documents.
Regards
Chas Kitson
> -----Original Message-----
> From: openroad-users-bounces at peerlessit.com
> [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Durwin Wright
> Sent: 12 March 2008 02:27
> To: International OpenROAD Users
> Subject: Re: [Openroad-users] Timezone Question
>
Hello Darren,
I looked in the SQL Reference Guide as well and saw the following,
Absolute Time Input Formats
The legal format for inputting an absolute time is
'hh:mm[:ss] [am|pm] [timezone]'
Input formats for absolute times are assumed to be on a 24-hour clock. If a
time
with an am or pm designation is entered, then the time is converted to a
24-hour
internal and displayed representation.
If timezone is omitted, the local time zone designation is assumed. Times
are
stored as Greenwich Mean Time (GMT) and displayed using the time zone
adjustment specified by II_TIMEZONE_NAME. For details about time zone
settings and valid time zones, see your Getting Started guide.
If an absolute time without a date is entered, the date defaults to the
current
system date.
When I tried your example the following would work,
select date('12-mar-2008 12:23 gmt1')
thru
select date('12-mar-2008 12:23 gmt12')
select date('12-mar-2008 12:23 gmt+1')
thru
select date('12-mar-2008 12:23 gmt+12')
select date('12-mar-2008 12:23 gmt-1')
thru
select date('12-mar-2008 12:23 gmt-12')
However any other valid II_TIMEZONE_NAME would not work including fractional
offsets like "GMT-1.5".
This behavior rang a bell in my head because it resembles the behavior of
the old II_TIMEZONE values instead of the II_TIMEZONE_NAME values.The
II_TIMEZONE was last used in INGRES 6.4 and was deprecated in Open Ingres
1.1. Perhaps the documentation is a holdover from INGRES 6.4.
You have either run into a documentation problem in the description of
Absolute Time Formats or the date() scalar function needs to be expanced to
allow the use of any valid II_TIMEZONE_NAME value. I personally would like
to see the latter. I will pose this question to my colleagues in Ingres
Corporation and see if anyone else remembers any specifics.
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
Chris.Beall at dhs.vic.gov.au
Sent: Tuesday, March 11, 2008 5:30 PM
To: International OpenROAD Users
Subject: Re: [Openroad-users] Timezone Question
Hi Darren
I noticed there is a syntax in the SQL Reference Guide which includes the
timezone.
Absolute Time Input Formats
The legal format for inputting an absolute time is
'hh:mm[:ss] [am |pm] [timezone]'
I experimented with various timezones, and could not seem to get non-gmt
timezones (e.g. "australia-victoria", "na-pacific") to work. This could be
how our installation is built. However gmt seemed to work.
1> select date('12-mar-2008 12:23 gmt9')
+-------------------------+
│col1 │
+-------------------------│
│12.03.2008 14:23:00 │
+-------------------------+
(1 row)
Regards, Chris
Chris Beall : Department of Human Services
(03) 9096 5644 : Chris.Beall at dhs.vic.gov.au
Hi Darren
You may want to look at having OpenROAD deamons running with specific
timezones, and sending database events to the appropriate daemon which has
the timezone that you want to lookup. The daemon could then reply with
another database event with the answer.
Regards,
Robert Allely
POAL Auckland NZ
From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Darren Harvey
Sent: Wednesday, 12 March 2008 10:34 a.m.
To: 'International OpenROAD Users'
Subject: Re: [Openroad-users] Timezone Question
Hi Gareth
Unfortunately the need is a bit more complex than that. We have external
clocking devices that are connected via TCP/IP and may exist in any
Timezone, and we need to be able to send the local time to these devices. I
will allocate a Timezone type variable to the definition of the clocks. My
theory is that we know that the TIMEZONE the clock resides in is
'AUSTRALIA-SOUTH' or 'AUSTRALIA-WEST' or 'NZ', etc. And I need to be able to
send a string parameter to the clocks that contains the correct local time.
I don't want to rely on just adding or deducting a pre-specified number of
minutes from the server based time, as we need to take into account Daylight
Savings changes. So I would like to have some method to get me the current
time for a specified Timezone.
Thanks
Darren
Regards
Darren Harvey
<<...OLE_Obj...>>
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.
From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of
gareth.2.edwards at bt.com
Sent: Tuesday, 11 March 2008 10:46 PM
To: openroad-users at peerlessit.com
Subject: Re: [Openroad-users] Timezone Question
Just take half an hour off?
ACDT = date('now') - '30 mins';
Cheers,
Gareth Edwards
BT Global Services
tel: +44 (0)131 345 3629
email: gareth.2.edwards at bt.com <mailto:gareth.2.edwards at bt.com>
From: openroad-users-bounces at peerlessit.com
[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Darren Harvey
Sent: Friday, March 07, 2008 6:25 AM
To: 'OpenRoad Users'
Subject: [Openroad-users] Timezone Question
Hi All
I need to be able to determine the current time in a different Timezone. For
example: If my Ingres Timezone parameter is set to 'AUSTRALIA-VICTORIA', how
can I determine the current time in 'AUSTRALIA-SOUTH'?
(Using OR2006 with Ingres2006)
Thanks
Darren
Regards
Darren Harvey
<<...OLE_Obj...>>
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 e-mail is privileged and confidential. If you are not the intended
recipient please delete the message and notify the sender at Ports of
Auckland Limited.
________________________________________________________________
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
____________________________________________________________________________
_____
This email contains confidential information intended only for the person
named above and may be subject to legal privilege. If you are not the
intended recipient, any disclosure, copying or use of this information is
prohibited. The Department provides no guarantee that this communication is
free of virus or that it has not been intercepted or interfered with. If you
have received this email in error or have any other concerns regarding its
transmission, please notify Postmaster at dhs.vic.gov.au
____________________________________________________________________________
_____
>
**********************************************************************
This e-mail including any attachments is provided for general information purposes only and does not constitute advice except to the addressee if expressly stated in this email. For the avoidance of doubt the contents of this email and any attachments are subject to contract and are not intended to and will not constitute a legally binding contract or any part of a legally binding contract.
The information contained in this email and any attachments is confidential. It is intended only for the use of the intended recipient at the email address to which it has been addressed. If the reader of this message is not an intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution or copying of the message or associated attachments is strictly prohibited.
If you have received this e-mail in error, please notify postmaster at RWEsystemsUK.com and permanently delete the email and any attachments immediately from your system. You should not retain, copy or use this email or any attachment for any purpose, nor disclose all or any part of the contents to any other person. Thank you for your co-operation.
Neither RWE Systems UK Ltd nor any of the other companies in the RWE Group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses.
RWE Systems UK Ltd Registered office: Mistral, Westlea Campus, Chelmsford Road, Swindon, Wiltshire, SN5 7EZ. Registered in England and Wales: company number 6052966.
*******************************************
________________________________________________________________
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
________________________________________________________________
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
________________________________________________________________
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
More information about the Openroad-users
mailing list