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, Daylight Saving Time, And Unix Timestamps ~ Wed, 01 Feb 2012 15:59:59 +0000

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;

Code,  Oracle,  PLSQL,  Technology

Comments