Re: Getting GMT Time in Oracle?

From: spencer <spencerp_at_swbell.net>
Date: Tue, 31 Oct 2000 23:56:10 -0600
Message-ID: <JeOL5.312$K93.174519@nnrp1.sbc.net>

>
> This is probably a simple question, but I haven't figured it out
> yet. I would like to get the current time in GMT from Oracle (8,
> HP-UX).
>
> The 'sysdate' function returns the local time. But, it does not
> seem to return the time zone. The NEW_TIME() function also seems
> to not be of much help, because I need to have the current
> time zone, plus I would need to know if I'm in DST or not.
>

you are correct, oracle 'sysdate' does not include timezone, so you have to have some other way to know the "timezone".

for my oracle instances running on HP-UX i know the timezone since the TZ environment variable for the oracle userid is set (in the .profile) to GMT ( TZ=GMT; export TZ ), so that 'sysdate' is always reported in GMT.

the downside (if you consider it one) is that all 'date' entries in the system tables and logfiles are reported in GMT.

and you are absolutely right about the NEW_TIME() function being of no help... in order to use it, you need to know whether daylight savings time is in effect or not... both of these functions return a result:

NEW_TIME(sysdate,'GMT','CDT')
NEW_TIME(sysdate,'GMT','CST')

when what you need is for this expression to return a result:

NEW_TIME(sysdate,'GMT','CST5CDT')

this functionality is built in to HP-UX 11, with daylight savings time changes handled for timezones nearly worldwide.

if oracle will not provide the functionality, then maybe someone else could provide it as a module that calls the native HP-UX time functions... and somehow link it as a shared library (like a windows dll), and be able provide an oracle package to access the module...

the package name could be something like   HPUX
with functions defined like:

• to return the setting of the TZ environment variable for oracle
• (the HP-UX timezone associated with oracle sysdate) FUNCTION get_oracle_tz RETURN VARCHAR2;
• return sysdate value in specified HP-UX timezone FUNCTION get_local_sysdate (as_timezone VARCHAR2 DEFAULT NULL )RETURN DATE;
• return current date in GMT FUNCTION get_gmt_sysdate RETURN DATE;
• return current date in GMT FUNCTION new_time (ad_date_value IN DATE ,as_time_zone IN VARCHAR2 ,as_local_tz IN VARCHAR2 )RETURN DATE;

etc.

these functions could then be used in PL/SQL, and with the correct pragma restrict_references settings, perhaps even within SQL statements.

DECLARE

  ls_oracle_tz VARCHAR2(30);
  ls_local_tz VARCHAR2(30);
  ld_local_dt DATE;

BEGIN

• get TZ environment variable in effect for oracle ls_oracle_tz := HPUX.get_oracle_tz;

  DBMS_OUTPUT.PUT_LINE('oracle tz='||ls_oracle_tz);

• convert date value to desired time zone ls_local_tz := 'CST5CDT'; ld_local_dt := HPUX.new_time(sysdate,ls_oracle_tz,ls_local_tz);

  DBMS_OUTPUT.PUTLINE(to_char(ld_local_dt,'MM/DD/YYYY HH24:MI')     ||' '||ls_local_tz);

END; Received on Tue Oct 31 2000 - 23:56:10 CST