Re: Problem with DBTIMEZONE

maybe I'm answering the wrong question, but from what I can see your number
is a unix timestamp in mSecs where a unix timestamp is seconds since 00:00 1
Jan 1970 GMT

This function will convert this mSecs unix timestamp to a regular date
taking into account the local timezone:

CREATE OR REPLACE FUNCTION msecs_ts_to_date(p_mSecs IN NUMBER) RETURN DATE
  deterministic
  parallel_enable
IS
  local_epoch_date CONSTANT TIMESTAMP WITH TIME ZONE :=
TO_TIMESTAMP_TZ('01/01/1970 12:00:00 Pacific/Auckland', 'DD/MM/YYYY
HH24:MI:SS TZR');
  oracle_date_tz TIMESTAMP WITH TIME ZONE := local_epoch_date +
                                             NUMTODSINTERVAL(p_mSecs / 1000,
'SECOND');
  oracle_date    DATE := TO_DATE(TO_CHAR(oracle_date_tz, 'DD-MON-YYYY
HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS');
BEGIN
  RETURN(oracle_date);
END msecs_ts_to_date;

select msecs_ts_to_date(1221152048851) from dual;

--> 12-Sep-08 4:54:08 a.m.

Just change the local_epoch_date to match your env.  So that'll probably be
01/01/1970 12:00:00 minus 5 or 6 (what ever) hours and then the region (just
in case I wasn't clear)

Cheers
Tony

Other related posts: