Re: how to set SYSDATE to return GMT

From: Mark D Powell <markp7832_at_my-deja.com>
Date: 2000/06/29
Message-ID: <8jg2ih$kt3$1@nnrp1.deja.com>#1/1

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.

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