[Openroad-users] Timezone Question

Wed Mar 12 13:26:32 EST 2008

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 

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 

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

_________________________________________________________________________________

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.peerlessit.com/pipermail/openroad-users/attachments/20080311/2e6cb820/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/20080311/2e6cb820/attachment.jpe 