PL/SQL: Converting UNIX Timezones to Oracle Timestamp

readme.md

###Instructions

Package code below. You will only need to use the unix_to_timezone function:

Usage:

set serveroutput on
begin
 dbms_output.put_line(UTILS_PKG.unix_to_timezone(1422937521000, 'Australia/Sydney'));
end;
`````

Note first parameter is in miliseconds since 01/01/1970...if using seconds, multiply by 1000

UTILS_PKG.pkb

create or replace package body "UTILS_PKG" as 

 -- Splits a string into array delitmited by p_delim and returns the array index at p_index
  function split_idx (p_in_string in varchar2, 
                      p_delim     in varchar2, 
                      p_index     in number)
    return varchar2
  is
    i       number :=0;
    pos     number :=0;
    lv_str  varchar2(10000) := p_in_string;

    type vc2_arr_aat is table of varchar2(32767) index by pls_integer;         
  
    l_strings vc2_arr_aat;
  begin
    -- determine first chuck of string
    pos := instr(lv_str, p_delim, 1, 1);
    -- while there are chunks left, LOOP
    while ( pos != 0) 
    loop
       -- increment counter
       i := i + 1;
       -- create array element for chuck of string
       l_strings(i) := substr(lv_str,1,pos-1);
       -- remove chunk from string
       lv_str := substr(lv_str,pos+1,length(lv_str));
       -- determine next chunk
       pos := instr(lv_str,p_delim,1,1);
       -- no last chunk, add to array
       if pos = 0
       then
          l_strings(i+1) := lv_str;
       end if;
    end loop;
    -- return array
    return trim(l_strings(p_index));
  end split_idx;
  
  -- Returns at unix timestamp
  function unix_to_timezone(p_unix_timestamp  in integer,
                            p_timezone        in varchar2)
      return timestamp
  is
      l_offset          varchar2(10)    := null;
      l_timestamp       timestamp;

      l_offset_seconds  integer;
  begin
      select tz_offset(p_timezone)
      into l_offset
      from dual;

      l_offset_seconds := to_number(substr(split_idx(l_offset, ':', 1), 2,4))*3600 + 
                              to_number(split_idx(l_offset, ':', 2))*60;


      if substr(l_offset, 1,1) = '+'
      then
          select 
          to_timestamp('1970-01-01 00:00:00', 'RRRR-MM-DD HH24:MI:SS') + numtodsinterval( (p_unix_timestamp/1000)+l_offset_seconds, 'SECOND')
          into l_timestamp
          from dual;
      else
          select  
          to_timestamp('1970-01-01 00:00:00', 'RRRR-MM-DD HH24:MI:SS') + numtodsinterval( (p_unix_timestamp/1000)-l_offset_seconds, 'SECOND')
          into l_timestamp
          from dual;
      end if;
    return l_timestamp;
  end unix_to_timezone;
end utils_pkg;
/
show errors;

UTILS_PKG.pks

create or replace package "UTILS_PKG" as 

 -- Splits a string into array delitmited by p_delim and returns the array index at p_index
  function split_idx (p_in_string in varchar2, 
                      p_delim     in varchar2, 
                      p_index     in number)
    return varchar2;

  -- Returns at unix timestamp
  function unix_to_timezone(p_unix_timestamp  in integer,
                            p_timezone        in varchar2)
   return timestamp;
end package;
/
show errors;