Room Full of Mirrors

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;

Notice: the functions in this post are fragile. See Oracle And Unix Timestamps Revisited for a more robust implementation.

If ever there was a combination to make you hate developing web applications against an Oracle database, it’s the one defined in the title of this post. Let’s see:

1. Unix timestamps are used in a lot of web applications because they’re easy to work with
2. Oracle doesn’t have any native functions for working with Unix timestamps
3. You have no way of knowing when a bored government will change the dates that DST starts and ends
4. You shouldn’t include a timezone in a Unix timestamp

After much searching, and piecing together of different solutions to slightly different problems, I have written the following three functions that should cover all of your Unix timestamp handling needs within an Oracle database:

CREATE OR REPLACE
  FUNCTION unix_time
    RETURN INTEGER
  AS
    ut INTEGER     := 0;
    tz VARCHAR2(8) := '';
  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
     *
     * @return integer
     */

    -- Get the current timezone abbreviation (stupid DST)
    SELECT
      extract(timezone_abbr FROM CURRENT_TIMESTAMP)
    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,
      local_tz  IN INTEGER DEFAULT 0
    )
    RETURN DATE
  AS
    converted_date DATE;
    tz VARCHAR2(8) := '';
  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 local_tz 0 = UTC, 1 = Local database 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 local_tz <> 0 THEN
      -- Get the current timezone abbreviation (stupid DST)
      SELECT
        extract(timezone_abbr FROM CURRENT_TIMESTAMP)
      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
    )
    RETURN INTEGER
  AS
    ut INTEGER     := 0;
    tz VARCHAR2(8) := '';
  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.
     *
     * @return integer
     */

    -- Get the local timezone from the passed in date
    SELECT
      extract(timezone_abbr FROM CAST(in_date AS TIMESTAMP
    WITH
      local TIME zone))
    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;