Notice:

This page has been converted from a dynamic Wordpress article to a static HTML document. As a result, some content may missing or not rendered correctly.

Oracle And Unix Timestamps Revisited ~ Fri, 10 Feb 2012 18:35:11 +0000

In my last post I provided some Oracle PL/SQL functions for dealing with Oracle DATEs and Unix timestamps. After using these functions in an application that is executed via Oracle Application Server I discovered a problem. In short, if the executing environment doesn't supply any time zone information then the part of the functions that determines the local time zone would fail.

The following versions of the functions will work correctly no matter what time zone information is present in the executing environment:

create or replace
function unix_time
  (
    in_tz in varchar2 default 'America/New_York'
  )
  return integer
as
  ut      integer     := 0;
  tz      varchar2(8) := '';
  tz_date timestamp with time zone;
  tz_stmt varchar2(255);
begin
  /**
   * This function is for getting the current number of seconds since
   * 01 January 1970 in UTC, i.e. a Unix timestamp.
   *
   * @author James Sumners
   * @date 01 February 2012
   *
   * @param in_tz Specify the local time zone for converting to DST
   *
   * @return integer
   */

  -- Get the current timezone abbreviation (stupid DST)
  tz_stmt := 'select systimestamp at time zone ''' || in_tz || ''' from dual';
  execute immediate tz_stmt into tz_date;
  select
    extract(timezone_abbr from tz_date)
  into tz
  from dual;

  -- Get the Unix timestamp
  select
    (new_time(sysdate, tz, 'GMT') - to_date('01-JAN-1970', 'DD-MM-YYYY')) * (86400)
  into ut
  from dual;

  return ut;
end unix_time;

create or replace
function unix_time_to_date
  (
    unix_time   in integer,
    to_local_tz in integer default 0,
    local_tz    in varchar2 default 'America/New_York'
  )
  return date
as
  converted_date  date;
  tz              varchar2(8) := '';
  tz_date         timestamp with time zone;
  tz_stmt         varchar2(255);
begin
  /**
   * This function is used to convert a Unix timestamp (UTC) into an Oracle DATE. The converted DATE will
   * be in UTC unless the optional local_tz parameter is provided.
   *
   * @author James Sumners
   * @date 01 February 2012
   *
   * @param unix_time The Unix timestamp to convert
   * @param to_local_tz 0 = UTC, 1 = Local database time zone
   * @param local_tz The name of the local time zone
   *
   * @return date
   */
  select
    to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + numtodsinterval(unix_time, 'SECOND')
  into converted_date
  from dual;

  if to_local_tz <> 0 then
    -- Get the current timezone abbreviation (stupid DST)
    tz_stmt := 'select systimestamp at time zone ''' || local_tz || ''' from dual';
    execute immediate tz_stmt into tz_date;
    select
      extract(timezone_abbr from tz_date)
    into tz
    from dual;

    -- Convert the GMT time to the current local timezone
    converted_date := new_time(converted_date, 'GMT', tz);
  end if;

  return converted_date;
end unix_time_to_date;

create or replace
  function unix_time_from_date
      (
        in_date   in date,
        in_src_tz in varchar2 default 'America/New_York'
      )
    return integer
  as
    ut      integer       := 0;
    tz      varchar2(8)   := '';
    tz_date timestamp with time zone;
    tz_stmt varchar2(255);
  begin
    /**
     * This function is used to convert an Oracle DATE (local timezone) to a Unix timestamp (UTC).
     *
     * @author James Sumners
     * @date 01 February 2012
     *
     * @param in_date An Oracle DATE to convert. It is assumed that this date will be in the local timezone.
     * @param in_src_tz Indicates the time zone of the in_date parameter.
     *
     * @return integer
     */

    -- Get the current timezone abbreviation (stupid DST)
    tz_stmt := 'select systimestamp at time zone ''' || in_src_tz || ''' from dual';
    execute immediate tz_stmt into tz_date;
    select
      extract(timezone_abbr from tz_date)
    into tz
    from dual;

    -- Get the Unix timestamp
    select
      (new_time(in_date, tz, 'GMT') - to_date('01-JAN-1970', 'DD-MM-YYYY')) * (86400)
    into ut
    from dual;

    return ut;
end unix_time_from_date;

Code,  Oracle,  PLSQL,  Technology

Comments