Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to set SYSDATE to return GMT

Re: how to set SYSDATE to return GMT

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

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


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Jun 29 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US