Re: Problem with DBTIMEZONE
Date: Fri, 17 Oct 2008 09:57:46 +1300
Message-ID: <4a38d9060810161357l33e09187u436ce7d2a3d129bd@mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 16 2008 - 15:57:46 CDT