Blog

Unix Timestamp describes the point in time as the number of seconds that have elapsed since 00:00:00 (UTC), Thursday, 1 January 1970. The simple function implemented in this post helps you to calculate Unix Timestamp in Oracle Database. It can be useful in many different cases; i.e. integration with Java.

Below is an example of Unix Timestamp. More about it you cam find here.

How to calculate Unix Timestamp in PL/SQL

Oracle Database hasn’t implemented UUID function yet, but you can use the following function:

function unix_timestamp(pi_date date) return number is
    c_base_date constant date := to_date('1970-01-01', 'YYYY-MM-DD');
    c_seconds_in_day constant number := 24 * 60 * 60;
    v_unix_timestamp number;
begin
    v_unix_timestamp := trunc((pi_date - c_base_date) * c_seconds_in_day);
    
    if (v_unix_timestamp < 0 ) then
        raise_application_error(-20000, 'unix_timestamp:: unix_timestamp cannot be nagative');
    end if;
    
    return v_unix_timestamp;
end unix_timestamp;