Re: Problem with DBTIMEZONE
- From: "Tony Adolph" <tony.adolph.dba@xxxxxxxxx>
- To: sbecker6925@xxxxxxxxx
- Date: Fri, 17 Oct 2008 09:57:46 +1300
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:
