Re: how to set SYSDATE to return GMT

From: <ddf_dba_at_my-deja.com>
Date: 2000/06/29
Message-ID: <8jg7pr$p9m$1@nnrp1.deja.com>#1/1

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.

Received on Thu Jun 29 2000 - 00:00:00 CDT