Steven Haas wrote:
>
> Hey folks,
>
> Oracle 8.1.7, Solaris
>
> I have a possible requirement that all record
> timestamps must use GMT. Has anyone found an
> easy way to determine the sysdate timezone to use
> in the new_time() function.
>
> Thanks much...
>
> Steve
Steve,
I have very recently played a little bit with such things, and it
seems to me pretty messy, especially when you happen to live in time
zones whence you need not even think about sending your resume to
OraStaff (seems better in 9.x).
I have a few days ago discovered the command :
ALTER DATABASE SET TIME_ZONE = 'blahblah';
where 'blahblah' can be specified either as a code (which doesn't really
solve your problem) or as '+00:00' or '-00:00' to specify your offset
to/from GMT, which I find easier to guess.
Once your database knows where it stands, you can use function
dbtimezone (which for a reason I have been too lazy to find out I had to
specify as sys.standard.dbtimezone in a procedure) to get it. Then, you
can compute the GMT date as follows :
declare
my_timezone varchar2(30);
pos number;
timeoffset number;
begin
select sys.standard.dbtimezone
into my_timezone
from dual;
pos := instr(my_timezone, ':');
if (pos = 0)
then
--
-- Timezone was specified as an abbreviation
--
dbms_session.set_nls('NLS_DATE_FORMAT', 'DD-MON-YYYY HH24:MI:SS');
timeoffset := SYSDATE - NEW_TIME(SYSDATE, my_timezone, 'GMT');
else
--
-- Timezone was specified as a time offset
--
timeoffset := to_number(substr(my_timezone, 2,
pos-2))/24
+ to_number(substr(my_timezone,
pos+1,2))/1440;
if (substr(my_timezone, 1, 1) = '-')
then
timeoffset := -1 * timeoffset;
end if;
end if;
end;
Create a packaged function GMTDATE, compute timeoffset as above in the
initialization section of the package, and then simply make GMTDATE
return SYSDATE + timeoffset and it should do.
HTH,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
