Oracle-L: Re: usage of new

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Thu, 14 Nov 2002 08:18:37 -0800
Message-ID: <F001.005035EF.20021114081837@fatcity.com>

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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).

Received on Thu Nov 14 2002 - 10:18:37 CST

Re: usage of new_time() function