Re: how to set SYSDATE to return GMT

I heard Oracle 8.2 will include real timezone support (grep the 8.1.6 catalog sql files for the UTC/GMT support coming our way). Meanwhile we've written a simple Java stored procedure to pull this out of System.getProperty("user.timezone").

Evgenii Bazarov wrote in message <8jmbm2$17j$1_at_nnrp1.deja.com>...
>Thanks a lot for the answers! However both answers
>address only CLIENT solutions. I would like to find
>total solution dealing with server.
>
>I was under impression that SYSDATE is actually returns
>the server time. Imagine different types of clients
>(sqlplus, ColdFusion server, EJB server etc) running
>on distributed network in New-York, London, LA, Tokyo
>and accessing Oracle server in New-York. I want
>SYSDATE consistently return the GMT for all types of client
>applications and operating systems.
>
>Is this possible with Oracle 8.1.5(6)?
>
>Best regards,
>Evgenii
>
>In article <8jg7pr$p9m$1_at_nnrp1.deja.com>,
> ddf_dba_at_my-deja.com wrote:
>> In article <8jg2ih$kt3$1_at_nnrp1.deja.com>,
>> Mark D Powell <markp7832_at_my-deja.com> wrote:
>> > In article <8jfmck$agu$1_at_nnrp1.deja.com>,
>> > ddf_dba_at_my-deja.com wrote:
>> > > In article <8jfibi$778$1_at_nnrp1.deja.com>,
>> > > evgeniibazarov_at_usa.net wrote:
>> > > > Hello,
>> > > >
>> > > > Is there way to set up Oracle server such that
>> > > > each SYSDATE always returns GMT. I prefer to avoid
>> > > > any changes in Unix time zone settings. The server
>> > > > runs on Solaris.
>> > > >
>> > > > I was trying to find any relevant settings in *.ora
>> > > > config files on my server, but couldn't find anything.
>> > > >
>> > > > Thanks,
>> > > > Evgenii
>> > > >
>> > > > Sent via Deja.com http://www.deja.com/
>> > > > Before you buy.
>> > > >
>> > >
>> > > Since Oracle is pulling the current date and time from the
 operating
>> > > system the only way I know of to return GMT is to set the TZ
 environment
>> > > variable to GMT:
>> > >
>> > > TZ=GMT; export TZ (for Bourne shell, bash, Korn shell)
>> > >
>> > > This is a local timezone setting and does not affect the actual
 system
>> > > date/time. This can be done for each user account that accesses
 the
>> > > database or you can use a "wrapper" to sqlplus to set the TZ
 variable
>> > > and call sqlplus:
>> > >
>> > > #!/bin/sh
>> > > #
>> > > # splus
>> > > #
>> > > # Run SQL*Plus so Oracle will return GMT
>> > > # for SYSDATE calls
>> > > #
>> > >
>> > > # Set the environment
>> > >
>> > > . /home/oracle/.profile
>> > >
>> > > # Reset the local timezone variable to GMT
>> > >
>> > > TZ=GMT; export TZ
>> > >
>> > > # Run SQL*Plus
>> > >
>> > > sqlplus "$1"
>> > >
>> > > This will "set" the TZ variable for the SQL*Plus process but leave
 it
>> > > undisturbed for OS processes since the script starts another shell
>> > > process to execute the script commands; once the script terminates
 the
>> > > timezone returns to the value set at login. This avoids setting
 all
>> > > users accessing the database to the GMT timezone and still returns
 GMT
>> > > for SQL*Plus queries.
>> > >
>> > > There is an 'nls_time_tz_format' initialization parameter but I
 cannot
>> > > find any information as to its use. Currently the search engine
 at
>> > > www.oracle.com is not working; maybe someone else out there has
 set
 this
>> > > parameter before and knows what values are expected.
>> > >
>> > > I hope this helps somewhat. I apologize for not having more
 information
>> > > to share.
>> > > --
>> > > David Fitzjarrell
>> > > Oracle Certified DBA
>> > >
>> > And why not just use the built-in function:
>> > UT1> l
>> > 1 select to_char(sysdate,'YYYYMMDD HH24:MI:SS') Local,
>> > 2 to_char(new_time(sysdate,'EDT','GMT'),'YYYYMMDD
>> > HH24:MI:SS') GMT
>> > 3* from sys.dual
>> > UT1> /
>> >
>> > LOCAL GMT
>> > ----------------- -----------------
>> > 20000629 13:52:55 20000629 17:52:55
>> >
>> > I believe that the East Coast is 5 hours from GMT so Daylight saving
>> > time would be 4 hours so this appears correct, but date/time is not
 my
>> > speciality.
>> > --
>> > Mark D. Powell -- The only advice that counts is the advice that
>> > you follow so follow your own advice --
>> >
>> > Sent via Deja.com http://www.deja.com/
>> > Before you buy.
>> >
>>
>> I consider a day wasted when I do not learn something new.
>>
>> --
>> David Fitzjarrell
>> Oracle Certified DBA
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Sun Jul 02 2000 - 00:00:00 CDT