isitdevops.com/databases

create or replace
FUNCTION fn_adjusted_date (input_date NUMBER)  
RETURN DATE       IS  
loutput_date   DATE;  
linput_date      DATE;  
lyear               INT;  
lcurrentyear     INT;  
loffset             NUMBER(20,16) :=0;
BEGIN    

  linput_date := TO_DATE (’01-JAN-1970′) + input_date * 0.0000115740740740741;  

  IF input_date IS NULL     
  THEN
    loutput_date:= NULL;   
  ELSE     
    SELECT linput_date + DECODE (SUBSTR (TZ_OFFSET (DBTIMEZONE), 1, 1), ‘-‘, -1, ‘+’, 1) * TO_DSINTERVAL
   (‘0 ‘ || SUBSTR (TZ_OFFSET (DBTIMEZONE), 2, 5)|| ‘:00’) INTO loutput_date FROM DUAL;       
  END IF;  

RETURN loutput_date;

EXCEPTION   
  WHEN NO_DATA_FOUND  
    THEN NULL;

END fn_adjusted_date;

As per the Remedy 7.1 Database Reference guide,

Appendix A

To convert the date and time format for an Oracle® database

1 Using any front-end tool that enables direct access to an Oracle SQL database,
log in as a user with write access to the AR System tables.

2 Type the following command:
SELECT TO_CHAR(TO_DATE(’01/01/1970 00:00:00′, ‘MM/DD/YYYYHH24:MI:SS’) + 
            ((C + )/(60*60*24)),’MM/DD/YYYY HH24:MI:SS’) FROM T;
where      
  is the number of the column for the date and time field,
  is the number of the form table, 
  is a positive or negative number representing the number of seconds later or earlier than GMT.

See the your Oracle documentation for information about the TO_DATE and TO_CHARfunctions.

select to_char(sysdate,’dd-mon-yy hh24:mi:ss’),
       to_char(sysdate+to_dsinterval(‘0 ‘ || SUBSTR (TZ_OFFSET (DBTIMEZONE), 2, 5)|| ‘:00′),’dd-mon-yy hh24:mi:ss’)
from dual;

declare loutput_date date;
  linput_date date;
  tmp varchar2(30);
begin
  linput_date := sysdate;
  SELECT linput_date + DECODE (SUBSTR (TZ_OFFSET (DBTIMEZONE), 1, 1), ‘-‘, -1, ‘+’, 1) * TO_DSINTERVAL
   (‘0 ‘ || SUBSTR (TZ_OFFSET (DBTIMEZONE), 2, 5)|| ‘:00’) INTO loutput_date FROM DUAL;
  select to_char(loutput_date,’dd-mon-yy hh24:mi:ss’) into tmp from dual;
  dbms_output.put_line(tmp);
end;
/

select DBTIMEZONE from dual;
ALTER database SET TIME_ZONE = ‘America/Denver’;
ALTER database SET TIME_ZONE = ‘-07:00’;
select SYSTIMESTAMP from dual;
select tzname,tzabbrev from V$TIMEZONE_NAMES where tzabbrev = ‘GMT’

select to_char(current_date,’dd-mon-yy hh24:mi:ss’) from dual;

You need to stop using DATE and start using TIMESTAMP WITH TIMEZONE datatypes.

Both systems will store the same date-time value, but they will have different timezone settings.

When you look at the value of the TIMESTAMP WITH TIMEZONE, it includes both the date-time and the timezone offset.

This means that even on a single machine you can have databases with different timezones.