Re: Problem with DBTIMEZONE

From: Tony Adolph <tony.adolph.dba_at_gmail.com>
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-l
Received on Thu Oct 16 2008 - 15:57:46 CDT

Original text of this message