Oracle UTC to Standard Time format and back

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“)
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Print | posted @ Friday, April 14, 2006 3:34 PM

Twitter