Ever have to work with a DB that stores time in UTC format?
Yes, it's a tad annoying at first. The PL/SQL functions below may be handy when you've got a DB side UTC field.
CREATE OR REPLACE
function convert_utc_date (utc_in in number) return date is
begin
return new_time(to_date('1970-01-01 12:00:00 AM','yyyy-mm-dd hh:mi:ss am')+(utc_in/(60*60*24)),'EST','GMT');
-- ex: select to_char(convert_utc_date(1133776800+(60*60*2)),'yyyy-mm-dd hh:mi:ss')a from dual;
end;
CREATE OR REPLACE
function convert_date_utc (date_in varchar2) return number is
begin
return (new_time(to_date(date_in,'DD-MON-YY HH:MI AM'),'GMT','CDT')- to_date('01-01-1970','mm-dd-yyyy'))*86400;
-- ex: select to_char(convert_date_utc('05-DEC-05 12:00 AM'))a from dual;
end;
/
You may have to adjust for your time zone (ex 1 “60*60*2“)