[Openroad-users] Timezone Question
Simon Lovell
simonl at unwired.com.au
Thu Mar 13 16:05:29 EST 2008
Wouldn't the MESSAGE statement be not quite what is required? You need
it to get to the log file, so the best way would be to use
CurProcedure.Trace(text = varchar(date('now'))).
Darren Harvey wrote:
>Many thanks for all the responses.
>
>Bodo, I will give your suggestion a go as it sounds simple and exactly what
>we need.
>
>Thanks Again
>
>Darren
>
>
>
>-----Original Message-----
>From: openroad-users-bounces at peerlessit.com
>[mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Bodo Bergmann
>Sent: Wednesday, 12 March 2008 11:22 PM
>To: International OpenROAD Users
>Subject: Re: [Openroad-users] Timezone Question
>
>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
>
>________________________________________________________________
>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