How To Convert Unix Timestamp To Date Format In PLSQL

Some times you come into a situation where you are given a date in the Unix Timestamp format, and you want to convert it to Oracle date format to store it in your Oracle table.

A sample of this can be that you are loading data from CA Service Desk (which is SQL Server based – using dates stored as Unix time stamp) into your Oracle database.

In this post I will show you how to convert an Unix Timestamp to Oracle date format, and how to convert Oracle date format to Unix timestamp.

Unix Timestamp

Before we start, let’s look at what a Unix timestamp is.

Unix time, or POSIX time, is a system for describing points in time, defined as the number of seconds elapsed since midnight proleptic Coordinated Universal Time (UTC) of January 1, 1970, not counting leap seconds. It is used widely, not only in Unix-like operating systems, but also in many other computing systems and file formats. It is neither a linear representation of time nor a true representation of UTC (though it is frequently mistaken for both), as it cannot represent standard UTC leap seconds (e.g. December 31, 1998 23:59:60), although otherwise the times it represents are UTC. Unix time may be checked on some Unix systems by typing date +%s on the command line.

~ From Wikipedia

Unix Timestamp to Oracle date format sample

Ok, now that we for sure know what a Unix timestamp is, here is an example of two functions that I use for these purposes: 

DECLARE
    vSysdate VARCHAR2(4000);
    plsiUnixTS PLS_INTEGER;
    dSysdate DATE;
    FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
        /**
        * Converts a UNIX timestamp into an Oracle DATE
        */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_ts PLS_INTEGER := 2145916799; -- 2938-12-31 23:59:59
        min_ts PLS_INTEGER := -2114380800; -- 1903-01-01 00:00:00
        oracle_date DATE;
    BEGIN
        IF unixts > max_ts THEN
            RAISE_APPLICATION_ERROR(
            -20901,
            'UNIX timestamp too large for 32 bit limit'
            );
        ELSIF unixts < min_ts THEN
            RAISE_APPLICATION_ERROR(
            -20901,
            'UNIX timestamp too small for 32 bit limit' );
        ELSE
            oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
        END IF;
    RETURN (oracle_date);
    END unixts_to_date;
    -- #### --------------------------------------------------------------
    FUNCTION date_to_unixts(oracle_date IN DATE) RETURN PLS_INTEGER IS
        /**
        * Converts an Oracle DATE to a UNIX timestamp
        */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_date DATE := TO_DATE('20380101000000','YYYYMMDDHH24MISS');
        min_date DATE := TO_DATE('19030101000000','YYYYMMDDHH24MISS');
        unix_ts PLS_INTEGER;
    BEGIN
        IF oracle_date > max_date THEN
            RAISE_APPLICATION_ERROR( -20902,'Date too large for 32bit UNIX timestamp' );
        ELSIF oracle_date < min_date THEN
            RAISE_APPLICATION_ERROR( -20902,'Date too small for 32bit UNIX timestamp' );
        ELSE
            unix_ts := (oracle_date - unix_epoch) / (1/86400);
        END IF;
        RETURN (unix_ts);
    END date_to_unixts;
BEGIN
    vSysdate := TO_CHAR(SYSDATE, 'YYYYMMDD HH:MI:SS AM');
    dbms_output.put_line('SYSDATE value: ' || vSysdate);
    dbms_output.put_line(' ');
    dbms_output.put_line('First, let us convert a formatted SYSDATE value into a Unix timestamp');
    plsiUnixTS := date_to_unixts(TO_DATE(vSysdate, 'YYYYMMDD HH:MI:SS AM'));
    dbms_output.put_line('' || plsiUnixTS);
    dbms_output.put_line(' ');
    dbms_output.put_line('Next, let us convert the Unix timestamp we got back to an Oracle date to see if they are the same');
    dSysdate := unixts_to_date(plsiUnixTS);
    dbms_output.put_line(TO_CHAR(dSysdate, 'YYYYMMDD HH:MI:SS AM'));
END;
--The output of it is:
SYSDATE value: 20101109 05:59:53 PM
First, let us convert a formatted SYSDATE value into a Unix timestamp
1289325593
Next, let us convert the Unix timestamp we got back to an Oracle date to see if they are the same
20101109 05:59:53 PM

Just for the records: After a lot of searching on the Internet (trying different methods – usually not doing what I wanted them to do), these are the two functions I ended up with (don’t remember where I found them). I did not create the functions, and I may or may not have modified them. I have just gathered them here so that it will be easier for people like me to find them.

If you want to have a general test site to test your conversions, try this page: http://unixtimetool.com/

That is it. I hope it was helpful for you.